DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Listing of Parameter in Procedure/Functions

  1. #1
    Join Date
    Jan 2001
    Posts
    50

    Listing of Parameter in Procedure/Functions

    Is there any catalog View which will list all the data types of variables declared in a stored procedure/Functions. There is a catalog view ALL_ARGUMENTS/USER_ARGUMENTS which lists the IN/OUT/INOUT parameters of a procedure/functions. But I would like to get some catalog table where along with the parameter local declared variables will also be there.

    Regards
    Aniruddha
    Aniruddha Gupta

  2. #2
    Join Date
    May 2005
    Posts
    10
    Only way you can look at local variables declared in a procedure/function/package is to have a look at the source code in user_source or all_source

  3. #3
    Join Date
    Jan 2001
    Posts
    50
    Hi,
    Theres a problem here, see we have a table whicg needs to be populated with the name of procedure the variable name and corresponding data type, we are trying to write a procedure which will be doing after goin thru each and every procedure. As far arguments are concerened its simple we have done that but now its necessary to read and store local variable too. Can anyone provide some idea i understand the user_source can be used but how to use it.
    Aniruddha Gupta

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    This will be a nightmare as you'll basically need to write code to parse the PL/SQL, which can be very complex. Just think how you might parse code with comments, linebreaks, inherited datatypes, etc like this :

    DECLARE
    v_val NUMBER;
    v_valdup CONSTANT /*a date in number format from v_val*/
    v_val%TYPE := 20030101;
    BEGIN
    dbms_output.put_line(v_valdup);
    END;

    or
    DECLARE
    CURSOR c_rec IS SELECT table_name FROM user_tables;
    v_val c_rec%ROWTYPE;
    BEGIN
    DECLARE
    v_val2 v_val.table_name%TYPE;
    BEGIN
    dbms_output.put_line(v_val2);
    END;
    END;

    (and don't even get me started on anything from CREATE TYPE)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width