Home » RDBMS Server » Server Utilities » Can not load LOB/XML in a nested table along with the parent table (ORACLE 10g,)
Can not load LOB/XML in a nested table along with the parent table [message #539728] Wed, 18 January 2012 04:53 Go to next message
yaggy
Messages: 21
Registered: January 2012
Junior Member
I have created a nested table as

create or replace TYPE "TEXT" IS TABLE OF CLOB;


My Table structure is

CREATE TABLE nESTED_TABLE
  (
    "ID"           NUMBER(38,0),
    "NAME"         VARCHAR2(100 BYTE),
    "COMMENTS" 	text ,
   
  )NESTED TABLE "COMMENTS" STORE AS "COMMENTS" ;


I am uploading the data through SQL Loader my
ctl file is

LOAD DATA
INFILE 'C:\nested_table.dat' 
BADFILE 'C:\nested_table.bad'
DISCARDFILE 'C:\nested_table.dsc'
INTO TABLE NESTED_TABLE
TRUNCATE
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
(ID,
NAME	"decode(:NAME,'\\\N',null, :NAME)",
COMMENTS	NESTED TABLE TERMINATED BY X'9' (group_name  terminated by ','))


I am getting the following error:
Can not load LOB/XML in a nested table along with the parent table.
another problem i am facing that
My dat file contains \N instead of Null values so while uploading the data in Comments field(Nested Column) I am not able to replace \N into Null using any sql strings.How can I fixed this error....
Following is my dat file

1 neham abcd,csadfasf,asfdfasdfad
2 sapnam \N


Thanks In Advance........


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Wed, 18 January 2012 14:40] by Moderator

Report message to a moderator

Re: Can not load LOB/XML in a nested table along with the parent table [message #539739 is a reply to message #539728] Wed, 18 January 2012 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

My advice is not not use nested tables to the relation model one: master/detail tables.
Using nested tables you will have many problems (starting by this one) and bad performances.

Regards
Michel
Re: Can not load LOB/XML in a nested table along with the parent table [message #539844 is a reply to message #539728] Wed, 18 January 2012 17:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Your data may have been modified by the forum. There appears to be spaces, not tabs, between columns, so I used whitespace as a delimiter, in the demonstration below. You also had an extra comma in your create table statement, so I fixed that in the demonstration below.

SQL*Loader has a lot of restrictions regarding nested tables, such as not being able to use lobs or SQL expressions with them. If you were to use varchar2(4000) instead of clob in your text type, that would eliminate the error. I assume that you are using clob because you need that size, so I have provided a workaround below, that loads into a staging table, then inserts the data from the staging table into the nested_table. It also enables you to check for \N for null values.

-- nested_table.dat:
1 neham abcd,csadfasf,asfdfasdfad
2 sapnam \N


-- test.ctl using default directory and
-- using whitespace as default delimiter instead of tab
-- and loading into staging table:
LOAD DATA
INFILE 'nested_table.dat'
BADFILE 'nested_table.bad'
DISCARDFILE 'nested_table.dsc'
INTO TABLE staging
TRUNCATE
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(ID,
NAME "decode(:NAME,'\\\N',null, :NAME)",
COMMENTS CHAR(50000))


-- staging table:
SCOTT@orcl_11gR2> CREATE TABLE staging
  2    ("ID"	    NUMBER(38,0),
  3  	"NAME"	    VARCHAR2(100 BYTE),
  4  	"COMMENTS"  CLOB)
  5  /

Table created.


-- load into staging table and results:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl

SCOTT@orcl_11gR2> column name	  format a15
SCOTT@orcl_11gR2> column comments format a40
SCOTT@orcl_11gR2> select * from staging
  2  /

        ID NAME            COMMENTS
---------- --------------- ----------------------------------------
         1 neham           abcd,csadfasf,asfdfasdfad
         2 sapnam          \N

2 rows selected.


-- your original type:
SCOTT@orcl_11gR2> create or replace TYPE "TEXT" IS TABLE OF clob;
  2  /

Type created.


-- nested_table (removed extra comma after text):
SCOTT@orcl_11gR2> CREATE TABLE nESTED_TABLE
  2    ("ID"	    NUMBER(38,0),
  3  	"NAME"	    VARCHAR2(100 BYTE),
  4  	"COMMENTS"  text )
  5    NESTED TABLE "COMMENTS" STORE AS "COMMENTS"
  6  /

Table created.


-- insert from staging table to nested_table:
SCOTT@orcl_11gR2> declare
  2    v_coms	  clob;
  3    v_comments text;
  4  begin
  5    for r in (select * from staging) loop
  6  	 v_comments := text();
  7  	 if r.comments != '\N' then
  8  	   v_coms := concat (r.comments, ',');
  9  	   while v_coms is not null loop
 10  	     v_comments.extend;
 11  	     v_comments(v_comments.last) :=
 12  	       dbms_lob.substr
 13  		 (v_coms,
 14  		  dbms_lob.instr (v_coms, ',') - 1,
 15  		  1);
 16  	     v_coms :=
 17  	       dbms_lob.substr
 18  		 (v_coms,
 19  		  dbms_lob.getlength (v_coms),
 20  		  dbms_lob.instr (v_coms, ',') + 1);
 21  	   end loop;
 22  	 end if;
 23  	 insert into nested_table (id, name, comments)
 24  	   values (r.id, r.name, v_comments);
 25    end loop;
 26  end;
 27  /

PL/SQL procedure successfully completed.


-- results displayed in two ways:
SCOTT@orcl_11gR2> select * from nested_table
  2  /

        ID NAME            COMMENTS
---------- --------------- ----------------------------------------
         1 neham           TEXT('abcd', 'csadfasf', 'asfdfasdfad')
         2 sapnam          TEXT()

2 rows selected.

SCOTT@orcl_11gR2> select nt.id, nt.name, t.column_value as comments
  2  from   nested_table nt,
  3  	    table (nt.comments) (+) t
  4  /

        ID NAME            COMMENTS
---------- --------------- ----------------------------------------
         1 neham           abcd
         1 neham           csadfasf
         1 neham           asfdfasdfad
         2 sapnam

4 rows selected.

Re: Can not load LOB/XML in a nested table along with the parent table [message #541368 is a reply to message #539728] Mon, 30 January 2012 22:53 Go to previous messageGo to next message
yaggy
Messages: 21
Registered: January 2012
Junior Member
Hi Michel,
I am migrating Postgresql database to Oracle.
In Postgre there are tables having array columns..
so what would be the best approach in oracle for migrating array datatype of Postgresql.

Thanks in Advance...
Re: Can not load LOB/XML in a nested table along with the parent table [message #541393 is a reply to message #541368] Tue, 31 January 2012 01:44 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the relational master/details tables instead that is (logically) store outside the master table the arrays.
(I said logically because anyway, behing the scene, Oracle store the arrays in detail table.)

Regards
Michel
Previous Topic: export/import xml records in oracle 11gR2
Next Topic: how to exclude streams
Goto Forum:
  


Current Time: Thu Mar 28 07:22:24 CDT 2024