On Jun 27, 9:20 am, sybrandb <sybra..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Sybrand,
I think Keith is on the very beginning of his pl/sql programming path.
He needs guidance, not sarcasm.
Keith,
Before you start any programming attempts, please read:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
You will find many examples as well.
Thomas
Received on Wed Jun 27 2007 - 09:04:59 CDT
