Execute Immediate Statement with the procedure that contain OUT Mode parameter [message #325531] |
Fri, 06 June 2008 09:13 |
|
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 #325551 is a reply to message #325533] |
Fri, 06 June 2008 09:59 |
|
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 #325586 is a reply to message #325581] |
Fri, 06 June 2008 10:56 |
|
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 |
|
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 |
|
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
|
|
|
|
|
|
|
|