ゞ沉默是金ゞ

鱼离不开水,但是没有说不离开哪滴水.
posts - 98,comments - 104,trackbacks - 0

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();

        }

}

posted on 2012-08-13 16:17 ゞ沉默是金ゞ 阅读(315) 评论(0)  编辑  收藏 所属分类: DB

只有注册用户登录后才能发表评论。


网站导航: