Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    Unanswered: BULK Insert procedure

    I have declared the following types and a small procedure to insert using bulk

    I got the following error message on line 10 PLEASE HELP not sure whats wrong -- Have not posted in quite a while -- ANy help greatly appreciated

    line10=insert into tetris_execution values te_data (indx);

    SQL> show errors
    Errors for PROCEDURE INS_TETRIS_EXEC_DATA:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    10/4 PL/SQL: SQL Statement ignored
    10/4 PL/SQL: ORA-00932: inconsistent datatypes: expected - got


    Code:
    create or replace type te_object_rec_type is object (
    TCAPREFERENCE                                      VARCHAR2(50),
    EXECTIME                                           TIMESTAMP(3),
    FIXMSGSEQNUM                                       NUMBER(14),
    FIXORIGSENDINGTIME                                 TIMESTAMP(3),
    ISMANUALEXECUTION                                  NUMBER(1),
    TXNTYPE                                            VARCHAR2(12),
    SIDE                                               VARCHAR2(12),
    ACCOUNT                                            VARCHAR2(20),
    PRODUCT                                            VARCHAR2(20),
    PRODUCTSOR                                         VARCHAR2(20),
    CURRENCY                                           VARCHAR2(4),
    QUANTITY                                           NUMBER(18,8),
    PRICE                                              NUMBER(18,8),
    EXECID                                             VARCHAR2(100),
    EXECREFID                                          VARCHAR2(100),
    TRADEDATE                                          TIMESTAMP(3),
    TRANSACTTIME                                       TIMESTAMP(3),
    SETTLEMENTDATE                                     TIMESTAMP(3),
    ORIGINATINGSYSTEM                                  VARCHAR2(20),
    CREATED_BY                                         VARCHAR2(20),
    CREATED_DATE                                       DATE,
    MODIFIED_BY                                        VARCHAR2(20),
    MODIFIED_DATE                                      DATE
    )
    /
    create or replace type te_assoc_array_typ is varray(1000) of  te_object_rec_type
    /

    Code:
    create or replace procedure ins_tetris_exec_data (te_data IN te_assoc_array_typ)
    as
    bulk_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
    --
    BEGIN
    --
    FORALL indx IN te_data.FIRST .. te_data.LAST
       SAVE EXCEPTIONS
       insert into tetris_execution values te_data (indx);
    EXCEPTION
       WHEN bulk_errors
       THEN
       FOR j in 1 .. SQL%BULK_EXCEPTIONS.COUNT
       LOOP
          dbms_output.put_line ('Error from row #'||to_char(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' || SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
       END LOOP;
    END ins_tetris_exec_data;
    /
    Code:
    select * from client where clue > 0;
    0 rows returned

  2. #2
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    forgot create table as per below

    Code:
    create table tetris_execution (
    tcapreference           varchar2(50) not null,
    exectime                timestamp(3),
    fixmsgseqnum            number (14),
    fixorigsendingtime      timestamp(3),                   --number(20),
    ismanualexecution       number(1),
    txntype                 varchar2(12),
    side                    varchar2(12),
    account                 varchar2(20),
    product                 varchar2(20),
    productsor              varchar2(20),
    currency                varchar2(4),
    quantity                number (18,8),
    price                   number(18,8),
    execid                  varchar2(100),
    execrefid               varchar2(100),
    tradedate               timestamp(3),
    transacttime            timestamp(3),
    settlementdate          timestamp(3),
    originatingsystem       varchar2(20),
    created_by              varchar2(20),
    created_date            date,
    modified_by             varchar2(20),
    modified_date           date
    --constraint tetris_execution_pk PRIMARY_KEY (tcapreference)
    );
    Code:
    select * from client where clue > 0;
    0 rows returned

  3. #3
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    FIgured it out -- answer for any who cares --

    FInally found some obscure syntax afer googling all day yesterday

    heres how it works

    types defined as

    Code:
    create or replace type te_object_rec_type is object (
    TCAPREFERENCE                                      VARCHAR2(50),
    EXECTIME                                           TIMESTAMP(3),
    FIXMSGSEQNUM                                       NUMBER(14),
    FIXORIGSENDINGTIME                                 TIMESTAMP(3),
    ISMANUALEXECUTION                                  NUMBER(1),
    TXNTYPE                                            VARCHAR2(12),
    SIDE                                               VARCHAR2(12),
    ACCOUNT                                            VARCHAR2(20),
    PRODUCT                                            VARCHAR2(20),
    PRODUCTSOR                                         VARCHAR2(20),
    CURRENCY                                           VARCHAR2(4),
    QUANTITY                                           NUMBER(18,8),
    PRICE                                              NUMBER(18,8),
    EXECID                                             VARCHAR2(100),
    EXECREFID                                          VARCHAR2(100),
    TRADEDATE                                          TIMESTAMP(3),
    TRANSACTTIME                                       TIMESTAMP(3),
    SETTLEMENTDATE                                     TIMESTAMP(3),
    ORIGINATINGSYSTEM                                  VARCHAR2(20),
    CREATED_BY                                         VARCHAR2(20),
    CREATED_DATE                                       DATE,
    MODIFIED_BY                                        VARCHAR2(20),
    MODIFIED_DATE                                      DATE
    )
    /
    create or replace type te_varray_typ is varray(10000) of  te_object_rec_type 
    /
    Insert proc modified to this

    Code:
    create or replace procedure ins_tetris_exec_data (te_data IN te_varray_typ) as bulk_errors EXCEPTION; 
    PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
    --
    BEGIN
    --
    FORALL indx IN te_data.FIRST .. te_data.LAST
       SAVE EXCEPTIONS
       insert into tetris_execution values (
    TREAT(te_data(indx) as te_object_rec_type).TCAPREFERENCE,
    TREAT(te_data(indx) as te_object_rec_type).EXECTIME                                         ,
    TREAT(te_data(indx) as te_object_rec_type).FIXMSGSEQNUM                                    ,
    TREAT(te_data(indx) as te_object_rec_type).FIXORIGSENDINGTIME                             ,
    TREAT(te_data(indx) as te_object_rec_type).ISMANUALEXECUTION                             ,
    TREAT(te_data(indx) as te_object_rec_type).TXNTYPE                                      ,
    TREAT(te_data(indx) as te_object_rec_type).SIDE                                        ,
    TREAT(te_data(indx) as te_object_rec_type).ACCOUNT                                    ,
    TREAT(te_data(indx) as te_object_rec_type).PRODUCT                                   ,
    TREAT(te_data(indx) as te_object_rec_type).PRODUCTSOR                               ,
    TREAT(te_data(indx) as te_object_rec_type).CURRENCY                                ,
    TREAT(te_data(indx) as te_object_rec_type).QUANTITY                               ,
    TREAT(te_data(indx) as te_object_rec_type).PRICE                                 ,
    TREAT(te_data(indx) as te_object_rec_type).EXECID                               ,
    TREAT(te_data(indx) as te_object_rec_type).EXECREFID                           ,
    TREAT(te_data(indx) as te_object_rec_type).TRADEDATE                          ,
    TREAT(te_data(indx) as te_object_rec_type).TRANSACTTIME                      ,
    TREAT(te_data(indx) as te_object_rec_type).SETTLEMENTDATE                   ,
    TREAT(te_data(indx) as te_object_rec_type).ORIGINATINGSYSTEM               ,
    TREAT(te_data(indx) as te_object_rec_type).CREATED_BY                     ,
    TREAT(te_data(indx) as te_object_rec_type).CREATED_DATE                  ,
    TREAT(te_data(indx) as te_object_rec_type).MODIFIED_BY                  ,
    TREAT(te_data(indx) as te_object_rec_type).MODIFIED_DATE ); EXCEPTION
       WHEN bulk_errors
       THEN
       FOR j in 1 .. SQL%BULK_EXCEPTIONS.COUNT
       LOOP
          dbms_output.put_line ('Error from row #'||to_char(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' || SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
       END LOOP;
    END ins_tetris_exec_data;
    /
    Code:
    select * from client where clue > 0;
    0 rows returned

  4. #4
    Join Date
    Apr 2009
    Posts
    1

    Thanks

    I was looking for this solution whole day long. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •