Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Retrieving parameters sent into procedure

DivenDiven Member Posts: 3
I have run into an issue with retrieving the parameters sent into a procedure. This proc has around 160 IN params and I have to run every single one if them through a function for auditing purposes. I am trying to clean up my code by getting all of the values for the parameters without having to go through them one by one and grab the value.

hence:
Func(p_param1)
Func(p_param2)
...
Func(p_param160)

If I can get the values when they come in I can use a LOOP and clean up my code dramaticly. I get the parameter list from ALL_ARGUMENTS, so now I just need the values. So my question is this, is there anywhere in the data dictionary that holds these values that I can select from, or somehow get them from metadata?

Comments

  • infidelinfidel Member Posts: 2,900
    : I have run into an issue with retrieving the parameters sent into a procedure. This proc has around 160 IN params and I have to run every single one if them through a function for auditing purposes. I am trying to clean up my code by getting all of the values for the parameters without having to go through them one by one and grab the value.
    :
    : hence:
    : Func(p_param1)
    : Func(p_param2)
    : ...
    : Func(p_param160)
    :
    : If I can get the values when they come in I can use a LOOP and clean up my code dramaticly. I get the parameter list from ALL_ARGUMENTS, so now I just need the values. So my question is this, is there anywhere in the data dictionary that holds these values that I can select from, or somehow get them from metadata?

    Not that I'm aware of.

    You could try something like:

    [code]
    for r_arg in x_args loop
    v_sql := 'select ' || r_arg.argument_name || ' from dual';
    execute immediate v_sql into v_value;
    end loop;
    [/code]

    Something like this might work, I do not know. The idea is that you have some cursor, x_args, that selects from all_arguments. For each argument record in the cursor, do some dynamic SQL to get the value of that argument by name. It depends on whether the dynamic SQL executes in a context that is aware of your procedure arguments. Worth a shot, I suppose.

    If it does work, it probably won't be very efficient because of the dynamic SQL stuff.

    As a sidebar, 160 arguments for one procedure seems like an awful lot. I'm sure there's a better way to do what you're trying to do, but I don't know anything about the project so I can't really comment.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • DivenDiven Member Posts: 3
    : : I have run into an issue with retrieving the parameters sent into a procedure. This proc has around 160 IN params and I have to run every single one if them through a function for auditing purposes. I am trying to clean up my code by getting all of the values for the parameters without having to go through them one by one and grab the value.
    : :
    : : hence:
    : : Func(p_param1)
    : : Func(p_param2)
    : : ...
    : : Func(p_param160)
    : :
    : : If I can get the values when they come in I can use a LOOP and clean up my code dramaticly. I get the parameter list from ALL_ARGUMENTS, so now I just need the values. So my question is this, is there anywhere in the data dictionary that holds these values that I can select from, or somehow get them from metadata?
    :
    : Not that I'm aware of.
    :
    : You could try something like:
    :
    : [code]
    : for r_arg in x_args loop
    : v_sql := 'select ' || r_arg.argument_name || ' from dual';
    : execute immediate v_sql into v_value;
    : end loop;
    : [/code]
    :
    : Something like this might work, I do not know. The idea is that you have some cursor, x_args, that selects from all_arguments. For each argument record in the cursor, do some dynamic SQL to get the value of that argument by name. It depends on whether the dynamic SQL executes in a context that is aware of your procedure arguments. Worth a shot, I suppose.
    :
    : If it does work, it probably won't be very efficient because of the dynamic SQL stuff.
    :
    : As a sidebar, 160 arguments for one procedure seems like an awful lot. I'm sure there's a better way to do what you're trying to do, but I don't know anything about the project so I can't really comment.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    : [code]
    : $ select * from users where clue > 0
    : no rows returned
    : [/code]
    :
    :

    This is what I tried to begin with but selecting p_param from dual will get you nothing but an error. I keep getting told that it's impossible to achieve what I am needing to do. A good friend of mine told me that it should be able to be done using DBMS_SQL, but I haven't been able to see a solution in there either. Thanks for your response and I'll keep digging around to see if I can stumble onto anything.

  • infidelinfidel Member Posts: 2,900
    See if this helps you any...

    [code]
    declare

    function dynamic_arg(i_arg in varchar2) return varchar2 is
    v_sql varchar2(256) := 'select :arg from dual';
    v_return varchar2(256);
    begin
    execute immediate v_sql into v_return using i_arg;
    return v_return;
    end;

    begin

    dbms_output.put_line(dynamic_arg('FOOBAR'));

    end;
    /
    [/code]


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • DivenDiven Member Posts: 3
    : See if this helps you any...
    :
    : [code]
    : declare
    :
    : function dynamic_arg(i_arg in varchar2) return varchar2 is
    : v_sql varchar2(256) := 'select :arg from dual';
    : v_return varchar2(256);
    : begin
    : execute immediate v_sql into v_return using i_arg;
    : return v_return;
    : end;
    :
    : begin
    :
    : dbms_output.put_line(dynamic_arg('FOOBAR'));
    :
    : end;
    : /
    : [/code]
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    : [code]
    : $ select * from users where clue > 0
    : no rows returned
    : [/code]
    :
    :
    When I first took a glance at this I thought you had it but this returns the value of what is returned from all_aruments, not the value of the IN param for that value. What is returned from all_arguments is a list of strings of the parmeter names such as p_param1, p_param2 etc...

    [code]
    CREATE OR REPLACE PACKAGE BODY MY_PACKAGE AS

    FUNCTION DYNAMIC_ARG(i_arg IN VARCHAR2) RETURN VARCHAR2 IS
    v_sql VARCHAR2(256) := 'SELECT :arg FROM dual';
    v_value VARCHAR2(256);
    BEGIN
    EXECUTE IMMEDIATE v_sql INTO v_value USING i_arg;
    RETURN v_value;
    END DYNAMIC_ARG;

    PROCEDURE MY_PROCEDURE (p_param1 VARCHAR2,
    p_param2 NUMBER,
    p_param3 DATE)
    IS
    CURSOR cur_args IS
    SELECT argument_name -- This is a list of strings
    FROM all_arguments
    WHERE owner = 'DIVEN'
    AND object_name = 'MY_PROCEDURE'
    AND package_name = 'MY_PACKAGE';
    BEGIN
    FOR r IN cur_args LOOP
    v_value := DYNAMIC_ARG(r.argument_name);
    v_xml := FUNCTION(p_paramname => SUBSTR(r.argument_name,3),
    p_paramvalue => v_value);
    END LOOP;
    END;
    [/code]

    Lets look at the first loop:
    [code]
    FOR r IN cur_args LOOP
    -- Current value for r.argument_name is 'p_param1'
    v_value := DYNAMIC_ARG(r.argument_name);
    --Returns 'p_param1'
    v_xml := XML_FUNCTION(p_paramname => SUBSTR(r.argument_name,3),
    p_paramvalue => v_value);
    --End up with v_xml := p_param1
    --instead of v_xml := MyNameIsDiven
    END LOOP;
    [/code]

    This returns:
    p_param1
    p_param2
    p_param3

    not the values of those parameters:
    'MyNameIsDiven'
    123456
    01-JAN-2004

    I know I put a huge cluster above and I don't know if it makes sense but thanks for the reply and I'm still beating my head against the wall on this one. If you have any othere ideas, please let me know.

    --Diven
  • infidelinfidel Member Posts: 2,900
    : : See if this helps you any...
    : :
    : : [code]
    : : declare
    : :
    : : function dynamic_arg(i_arg in varchar2) return varchar2 is
    : : v_sql varchar2(256) := 'select :arg from dual';
    : : v_return varchar2(256);
    : : begin
    : : execute immediate v_sql into v_return using i_arg;
    : : return v_return;
    : : end;
    : :
    : : begin
    : :
    : : dbms_output.put_line(dynamic_arg('FOOBAR'));
    : :
    : : end;
    : : /
    : : [/code]
    : :
    : :
    : : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    : :
    : : [code]
    : : $ select * from users where clue > 0
    : : no rows returned
    : : [/code]
    : :
    : :
    : When I first took a glance at this I thought you had it but this returns the value of what is returned from all_aruments, not the value of the IN param for that value. What is returned from all_arguments is a list of strings of the parmeter names such as p_param1, p_param2 etc...
    :
    : [code]
    : CREATE OR REPLACE PACKAGE BODY MY_PACKAGE AS
    :
    : FUNCTION DYNAMIC_ARG(i_arg IN VARCHAR2) RETURN VARCHAR2 IS
    : v_sql VARCHAR2(256) := 'SELECT :arg FROM dual';
    : v_value VARCHAR2(256);
    : BEGIN
    : EXECUTE IMMEDIATE v_sql INTO v_value USING i_arg;
    : RETURN v_value;
    : END DYNAMIC_ARG;
    :
    : PROCEDURE MY_PROCEDURE (p_param1 VARCHAR2,
    : p_param2 NUMBER,
    : p_param3 DATE)
    : IS
    : CURSOR cur_args IS
    : SELECT argument_name -- This is a list of strings
    : FROM all_arguments
    : WHERE owner = 'DIVEN'
    : AND object_name = 'MY_PROCEDURE'
    : AND package_name = 'MY_PACKAGE';
    : BEGIN
    : FOR r IN cur_args LOOP
    : v_value := DYNAMIC_ARG(r.argument_name);
    : v_xml := FUNCTION(p_paramname => SUBSTR(r.argument_name,3),
    : p_paramvalue => v_value);
    : END LOOP;
    : END;
    : [/code]
    :
    : Lets look at the first loop:
    : [code]
    : FOR r IN cur_args LOOP
    : -- Current value for r.argument_name is 'p_param1'
    : v_value := DYNAMIC_ARG(r.argument_name);
    : --Returns 'p_param1'
    : v_xml := XML_FUNCTION(p_paramname => SUBSTR(r.argument_name,3),
    : p_paramvalue => v_value);
    : --End up with v_xml := p_param1
    : --instead of v_xml := MyNameIsDiven
    : END LOOP;
    : [/code]
    :
    : This returns:
    : p_param1
    : p_param2
    : p_param3
    :
    : not the values of those parameters:
    : 'MyNameIsDiven'
    : 123456
    : 01-JAN-2004
    :
    : I know I put a huge cluster above and I don't know if it makes sense but thanks for the reply and I'm still beating my head against the wall on this one. If you have any othere ideas, please let me know.

    Ok, I see where the disconnect is between your problem and my solution. I understand your problem better now, but am pretty sure what you're asking isn't possible. Or at the very least extremely obscure. I'll keep looking, but you may be better off, perhaps, writing a script or program that generates the cookie-cutter code and then just copy-paste it into the procedure.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.