Prepared Statement and Callable Statement in OAF


We use Prepared Statement to Execute a Query or a SQL Statement and we use callable statement to call a package, function, procedure

In general, to invoke a stored procedure 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. Bind any variables.
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters.
5. Close the statement.

For Prepared Statement we use directly for any DML operations. To Execute the SELECT Query we have to use the class Result set to get the Data..

if(pageContext.getParameter(EVENT_PARAM).equals(“TEST”))
{
String   valuetobePassed1=repId+””;
String valuetobePassed2 = null;
System.out.println(“Condition called for test”);
CallableStatement callableStatement = txn.createCallableStatement(“declare begin :outVariable := RET_QUERY_REP.RET_QUERY(:2, :3); end;”,OADBTransaction.DEFAULT); //Refer to the Syntax where the String is a procedure
//Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
//Bind any variables.
try
{
callableStatement.registerOutParameter(1, Types.VARCHAR); //First Bind Varible should give us out Parameter
callableStatement.setString(2, valuetobePassed1); //Passing a variable to Second Variable
callableStatement.setString(3, valuetobePassed2); //Passing a variable to Second Variable
callableStatement.execute(); // Execute the statement.
outParamValue = (callableStatement.getString(1)).toString(); //Retrieve the values of any OUT parameters.
System.out.println(“The Out Parameter Obtained from the Procedure is “+outParamValue);
}
catch (SQLException sqle)  //Be cautious that execute() throws an SQL Exception
{
System.out.println(“Exception occured”);
}
//Sql Validation whether Query is Right or wrong
pageContext.writeDiagnostics(this, repId+outParamValue, 4);
System.out.println(“The output value is “+repId+”This “+outParamValue);
PreparedStatement transaction = (PreparedStatement)am.getOADBTransaction().createPreparedStatement(outParamValue,0);
OracleResultSet rs = null;  //Result Set to get the Data from the Query
try
{
rs=(OracleResultSet)transaction.executeQuery();
if(rs.next())
{
pageContext.writeDiagnostics(this, “Column one value”+rs.getString(1)+”Column two value”+rs.getString(2), 4 ); //We can see the Output
System.out.println(“Column one value”+rs.getString(1)+”Column two value”+rs.getString(2)); //We can see the Output
}
else
{
rs.close(); //Close the statement.
}
}
catch(Exception e)
{
pageContext.writeDiagnostics(this, “Exception Has occured”, 4 );
System.out.println(“Exception came Hence SQL query is wrong”);
}
}

 

 

 

The package that returns a Query from the table which is used in callable statement

 

CREATE OR REPLACE PACKAGE RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2
IS
QUERYSTR   VARCHAR2 (10000);
TYPE R_CURSOR IS REF CURSOR;
C_REF      R_CURSOR;
LV_TEMP    VARCHAR2 (1000);
LV_TEST    VARCHAR2 (1000);
LV_COLV    VARCHAR2 (1000);
BEGIN
IF P_VAL IS NULL THEN
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL (WHERE_CLAUSE, ‘1=1’)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ‘1’))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;          –Query without column names
ELSE
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL2 (WHERE_CLAUSE, WHERE_CLAUSE||’ AND ‘||P_VAL, ‘1=1’)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ‘1’))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;
END IF;

–DBMS_OUTPUT.PUT_LINE (‘THE QUERY OUTLINE ——-> ‘ || QUERYSTR);
OPEN C_REF FOR
SELECT   XRCV.REP_COLUMN
FROM   XXCUST_REPORT_V XRV, XXCUST_REPORT_COLUMN_V XRCV
WHERE   XRV.REP_ID = XRCV.REP_ID AND XRCV.REP_ID = P_REP_ID;
LOOP
FETCH C_REF INTO   LV_COLV;
EXIT WHEN C_REF%NOTFOUND;
LV_TEST := LV_TEST || LV_COLV || ‘, ‘;
–DBMS_OUTPUT.PUT_LINE(‘THE COLUMN NAMES ARE ——-> ‘||LV_TEST);
END LOOP;
CLOSE C_REF;
LV_TEMP := ‘SELECT ‘ || RPAD (LV_TEST, LENGTH (LV_TEST) – 2) || QUERYSTR;
DBMS_OUTPUT.PUT_LINE (‘THE FINAL QUERY IS  ARE ——-> ‘ || LV_TEMP);
RETURN LV_TEMP;
END RET_QUERY;
END RET_QUERY_REP;

 

 

About pacesettergraam

A good and realistic person

Posted on April 16, 2014, in OAF, Oracle, oracle apps, SQL/PLSQL and tagged , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: