This discussion is archived
6 Replies Latest reply: Jan 13, 2011 11:00 AM by gonzroman RSS

using BulkLoad but no success!

gonzroman Newbie
Currently Being Moderated
I'm trying to learn OWM and execute below PL/SQL block but NO success. I'm missing something and don't know how to figure out.
SQL> get x.sql
  1  SET SERVEROUT ON
  2  DECLARE
  3    v_version INTEGER;
  4    l_start   NUMBER;
  5  BEGIN
  6    l_start := DBMS_UTILITY.GET_TIME;
  7    SELECT DBMS_WM.GetBulkLoadVersion ('LIVE') INTO v_version FROM DUAL;
  8    DBMS_OUTPUT.PUT_LINE ('version: '||v_version);
  9    DBMS_WM.BeginBulkLoading (table_name=>'TMP_DICTIONARY',workspace=>'LIVE', version=>v_version);
 10    INSERT INTO tmp_dictionary
 11    SELECT tab_id_seq.nextval, table_name, comments
 12      FROM dictionary;
 13    DBMS_WM.CommitBulkLoading (table_name=>'TMP_DICTIONARY',discards_table=>'ERR$_TMP_DICTIONARY');
 14    DBMS_OUTPUT.PUT_LINE ('Query completed '||(round((dbms_utility.get_time-l_start)/100,2) ||' Seconds...' ));
 15  EXCEPTION
 16    WHEN OTHERS THEN
 17      DBMS_WM.ROLLBACKBULKLOADING ('TMP_DICTIONARY');
 18      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 19* END;

Below is the error after the query is executed. Can someone please help to solve the error message. Appreciate any help.


