Re: PLSQL: execute immediate call procedure

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Wed, 27 Jun 2007 03:56:49 GMT
Message-ID: <5elgi.8476$oh3.6798@newsfe03.phx>

"Keith Holmes" <orach_at_keithholmes.me.uk> wrote in news:4681394b$0$8729$ed2619ec_at_ptn-nntp-reader02.plus.net:

> 
> 
> "sybrandb" <sybrandb_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. Received on Tue Jun 26 2007 - 22:56:49 CDT