This discussion is archived
8 Replies Latest reply: Dec 6, 2012 3:30 AM by AlexAnd RSS

Checking an empty ROWTAG.

884492 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks. This approach is working fine.
  • 3. Re: Checking an empty ROWTAG.
    884492 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    <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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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" ;)

Legend

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