Re: PLSQL: execute immediate call procedure



"sybrandb" <sybrandb@xxxxxxxxx> wrote in message
news:1182872425.438835.230160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 26, 5:22 pm, "Keith Holmes" <o...@xxxxxxxxxxxxxxxxx> wrote:
I'm trying to call a procedure from within a procedure. All works fine
using syntax of the type:

execute immediate 'begin '||v_procname||'(:1); end;' using v_text;

but would it be more efficient to use syntax of the form:

execute immediate call ......; (except I can't get the syntax correct
for
doing the same thing.

Can someone advise me please?

The code I am using is:

create or replace procedure get_text_p1
(
v_select in varchar2, /* SQL select string */
v_procname in varchar2 /* procedure to be carried out - NOTE this must
be
in UPPER
case if CALL used*/
)
is
--
/**************************************************************************­*****************
Purpose: To retrieve a string on the basis of a SELECT statement
(v_select)
and
carry out process (v_procname)

Uses Objects:

Input example: begin get_text_p1('select bore_name from sobi',
'DO_SOMETHING_P1'); end;

*******************************Steps***************************************­*****************
** Step 1.
** Step 2.
** Step 3.
** Step 4.
** Step 5.
*
* Amendment History
*
* Version No. Changed/ Date Change
* Reviewed by
* notEntered Keith Holmes 26 Jun 2006 First Version
***************************************************************************­*****************/
--
v_return_cur sys_refcursor; /* ref cursor */
v_text clob; /* text retrieved */
--
begin
--
dbms_output.enable (100000);
--
open v_return_cur for v_select;
loop
fetch v_return_cur into v_text;
exit when v_return_cur%notfound;
--
execute immediate 'begin '||v_procname||'(:1); end;' using v_text;
dbms_output.put_line (v_text);
--
end loop;
close v_return_cur;
exception
when others then
dbms_output.put_line ('get_text_p1 '||sqlerrm);
end get_text_p1;
/

create or replace procedure do_something_p1
(
v_text in varchar2
)
is
--
begin
insert into proc_control_log (error) values (v_text);
exception
when others then
dbms_output.put_line ('do_something_p1 '||sqlerrm);
end do_something_p1;

Many thanks

Keith Holmes
o...@xxxxxxxxxxxxxxxxx

A quick scan through the docs would have learned you
a) CALL is supported in triggers and to call *external* procedures
only
b) apparently you are trying to apply for a contribution to Tom Kyte's
special webcast 'Worst practices in Oracle' as this (cr)app will be
utterly unscalable. Basically you seem to be trying to reengineer PL/
SQL to work like SQL*Plus.
This is not going to work, EVER.

--
Sybrand Bakker
Senior Oracle DBA

Thank you for your helpful comments!

You could usefully have suggested a better approach.

Keith Holmes


.

Relevant Pages

  • Re: PLSQL: execute immediate call procedure
    ... but would it be more efficient to use syntax of the form: ... notEntered Keith Holmes 26 Jun 2006 First Version ... Senior Oracle DBA ... is how to invoke the PL/SQL procedure V_PROCEDURE from SQL*Plus ...
    (comp.databases.oracle.misc)
  • Re: PLSQL: execute immediate call procedure
    ... but would it be more efficient to use syntax of the form: ... end loop; ... Senior Oracle DBA ...
    (comp.databases.oracle.misc)
  • PLSQL: execute immediate call procedure
    ... but would it be more efficient to use syntax of the form: ... notEntered Keith Holmes 26 Jun 2006 First Version ... end loop; ...
    (comp.databases.oracle.misc)
  • TIP #329: Try/Catch/Finally syntax
    ... exception handling mechanism. ... The *try...catch* syntax presented here is not intended to replace ... TCL_ERROR then each *catch* handler is considered in order until one ...
    (comp.lang.tcl)
  • Re: Ok.. live and learn.
    ... I wanted to associate information with every syntax ... and extending the Exception class let me do that easily. ... Two dwords seems about right for an OS feature: ... Two dwords would be enough for a pointer ...
    (comp.lang.pascal.delphi.misc)