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

view plainprint?

1. JTF_NUMBER_TABLE       // Table type of Number Type       
2. 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

view plainprint?

1. CREATE TABLE xx_test(  
2. invoice_id VARCHAR(2000),  
3. amount NUMBER);  

Below is the code for Package.

view plainprint?

1. CREATE OR REPLACE PACKAGE xx_passtabletype  
2. AS  
3. PROCEDURE xx_passtabletype_prc (  
4. xx_number_table              jtf_number_table,  
5. xx_varchar2_table   IN OUT   jtf_varchar2_table_100  
6. );  
7. END xx_passtabletype;  

view plainprint?

1. CREATE OR REPLACE PACKAGE BODY xx_passtabletype  
2. AS  
3. PROCEDURE xx_passtabletype_prc (  
4. xx_number_table              jtf_number_table,  
5. xx_varchar2_table   IN OUT   jtf_varchar2_table_100  
6. )  
7. AS  
8. BEGIN  
9. DBMS_OUTPUT.put_line ('I am here');  
10.   
11. FOR i IN xx_varchar2_table.FIRST .. xx_varchar2_table.LAST  
12. LOOP  
13. INSERT INTO xx_test  
14. (invoice_id, amount  
15. )  
16. VALUES (xx_varchar2_table (i), xx_number_table (i)  
17. );  
18.   
19. COMMIT;  
20. END LOOP;  
21.   
22. xx_varchar2_table :=  
23. jtf_varchar2_table_100 ('Error while inserting record');  
24. END xx_passtabletype_prc;  
25. 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:

view plainprint?

1. public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)  
2. {  
3. super.processFormRequest(pageContext, webBean);  
4.   
5. OAApplicationModule am = pageContext.getApplicationModule(webBean);  
6.   
7. if (pageContext.getParameter("go") != null)  
8. {  
9. String[] as = null;  
10. Number[] vNumber = null;  
11.   
12. Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();  
13. String mCreateSearchRequestStatement = null;  
14. OAViewObject vo = (OAViewObject)am.findViewObject("MainVO1");  
15. int j = vo.getFetchedRowCount();  
16. try  
17. {  
18. System.out.println("abouce try");  
19.   
20. vo.reset();  
21. if (vo.getFetchedRowCount() > 0)  
22. {  
23. System.out.println(String.valueOf("Fetched row count ").concat(String.valueOf(vo.getFetchedRowCount())));  
24. int i = 0;  
25. as = new String[j];  
26. vNumber = new Number[j];  
27.   
28. while (vo.hasNext())  
29. {  
30. vo.next();  
31.   
32. System.out.println(String.valueOf("Inisde the do while loop").concat(String.valueOf(i)));  
33.   
34. vNumber[i] = ((Number)vo.getCurrentRow().getAttribute("ViewAttr1"));  
35. as[i] = String.valueOf(vo.getCurrentRow().getAttribute("ViewAttr2")).concat(String.valueOf(""));  
36. i++;  
37. }  
38. }  
39.   
40. CallableStatement cs = conn.prepareCall("{call XX_PassTableType.XX_PassTableType_prc(:1, :2)}");  
41. ARRAY array = new ARRAY(new ArrayDescriptor("APPS.JTF_NUMBER_TABLE", conn), conn, vNumber);  
42. ARRAY array1 = new ARRAY(new ArrayDescriptor("APPS.JTF_VARCHAR2_TABLE_100", conn), conn, as);  
43.   
44. cs.setArray(1, array);  
45. cs.setArray(2, array1);  
46. cs.registerOutParameter(2, 2003, "JTF_VARCHAR2_TABLE_100");  
47.   
48. cs.execute();  
49. ARRAY error = null;  
50. error = (ARRAY)cs.getArray(2);  
51.   
52. if ((error != null) && (error.length() > 0))  
53. {  
54. System.out.println(String.valueOf("Error is ").concat(String.valueOf(error.getArray())));  
55.   
56. String[] retError = new String[j];  
57. retError = (String[])error.getArray();  
58.   
59. System.out.println(String.valueOf("Error in saving data").concat(String.valueOf(retError[0])));  
60. }  
61. cs.close();  
62. }  
63. catch (Exception exception)  
64. {  
65. throw new OAException(String.valueOf("Code Blast").concat(String.valueOf(exception)), 0);  
66. }  
67. }  
68. }  

Hope it hel