Printable View
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
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
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.
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)