OAF : Passing a Table Type Object to Oracle Stored Procedure & Retrieving Error Stack

Folks

Most of us have hard time in playing with Oracle's Table type object and Java's Array Descriptor.So i thought of posting it so that it could help us all.

For this Exercise i have taken two Standard Table Type objects named

JTF_NUMBER_TABLE       // Table type of Number Type     
JTF_VARCHAR2_TABLE_100  //Table type of Varchar2(100) Type

These two Objects are input to the Stored Procedure XX_PassTableType_prc in Package XX_PassTableType.

Table script that was used for this exercise is as follows

CREATE TABLE xx_test(
invoice_id VARCHAR(2000),
amount NUMBER);

Below is the code for Package.

CREATE OR REPLACE PACKAGE xx_passtabletype
AS
PROCEDURE xx_passtabletype_prc (
xx_number_table              jtf_number_table,
xx_varchar2_table   IN OUT   jtf_varchar2_table_100
);
END xx_passtabletype;

CREATE OR REPLACE PACKAGE BODY xx_passtabletype
AS
PROCEDURE xx_passtabletype_prc (
xx_number_table              jtf_number_table,
xx_varchar2_table   IN OUT   jtf_varchar2_table_100
)
AS
BEGIN
DBMS_OUTPUT.put_line ('I am here');

FOR i IN xx_varchar2_table.FIRST .. xx_varchar2_table.LAST
LOOP
INSERT INTO xx_test
(invoice_id, amount
)
VALUES (xx_varchar2_table (i), xx_number_table (i)
);

COMMIT;
END LOOP;

xx_varchar2_table :=
jtf_varchar2_table_100 ('Error while inserting record');
END xx_passtabletype_prc;
END xx_passtabletype;

Now coming to OAF Part.For this, we have create a Advanced table Region with two
columns & Submit Button. Now, on the click of button in processFormRequest() of Controller we are passing the Table View Object Data to the Stored Procedure with the help of Array Descriptor.

Controller Code:

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);

OAApplicationModule am = pageContext.getApplicationModule(webBean);

if (pageContext.getParameter("go") != null)
{
String[] as = null;
Number[] vNumber = null;

Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
String mCreateSearchRequestStatement = null;
OAViewObject vo = (OAViewObject)am.findViewObject("MainVO1");
int j = vo.getFetchedRowCount();
try
{
System.out.println("abouce try");

vo.reset();
if (vo.getFetchedRowCount() > 0)
{
System.out.println(String.valueOf("Fetched row count ").concat(String.valueOf(vo.getFetchedRowCount())));
int i = 0;
as = new String[j];
vNumber = new Number[j];

while (vo.hasNext())
{
vo.next();

System.out.println(String.valueOf("Inisde the do while loop").concat(String.valueOf(i)));

vNumber[i] = ((Number)vo.getCurrentRow().getAttribute("ViewAttr1"));
as[i] = String.valueOf(vo.getCurrentRow().getAttribute("ViewAttr2")).concat(String.valueOf(""));
i++;
}
}

CallableStatement cs = conn.prepareCall("{call XX_PassTableType.XX_PassTableType_prc(:1, :2)}");
ARRAY array = new ARRAY(new ArrayDescriptor("APPS.JTF_NUMBER_TABLE", conn), conn, vNumber);
ARRAY array1 = new ARRAY(new ArrayDescriptor("APPS.JTF_VARCHAR2_TABLE_100", conn), conn, as);

cs.setArray(1, array);
cs.setArray(2, array1);
cs.registerOutParameter(2, 2003, "JTF_VARCHAR2_TABLE_100");

cs.execute();
ARRAY error = null;
error = (ARRAY)cs.getArray(2);

if ((error != null) && (error.length() > 0))
{
System.out.println(String.valueOf("Error is ").concat(String.valueOf(error.getArray())));

String[] retError = new String[j];
retError = (String[])error.getArray();

System.out.println(String.valueOf("Error in saving data").concat(String.valueOf(retError[0])));
}
cs.close();
}
catch (Exception exception)
{
throw new OAException(String.valueOf("Code Blast").concat(String.valueOf(exception)), 0);
}
}
}

Hope it helps!!!

Let me know if you finds any issues...

Cheers