1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to pass parameters to a pl/sql function from Oracle Discoverer Desktop?

Discussion in 'Oracle Discoverer' started by Gabo86, Feb 20, 2009.

  1. Gabo86

    Gabo86 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone, i have a question:

    How can i pass parameters to my pl/sql function (which i have registered in the Discoverer Administrator before). I want to use the data that appears in my workbook as parameters to the function.

    Thanks in advance

    Gabriel
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Re: How to pass parameters to a pl/sql function from Oralce Discoverer Desktop?

    hi, you mean that your trying to send the data which was queried by discoverer in your worksheet to a PL/SQL function? Is the number of parameters fixed or dynamic?

    A function can be called that uses multiple parameters (seen when registering the function) but it's for a set number of parameters, not a dynamic number of rows. So passing multiple rows to a function won't work. What is the scenario that you are trying?

    Maybe you should also look into analytical functions. These are SQL functions which can also be used in Discoverer. Discoverer provides you with the syntax of these functions when you create a calculation. You should take a look, sorry i can't comment more without knowing what exactly you are trying to do.
     
  3. Gabo86

    Gabo86 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi sameer, thanks for your reply.. i'm going to clarify my post:
    "you mean that your trying to send the data which was queried by discoverer in your worksheet to a PL/SQL function?"

    The answer is YES, i want to call my pl/sql function for EACH row in my worksheet, using as parameters, the same data of the row (the number of parameters is fixed). Is this possible?

    Thanks in advance
     
  4. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    in that case you can create a dummy calculation in discoverer which passes each column to the function as a parameter.

    Then the function will be called for each row returned by the database as part of the SQL statement. However one catch with this will be that if you have a group sort in the worksheet, the function might be called more than once for each row.