Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate Statement with the procedure that contain OUT Mode parameter (Oracle 10g)
Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325531] Fri, 06 June 2008 09:13 Go to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member

Dear All,

I need an example code for Execute Immediate.

specially it conatin procedure - OUT / IN OUT Mode parameters.

I am using Execute Immediate for single commands.

is it possible to include a calling procedure in Execute Immediate statement.

i encountered "SQL Statement is not properly ended" Error - when i use Execute Immediate - which conatin procedure - OUT / IN OUT Mode parameters.

Let me know any example code for this.

Thanks and Regards,

SURESH
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325533 is a reply to message #325531] Fri, 06 June 2008 09:17 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about posting what you have tried so far ?

In the mean time check for the syntax for execute immediate in the following link

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm#LNPLS01317

Regards

Raj
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325551 is a reply to message #325533] Fri, 06 June 2008 09:59 Go to previous messageGo to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member

The below code is copied from the link you given:

In addition, if you need to call a procedure whose name is unknown until runtime, you can pass a parameter identifying the procedure. For example, the following procedure can call another procedure (drop_table) by specifying the procedure name when executed.

CREATE PROCEDURE run_proc (proc_name IN VARCHAR2, table_name IN VARCHAR2) AS BEGIN
EXECUTE IMMEDIATE 'CALL "' || proc_name || '" ( :proc_name )' using table_name;
END;
/


I also to call a dynamic procedure.

I need to call a procedure with 5 IN & 1 OUT & 2 IN OUT Parameters.

How to call the dynamic procedure?
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325556 is a reply to message #325551] Fri, 06 June 2008 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
execute immediate 'begin proc(...); end;' using ...;

Regards
Michel
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325564 is a reply to message #325551] Fri, 06 June 2008 10:20 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
It is hardly ever necessary to use dynamic sql, let alone dynamically call a stored procedure.
Why don't you show us your logic; there might be other (read:better) ways.
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325581 is a reply to message #325564] Fri, 06 June 2008 10:50 Go to previous messageGo to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member

create or replace procedure test_receive(t_var1 in number, t_var2 out number)
as
begin
t_var2:= t_var1*10;
end;
/

create or replace procedure test_send(proc_name varchar2)
as
t_var3 number;
begin
EXECUTE IMMEDIATE 'CALL "' || proc_name || '" (t_var1,:t_var2)' using 5,t_var3;
dbms_output.put_line(t_var3);
end;
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325584 is a reply to message #325581] Fri, 06 June 2008 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
cgmsuresh wrote on Fri, 06 June 2008 08:50
create or replace procedure test_receive(t_var1 in number, t_var2 out number)
as
begin
t_var2:= t_var1*10;
end;
/

create or replace procedure test_send(proc_name varchar2)
as
t_var3 number;
begin
EXECUTE IMMEDIATE 'CALL "' || proc_name || '" (t_var1,:t_var2)' using 5,t_var3;
dbms_output.put_line(t_var3);
end;


This implementation is a GREAT way to create a inefficient & non-scalable application.
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325586 is a reply to message #325581] Fri, 06 June 2008 10:56 Go to previous messageGo to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member


begin
test_send('test_receive');
end;

when i execute the above begin-end, the following error is thrown:


Error starting at line 2 in command:
begin
test_send('test_receive');
end;
Error report:
ORA-01006: bind variable does not exist
ORA-06512: at "TST.TEST_SEND", line 5
ORA-06512: at line 2
01006. 00000 - "bind variable does not exist"
*Cause:
*Action:


Kindly let us know the cause for this problem.

AND ALSO LET ME SUGGEST - HOW TO USE EXECUTE IMMEDIATE WITH EXAMPLE.
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325587 is a reply to message #325581] Fri, 06 June 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create or replace procedure test_receive(t_var1 in number, t_var2 out number)
  2  as
  3  begin
  4  t_var2:= t_var1*10;
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure test_send(proc_name varchar2)
  2  as
  3  t_var3 number;
  4  begin
  5  EXECUTE IMMEDIATE 'CALL "' || proc_name || '" (t_var1,:t_var2)' using 5,t_var3;
  6  dbms_output.put_line(t_var3);
  7  end;
  8  /

Procedure created.

SQL> exec test_send('test_receive')
BEGIN test_send('test_receive'); END;

