This discussion is archived
3 Replies Latest reply: Nov 15, 2012 3:39 AM by 974276 RSS

error while loading XML files into scott user

615721 Newbie
Currently Being Moderated
Hi All,

I'm new to xml files. I need to load xml files into database through OWB.

I have xml file in my local machine & am trying to load into table PO of Scott. Scott is registered as repository user.
Followed same steps as specified in userguide.

But, when executing the procedure ( in two ways one as just table name, and other as user.table name) it is showing the below error:

Procedure is:(1)--with username.tablename
begin
wb_xml_load(
'<OWBXMLRuntime>'||
'<XMLSource>'||
'<file>&&SAMPLES_DIR.sample1.xml</file>'||
'</XMLSource>'||
'<targets>'||
'<target dateFormat="yyyy.MM.dd">scott.PO</target>'||
'</targets>'||
'</OWBXMLRuntime>'
);
end;
/
ERROR at line 1:
ORA-20006: Error occurred while truncating target database object SCOTT.PO.
Base exception: ORA-01031: insufficient privileges
ORA-06512: at "OWBSYS.WB_XML_LOAD_F", line 12
ORA-06512: at "OWBSYS.WB_XML_LOAD", line 4
ORA-06512: at "SCOTT.SAMPLE1", line 3
ORA-06512: at line 1


Procedure is:(2) with out username
begin
wb_xml_load(
'<OWBXMLRuntime>'||
'<XMLSource>'||
'<file>&&SAMPLES_DIR.sample1.xml</file>'||
'</XMLSource>'||
'<targets>'||
'<target dateFormat="yyyy.MM.dd">PO</target>'||
'</targets>'||
'</OWBXMLRuntime>'
);
end;
/

ERROR at line 1:
ORA-20006: Error occurred while truncating target database object PO.
Base exception: ORA-00942: table or view does not exist
ORA-06512: at "OWBSYS.WB_XML_LOAD_F", line 12
ORA-06512: at "OWBSYS.WB_XML_LOAD", line 4
ORA-06512: at line 2

xml file:
<ROWSET>
<ROW>
<ID>100</ID>
<ORDER_DATE>2000.12.20</ORDER_DATE>
<SHIPTO_NAME>Adrian Howard</SHIPTO_NAME>
<SHIPTO_STREET>500 Marine World Parkway</SHIPTO_STREET>
<SHIPTO_CITY>Redwood City</SHIPTO_CITY>
<SHIPTO_STATE>CA</SHIPTO_STATE>
<SHIPTO_ZIP>94065</SHIPTO_ZIP>
</ROW>     
</ROWSET>


Note: Everything works fine if I create PO table in OWBSYS user and execute the procedurein OWBSYS user. OWBSYS.PO table will be loaded.

What privileges are missing, what shouldI do if I want to execute the procedure from scott user and load the table of scott.

Thanks in advance for the help.

Regards,
Joshna
  • 1. Re: error while loading XML files into scott user
    David Allan Guru
    Currently Being Moderated
    Hi Joshna

    Best to leverage the XML capabilities of the database for manipulating and loading XML, see here
    http://blogs.oracle.com/warehousebuilder/2007/09/leveraging_xdb.html

    Cheers
    David
  • 2. Re: error while loading XML files into scott user
    677887 Explorer
    Currently Being Moderated
    Hi Joshna,

    Please follow below steps to load xml file to oracle database.
    1.First connect to owb (Design Center) through your repository owner user (ex : REP_OWNER).
    2. Import WB_XML_LOAD procedure . and exit to repository owner.
    3. connect to owb design center through your repository user (ex : REP_USER)
    Create New mapping and drag one Constant Operator and create one attribute, paste / edit following code
    '<OWBXMLRuntime>'||
    '<XMLSource>'||
    '<file>E:\SOURCE\emp.xml</file>'||
    '</XMLSource>'||
    '<targets>'||
    '<target truncateFirst = "FALSE" dateFormat="yyyy.MM.dd">rep_user.emp</target>'||

    '</targets>'||

    '</OWBXMLRuntime>'

    4. Drag pre mapping operator and select WB_XML_LOAD procedure
    5. Connect Constant Operator attribute to pre mapping operator.
    6. Drag two dummy tables and connect source to target. (ex : drag t1 (table) tab two times and connect.
    7. Validate and deploy the mapping.
    8. grant necessary grant command to rep_owner user to rep_user user.
    (Note : target truncateFirst = "FALSE" by default truncate the table. So you have to give grant privileges
    To rep_user , select ,insert, delete privileges.
    9. Execute the mapping , and check EMP table. (Note : before loading EMP table delete all records ).
    10 . If you want more description please go through the below link
    http://download.oracle.com/docs/html/A95931_01/apf.htm

    Regards
    Venkat
  • 3. Re: error while loading XML files into scott user
    974276 Newbie
    Currently Being Moderated
    Hi Joshna,

    have you resolved the issue? if so, can you please share steps for granting truncate permission to table.

    thanks

Legend

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