version: 0
total time before calling cleanupRollbackBulkLoading: 21
time for indx fld 1 : 0
time for indx fld 2 : 7
time for indx fld 3 : 22
time for indx fld 4 : 13
time for indx fld 5 : 16
time for indx fld 6 : 20
time for indx fld 7 : 14
time for indx fld 8 : 45
time for indx fld 9 : 19
time for indx fld 10 : 169
time for indx fld 11 : 2
time for indx fld 12 : 6
time for indx fld 13 : 163
total time for cleanupRollbackBulkLoading: 504
DECLARE
*
ERROR at line 1:
ORA-20001: An error was encountered - -42399 -ERROR- ORA-42399: cannot perform a DML operation on a read-only view
ORA-06512: at line 17
Edited by: gonzroman on Jan 7, 2011 7:58 AM
  • 1. Re: using BulkLoad but no success!
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    You do not want to insert the rows into the top level TMP_DICTIONARY view (the original table name prior to EnableVersioning). You would want to insert them instead into the _LT table and include the version and createtime(if appropriate) columns.  The version that you would use would be the one returned from GetBulkLoadVersion and specified in BeginBulkLoading.

    Let me know if this helps.

    Regards,
    Ben
  • 2. Re: using BulkLoad but no success!
    gonzroman Newbie
    Currently Being Moderated
    Appreciate your reply. I'm confuse, why would I need to insert on _LT tables instead of enable version table? It means I need to manually assign a value on verstion, createtime, etc columns? Below is the description of the tables. Thanks again for your help.
    SQL> desc tmp_dictionary_lt
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     TAB_ID                                    NOT NULL NUMBER
     TABLE_NAME                                NOT NULL VARCHAR2(30 CHAR)
     COMMENTS                                           VARCHAR2(4000 CHAR)
     VERSION                                   NOT NULL NUMBER(38)
     CREATETIME                                         TIMESTAMP(6) WITH TIME ZONE
     RETIRETIME                                         TIMESTAMP(6) WITH TIME ZONE
     NEXTVER                                            VARCHAR2(500 CHAR)
     DELSTATUS                                 NOT NULL NUMBER(38)
     LTLOCK                                             VARCHAR2(100 CHAR)
    
    SQL> desc tmp_dictionary
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     TAB_ID                                    NOT NULL NUMBER
     TABLE_NAME                                NOT NULL VARCHAR2(30 CHAR)
     COMMENTS                                           VARCHAR2(4000 CHAR)
    Edited by: gonzroman on Jan 7, 2011 7:57 AM
  • 3. Re: using BulkLoad but no success!
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    I would suggest looking through section 1.7 of the Workspace Manager doc. It might clarify a number of your questions. Essentially, the data is inserted into _LT along with the version column, and possibly the createtime column as well.  So, you would have:

    insert into tmp_dictionary_lt(tab_id, table_name, comments, version) values(<user_defined_values>, :version)

    where :version is the version returned from GetBulkLoadVersion. Using sql loader to load these columns is also supported.

    Then when the CommitBulkLoading procedure is called, the remaining metadata columns are populated or modified as appropriate.

    If you have any additional questions or clarifications from the doc, let me know.

    Regards,
    Ben
  • 4. Re: using BulkLoad but no success!
    gonzroman Newbie
    Currently Being Moderated
    Thank you again on your response. Appreciate your patience. I made a change on the script and start from scratch, but can't get to work. This time, it's a new error message. It seems that it can do the INSERT statement but erroring out on the CommitBulkLoading. Can you please help for the meantime. I'm reading this document 'Workspace Manager Developer's Guide (11g Release 2 - 11.2)', is that the same doc. you mentioned on your reply?
    SQL>
    exec dbms_wm.enableversioning ('tmp_dictionary',hist=>'view_wo_overwrite');SQL>
    
    PL/SQL procedure successfully completed.
    
    SQL> get x.sql
      1  SET SERVEROUT ON
      2  DECLARE
      3    v_version INTEGER;
      4    l_start   NUMBER;
      5  BEGIN
      6    l_start := DBMS_UTILITY.GET_TIME;
      7    SELECT DBMS_WM.GetBulkLoadVersion ('LIVE') INTO v_version FROM DUAL;
      8    DBMS_OUTPUT.PUT_LINE ('version: '||v_version);
      9    DBMS_WM.BeginBulkLoading (table_name=>'TMP_DICTIONARY',workspace=>'LIVE', version=>v_version);
     10    INSERT INTO tmp_dictionary_lt
     11      (tab_id, table_name, comments, version)
     12    SELECT tab_id_seq.nextval, table_name, comments, v_version
     13      FROM dictionary;
     14    DBMS_WM.CommitBulkLoading (table_name=>'TMP_DICTIONARY',discards_table=>'ERR$_TMP_DICTIONARY');
     15    DBMS_OUTPUT.PUT_LINE ('Query completed '||(round((dbms_utility.get_time-l_start)/100,2) ||' Seconds...' ));
     16  EXCEPTION
     17    WHEN OTHERS THEN
     18      DBMS_WM.ROLLBACKBULKLOADING ('TMP_DICTIONARY');
     19      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
     20* END;
    SQL> @x.sql
    version: 0
    total time before calling cleanupEndBulkLoading: 45
    DECLARE
    *
    ERROR at line 1:
    ORA-20171: WM error: WMDEV.TMP_DICTIONARY is in BL_F_BEGIN state, bulk loading cannot be rolled back
    ORA-06512: at "WMSYS.OWM_BULK_LOAD_PKG", line 525
    ORA-06512: at "WMSYS.LT", line 12657
    ORA-06512: at line 17
    Edited by: gonzroman on Jan 7, 2011 7:57 AM
  • 5. Re: using BulkLoad but no success!
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Yes, that is the correct doc to be using.

    The error being raised is only used during RollBackBulkLoading. So, it appears that some other error is being thrown, which is caught by your exception block, and then RollBackBulkLoading also has an error. Although, the error is most likely occurring within CommitBulkLoading, without knowing the original error, I can't really comment on what the problem might be. I would suggest to execute the statements 1 by 1 so that you can know exactly where it is failing and with what error. When you get it to work, you could then create a procedure based off of that.

    Regards,
    Ben
  • 6. Re: using BulkLoad but no success!
    gonzroman Newbie
    Currently Being Moderated
    Thanks Ben for all your input.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points