OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » multiple rows (Sqlldr 8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

multiple rows [message #487057]

Wed, 22 December 2010 05:01

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?

Report message to a moderator

Re: multiple rows [message #487058 is a reply to message #487057]

Wed, 22 December 2010 05:09

Littlefoot
Messages: 21826
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?

Report message to a moderator

Re: multiple rows [message #487059 is a reply to message #487058]

Wed, 22 December 2010 05:12

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

Report message to a moderator

Re: multiple rows [message #487081 is a reply to message #487059]

Wed, 22 December 2010 09:47

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator

Re: multiple rows [message #487112 is a reply to message #487081]

Wed, 22 December 2010 13:01

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator

Re: multiple rows [message #487124 is a reply to message #487112]

Wed, 22 December 2010 21:30

chandanacsr
Messages: 6
Registered: December 2010

Junior Member

thanks a lot. i will try it!
Smile

Report message to a moderator

Re: multiple rows [message #487125 is a reply to message #487124]

Wed, 22 December 2010 22:33

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.

Report message to a moderator

Re: multiple rows [message #487126 is a reply to message #487125]

Wed, 22 December 2010 22:39

Barbara Boehmer
Messages: 9106
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.

Report message to a moderator

Re: multiple rows [message #487127 is a reply to message #487125]

Wed, 22 December 2010 23:06

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

Report message to a moderator

Re: multiple rows [message #487129 is a reply to message #487127]

Thu, 23 December 2010 01:01

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 13 09:24:43 CST 2026