Home » SQL & PL/SQL » SQL & PL/SQL » How to List all parameters in a procedure (Oracle 9i)
How to List all parameters in a procedure [message #303474] Fri, 29 February 2008 08:33 Go to next message
shall42
Messages: 8
Registered: October 2006
Junior Member
Example
Procedure myproc(ci_Name_tx,ci_City_tx,ci_Zip_tx) AS
v_sql_tx varchar2(4000);
BEGIN
v_sql_tx:=
'...ci_Name_tx='||ci_Name_tx||'...'
||CHR(10)||
'...ci_City_tx='||ci_City_tx||'...'
||CHR(10)||
'...ci_Zip_tx='||ci_Zip_tx||'...';

END;
Currently I do it like the above.
I have another procedure that has 25 parameters.
I thought there might be an easier way to list all
parameters and their values.

Is there a better way to list these values?

TIA.
Steve
Re: How to List all parameters in a procedure [message #303486 is a reply to message #303474] Fri, 29 February 2008 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
Do you know a way in any language?
Do you know the way to see the content of 25 boxes without opening all of them (precision: you are not Superman and have not X-ray eyes)?

Regards
Michel
Re: How to List all parameters in a procedure [message #303491 is a reply to message #303486] Fri, 29 February 2008 09:48 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You can do it in C if you have varying number of parameters as an input to that function or procedure. But you cannot tell the parameter name but you can tell the argument position.

Having said that I don't think it is possible in oracle but I would love to see.

Regards

Raj
Re: How to List all parameters in a procedure [message #303497 is a reply to message #303491] Fri, 29 February 2008 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is possible is to have an array of parameters that are record name/value then it is easy to loop into the array.

Regards
Michel
Re: How to List all parameters in a procedure [message #305040 is a reply to message #303497] Fri, 07 March 2008 13:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It could be useful for somebody. Some might already knew this still I thought it's worth sharing it.

create or replace procedure test_proc (p_arg1 number)
is
begin
   dbms_output.put_line('proc1');
end;

create or replace procedure test_proc1 (p_arg1 number, p_arg2 varchar2)
is
begin
   dbms_output.put_line('proc2');
end;

SQL> select object_name, argument_name, data_type
  2  from user_arguments
  3  where object_name in ('TEST_PROC','TEST_PROC1')
  4  order by 1;

OBJECT_NAME                    ARGUMENT_NAME                  DATA_TYPE
------------------------------ ------------------------------ ------------------------------
TEST_PROC                      P_ARG1                         NUMBER
TEST_PROC1                     P_ARG1                         NUMBER
TEST_PROC1                     P_ARG2                         VARCHAR2


Regards

Raj
Re: How to List all parameters in a procedure [message #305069 is a reply to message #305040] Sat, 08 March 2008 00:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That's the easy part Smile
The trouble part would be
- private packaged procedures
- logging the value of the parameter.

I tried to create something alike some years ago (9i) and ran into those two problems.
Previous Topic: Run PL SQL code from external file
Next Topic: Order of SQL execution
Goto Forum:
  


Current Time: Sat Dec 03 12:22:51 CST 2016

Total time taken to generate the page: 0.23970 seconds