Oracle procedures and calling them from a BSFN

We are doing some pretty edgy stuff at the moment.  We are getting JD Edwards to call oracle procedures via OCI calls in kernels.  This is some nice stuff to have complete commitment control over function and procedure calls from within E1.

We are working on all of the syntax and semantic issues at the moment.

At the end of the day we are going to have a generic BSFN that is going to take 3 parameters:

procedurename

parameters in (pipe delimited)

parameters out (pipe delimited)

Therefore, the BSFN can be nice and generic and send a bunch of parameters to the stored proc, it’ll extract them all with it’s beautiful PL/SQL logic and hand any returns back when it is done – nice!

Note that this will be timed out if there is no response to the BSFN in 60 seconds, which will be no good for interactive apps

create or replace Procedure TESTDTA.ProcTest
(procName IN varchar2, environ IN varchar2, paramList IN varchar2, resultsList OUT varchar2)
AS
  nextID number;
BEGIN
DBMS_OUTPUT.PUT_LINE('In Procedure');
SELECT testdta.ProcedureSeq.nextval INTO NextID FROM DUAL ;
insert into TESTDTA.ProcedureTest values (ProcTest.procName, ProcTest.environ, ProcTest.paramList, CAST(nextID as varchar(20) ) );
END;

Run it with the following:

declare
  ReturnVal  varchar(20);
begin
  TESTDTA.ProcTest('This is procedure name', 'DV910', 'This|Is|999|params', ReturnVal);
  DBMS_OUTPUT.ENABLE(1000000);
  dbms_output.put_line(ReturnVal) ;
END ;

Check that things get inserted. 

select * from testdta.ProcedureTEst;

Nice, all is working. Note that the procedure needed to be created in the same schema as the sequence and the table.  If I did not create the procedure in the same owner / schema – it was unable to find the sequence or the table even when I explicitly referenced them.

So, this is perfect.  I have a working procedure in my TESTDTA owner – now I have to code the OCI routines to call the procedure.  There are two ways of doing this:  OCI (nice, neat secure) or system out a call to a vbscript (not nice, neat or secure – but quick).  I’m going with OCI.

OCI is going to be slightly painful, for a number of reasons.  I need to use 32bit OCI libraries and I also need to do “loadlib” calls and function pointers – as the oci libs are not going to be in my lib path when compiling BSFN’s on the server (or the client).  This is somewhat painful.  I’ll get it all working standalone and then build my function pointers into a BSFN.

This might be a longer post than usual, so I’ll do it in parts.