Re: PLSQL: execute immediate call procedure

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

.

Relevant Pages

  • Preview chapter about the structured syntax definition of Seed7
    ... I am writing a chapter about the structured syntax definition ... will be used as base to explain the S7SSD. ... that a new statement, the 'loop' statement, should be defined. ... Priority and assoziativity ...
    (comp.compilers)
  • Re: Am I the only one who would love these extentions? - Python 3.0 proposals (long)
    ... Why is it nice enough to make it be a syntax addition, ... to point an image viewer/editor at a chunk of Python code. ... You argue consistancy to other keywords. ... Why is this new loop construct of yours useful enough ...
    (comp.lang.python)
  • Re: How about this syntactic candy?
    ... I can think of that's worse than adding a rarely needed, rarely beneficial syntax is adding one that actually allows one to lie to the compiler in a way that _breaks_ the code. ... But more significantly, IMHO, the reason that syntax has survived so many generations of this family of languages is that it's so commonly useful. ... simple for loop. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: PEP-315 ("do" loop)
    ... But it's syntax seems like an acquired taste to me. ... Would be defined to work exactly like a while loop except the test is not ... and my news client breaks the formatting. ... "improving" the basic while loop, none of which seem to be a natural ...
    (comp.lang.python)
  • Re: spurious wakeup
    ... It can be wrapped in a macro if you insist on ... > You must remember to repeat this loop at all places of the wait call ... checking of all values of variables which influence the predicate. ... > Templates are the safe syntax for a lot of macros. ...
    (comp.lang.cpp)