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
orach@keithholmes.me.uk

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

"sybrandb" <sybrandb@gmail.com> wrote in message
news:1182872425.438835.230160@n60g2000hse.googlegr oups.com...
On Jun 26, 5:22 pm, "Keith Holmes" <o...@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...@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

"Keith Holmes" <orach@keithholmes.me.uk> wrote in
news:4681394b$0$8729$ed2619ec@ptn-nntp-reader02.plus.net:

>
>
> "sybrandb" <sybrandb@gmail.com> wrote in message
> news:1182872425.438835.230160@n60g2000hse.googlegr oups.com...
> On Jun 26, 5:22 pm, "Keith Holmes" <o...@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...@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.

On Jun 27, 5:56 am, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> "Keith Holmes" <o...@keithholmes.me.uk> wrote innews:4681394b$0$8729$ed2619ec@ptn-nntp-reader02.plus.net:
>
>
>
>
>
>
>
> > "sybrandb" <sybra...@gmail.com> wrote in message
> >news:1182872425.438835.230160@n60g2000hse.googleg roups.com...
> > On Jun 26, 5:22 pm, "Keith Holmes" <o...@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...@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

"A quick scan through the docs would have learned you "

Actually it would have taught him.

On Jun 27, 9:20 am, sybrandb <sybra...@gmail.com> wrote:
> On Jun 27, 5:56 am, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>
>
>
>
>
> > "Keith Holmes" <o...@keithholmes.me.uk> wrote innews:4681394b$0$8729$ed2619ec@ptn-nntp-reader02.plus.net:
>
> > > "sybrandb" <sybra...@gmail.com> wrote in message
> > >news:1182872425.438835.230160@n60g2000hse.googleg roups.com...
> > > On Jun 26, 5:22 pm, "Keith Holmes" <o...@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...@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...b14261/toc.htm
You will find many examples as well.
Thomas

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

I didn't understand the code, but if you really want to use CALL then
go ahead (tested in 10.2.0.1):

SQL> BEGIN
2 EXECUTE IMMEDIATE 'CALL DBMS_OUTPUT.PUT_LINE(''Using
CALL'')';
3 END;
4 /
Using CALL

PL/SQL procedure successfully completed.

I don't see what that buys you though.

Do you really want to ignore all errors? ('WHEN OTHERS THEN debug
statement')

Was the procedure really written a year ago?