Re: PLSQL: execute immediate call procedure

On Jun 26, 5:22 pm, "Keith Holmes" <o..._at_keithholmes.me.uk> 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..._at_keithholmes.me.uk

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