*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "MICHEL.TEST_SEND", line 5
ORA-06512: at line 1


SQL> create or replace procedure test_send(proc_name varchar2)
  2  as
  3  t_var3 number;
  4  begin
  5  EXECUTE IMMEDIATE 'CALL "' || proc_name || '" (:t_var1,:t_var2)' using 5,t_var3;
  6  dbms_output.put_line(t_var3);
  7  end;
  8  /

Procedure created.

SQL> exec test_send('test_receive')
BEGIN test_send('test_receive'); END;

*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
ORA-06512: at "MICHEL.TEST_SEND", line 5
ORA-06512: at line 1


SQL> exec test_send('TEST_RECEIVE')
BEGIN test_send('TEST_RECEIVE'); END;

*
ERROR at line 1:
ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at "MICHEL.TEST_SEND", line 5
ORA-06512: at line 1

SQL> create or replace procedure test_send(proc_name varchar2)
  2  as
  3  t_var3 number;
  4  begin
  5  EXECUTE IMMEDIATE 'CALL "' || proc_name || '" (:t_var1,:t_var2)' using 5, out t_var3;
  6  dbms_output.put_line(t_var3);
  7  end;
  8  /

Procedure created.

SQL> exec test_send('TEST_RECEIVE')
50

PL/SQL procedure successfully completed.

You could do the same thing yourself.

Regards
Michel
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325594 is a reply to message #325587] Fri, 06 June 2008 11:20 Go to previous messageGo to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member

Thanks Michel.

Your Query helped me a lot.

BUT, WHEN WE ACCESS IN IN PROCEDURE, THE FOLLOWING ERROR IS THROWN.

LET ME HELP TO SOLVE THIS ISSUE.


Error starting at line 1 in command:
begin
test_send('TEST_RECEIVE');
end;
Error report:
ORA-06576: not a valid function or procedure name
ORA-06512: at "TST.TEST_SEND", line 5
ORA-06512: at line 2
06576. 00000 - "not a valid function or procedure name"
*Cause: Could not find a function (if an INTO clause was present) or
a procedure (if the statement did not have an INTO clause) to
call.
*Action: Change the statement to invoke a function or procedure



Thanks and Regards

SURESH

[Updated on: Fri, 06 June 2008 11:25]

Report message to a moderator

Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325598 is a reply to message #325531] Fri, 06 June 2008 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
cgmsuresh,
When you find yourself in a hole the first thing you should do is STOP DIGGING!

Please humor me & explain what problem you are really trying to solve at the 10000 foot level.

IMO, there is NEVER any reason to use EXECUTE IMMEDIATE to invoke PL/SQL procedures.

Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325601 is a reply to message #325594] Fri, 06 June 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I got the same thing with your code, I analyzed it, understood why, and fixed the call. All is in my post.
Do the same thing.

But BEFORE follow Ana's advice and post the real problem you're trying to solve with this function.

Regards
Michel
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325797 is a reply to message #325601] Sun, 08 June 2008 23:21 Go to previous messageGo to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member

Hi

My problem is:

I need to call a dynamic procedure with IN/OUT Parameters.

As Michel said, i can call the test_send in SQL> prompt.

But, if i try to call the test_send procedure in anonymous block, i got an error.

I can't find the fix provided by michel in his post.

Kindly let me know that post.

Thanks and Regards,

SURESH
Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325799 is a reply to message #325531] Sun, 08 June 2008 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting Guidelines at stated in URL above

Please humor me & explain what problem you are really trying to solve at the 10000 foot level.

IMO, there is NEVER any reason to use EXECUTE IMMEDIATE to invoke PL/SQL procedures.

[Updated on: Sun, 08 June 2008 23:27] by Moderator

Report message to a moderator

Re: Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325814 is a reply to message #325797] Mon, 09 June 2008 00:46 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I can't find the fix provided by michel in his post.

And what did you do to try to find the origin of your error?
Copy and paste the whole session from start to error just like I did AND FORMAT IT.

But BEFORE follow Ana's advice and post the real problem you're trying to solve with this function.

Regards
Michel
Previous Topic: PLSQL sorting
Next Topic: Oracle to Excel - Display problem!
Goto Forum:
  


Current Time: Mon Dec 02 07:46:22 CST 2024