Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /******************************************************************************/
- /*The Script used to create Object type in this example is as follows:*******/
- /******************************************************************************/
- create or replace type rectype as object(col1 varchar2(10),col2 Date,col3 Number);
- 2)Define table of object u have defined above which u need to use in ur pl/sql procedure of some pkg.Remember define it as global table type and not inside package specification, because in that case our jdbc code would not be to find this object type.
- /******************************************************************************/
- /*The Script used to create table of Object type is as follows:*******/
- /******************************************************************************/
- create or replace type rectab as table of rectype;
- 3)Defining package specification and procedure definition
- /*********************************************************************************/
- /*The Script used to create package specification in this eg.is as follows:*******/
- /******************************************************************************/
- create or replace package ioStructArray as
- procedure testproc(iorec in rectab,orec out rectab);
- end ioStructArray;
- /
- 4)Defining package body and procedure
- /*********************************************************************************/
- /*The Script used to create package specification in this eg.is as follows:*******/
- /******************************************************************************/
- create or replace package body ioStructArray as
- procedure testproc(iorec in rectab,orec out rectab) is
- begin
- /*see how to loop and assign values*/
- for i in 1..iorec.count loop
- iorec(i).col1 := orec(i).col2;
- iorec(i).col2 := orec(i).col1;
- end loop;
- end testproc;
- end ioStructArray;
- /
- 5)Getting connection object in JDBC Code :
- //Getting db connection in a jdbc
- DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
- // Connect to the database
- Connection conn=DriverManager.getConnection ("jdbc:oracle:oci8:@S692815.WORLD",
- "scott", "tiger");
- 6)If writing in AM in OAF page, code to get connection:
- OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getOADBTransaction();
- OracleConnection conn = (OracleConnection)oadbtransactionimpl.getJdbcConnection();
- 7)Lets, consider a simple scenario where we have a XXVO in AM and I have to pass all VO rows data to the pl/sql procedure we just created and then receieve a table of data back and then based on it values do something in AM.Here is the Code:
- //imports
- import oracle.sql.*;
- import oracle.jdbc.driver.OracleConnection;
- import oracle.jdbc.driver.OracleCallableStatement;
- import oracle.apps.fnd.framework.server.OADBTransaction;
- import oracle.apps.fnd.framework.server.OADBTransactionImpl;
- Public void arryToPLSQL()
- {
- //Getting Db connection
- OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getOADBTransaction();
- OracleConnection conn = (OracleConnection)oadbtransactionimpl.getJdbcConnection();
- //Defining variables
- //oracle.sql.ARRAY we will use as out parameter from the package
- //and will store pl/sql table
- ARRAY message_display = null;
- //ArrayList to store object of type struct
- ArrayList arow= new ArrayList();
- //StructDescriptor >> use to describe pl/sql object
- //type in java.
- StructDescriptor voRowStruct = null;
- //ArrayDescriptor >> Use to describe pl/sql table
- //as Array of objects in java
- ArrayDescriptor arrydesc = null;
- //Input array to pl/sql procedure
- ARRAY p_message_list = null;
- //Oracle callable statement used to execute procedure
- OracleCallableStatement cStmt=null;
- try
- {
- //initializing object types in java.
- voRowStruct = StructDescriptor.createDescriptor("RECTYPE",conn);
- arrydesc = ArrayDescriptor.createDescriptor("RECTAB",conn);
- }
- catch (Exception e)
- {
- throw OAException.wrapperException(e);
- }
- for(XXVORowImpl row = (XXVORowImpl)XXVO.first();
- row!=null;
- row = (XXVORowImpl)XXVO.next())
- {
- //We have made this method to create struct arraylist
- // from which we will make ARRAY
- //the reason being in java ARRAY length cannot be dynamic
- //see the method defination below.
- populateObjectArraylist(row,voRowStruct,arow);
- }
- //make array from arraylist
- STRUCT [] obRows= new STRUCT[arow.size()];
- for(int i=0;i < arow.size();i++)
- {
- obRows[i]=(STRUCT)arow.get(i);
- }
- try
- {
- p_message_list = new ARRAY(arrydesc,conn,obRows);
- }
- catch (Exception e)
- {
- throw OAException.wrapperException(e);
- }
- //jdbc code to execute pl/sql procedure
- try
- {
- cStmt
- =(OracleCallableStatement)conn.prepareCall("{CALL ioStructArray.testproc(:1,:2)}");
- cStmt.setArray(1,p_message_list);
- cStmt.registerOutParameter(2,OracleTypes.ARRAY,"RECTAB");
- cStmt.execute();
- //getting Array back
- message_display = cStmt.getARRAY(2);
- //Getting sql data types in oracle.sql.datum array
- //which will typecast the object types
- Datum[] arrMessage = message_display.getOracleArray();
- //getting data and printing it
- for (int i = 0; i < arrMessage.length; i++)
- {
- oracle.sql.STRUCT os = (oracle.sql.STRUCT)arrMessage[i];
- Object[] a = os.getAttributes();
- System.out.println("a [0 ] >>attribute1=" + a[0]);
- System.out.println("a [1 ] >>attribute2=" + a[1]);
- System.out.println("a [2 ] >>attribute3=" + a[2]);
- //You can typecast back these objects to java object type
- }
- }
- catch (Exception e1)
- {
- throw OAException.wrapperException(e1);
- }
- }
- /*Our custom method which will populate
- arraylist with struct object type
- */
- public void populateObjectArraylist( XXVORowImpl row,StructDescriptor voRowStruct , ArrayList arow)
- {
- Object[] attribMessage = new Object[3];
- String attr1 = null;
- Date attr2 = null;
- Number attr3 = null;
- //Get value from Vo row and put in attr1,att2 and attr 3
- //Putting values in object array
- attribMessage[0]=attr1;
- attribMessage[1]=attr2;
- attribMessage[2]=attr3;
- try
- {
- STRUCT loadedStructTime = new STRUCT(voRowStruct, conn, attribMessage);
- arow.add(loadedStructTime);
- }
- catch (Exception e)
- {
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement