Hi
I'm not familiar with story server but if it allows you to create store=
d
procedures what is the problem with the example I gave?
PROCEDURE run_proc (proc_name IN varchar2, argument1 INnumber, argument=
2 IN
number)
IS
cid number;
string varchar2(1000);
e_severe_error exception;
pragma exception_init( e_severe_error, -4068);
BEGIN
/*execute procedure*/
cid :=3D dbms_sql.open_cursor;
string :=3D 'begin '||proc_name||'('||argument1||','||argument2||=
'); end;';
dbms_sql.parse(cid,string,dbms_sql.native);
number_rows :=3D dbms_sql.execute(cid);
dbms_sql.close_cursor(cid);
EXCEPTION
WHEN e_severe_error THEN
/*re-execute procedure*/
cid :=3D dbms_sql.open_cursor;
string :=3D 'begin '||proc_name||'('||argument1||','||argument2||=
'); end;';
dbms_sql.parse(cid,string,dbms_sql.native);
number_rows :=3D dbms_sql.execute(cid);
dbms_sql.close_cursor(cid);
WHEN OTHERS THEN NULL;
END;
Jack
paquette stephane <stephane_paquette_at_yahoo.com>@fatcity.com on 04/26/20=
00
04:00:04 PM
Please respond to ORACLE-L_at_fatcity.com
Sent by: root_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: Dynamic procedure calling to solve ORA-4068
That is what I want to do but our web site is
developped with Vignette Story Server. We can not do
anonymous pl/sql block in Story Server (If we can
please show me how). So what I'm working on is a
stored proc that can launch dynamically stored proc.
I've a prototype, now I'm trying to catch the out
parameters.
If the call failed with an ora-4068, I'll just
relaunch the procedure.
- Jack van Zanen <nlzanen1_at_ey.nl> a =E9crit:
>
> Hi,
>
>
> If I understand correctly you can use an exception
> handler to trap error message
> 4068 and define an action to take when it happens.
>
> ******************
>
>
> PROCEDURE ins_sysstat
> IS
> e_severe_error exception;
> pragma exception_init( e_severe_error, -4068);
> BEGIN
> execute procedure
> ....
> EXCEPTION
> WHEN e_severe_error THEN
> execute procedure
> ...
> END
>
> ******************
>
>
> Hope this helps
>
>
>
> Jack
>
>
>
>
> paquette stephane
> <stephane_paquette_at_yahoo.com>@fatcity.com on
> 04/26/2000
> 11:29:09 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
>
>
> I'm trying to find a way to solve our ORA-4068
> errors.
> We have persistent connections and the wonderful
> world of internet wants us to change the code in
> production pretty often.
>
> Does someone has done dynamic procedure calling ?
> My idea is to have a generic procedure (the
> launcher)that would receive a procedure and its
> arguments as a in parameter.
> If the procedure call failed with an ORA-4068, the
> launcher would call it again.
>
> Anybody ?
>
>
> =3D=3D=3D=3D=3D
> Stephane Paquette
> DBA Oracle
> stephane_paquette_at_yahoo.com
> spaquette_at_houra.fr
> (33) 01 53 93 06 50
>
> __________________________________________________
> Do You Yahoo!?
> Send online invitations with Yahoo! Invites.
> http://invites.yahoo.com
> --
> Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D
> INET: stephane_paquette_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
>
>
>
>
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> De informatie verzonden met dit E-mail bericht is
> uitsluitend bestemd voor
> de geadresseerde. Gebruik van deze informatie door
> anderen dan de
> geadresseerde is verboden. Openbaarmaking,
> vermenigvuldiging, verspreiding
> en/of verstrekking van deze informatie aan derden is
> niet toegestaan.
> Ernst & Young staat niet in voor de juiste en
> volledige overbrenging van de
> inhoud van een verzonden E-mail, noch voor tijdige
> ontvangst daarvan.
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> The information contained in this communication is
> confidential and may be
> legally privileged. It is intended solely for the
> use of the individual or
> entity to whom it is addressed and others authorised
> to receive it. If you
> are not the intended recipient you are hereby
> notified that any disclosure,
> copying, distribution or taking any action in
> reliance on the contents of
> this information is strictly prohibited and may be
> unlawful. Ernst &
> Young is neither liable for the proper and complete
> transmission of the
> information contained in this communication nor for
> any delay in its
> receipt.
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
>
>
> --
> Author: Jack van Zanen
> INET: nlzanen1_at_ey.nl
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
=3D=3D=3D=3D=3D
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
Do You Yahoo!?
Send online invitations with Yahoo! Invites.
http://invites.yahoo.com
--
Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D
INET: stephane_paquette_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
De informatie verzonden met dit E-mail bericht is uitsluitend bestemd v=
oor
de geadresseerde. Gebruik van deze informatie door anderen dan de
geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreid=
ing
en/of verstrekking van deze informatie aan derden is niet toegestaan.
Ernst & Young staat niet in voor de juiste en volledige overbrenging va=
n de
inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
The information contained in this communication is confidential and may=
be
legally privileged. It is intended solely for the use of the individual=
or
entity to whom it is addressed and others authorised to receive it. If =
you
are not the intended recipient you are hereby notified that any disclos=
ure,
copying, distribution or taking any action in reliance on the contents=
of
this information is strictly prohibited and may be unlawful. Ernst &
Young is neither liable for the proper and complete transmission of th=
e
Received on Thu Apr 27 2000 - 02:42:19 CDT
