Home » RDBMS Server » Server Utilities » multiple rows (Sqlldr 8i)
multiple rows [message #487057] Wed, 22 December 2010 05:01 Go to next message
chandanacsr
Messages: 6
Registered: December 2010
Junior Member
The following control file updates multiple rows in database table.



LOAD DATA
INFILE *
replace
INTO TABLE temp_tab
FIELDS TERMINATED BY ","
(Data LOBFILE(CONSTANT cadd_pass.xml) terminated by eof
)


There are 21 lines in the xml. So 21 rows are updated in table.
Can you help me to update only one row?
Re: multiple rows [message #487058 is a reply to message #487057] Wed, 22 December 2010 05:09 Go to previous messageGo to next message
Littlefoot
Messages: 21687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Loader loads data, it doesn't update anything. Moreover, you chose to use REPLACE that removes previous contents from the table.

What do you want to do, actually? What do you have?
Re: multiple rows [message #487059 is a reply to message #487058] Wed, 22 December 2010 05:12 Go to previous messageGo to next message
chandanacsr
Messages: 6
Registered: December 2010
Junior Member
yes i knw it loads data. the control file uploads it in table.
i have to replace it because i donot need old records.
i need to upload the whole xml file as one single row
Re: multiple rows [message #487081 is a reply to message #487059] Wed, 22 December 2010 09:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You were using a method for loading multiple lobs per file. The following method is for one lob per file. You can add additional file names for additional lobs and the same syntax can be used to load into either an xmltype column or clob column.

-- cadd_pass.xml:
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>


-- test.ctl:
LOAD DATA
INFILE *
replace
INTO TABLE temp_tab
FIELDS TERMINATED BY ","
(ext_fname  FILLER,
Data LOBFILE(ext_fname) terminated by eof
)
BEGINDATA:
cadd_pass.xml


SCOTT@orcl_11gR2> create table temp_tab
  2    (data  xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from temp_tab
  2  /

DATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <LOC>NEW YORK</LOC>
  </ROW>
  <ROW>
    <DEPTNO>20</DEPTNO>
    <DNAME>RESEARCH</DNAME>
    <LOC>DALLAS</LOC>
  </ROW>
  <ROW>
    <DEPTNO>30</DEPTNO>
    <DNAME>SALES</DNAME>
    <LOC>CHICAGO</LOC>
  </ROW>
  <ROW>
    <DEPTNO>40</DEPTNO>
    <DNAME>OPERATIONS</DNAME>
    <LOC>BOSTON</LOC>
  </ROW>
</ROWSET>


1 row selected.

SCOTT@orcl_11gR2>

Re: multiple rows [message #487112 is a reply to message #487081] Wed, 22 December 2010 13:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I might also point out that SQL*Loader is not the only way to load your XML data. If the file is on your server, not your client, then you can just use SQL, as shown below.

SCOTT@orcl_11gR2> create table temp_tab
  2    (data  xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> insert into temp_tab (data)
  2  select xmltype
  3  	      (bfilename
  4  		('MY_DIR',
  5  		 'cadd_pass.xml'),
  6  	       NLS_CHARSET_ID ('WE8ISO8859P1'))
  7  from   dual
  8  /

1 row created.

SCOTT@orcl_11gR2> select * from temp_tab
  2  /

DATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <LOC>NEW YORK</LOC>
  </ROW>
  <ROW>
    <DEPTNO>20</DEPTNO>
    <DNAME>RESEARCH</DNAME>
    <LOC>DALLAS</LOC>
  </ROW>
  <ROW>
    <DEPTNO>30</DEPTNO>
    <DNAME>SALES</DNAME>
    <LOC>CHICAGO</LOC>
  </ROW>
  <ROW>
    <DEPTNO>40</DEPTNO>
    <DNAME>OPERATIONS</DNAME>
    <LOC>BOSTON</LOC>
  </ROW>
</ROWSET>


1 row selected.

SCOTT@orcl_11gR2>

Re: multiple rows [message #487124 is a reply to message #487112] Wed, 22 December 2010 21:30 Go to previous messageGo to next message
chandanacsr
Messages: 6
Registered: December 2010
Junior Member
thanks a lot. i will try it!
Smile
Re: multiple rows [message #487125 is a reply to message #487124] Wed, 22 December 2010 22:33 Go to previous messageGo to next message
chandanacsr
Messages: 6
Registered: December 2010
Junior Member
I pasted the above mentioned ctrl file. Following was the error:

SQL*Loader-502: unable to open data file '<?xml version="1.0" ?>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory

And there was nothing inserted in the table.
the datatype of Column Data is CLOB.
Re: multiple rows [message #487126 is a reply to message #487125] Wed, 22 December 2010 22:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You need to provide a copy and paste of everything, as I did. If the file is not in the default path, then you will need to provide the complete path. Your error message looks like you did something strange. I also noticed that you are using an outdated version, 8i. I will be logging out for the night soon.
Re: multiple rows [message #487127 is a reply to message #487125] Wed, 22 December 2010 23:06 Go to previous messageGo to next message
chandanacsr
Messages: 6
Registered: December 2010
Junior Member
cadd_pass.xml:
<?xml version="1.0" ?>
<ROWSET>
<MESSAGE_ID>123456789</MESSAGE_ID>
<INTERFACE_NAME>CAMS</INTERFACE_NAME>
<REQUEST_TYPE>ADD</REQUEST_TYPE>
<USER_NAME>SURINCI1</USER_NAME>
<USER_DETAILS>AJAYKUMAR SURI</USER_DETAILS>
<CONTACT_NO>1234567890</CONTACT_NO>
<OUC>DEH C</OUC>
<EIN>702031463</EIN>
<PASSWORD1>TEST1234</PASSWORD1>
<ROLE_NAME>FIELD TECHNICIAN</ROLE_NAME>
<HOME_AREA>LONDON (INNER)</HOME_AREA>
<HOME_PATCH>1LC4</HOME_PATCH>
<NATIONAL_ACCESS>NO</NATIONAL_ACCESS>
<AREAS>
<AREA_NAME>LONDON (INNER)</AREA_NAME>
<AREA_NAME>LONDON (OUTER)</AREA_NAME>
<AREA_NAME>NORTH EAST</AREA_NAME>
</AREAS>
</ROWSET>
</ENDLOB>


chandana_ctrl.ctl:
LOAD DATA
INFILE *
replace
INTO TABLE ctemp_tab
FIELDS TERMINATED BY ","
(ext_fname FILLER,
Data LOBFILE(ext_fname) terminated by eof
)
BEGINDATA:
cadd_pass.xml

sqlldr command:
sqlldr pirm_owner/pirm_owner control=chandana_ctrl.ctl data=cadd_pass.xml

output:
SQL*Loader: Release 8.1.7.4.0 - Production on Thu Dec 23 10:21:50 2010

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL*Loader-275: Data is in control file but "INFILE *" has not been specified.

SQL*Loader-502: unable to open data file '<?xml version="1.0" ?>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file '<ROWSET>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <MESSAGE_ID>123456789</MESSAGE_ID>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <INTERFACE_NAME>CAMS</INTERFACE_NAME>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <REQUEST_TYPE>ADD</REQUEST_TYPE>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <USER_NAME>SURINCI1</USER_NAME>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <USER_DETAILS>AJAYKUMAR SURI</USER_DETAILS>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <CONTACT_NO>1234567890</CONTACT_NO>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <OUC>DEH C</OUC>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <EIN>702031463</EIN>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <PASSWORD1>TEST1234</PASSWORD1>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <ROLE_NAME>FIELD TECHNICIAN</ROLE_NAME>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <HOME_AREA>LONDON (INNER)</HOME_AREA>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <HOME_PATCH>1LC4</HOME_PATCH>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <NATIONAL_ACCESS>NO</NATIONAL_ACCESS>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <AREAS>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <AREA_NAME>LONDON (INNER)</AREA_NAME>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <AREA_NAME>LONDON (OUTER)</AREA_NAME>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' <AREA_NAME>NORTH EAST</AREA_NAME>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file ' </AREAS>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file '</ROWSET>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file '</ENDLOB>' for field DATA table CTEMP_TAB
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
Commit point reached - logical record count 22


I have carried out all this using Shell scripting
Re: multiple rows [message #487129 is a reply to message #487127] Thu, 23 December 2010 01:01 Go to previous message
chandanacsr
Messages: 6
Registered: December 2010
Junior Member
i changed the script to:
sqlldr pirm_owner/pirm_owner control=chandana_ctrl.ctl data=cadd_pass.xml load=1



now its working Smile
thanks.

[Updated on: Thu, 23 December 2010 07:59] by Moderator

Report message to a moderator

Previous Topic: Export Hangs at 'exporting cluster definitions'
Next Topic: Data pump Export with query and date value
Goto Forum:
  


Current Time: Sun Sep 19 20:56:03 CDT 2021