OraFAQ Forum: SQL & PL/SQL » To Call Procedure from another procedure

Home » SQL & PL/SQL » SQL & PL/SQL » To Call Procedure from another procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To Call Procedure from another procedure [message #284949]

Mon, 03 December 2007 00:06

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

Report message to a moderator

Re: To Call Procedure from another procedure [message #284952 is a reply to message #284949]

Mon, 03 December 2007 00:10

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

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

Report message to a moderator

Re: To Call Procedure from another procedure [message #284958 is a reply to message #284949]

Mon, 03 December 2007 00:25

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

Report message to a moderator

Re: To Call Procedure from another procedure [message #284964 is a reply to message #284949]

Mon, 03 December 2007 00:32

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: To Call Procedure from another procedure [message #284975 is a reply to message #284964]

Mon, 03 December 2007 00:52

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

Frank
Messages: 7901
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.

Report message to a moderator

Re: To Call Procedure from another procedure [message #284981 is a reply to message #284949]

Mon, 03 December 2007 01:19

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

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is your interpretation, let OP explain his problem.

Regards
Michel

Report message to a moderator

Re: To Call Procedure from another procedure [message #284985 is a reply to message #284981]

Mon, 03 December 2007 01:25

Frank
Messages: 7901
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

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.

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

Frank
Messages: 7901
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.

Report message to a moderator

Re: To Call Procedure from another procedure [message #285056 is a reply to message #284999]

Mon, 03 December 2007 04:12

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

Report message to a moderator

Previous Topic:	Is there any way to refresh the package initialization section ?
Next Topic:	Query - Period by Day?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 07 18:10:35 CDT 2025