Home » SQL & PL/SQL » SQL & PL/SQL » To Call Procedure from another procedure
To Call Procedure from another procedure [message #284949] Mon, 03 December 2007 00:06 Go to next message
spkj
Messages: 5
Registered: December 2007
Junior Member
hi,

I am facing a problem in calling a one procedure's paramter in another procedure.

problem specification
---------------------

I want to call one procedure's out paramter in another procedure.
Below is the layout of how i am calling the out parameter of procedure A in Procedure B.
but this is giving an error 'invalid identifier'.

Procedure A
( element_id Out
)

Procedure B
(
value1 in
vlaue2 out
)
IS
l_element_id tablename%type;

BEGIN

select value2
into l_val2
where
l_element_id = ProcedureA(element_id)

please suggest me any work arounds on this ASAP.

thanks,
spkj

Re: To Call Procedure from another procedure [message #284952 is a reply to message #284949] Mon, 03 December 2007 00:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

1. Not clear How you are calling procedure A
2. Not given from clause for Select statement
3. l_element_id could be tablename.fieldname%type
(But wondering if l_element_id is fildname why it is defined) ;

If procedure A needs just to return a value make it as Function and try post your full code after that.


Thumbs Up
Rajuvan

[Updated on: Mon, 03 December 2007 00:14]

Report message to a moderator

Re: To Call Procedure from another procedure [message #284955 is a reply to message #284949] Mon, 03 December 2007 00:18 Go to previous messageGo to next message
spkj
Messages: 5
Registered: December 2007
Junior Member
hi,
thanks for the reply.

both procedure A and Procedure B are inside a package.
Procedure A is called at several places in the package already.
i think again writing a function to achieve the same task will be overhead.

query is
Select value2
into l_val2
from tablename
where
l_val1 = tablename.column_name
and
l_element_id = ProcedureA(element_id)

thanks,
seetha
Re: To Call Procedure from another procedure [message #284958 is a reply to message #284949] Mon, 03 December 2007 00:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


1. Still not clear what do u mean by

l_val1 = tablename.column_name


If Both Procedures A and B are there in the same package , then better to make element_id globally accessible to package A and B ( prefer to declare in the package body), so that value will be assigned to element_id inside Procedure A (No output argument is needed) and use the select as

l_element_id =element_id 


Once it is assigned in procedure A.


Thumbs Up
Rajuvan
Re: To Call Procedure from another procedure [message #284964 is a reply to message #284949] Mon, 03 December 2007 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

You can't use procedures in SQL, just functions.

Regards
Michel
Re: To Call Procedure from another procedure [message #284975 is a reply to message #284964] Mon, 03 December 2007 00:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

OP wnats to use

Select value2
into l_val2
from tablename
where 
l_val1 = tablename.column_name
and
l_element_id = ProcedureA(element_id)


inside the package procedure Not in SQL

Thumbs Up
Rajuvan.

[Updated on: Mon, 03 December 2007 01:20]

Report message to a moderator

Re: To Call Procedure from another procedure [message #284977 is a reply to message #284975] Mon, 03 December 2007 00:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot use a procedure in sql.

Please explain how this would lead to a valid clause
Quote:

and l_element_id = ProcedureA(element_id)
since a procedure does not return a value.
Re: To Call Procedure from another procedure [message #284981 is a reply to message #284949] Mon, 03 December 2007 01:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

You cannot use a procedure in sql.



I think some sort of misunderstanding between OP and other memnbers .

Let me clariffy what I understood from OP's description.

1. There is a package
2. There are two procedures inside the package A and B
3. procedure A will be called first (right now with our argument to which one valued will be assigned)
4. In procedure B they want to compare the 'Procedure A output value' with a field in a table using an SQL .

My suggesion was to

1. make a Package variable globally accessible to procedure A and B inside the Package
2. Value will be assigned to package variable inside Procedure A
3. Refer the value for comparison using SQL inside the procedure B.


Thumbs Up
Rajuvan

[Updated on: Mon, 03 December 2007 01:20]

Report message to a moderator

Re: To Call Procedure from another procedure [message #284982 is a reply to message #284981] Mon, 03 December 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is your interpretation, let OP explain his problem.

Regards
Michel
Re: To Call Procedure from another procedure [message #284985 is a reply to message #284981] Mon, 03 December 2007 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The original poster should create a function that can act as a wrapper around his procedure. The function will return the out-parameter from the procedure. No messing around with global package variables etc; this is pure about re-using existing code.

[Edit: (only saw Michel's reply after posting) And this is my interpretation of the problem Smile]

[Updated on: Mon, 03 December 2007 01:26]

Report message to a moderator

Re: To Call Procedure from another procedure [message #284997 is a reply to message #284949] Mon, 03 December 2007 01:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes Michel ,
let OP respond .

I explained because Frank's reply was asking me the explanation of clause
l_element_id = ProcedureA(element_id)

in OP's query.

I just tried to clear the air Smile

@ Frank

I replied the same to OP

Quote:

If procedure A needs just to return a value make it as Function


But OP's reply was

Quote:

Procedure A is called at several places in the package already.
i think again writing a function to achieve the same task will be overhead.


Thumbs Up
Rajuvan.

[Updated on: Mon, 03 December 2007 01:47]

Report message to a moderator

Re: To Call Procedure from another procedure [message #284999 is a reply to message #284997] Mon, 03 December 2007 01:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is exactly my point. The original poster does not want to rewrite his procedure into a function, so I advise him to add a wrapper.
Re: To Call Procedure from another procedure [message #285056 is a reply to message #284999] Mon, 03 December 2007 04:12 Go to previous message
spkj
Messages: 5
Registered: December 2007
Junior Member
hi All,

thanks for ur replies.
i have used global variable method suggested and it works fine.
thanks once agian
Previous Topic: Is there any way to refresh the package initialization section ?
Next Topic: Query - Period by Day?
Goto Forum:
  


Current Time: Fri Dec 02 19:01:26 CST 2016

Total time taken to generate the page: 0.32589 seconds