Re: External Procedures: Embedded SQL and OCI callbacks both possible?

The requirement is for a stored procedure which returns the same mailing address as is sent out on a customer's bill. The mailing address on a customers bill is retrieved by a pretty complicated Pro*C routine. If at all possible we would like to avoid duplicating this code, so I am investigating the possibility of calling the existing code via an external procedure. Thus the need to call embedded SQL from the external procedure. (The external procedure will actually call the existing address procedure which uses embedded SQL).

The need for the OCI callback stems from the desire to pass an object as a parameter to the external procedure. This is desired because otherwise I would have to pass over 90 parameters to the external routine. This is new territory for me, but all the examples I have seen show VARCHAR2s in the PL/SQL passed as OCIString pointers into the C routine. The OCIString variables are then set with OCI routines which require(?) the context returned by OCIExtProcGetEnv.

That said, they are only needs in the sense that I need to explore the feasibility of this approach. I truly do appreciate your learned opinion.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:j4tgju0r1dmu3n2fq28puqpseh5uu75g3i_at_4ax.com...
> On Fri, 19 Jul 2002 18:28:44 GMT, "Stewart Houck" <shouck_at_pnm.com>
> wrote:
>
> >Oracle 8i Enterprise Edition 8.1.7.3
> >
> >I have the need in an external procedure (Pro*C) to both call a procedure
> >containing embedded SQL and to also use an OCI callback. I can
successfully
> >do one or the other, but not both. It seems that the problem has to do
with
> >the contexts they both need (though I'm not sure). The following
statements
> >are at the beginning of the routine to set the contexts:
> >
> > status = OCIExtProcGetEnv(ctx, &envh, &svch, &errh);
> > EXEC SQL register CONNECT USING :ctx;
> >
> >With the above statements, I receive an ORA-1012 (not logged on) error
when
> >the SQL is executed. If I reverse the order, I get an ORA-03114 (not
> >connected to oracle) and then ORA-03113 (end-of-file on communication
> >channel), and I seem to lose the connection to the extproc.
> >
> >Is it possible to use both Embedded SQL and OCI callbacks in one call to
an
> >external procedure? If so, how is it done.
> >
> >Thanks
> >
>
> Please explain why you have this 'need'
> I can not imagine why anyone already running sql or pl/sql would call
> an external procedure to connect to the database again.
> That is definitely a waste of resources and looks like a not well
> thought out architecture.
>
> Regards
>
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Fri Jul 19 2002 - 16:17:56 CDT