You're close. What I'm working on needs dynamic number
of in and out parameters. Also, Story Server does not
accept the out parameters to be in an array.
- Jack van Zanen <nlzanen1_at_ey.nl> a icrit:
>
> Hi
>
>
> I'm not familiar with story server but if it allows
> you to create stored
> procedures what is the problem with the example I
> gave?
>
> **************************
> PROCEDURE run_proc (proc_name IN varchar2, argument1
> INnumber, argument2 IN
> number)
> IS
> cid number;
> string varchar2(1000);
> e_severe_error exception;
> pragma exception_init( e_severe_error, -4068);
> BEGIN
> /*execute procedure*/
> cid := dbms_sql.open_cursor;
> string := 'begin
> '||proc_name||'('||argument1||','||argument2||');
> end;';
> dbms_sql.parse(cid,string,dbms_sql.native);
> number_rows := dbms_sql.execute(cid);
> dbms_sql.close_cursor(cid);
> EXCEPTION
> WHEN e_severe_error THEN
> /*re-execute procedure*/
> cid := dbms_sql.open_cursor;
> string := 'begin
> '||proc_name||'('||argument1||','||argument2||');
> end;';
> dbms_sql.parse(cid,string,dbms_sql.native);
> number_rows := 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/2000
> 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:
>
>
> 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 icrit:
> >
> > 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 ?
> >
> >
> > =====
> > 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: =?iso-8859-1?q?paquette=20stephane?=
> > 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).
> >
> >
> >
> >
> >
> >
>
> > 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.
> >
>
> > The information contained in this communication is
> > confidential and may be
> > legally privileged. It is intended solely for the
>
=== message truncated ===
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
Received on Thu Apr 27 2000 - 07:28:03 CDT
