8 Replies Latest reply: Dec 6, 2012 5:30 AM by AlexAnd RSS

    Checking an empty ROWTAG.

    884492
      I have created a table EMP:

      CREATE TABLE EMP
      (
      COMPANY_ID      VARCHAR2(20),
      EMPLOYEE_ID          VARCHAR2(11),
      CURRENCY_CODE VARCHAR2(3),
      CURR_AMOUNT NUMBER,
      COMMENTS     VARCHAR2(50)
      )

      There is no primary key in the table.

      The code for inserting data into table is:
      insertContext_:=DBMS_XMLSAVE.newContext('EMP');
      DBMS_XMLSAVE.setRowTag(insertContext_,'EMP_REC');
      rows_:= DBMS_XMLSAVE.insertXML(insertContext_,app_trav_exp_);
      DBMS_XMLSAVE.closeContext(insertContext_);

      When I am passing the following XML; it is inserting one row in the table:

      <EMP_REC>
      <COMPANY_ID>104010</COMPANY_ID>
      <EMPLOYEE_ID>1681</EMPLOYEE_ID>
      <CURRENCY_CODE>EUR</CURRENCY_CODE>
      <CURR_AMOUNT>100</CURR_AMOUNT>
      <COMMENTS>Mission Order Number 2</COMMENTS>
      </EMP_REC>

      This is fine.
      But when I am passing the following XML; then also it is inserting one row with every column as NULL value:

      <EMP_REC>

      </EMP_REC>

      But for this case I dont want to insert any row in the table.

      So is there any way to identify whether ROWTAG contains any data or not? Or is there any other approach, so that the code should not insert NULL values in all columns?


      Regards,
      Sudhanshu
        • 1. Re: Checking an empty ROWTAG.
          odie_63
          You may switch to another approach :
          SQL> DECLARE
            2  
            3    input_doc  VARCHAR2(4000) :=
            4  '<ROOT>
            5   <EMP_REC>
            6    <COMPANY_ID>104010</COMPANY_ID>
            7    <EMPLOYEE_ID>1681</EMPLOYEE_ID>
            8    <CURRENCY_CODE>EUR</CURRENCY_CODE>
            9    <CURR_AMOUNT>100</CURR_AMOUNT>
           10    <COMMENTS>Mission Order Number 2</COMMENTS>
           11   </EMP_REC>
           12   <EMP_REC>
           13   </EMP_REC>
           14  </ROOT>';
           15  
           16  BEGIN
           17  
           18    INSERT INTO emp (company_id, employee_id, currency_code, curr_amount, comments)
           19    SELECT company_id, employee_id, currency_code, curr_amount, comments
           20    FROM XMLTable(
           21           '/ROOT/EMP_REC[*]'
           22           passing xmlparse(document input_doc)
           23           columns
           24             COMPANY_ID     VARCHAR2(20)
           25           , EMPLOYEE_ID    VARCHAR2(11)
           26           , CURRENCY_CODE  VARCHAR2(3)
           27           , CURR_AMOUNT    NUMBER
           28           , COMMENTS       VARCHAR2(50)
           29         )
           30    ;
           31  
           32  END;
           33  /
           
          PL/SQL procedure successfully completed
           
          SQL> select * from emp;
           
          COMPANY_ID           EMPLOYEE_ID CURRENCY_CODE CURR_AMOUNT COMMENTS
          -------------------- ----------- ------------- ----------- --------------------------------------------------
          104010               1681        EUR                   100 Mission Order Number 2
           
          • 2. Re: Checking an empty ROWTAG.
            884492
            Thanks. This approach is working fine.
            • 3. Re: Checking an empty ROWTAG.
              884492
              Hi Odi,

              I am facing problem with DATE columns when I am using this approach.

              CREATE TABLE EXPENSE_TAB
              (
              COMPANY_ID     VARCHAR2(20),
              EMPLOYEE_ID          VARCHAR2(11),
              START_DATE      DATE,
              END_DATE     DATE,
              EXPENSE_INFO      VARCHAR2(2000),
              PROJECT_ACTIVITY VARCHAR2(50)
              )

              DECLARE

              input_doc VARCHAR2(4000) :=
              '<RECEIVE_DATA>
              <EXPENSES>
              <EXPENSE_DETAILS>
              <COMPANY_ID>104010</COMPANY_ID>
              <EMPLOYEE_ID>TPBV.1010</EMPLOYEE_ID>
              <START_DATE>1/2/2012 00:00:00</START_DATE>
              <END_DATE>2/2/2012 00:00:00</END_DATE>
              <EXPENSE_INFO>ODF:123 NDF:456</EXPENSE_INFO>
              <PROJECT_ACTIVITY>021281T020.B</PROJECT_ACTIVITY>
              </EXPENSE_DETAILS>
              </EXPENSES>
              <EMP_PAY_TRANS>
              <EMP_REC>

              </EMP_REC>
              </EMP_PAY_TRANS>
              </RECEIVE_DATA>';



              BEGIN

              INSERT INTO EXPENSE_TAB (company_id, employee_id, start_date, end_date, expense_info,Project_Activity)
              SELECT company_id, employee_id, start_date, end_date, expense_info,Project_Activity
              FROM XMLTable(
              '/RECEIVE_DATA/EXPENSES/EXPENSE_DETAILS[*]'
              passing xmlparse(document input_doc)
              columns
              COMPANY_ID VARCHAR2(20)
              , EMPLOYEE_ID VARCHAR2(11)
              , START_DATE DATE
              , END_DATE DATE
              , EXPENSE_INFO VARCHAR2(2000)
              , PROJECT_ACTIVITY VARCHAR2(50)
              )
              ;

              END;

              When I am executing this anonymous block, I am getting the following error:

              ORA-01830: date format picture ends before converting entire input string

              Pls help me out.

              Regards,
              Sudhanshu
              • 4. Re: Checking an empty ROWTAG.
                AlexAnd
                SELECT company_id, employee_id, to_date(start_date, 'dd/mm/yyyy hh24:mi:ss') as start_date, to_date(end_date, 'dd/mm/yyyy hh24:mi:ss') as end_date, expense_info,Project_Activity
                FROM XMLTable(
                '/RECEIVE_DATA/EXPENSES/EXPENSE_DETAILS[*]'
                passing xmlparse(document input_doc)
                columns
                COMPANY_ID VARCHAR2(20)
                , EMPLOYEE_ID VARCHAR2(22)
                , START_DATE VARCHAR2(30)
                , END_DATE VARCHAR2(30)
                , EXPENSE_INFO VARCHAR2(2000)
                , PROJECT_ACTIVITY VARCHAR2(50)
                )
                • 5. Re: Checking an empty ROWTAG.
                  884492
                  Hi Alex,

                  Thanks for quick help. This is working fine. But what was the problem with taking START_DATE and END_DATE as DATE instead of VARCHAR2.

                  Regards,
                  Sudhanshu
                  • 6. Re: Checking an empty ROWTAG.
                    AlexAnd
                    >
                    <START_DATE>1/2/2012 00:00:00</START_DATE>
                    >
                    1/2/2012 00:00:00 is string
                    so oracle don't know about what is date

                    if you try
                    >
                    , START_DATE DATE
                    >
                    oracle try converting to date by default date format in nls, so error can be raised

                    using
                    >
                    to_date(start_date, 'dd/mm/yyyy hh24:mi:ss')
                    >
                    you say oracle to convert to date by your indicated mask
                    • 7. Re: Checking an empty ROWTAG.
                      odie_63
                      AlexAnd wrote:
                      if you try
                      >
                      , START_DATE DATE
                      >
                      oracle try converting to date by default date format in nls, so error can be raised
                      No, Oracle expects the W3C format xs:date, e.g. '2012-12-06', it doesn't rely on NLS.
                      • 8. Re: Checking an empty ROWTAG.
                        AlexAnd
                        thanks for correct !1

                        this
                        >
                        '2012-12-06'
                        >
                        and
                         
                        2012/01/11
                        and
                        2012.01.11
                        and
                        2012 01 11
                        and
                        2012   01   11
                        also work

                        so "lexical space" ;)