Re: PLSQL: execute immediate call procedure

On Jun 27, 5:56 am, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> "Keith Holmes" <o..._at_keithholmes.me.uk> wrote innews:4681394b$0$8729$ed2619ec_at_ptn-nntp-reader02.plus.net:
>
>
>
>
>
>
>
> > "sybrandb" <sybra..._at_gmail.com> wrote in message
> >news:1182872425.438835.230160_at_n60g2000hse.googlegroups.com...
> > 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
>
> > Thank you for your helpful comments!
>
> > You could usefully have suggested a better approach.
>
> > Keith Holmes
>
> BEGIN V_PROCEDURE; END;
>
> is how to invoke the PL/SQL procedure V_PROCEDURE from SQL*Plus
>
> to do the same from within PL/SQL you only need to:
>
> V_PROCEDURE;
>
> from within an existing code block.- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

You didn't read his post. He wants EVERYTHING to be FULLY dynamic. Basically he is reinventing sql*plus by using PL/SQL.

--
Sybrand Bakker
Senior Oracle DBA