1. 1.Define the object type PROFILE_TAG_TYPE.
CREATE OR REPLACE TYPE PZN_ADMIN.PROFILE_TAG_TYPE
AS
OBJECT
(
MID VARCHAR2 (34),
TAG_ID NUMBER,
CUSTOMER_TYPE VARCHAR2(1),
SOURCE_SYSTEM VARCHAR2(30),
TAG_CREATED_DATE VARCHAR2(30),
INTEREST_LEVEL NUMBER(2),
SUPPRESSION_IND VARCHAR2(2),
SUPPRESSION_EXPIRY_DATE VARCHAR2(30),
LAST_HOUSEKEEPING_DATE VARCHAR2(30),
LAST_EVENT_DATE VARCHAR2(30),
REASON VARCHAR2(1500) );
2. 2. Grant PROFILE_TAG_TYPE execute access to PZN_MB_USER.
GRANT EXECUTE ON PZN_ADMIN.PROFILE_TAG_TYPE TO PZN_MB_USER;
3. 3. Define the array type reference to object PROFILE_TAG_TYPE.
CREATE TYPE PZN_ADMIN.PROFILE_TAG_ARRAY AS TABLE OF PZN_ADMIN.PROFILE_TAG_TYPE;
4. 4. Grant PROFILE_TAG_ARRAY execute access to PZN_MB_USER.
GRANT EXECUTE ON PZN_ADMIN.PROFILE_TAG_ARRAY TO PZN_MB_USER;
5. 5. Create store procedure package.
CREATE OR REPLACE
PACKAGE PZN_ADMIN.PZN_PROFILE_TAG_PKG
AS
PROCEDURE INSERT_PROFILE_TAG(
PTA PROFILE_TAG_ARRAY);
END PZN_PROFILE_TAG_PKG;
6. 6. Create store procedure package body.
CREATE OR REPLACE
PACKAGE BODY PZN_ADMIN.PZN_PROFILE_TAG_PKG
AS
PROCEDURE INSERT_PROFILE_TAG(
PTA PROFILE_TAG_ARRAY)
AS
BEGIN
FOR I IN PTA.FIRST..PTA.LAST
LOOP
INSERT
INTO PZN_ADMIN.PROFILE_TAG
(
PROFILE_TAG_ID,
MID,
TAG_ID,
CUSTOMER_TYPE,
SOURCE_SYSTEM,
TAG_CREATED_DATE,
INTEREST_LEVEL,
SUPPRESSION_IND,
SUPPRESSION_EXPIRY_DATE,
LAST_HOUSEKEEPING_DATE,
LAST_EVENT_DATE,
REASON
)
VALUES
(
SEQ_PROFILE_TAG_ID.NEXTVAL ,
PTA(I).MID,
PTA(I).TAG_ID,
PTA(I).CUSTOMER_TYPE,
PTA(I).SOURCE_SYSTEM,
TO_DATE(PTA(I).TAG_CREATED_DATE,'YYYY-MM-DD'),
PTA(I).INTEREST_LEVEL,
PTA(I).SUPPRESSION_IND,
TO_DATE(PTA(I).SUPPRESSION_EXPIRY_DATE,'YYYY-MM-DD'),
TO_DATE(PTA(I).LAST_HOUSEKEEPING_DATE,'YYYY-MM-DD'),
TO_DATE(PTA(I).LAST_EVENT_DATE,'YYYY-MM-DD'),
PTA(I).REASON
);
END LOOP;
END INSERT_PROFILE_TAG;
END PZN_PROFILE_TAG_PKG;
7. 7. Create synonym to PZN_MB_USER.
CREATE SYNONYM PZN_MB_USER.PZN_PROFILE_TAG_PKG FOR PZN_ADMIN.PZN_PROFILE_TAG_PKG;
8. 8. Grant execute access to PZN_MB_USER.
GRANT EXECUTE ON PZN_ADMIN.PZN_PROFILE_TAG_PKG TO PZN_MB_USER;
9. 9. Create the java class to call the procedure.
public class ProcedureTest2 {
public static void insertProfileTag(){
Connection dbConn = null;
try {
Object[] so1 = {"ee745b5782bfc311e0b5730a2aba15aa77",31,"C","eDB","2012-08-13",0,"0","2012-08-13","2012-08-13","2012-08-13","eDB"};
Object[] so2 = {"ee745b5782bfc311e0b5730a2aba15aa77",32,"C","eDB","2012-08-13",0,"0","2012-08-13","2012-08-13","2012-08-13","eDB"};
OracleCallableStatement callStatement = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
dbConn = DriverManager.getConnection("jdbc:oracle:thin:@da957116.fmr.com:1521:orcl", "PZN_MB_USER", "PZN_MB_USER123");
StructDescriptor st = new StructDescriptor("PZN_ADMIN.PROFILE_TAG_TYPE", dbConn);
STRUCT s1 = new STRUCT(st, dbConn, so1);
STRUCT s2 = new STRUCT(st, dbConn, so2);
STRUCT[] deptArray = { s1, s2 };
ArrayDescriptor arrayDept = ArrayDescriptor.createDescriptor("PZN_ADMIN.PROFILE_TAG_ARRAY", dbConn);
ARRAY deptArrayObject = new ARRAY(arrayDept, dbConn, deptArray);
callStatement = (OracleCallableStatement) dbConn.prepareCall("{call PZN_PROFILE_TAG_PKG.INSERT_PROFILE_TAG(?)}");
callStatement.setArray(1, deptArrayObject);
callStatement.executeUpdate();
dbConn.commit();
callStatement.close();
} catch (Exception e) {
System.out.println(e.toString());
e.printStackTrace();
}
}
public static void main(String[] args) {
insertProfileTag();
}
}