Home » SQL & PL/SQL » SQL & PL/SQL » How is Oracle passing arguments to methods?
icon5.gif  How is Oracle passing arguments to methods? [message #597857] Tue, 08 October 2013 16:51 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hi,

Have you any idea how I could pass list of arguments passed to a pl/sql block in a generic way without knowing their names in advance?
I mean that in machine languages you could just get the stack dumped. In PERL you would access @_.
In Oracle I could use AWR to extract values bound to a query using system views but what about PL/SQL code?
Let say that I have abstract method:
create package p is
procedure p(...) is
begin
<<here I'd like to dump all the arguments passed to my procedure without knowing what "..." is>>
end p;
end p;

I was trying t o search for this using google... but no luck.
Any idea?
Re: How is Oracle passing arguments to methods? [message #597861 is a reply to message #597857] Tue, 08 October 2013 18:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not possible.

SY.
Re: How is Oracle passing arguments to methods? [message #597863 is a reply to message #597857] Tue, 08 October 2013 22:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Have a look at

Describing Schema Metadata - OCI_ATTR_LIST_ARGUMENTS
icon3.gif  Re: How is Oracle passing arguments to methods? [message #597870 is a reply to message #597857] Wed, 09 October 2013 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can query user_arguments view to get the names and types of your parameters but you cannot access the parameter values by position.

icon13.gif  Re: How is Oracle passing arguments to methods? [message #597871 is a reply to message #597863] Wed, 09 October 2013 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is OCI_ATTR_LIST_ARGUMENTS a PL/SQL procedure or can be called from PL/SQL?

[Updated on: Wed, 09 October 2013 01:11]

Report message to a moderator

Re: How is Oracle passing arguments to methods? [message #597898 is a reply to message #597871] Wed, 09 October 2013 03:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Wed, 09 October 2013 11:41

Is OCI_ATTR_LIST_ARGUMENTS a PL/SQL procedure or can be called from PL/SQL?



Of course not. It is OCI. But is a good info.
If you think it is completely irrelevant, you can please remove the post.
icon4.gif  Re: How is Oracle passing arguments to methods? [message #597899 is a reply to message #597898] Wed, 09 October 2013 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
you can please remove the post.


And then you will create a topic complaining your posts have been deleted.

Re: How is Oracle passing arguments to methods? [message #597912 is a reply to message #597870] Wed, 09 October 2013 04:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Wed, 09 October 2013 11:40

You can query user_arguments view to get the names and types of your parameters but you cannot access the parameter values by position.



I could not understand that "you cannot access the parameter values by position." Sorry if I sound dumb, but just want to clarify.

SQL> CREATE OR REPLACE PROCEDURE P(A NUMBER,
  2                                B NUMBER,
  3                                C NUMBER,
  4                                D NUMBER,
  5                                E NUMBER) AS
  6  BEGIN
  7     NULL;
  8  END;
  9  /
 
Procedure created
 
SQL> sho err
No errors for PROCEDURE P 

SQL> select OBJECT_NAME, ARGUMENT_NAME, POSITION, SEQUENCE from USER_ARGUMENTS;
 
OBJECT_NAME                    ARGUMENT_NAME                    POSITION   SEQUENCE
------------------------------ ------------------------------ ---------- ----------
P                              E                                       5          5
P                              D                                       4          4
P                              C                                       3          3
P                              B                                       2          2
P                              A                                       1          1


We have the position and sequence to refer. Can't it be used?

Regards,
Lalit
Re: How is Oracle passing arguments to methods? [message #597914 is a reply to message #597912] Wed, 09 October 2013 04:57 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
you can get the parameter names by position, you can't get the parameter values by position.
icon11.gif  Re: How is Oracle passing arguments to methods? [message #597915 is a reply to message #597912] Wed, 09 October 2013 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE PROCEDURE P(A NUMBER,
  2                                B NUMBER,
  3                                C NUMBER,
  4                                D NUMBER,
  5                                E NUMBER) AS
  6  BEGIN
  7    for rec in (
  8     select ARGUMENT_NAME, POSITION from USER_ARGUMENTS 
  9     where OBJECT_NAME = 'P' order by POSITION
 10    ) loop
 11     dbms_output.put_line('Argument '||rec.position||' is named '||rec.argument_name);
 12     dbms_output.put_line('How to display value of argument '||reC.position||' there without having its name?');
 13    end loop;
 14  END;
 15  /

Procedure created.

SQL> exec p(1,2,3,4,5)
Argument 1 is named A
How to display value of argument 1 there without having its name?
Argument 2 is named B
How to display value of argument 2 there without having its name?
Argument 3 is named C
How to display value of argument 3 there without having its name?
Argument 4 is named D
How to display value of argument 4 there without having its name?
Argument 5 is named E
How to display value of argument 5 there without having its name?

PL/SQL procedure successfully completed.

[Updated on: Wed, 09 October 2013 04:58]

Report message to a moderator

Re: How is Oracle passing arguments to methods? [message #597921 is a reply to message #597915] Wed, 09 October 2013 05:21 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
OK... Thanks Michel & Cookiemonster for the clarification. I wasn't just dumb, I was too blind to not see the difference in parameter value and name.
Previous Topic: how can I do it without error,keep the rowid
Next Topic: Copy Bulk data in txt file
Goto Forum:
  


Current Time: Wed Apr 24 03:59:13 CDT 2024