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

"Stewart Houck" <shouck_at_pnm.com> wrote in message news:8m%Z8.9903$i25.517087439_at_newssvr17.news.prodigy.com...
> 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
>
>

I agree entirely with Sybrand. Unless I've misunderstood, you're using the database to go out to an external procedure, which will then connect back to the database to work out its return, then pass that return back to the database calling routine.

IMHO, this is not a sensible way to go about things.

Regards,
Paul Received on Sat Jul 20 2002 - 14:21:15 CDT