Calling a Database Stored Function

[former_member395102]

Hi,  is there a way to call an oracle stored function in an  (remote-)database directly from a "to Database" Pass  or via JavaScript?  The Signature of the Stored Oracle Function is like this: 
-----------------------------------------
create or replace
FUNCTION MyFunction ( pDatum IN DATE)
RETURN NUMBER ...
----------------------------------------- 
An additional question is how to call oracle stored procedures  with "IN OUT" Parameters from a Pass or a JavaScript. 
We are using IdM 7.2 Stack 4. The Oracle Database is Version 10 
Thanks in advance,  Matthias

[former_member2987 · Accepted solution]

Matthias,

There's a couple of ways to do this....

The easiest way is to select the SQL Updating option in the To Database Pass.  From here you can put in virtually any SQL statement. 

The other way would be to use a To Generic pass that would call some JAVA code that executes your SQL.

Of course whatever statements you pass using either method will be run under the privileges of the MXMC_RT user so make sure it has the rights to access those database objects.

Hope this helps!

Matt

[former_member395102 · Accepted solution]

Matt,

thanks for your reply.
As you mentioned,I tried to call the function in
a to Database Pass- for the returning value

of the function i used a Job variable:

CALL MyFunction('02.07.2012') INTO :ZX_TEST_RESULT
The result is an error from the target database:

SQL Update failed. SQL:CALL MyFunction('02.07.2012') INTO :ZX_TEST_RESULT

java.sql.SQLException: ORA-01745: invalid host/bind variable name


The second attempt seemed to be successfull, but there is
another problem with transactions, because the stored
function makes an update and brings an oracle sql error "-14551":

SELECT UpdateReplizierdatum('02.07.2012') from dual

Another issue is how to handle the return value from the stored function in the pass.


So i think it makes more sense to make a to Generic Pass with a JavaScript/JAVA.
Because I'm not a JavaScript Expert it would be useful to get an Example
for connecting and work with a database via JDBC in a to Generic Pass.

Is there any common example for database connections in this context?

Thanks, Matthias

[former_member395102]

Hi,

thanks for the replies.
I think, i solved the issue. For this i wrote a JavaScript function which uses some JDBC Statements working with the remote database. The JavaScript function has two parameters: 1. the connectstring for the remote-database and 2. a parameter for the stored function. This way allows me to get the returning parameter of the stored function and work with it. This function is called in a "to Generic" Pass.

Best regards, Matthias

function call_myFunction(Par){
  var ConnectString = ""; // connectstring to remote-database
  var myDate = ""; // parameter for the stored function
// Import the needed Java-Packages
  importPackage(java.sql);
 importPackage(java.text);
// load the oracle driver
  java.lang.Class.forName("oracle.jdbc.driver.OracleDriver"); 
// connect to database
  ConnectString = Par.get("DataSource");
  var con = DriverManager.getConnection(ConnectString);
// define the callable SQL statement
  var call = "{ ? = call MyFunction(?) }";
  var cstmt = con.prepareCall(call);
// define returning value, oracle->NUMBER matches to Java->INTEGER
  cstmt.registerOutParameter(1, Types.INTEGER);
// format date and create timestamp
  var sdf = SimpleDateFormat( "dd.MM.yyyy HH:mm:ss" );
  var myDate = Par.get("myParam"); 
  var myDateFormated = sdf.parse(myDate)
  
  var TStamp = Timestamp(myDateFormated.getTime());
// set the IN param for the stored function
  cstmt.setTimestamp(2, TStamp);
// execute the statement
  var SQLResult = cstmt.execute();
// get the returning value of the stored function ...
  var val = cstmt.getInt(1);
// ... and do something...
// close the connection
  
  con.close();
}

[Former Member]

In 7.2 the initial load jobs for provisioning frameworks in the 'Create account attribute' pass

the  global script function sap_core_callStoredProcedurer is used for calling a storeproccedure.

You should ensure your storeproccedure is available and executable for rt user/role.

If you create them for the admin user you will have to set synonym for rt user to use it.

You can check the 5D-create-procs.sql part of Designtime for the database in question

how this is commonly done. Or possibly easier the 7.2 migration scripts on SDN.