6 Replies Latest reply: Jan 13, 2011 11:00 AM by gonzroman RSS

    using BulkLoad but no success!

    gonzroman
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    Thanks Ben for all your input.