Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate
execute immediate [message #134089] Tue, 23 August 2005 09:34 Go to next message
Messages: 90
Registered: July 2005
I am using oracle 9.2,

I have a package, where depending on a particular case i get the procedure name from some table.
After which I need to execute that procedure passing certain values. Also there is an out argument of type refcursor.

I am using the following statemnt

sql_str := 'begin ' || l_procName||'('||arg1||','||arg2||','||arg3||','||arg4||',NULL,NULL,NULL,my_refcur); end;';

and then using


when i execute this package ..i get the follwing message and i am disconnected from oracle
ERROR at line 1:
ORA-03113: end-of-file on communication channel

However, when instead of passing it in sql string and doing an execute immediate, if i just call some procedure say


it executes. without disconencting from oracle.

ANy clues?? Could I pass the name of the procedure as an expression?

Thnx for your time,
Re: execute immediate [message #134095 is a reply to message #134089] Tue, 23 August 2005 10:09 Go to previous messageGo to next message
Messages: 723
Registered: March 2005
Senior Member
I quite doubt you need EXECUTE IMMEDIATE for use,
but the correct syntax would be:

SQL> create or replace procedure 
  2  get_me_cursor(
  3   arg1 in varchar2, 
  4   arg2 in number, 
  5   arg3 in out sys_refcursor
  6  )
  7  is
  8  begin
  9   open arg3 for select 1 as a from dual;
 10  end;
 11  /

Procedure created.

SQL> declare
  2   a sys_refcursor;
  3   v number := null;
  4  begin
  5   execute immediate 'begin get_me_cursor(:1,:2,:3); end;' using 'a', v , in out a;
  6   fetch a into v;
  7   dbms_output.put_line(v);
  8   close a;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Re: execute immediate [message #258280 is a reply to message #134095] Fri, 10 August 2007 14:42 Go to previous messageGo to next message
Messages: 1
Registered: August 2007
Location: Federated
Junior Member
The example you showed is essentially the same thing I am doing, but I am getting the same error as the original poster. You say that you doubt that EXECUTE IMMEDIATE is needed for use. Can you suggest another way to select and call a procedure dynamically?
Re: execute immediate [message #258281 is a reply to message #134089] Fri, 10 August 2007 14:50 Go to previous messageGo to next message
Messages: 24958
Registered: January 2009
Location: SoCal
Senior Member
Just because you can build "code" within a PL/SQL procedure, does NOT mean it is good practice to do so.
In fact, I wish Oracle never had created such a monster.
IMO, it make programmers lazy.
If you don't know what procedure to call until during runtime,
I contend you have a flawed design.
Why can't you use IF .. THEN .. ELSE or CASE & have the procedures calls coded in advance?
Re: execute immediate [message #258324 is a reply to message #134089] Sat, 11 August 2007 00:37 Go to previous message
Michel Cadot
Messages: 63912
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
3113 almost always means Oracle bug.
Post you full version (4 decimals), upgrade to the last patchset, search on Metalink, call Oracle support are your options.

Previous Topic: Materialised view and database view
Next Topic: Oracle SQL error
Goto Forum:

Current Time: Mon Oct 24 00:39:44 CDT 2016

Total time taken to generate the page: 0.12319 seconds