on 06-29-2012 7:47 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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();
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.