Calling Database Procedures and Function From OAF

Calling Database Procedures and Function from OAF
According to Oracle Standards whenever it is possible we should use View Objects for
Database operation but in some situations we have to call PL/SQL Procedures and
Functions using JDBC.
In general, to invoke a stored procedure/Function from within an entity object or an

application module, you need to:
1. Create a JDBC CallableStatement with the PL/SQL block containing the stored
procedure invocation
2. Register OUT Parameter if any
3. Bind any variables.
4. Execute the statement.
5. Retrieve the values of any OUT parameters if any
6. Close the statement
Create Callable Statement:
To create callable statement we should import java.sql.CallableStatement package
If we are using PL/SQL Procedure, then String that we will pass to callable statement
will be in following form:
“BEGIN Package_name.procedure_name(:1,:2) END;” where :1 and :2 are Parameters
which may be either IN or OUT Parameters.
Ex:
1.String sqlString = "begin test_package.prc_Test(:1, :2);end;"
If we are using PL/SQL Function then it should be in following form:
“BEGIN :1 := Package_name.function_name(:2) END;” where :1 will be the contain the

results returned by database function and will be treated as OUT parameter and :2 is IN
Parameter.
Example:
1.“BEGIN :1= Test_Package.func_test(:2) END;”
where :1 will be considered as OUT parameter and needs to be registered while :2 is IN

Parameter.
Following piece of code creates a callable atement:

1.OADBTransaction txn = getDBTransaction();
2.String sqlString = "begin dbms_application_info.set_module(:1, :2);end;"
3.CallableStatement cs = txn.createCallableStatement(sqlString);
Register OUT Parameters:
Before registering OUT parameters we need to import import java.sql.Types; package

And after that we need to use following statement to register OUT parameter:
1.((OracleCallableStatement)cs.registerOutParameter(1, Types.VARCHAR, 0,
2000);
Types.VARCHAR can be replaced by other datatypes as per your requirement
Bind IN Parameters:
To bind in parameters we can use following statement: cs.setString(1, value); where
setString can be replaced by SetNumber/SetDate as per your requirement.
Execute Callable Statement:

To execute following statement: we can use execute method of callable statement.
Retriving OUT Parameter Value:

we can get the value of OUT parameter using getString/getNumber/getDate methods of
callable statement.
Example:
1.messageBuffer = cs.getString(1);
Close Statement:
To close the statement we can use close()method of callable statement
Example 1.
Callable Statement for Procedure with IN and OUT parameter.
01.import java.sql.CallableStatement;
02.import java.sql.SQLException;
03.import java.sql.Types;
04....
05.String outParamValue;
07.CallableStatement cs =
08.txn.createCallableStatement("begin Test_Package.test_prc(:1, :2);
09.end;");
10.try
11.{
2000);
13.cs.setString(1, “Reetesh”);
14.cs.execute();
15.outParamValue = cs.getString(1);
16.cs.close();
17.}
18.catch (SQLException sqle)
19.{
20.cs.close();
21.}
Example2: Calling PL/SQL Function
01.import java.sql.CallableStatement;
02.import java.sql.SQLException;
03.import java.sql.Types;
04....
05.String outParamValue;
07.CallableStatement cs =
08.txn.createCallableStatement("begin :1 := Test_Package.test_func(:2);
09.end;");
10.try
11.{
2000);
13.cs.setString(1,”Reetesh”);
14.cs.execute();
15.outParamValue = cs.getString(1);
16.cs.close();
17.}
18.catch (SQLException sqle)
19.{
20.cs.close();
21.}

How to Call OAF Page from Workflow Email

Notification
There was a requirement with one of my client to open an OAF page from workflow
email notification. In R12 below error will occur if you try to open an OAF Page URL
link directly.
You are trying to access a page that is no longer active.
- The referring page may have come from a previous session. Please select Home
to Proceed.
Note: When you call a standard page this error won't appear. This happens only when
you try to open a custom page.
Below are the steps to resolve the error:
1).Open the custom page in Jdev and select the PageLayoutRN. In the property
inspector set the Security Mode to selfSecured.
2).Set the rendered property to ${oa.FunctionSecurity.}
Eg: ${oa.FunctionSecurity.XXCUSTPG}
Note: Your user id should have access to the custom page function.
3).In your workflow procedure generate the OA page URL with the below function and
set the workflow attribute with the generated URL.
lc_url:= FND_RUN_FUNCTION.get_run_function_url(p_function_id => 53637,

p_resp_appl_id => null,
p_resp_id => null,
p_security_group_id => 0,
p_parameters =>'PARAM1='||p_lease_id,--'&'||'LeaseId=784',
p_override_agent => null,
p_org_id => null,
p_lang_code => null,
p_encryptParameters => false);
Now you should be able to open the OAF page from workflow email notification.

Calling Database Procedures and Function From OAF

Uploaded by

Copyright:

Available Formats

Calling Database Procedures and Function From OAF

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Calling Database Procedures and Function From OAF

Uploaded by

Copyright:

Available Formats

Calling Database Procedures and Function from OAF

In general, to invoke a stored procedure/Function from within an entity object or an

Create Callable Statement:

To create callable statement we should import java.sql.CallableStatement package

1.String sqlString = "begin test_package.prc_Test(:1, :2);end;"

If we are using PL/SQL Function then it should be in following form:

“BEGIN :1 := Package_name.function_name(:2) END;” where :1 will be the contain the

where :1 will be considered as OUT parameter and needs to be registered while :2 is IN

Following piece of code creates a callable atement:

Register OUT Parameters:

Before registering OUT parameters we need to import import java.sql.Types; package

Types.VARCHAR can be replaced by other datatypes as per your requirement

Execute Callable Statement:

Retriving OUT Parameter Value:

Callable Statement for Procedure with IN and OUT parameter.

Example2: Calling PL/SQL Function

How to Call OAF Page from Workflow Email

Below are the steps to resolve the error:

2).Set the rendered property to ${oa.FunctionSecurity.}

lc_url:= FND_RUN_FUNCTION.get_run_function_url(p_function_id => 53637,

You might also like