Stored procedure calling from JDE - yes please

I've been involved in a couple of projects that need the ability to call stored procedures from JD Edwards directly.  It does get messy.  Previously I've written an intermediate DLL which loads the OCI DLL's and eventually calls OCIExecuteStmt, which worked well and was secure.  This also did really nice and granular security [kinda] and transaction processing was a breeze.

Despite this being neat, it required constant maintenance, as you needed to repack the tools release with the intermediate DLL each time.  I also had to store the password for the user that needed to run the stored procedures in the JDE.INI.  This was cool and I wrote some encryption algorithms to that would encrypt and decrypt the passwords that I wanted to use.

I've changed tact, I don't know which method I prefer.  I now created a trigger on a standard JD Edwards table that would fire off and run the SP (with up to 3 parameters) before insert into the table.  I chose before insert, as this gave me transaction control.  It's pretty native that if the SP fails when the trigger calls it, then this will also bomb out the insert and return an error to JDE.  This allows for the transaction processing and error handling I need.

I actually do not really like this solution, an insert that calls a trigger that calls a stored procedure - but we are limited in how we are able to execute statements.

I created table F55TRIGX:

Name        Null?    Type          
----------- -------- --------------
TGMATH01    NOT NULL NUMBER        
TGY55SPROCN          NVARCHAR2(256)
TGY55PARM1           NVARCHAR2(512)
TGY55PARM2           NVARCHAR2(512)
TGY55PARM3           NVARCHAR2(512)

It's a little annoying that JDE needs a PK for a table like this, but hey-  I just soaked up a NN.  So, when you insert into the above, my trigger essentially runs:

execute immediate TGY55SPROCN[(TGY55PARM1,TGY55PARM2,TGY55PARM3)]

Note that everything in [] is optional.

I don't really need to provide much more than the trigger text below to get you working at an oracle site.  The trigger writing process is SO terrible for logging and debugging, so thank me later that you can start with a working example.

create or replace trigger TESTDTA.F55TRIGX_GO

before insert on TESTDTA.F55TRIGX

for each row

declare

  results2 varchar2(256);

  szProcedureName varchar(256);

  szParm1 varchar(512);

  szParm2 varchar(512);

  szParm3 varchar(512);

  szCommand varchar2(2048);

begin

  szParm1:=ltrim(rtrim(:new.TGY55PARM1));

  szParm2:=ltrim(rtrim(:new.TGY55PARM2));

  szParm3:=ltrim(rtrim(:new.TGY55PARM3));

  szProcedureName:=ltrim(rtrim(:new.TGY55SPROCN));

  if length(szProcedureName)>0 then

    -- Call this procedure with the parameters

    if(length(szParm1)>0 and length(szParm2)>0 and length(szParm3)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''',''' || szParm3 || ''')' || '; end;';

    elsif (length(szParm1)>0 and length(szParm2)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''')' || '; end;';

    elsif (length(szParm1)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''')' || '; end;';

    else

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '; end;';

    end if;

    execute immediate szCommand ;

    results2 := 'True';

  end if;

end;

This is a nice way for you to automate tasks or perform some "heavy lifting" in the database.  note that you probably need to prefix the SP with the owner or manage that in a consistent way.