Can Stored Procedure Transaction Groups use Oracle Package Functions?

Ignition
1

This is just a question to see if something is possible. I’ve been struggling with it for a few hours. Maybe you database guys can point me in the right direction.

My customer has an Oracle database with a package of functions that I am allowed to use to send data to their MES system. There are two things slightly out of the ordinary:

  1. the Oracle entity I need to use is a FUNCTION and not a Stored Procedure
  2. the Oracle entity is in a Package such that I have to use MesPkg.FunctionName to call it

If I use raw system.db calls in the Script Console like:

call = system.db.createSProcCall("MesPkg.FunctionName", "oracleDB")

call.registerReturnParam(system.db.INTEGER)

call.registerInParam(1, system.db.VARCHAR, "param1")
call.registerInParam(2, system.db.VARCHAR, "param2")
call.registerOutParam(3, system.db.VARCHAR)
  
system.db.execSProcCall(call)

It works great … no problems at all. So I can use it in scripting all I want. However I need to be able to store and forward these transaction calls.

If I put the MesPkg.FunctionName name in the Procedure Name box in a Stored Procedure Transaction Group and then trigger the group to execute, I get an error that says:

Error during group execution. ORA-06550: line 1, column 7:
PLS-00221:
’FunctionName’ is not a procedure or is undefined

… which is a true statement, in that FunctionName is really a Function and not a Procedure.

So, is the Transaction Group really that picky … functions and stored procedures are very similar and my customer uses them almost exclusively for their transactional work.

Or could it be that the Transaction Group is ignoring my package name prefix “MesPkg” since the error message omits the Package name when it says its undefined?

I appreciate any help anyone may be able to give!

2

Ok, did some playing around on the Oracle side and answered my own question. Will post here in case anyone else is interested.

Took the function code in the package and made it an independent function. Transaction Group still couldn’t find it.

Took the function code, removed all the Return statements and made it an independent “procedure”. Transaction Group found it and it worked fine.

Took the new “procedure” and placed it into the original package I was working with. Transaction Group found it and it worked fine.

So I learned that Transaction Groups can’t call an Oracle Function, even though the only difference I can see is that a Function has to have a “Return” value. Otherwise both Procedures and Functions both have input and output parameters and seem to operate the same.

Also, you CAN call an Oracle Function using the system.db.createSProcCall, which is a decent workaround unless you want to use the built-in store and forward functionality, of which I guess I can roll my own solution for that.

Thanks!

2 Likes