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  |
 |
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 #539844 is a reply to message #539728] |
Wed, 18 January 2012 17:28  |
 |
Barbara Boehmer
Messages: 9104
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 20:18:57 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
