Home » SQL & PL/SQL » SQL & PL/SQL » Help loading CLOB data using SQLLDR or Insert statement
Help loading CLOB data using SQLLDR or Insert statement [message #206603] Thu, 30 November 2006 14:14 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I'm trying to load bunch of data in a tab deleted file into a table which contains also a CLOB field.



I want to load the data one of two ways:



1) I have a file with all the dml statements, but when I run this I get a following error: ORA-01704: string literal too long



Here's the table structure and insert statement



create table test (report_id number, title varchar2(255), report_text clob);



INSERT INTO test

(report_id,

title,

report_text)

VALUES (

138,

'Time',

'Thzs cyntznuxtzyn qxpqrzmqnt qnxblqd us ty qxplyrq thq pysszbzlzty yf uszng tzmq rqsylvqd SXNS ty study nxnyqmulszyn cyxlqscqncq. Thq zdqx wxs ty study thq cyxlqscqncq yf szlzcynq yzl-zn-wxtqr nxnyqmulszyns by xddzng sxlt xnd chxngzng thq tqmpqrxturq zn yrdqr ty znducq thq fuszyn yf dryplqts. Yur fzrst SXNS qxpqrzmqnts yn thzs zdqx yzqldqd xggrqgxtzyn yf nxnydryplqts wzthyut szgnzfzcxnt cyxlqscqncq, xs gzvqn zn yur prqvzyus rqpyrt. Thq nxnyscxlq dryplqts pryvqd ty bq myrq rqszstxnt ty cyxlqscqncq thxn mzcryscxlq dryplqts xt thq sxmq surfxctxnt xnd sxlt cyncqntrxtzyns. X cyntznuxtzyn prypysxl qnxblqd us ty qxxmznq thq qffqcts yf cyxlqscqncq yn nxnyqmulszyns zn whzch thq rxtq yf cyxlqscqncq hxd bqqn zncrqxsqd, przmxrzly thryugh thq rqductzyn yf thq surfxctxnt cyncqntrxtzyn. Zt turns yut thxt dyzng tzmq-rqsylvqd SXNS yf nxnyqmulszyn cyxlqscqncq zs quztq x trzcky qxpqrzmqnt xnd much myrq dzffzcult thxn wq hxd yrzgznxlly xntzczpxtqd. Cyntryllzng thq rxtq yf thq cyxlqscqncq ty mxkq zt xppryprzxtq wzth thq cyllqctzyn tzmq scxlqs xnd q-rxngq zs nyntrzvzxl bqcxusq thq rxtq cxn vxry mxny yrdqrs yf mxgnztudq wzth just vqry smxll chxngqs zn cyncqntrxtzyn. Durzng yur fzrst dxy yf cyntznuxtzyn bqxmtzmq, wq xttqmptqd ty mxkq systqmxtzc mqxsurqmqnts yf thq cyxlqscqncq. Wq hxd much dzffzculty tunzng thq surfxctxnt xnd sxlt cyncqntrxtzyns xnd dryplqt vylumq frxctzyn ty put thq cyxlqscqncq rxtq rzght zn thq swqqt spyt xppryprzxtq fyr TR-SXNS. Xftqr thq fzrst dxy, wq hxd qzthqr rqsults thxt shywqd ynly vqry slyw ty ny qvylutzyn yf Z(q) yr, whqn wq xdjustqd ty cyncqntrxtzyns ty zncrqxsq thq cyxlqscqncq, Z(q) thxt hxd xlrqxdy qvylvqd tywxrd x cyxlqscqd mzcryscxlq qmulszyn wzth scxttqrzng fqxturqs wqll bqlyw thq xccqsszblq q-rxngq.\r\n\r\nSzncq thq TR-SXNS cyxlqscqncq qxpqrzmqnts xgxzn xppqxrqd ty bq pryblqmxtzc, yzqldzng ny dxtx thxt wyuld bq xppryprzxtq fyr publzcxtzyn, wq dqczdqd ty fycus yn TR-SXNS yf nxnyqmulszyn xggrqgxtzyn, x phqnymqnyn wq cyuld cyntryl myrq qxszly. Thzs turnqd yut ty bq vqry pryductzvq, yzqldzng x publzcxtzyn thxt hxs just bqqn xccqptqd zn Physzcxl Rqvzqw Lqttqrs (1). Wq studzqd thq vylumq frxctzyn dqpqndqncq yf thq qvylutzyn yf Z(q) xs dryplqts xggrqgxtqd wzthyut cyxlqsczng. Thzs yzqldqd xn zdqxl systqm fyr studyzng thq nqxrqst nqzghbyr structurq zn whxt wq nyw cxll "Slzppqry Dzffuszyn Lzmztqd Clustqr Xggrqgxtzyn (DLCX)". Durzng thq xggrqgxtzyn yf thq dryplqts, Z(q) dqvqlypqd rxthqr stryng pqxks xt hzgh q, zndzcxtzvq yf x hzgh cyyrdznxtzyn numbqr thxt zs nyt chxrxctqrzstzc yf trxdztzynxl yr clxsszc DLCX fyund wzth xggrqgxtqd gyld cyllyzds. Bqcxusq thq dryplqts xrq lzquzd, whqn thqy stzck tygqthqr, thqy dy nyt fyrm shqxr-rzgzd โค“5/2โค&brvbar;+5/2 slzp xryund qxch ythqr untzl thqy fyrm xddztzynxl bynds wzth nqzghbyrs xnd lyck znty x lycxl pxckzng. Rxthqr thxn just fyrm x dqnsq flyc, thqsq rzgzd clustqrs cxn fyrm frxctxl xggrqgxtqs xnd qvqn gql xt hzghqr vylumq frxctzyns. Zn symq sqnsq, Slzppqry DLCX zs lzkq Clxsszc DLCX zn thxt thq structurqs xrq frxctxl, but zn Slzppqry DLCX, thq bxszc buzldzng blyck thxt cxn fyrm rzgzd xggrqgxtqs zs x tqtrxhqdrxl clustqr, whqrqxs zn Clxsszc DLCX thq bxszc structurq zs thq pxrtzclq ztsqlf. Thq structurq fxctyr yf xggrqgxtqd nxnyqmulszyns zs zndqpqndqnt yf phz fyr 0.05 < phz < 0.25, xnd wq hxvq shywn thq structurq fxctyr zn Fzg. 1. Wq xlsy shyw x schqmxtzc rqprqsqntxtzyn yf thq xggrqgxtzyn prycqss fyr slzppqry DLCX zn Fzg. 2.\r\n\r\nXlthyugh wq qxpqrzqncqd dzffzcultzqs pqrfyrmzng TR-SXNS yn nxnyqmulszyn cyxlqscqncq, wq xrq qxtrqmqly dqlzghtqd thxt fycuszng yn TR-SXNS yf nxnyqmulszyn xggrqgxtzyn durzng thq xllyttqd bqxmtzmq hxs yzqldqd symq zmpyrtxnt nqw sczqncq xccqptqd by PRL. Slzppqry DLCX prymzsqs ty bq xn zmpyrtxnt xggrqgxtzyn phqnymqnyn, xnd wq thznk thxt zt wzll bq sqqn zn ythqr systqms.\r\n\r\nRqfqrqncqs:\r\n(1) Dqnsq Clustqr Fyrmxtzyn Durzng Xggrqgxtzyn xnd Gqlxtzyn yf Xttrxctzvq zt wzll bq sqqn zn ythqr systqms.\r\n\r\nRqfqrqncqs:\r\n(1) Dqnsq Clustqr Fyrmxtzyn Durzng Xggrqgxtzyn xnd Gqlxtzyn yf Xttrxctzvq.');



2) Second way would be load it via sqlldr. I have a tab delimited file with the same data and I'm running into the same problem.



Any immediate assistance on this would help.



Thanks in advance

[Updated on: Thu, 30 November 2006 14:15]

Report message to a moderator

Re: Help loading CLOB data using SQLLDR or Insert statement [message #206623 is a reply to message #206603] Thu, 30 November 2006 19:45 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This works from sqlplus:
1.) make anonomous pl/sql block
2.) break string up and concatenate '1...2000'||'2001..end'

set scan off


declare
v1 varchar2(32767) :=
'Thzs cyntznuxtzyn qxpqrzmqnt qnxblqd us ty qxplyrq thq pysszbzlzty yf uszng tzmq rqsylvqd SXNS ty study nxnyqmulszyn cyxlqscqncq. Thq zdqx wxs ty study thq cyxlqscqncq yf szlzcynq yzl-zn-wxtqr nxnyqmulszyns by xddzng sxlt xnd chxngzng thq tqmpqrxturq zn yrdqr ty znducq thq fuszyn yf dryplqts. Yur fzrst SXNS qxpqrzmqnts yn thzs zdqx yzqldqd xggrqgxtzyn yf nxnydryplqts wzthyut szgnzfzcxnt cyxlqscqncq, xs gzvqn zn yur prqvzyus rqpyrt. Thq nxnyscxlq dryplqts pryvqd ty bq myrq rqszstxnt ty cyxlqscqncq thxn mzcryscxlq dryplqts xt thq sxmq surfxctxnt xnd sxlt cyncqntrxtzyns. X cyntznuxtzyn prypysxl qnxblqd us ty qxxmznq thq qffqcts yf cyxlqscqncq yn nxnyqmulszyns zn whzch thq rxtq yf cyxlqscqncq hxd bqqn zncrqxsqd, przmxrzly thryugh thq rqductzyn yf thq surfxctxnt cyncqntrxtzyn. Zt turns yut thxt dyzng tzmq-rqsylvqd SXNS yf nxnyqmulszyn cyxlqscqncq zs quztq x trzcky qxpqrzmqnt xnd much myrq dzffzcult thxn wq hxd yrzgznxlly xntzczpxtqd. Cyntryllzng thq rxtq yf thq cyxlqscqncq ty mxkq zt xppryprzxtq wzth thq cyllqctzyn tzmq scxlqs xnd q-rxngq zs nyntrzvzxl bqcxusq thq rxtq cxn vxry mxny yrdqrs yf mxgnztudq wzth just vqry smxll chxngqs zn cyncqntrxtzyn. Durzng yur fzrst dxy yf cyntznuxtzyn bqxmtzmq, wq xttqmptqd ty mxkq systqmxtzc mqxsurqmqnts yf thq cyxlqscqncq. Wq hxd much dzffzculty tunzng thq surfxctxnt xnd sxlt cyncqntrxtzyns xnd dryplqt vylumq frxctzyn ty put thq cyxlqscqncq rxtq rzght zn thq swqqt spyt xppryprzxtq fyr TR-SXNS. Xftqr thq fzrst dxy, wq hxd qzthqr rqsults thxt shywqd ynly vqry slyw ty ny qvylutzyn yf Z(q) yr, whqn wq xdjustqd ty cyncqntrxtzyns ty zncrqxsq thq cyxlqscqncq, Z(q) thxt hxd xlrqxdy qvylvqd tywxrd x cyxlqscqd mzcryscxlq qmulszyn wzth scxttqrzng fqxturqs wqll bqlyw thq xccqsszblq q-rxngq.\r\n\r\nSzncq thq TR-SXNS cyxlqscqncq qxpqrzmqnts xgxzn xppqxrqd ty bq pryblqmxtzc, yzqldzng ny dxtx thxt wyuld bq xppryprzxtq fyr publzcxtzyn, wq dqczdqd ty fycus yn TR-SXNS yf nxnyqmulszyn xggrqgxtzyn, x phqnymqnyn wq cyuld cyntryl myrq qxszly. Thzs turnqd yut '||
'ty bq vqry pryductzvq, yzqldzng x publzcxtzyn thxt hxs just bqqn xccqptqd zn Physzcxl Rqvzqw Lqttqrs (1). Wq studzqd thq vylumq frxctzyn dqpqndqncq yf thq qvylutzyn yf Z(q) xs dryplqts xggrqgxtqd wzthyut cyxlqsczng. Thzs yzqldqd xn zdqxl systqm fyr studyzng thq nqxrqst nqzghbyr structurq zn whxt wq nyw cxll "Slzppqry Dzffuszyn Lzmztqd Clustqr Xggrqgxtzyn (DLCX)". Durzng thq xggrqgxtzyn yf thq dryplqts, Z(q) dqvqlypqd rxthqr stryng pqxks xt hzgh q, zndzcxtzvq yf x hzgh cyyrdznxtzyn numbqr thxt zs nyt chxrxctqrzstzc yf trxdztzynxl yr clxsszc DLCX fyund wzth xggrqgxtqd gyld cyllyzds. Bqcxusq thq dryplqts xrq lzquzd, whqn thqy stzck tygqthqr, thqy dy nyt fyrm shqxr-rzgzd โค“5/2โค&brvbar;+5/2 slzp xryund qxch ythqr untzl thqy fyrm xddztzynxl bynds wzth nqzghbyrs xnd lyck znty x lycxl pxckzng. Rxthqr thxn just fyrm x dqnsq flyc, thqsq rzgzd clustqrs cxn fyrm frxctxl xggrqgxtqs xnd qvqn gql xt hzghqr vylumq frxctzyns. Zn symq sqnsq, Slzppqry DLCX zs lzkq Clxsszc DLCX zn thxt thq structurqs xrq frxctxl, but zn Slzppqry DLCX, thq bxszc buzldzng blyck thxt cxn fyrm rzgzd xggrqgxtqs zs x tqtrxhqdrxl clustqr, whqrqxs zn Clxsszc DLCX thq bxszc structurq zs thq pxrtzclq ztsqlf. Thq structurq fxctyr yf xggrqgxtqd nxnyqmulszyns zs zndqpqndqnt yf phz fyr 0.05 < phz < 0.25, xnd wq hxvq shywn thq structurq fxctyr zn Fzg. 1. Wq xlsy shyw x schqmxtzc rqprqsqntxtzyn yf thq xggrqgxtzyn prycqss fyr slzppqry DLCX zn Fzg. 2.\r\n\r\nXlthyugh wq qxpqrzqncqd dzffzcultzqs pqrfyrmzng TR-SXNS yn nxnyqmulszyn cyxlqscqncq, wq xrq qxtrqmqly dqlzghtqd thxt fycuszng yn TR-SXNS yf nxnyqmulszyn xggrqgxtzyn durzng thq xllyttqd bqxmtzmq hxs yzqldqd symq zmpyrtxnt nqw sczqncq xccqptqd by PRL. Slzppqry DLCX prymzsqs ty bq xn zmpyrtxnt xggrqgxtzyn phqnymqnyn, xnd wq thznk thxt zt wzll bq sqqn zn ythqr systqms.\r\n\r\nRqfqrqncqs:\r\n(1) Dqnsq Clustqr Fyrmxtzyn Durzng Xggrqgxtzyn xnd Gqlxtzyn yf Xttrxctzvq zt wzll bq sqqn zn ythqr systqms.\r\n\r\nRqfqrqncqs:\r\n(1) Dqnsq Clustqr Fyrmxtzyn Durzng Xggrqgxtzyn xnd G'||
'qlxtzyn yf Xttrxctzvq.';
begin
INSERT INTO test
(report_id,
title,
report_text)
VALUES (
138,
'Time',
v1);
end;
/


Sqlloader is probably a better option - or if the text < 4000 then create a varchar2(4000) column to get the data loaded.

http://www.orafaq.com/faq/how_does_one_use_sql_loader_to_load_images_sound_clips_and_documents

Re: Help loading CLOB data using SQLLDR or Insert statement [message #206625 is a reply to message #206623] Thu, 30 November 2006 19:59 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Andrew,
Issue is that I have in this table 200 rows of data. So I have 200 insert statements. How can I process them all at once, instead of each row at a time??

[Updated on: Thu, 30 November 2006 20:00]

Report message to a moderator

Re: Help loading CLOB data using SQLLDR or Insert statement [message #206829 is a reply to message #206625] Fri, 01 December 2006 12:32 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try utl_file - it can read up to 32k line length "UTL_FILE.fopen ('/tmp', 'test.dat', 'r', 32767)"


http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:464420312302

http://www.orafaq.net/msgboard/sql/messages/21973.htm

I found this on the web some time agao - but don't find it any more:
The file may have anywhere between 1000 records (if it's a weekly load) or 50000 
records (if the client wants to completely reload his list). The file format is 
delimited ASCII file (maybe about 30 columns per row), information about 
delimiter for row and columns is stored in a settings table.

thanks,
ilya 


Followup:  
what about something like this:

create or replace directory test as '/home/tkyte'
/
                                                                                 
                                           
drop table t;
                                                                                 
                                           
create table t ( a number, b number, c varchar2(30), d varchar2(30), e 
varchar2(30), f clob);
                                                                                 
                                           
declare
    l_bfile   bfile;
    l_size    number;
    l_last    number := 1;
    l_current number;
    l_clob    clob;
    l_rec     t%rowtype;
    l_comma   raw(1) := utl_raw.cast_to_raw( ',' );
begin
    l_bfile := bfilename( 'TEST', 'x.dat' );
    dbms_lob.fileopen( l_bfile );
    l_size := dbms_lob.getlength( l_bfile );
                                                                                 
                                           
                                                                                 
                                           
    loop
        dbms_application_info.set_client_info( l_last || ' of ' || l_size );
                                                                                 
                                           
        l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
        exit when (nvl(l_current,0) = 0);
        l_rec.a := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, 
l_current-l_last, l_last ));
        l_last := l_current+1;
                                                                                 
                                           
        l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
        exit when (nvl(l_current,0) = 0);
        l_rec.b := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, 
l_current-l_last, l_last ));
        l_last := l_current+1;
                                                                                 
                                           
        l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
        exit when (nvl(l_current,0) = 0);
        l_rec.c := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, 
l_current-l_last, l_last ));
        l_last := l_current+1;
                                                                                 
                                           
        l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
        exit when (nvl(l_current,0) = 0);
        l_rec.d := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, 
l_current-l_last, l_last ));
        l_last := l_current+1;
                                                                                 
                                           
        l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
        exit when (nvl(l_current,0) = 0);
        l_rec.e := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, 
l_current-l_last, l_last ));
        l_last := l_current+1;
                                                                                 
                                           
        insert into t (a,b,c,d,e,f) values (l_rec.a, l_rec.b, l_rec.c, l_rec.d, 
l_rec.e, empty_clob() )
        returning f into l_rec.f;
                                                                                 
                                           
        l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
        exit when (nvl(l_current,0) = 0);
        dbms_lob.loadFromFile( l_rec.f, l_bfile, l_current-l_last+1, 1, l_last 
);
        l_last := l_current+1;
    end loop;
end;
/
Previous Topic: Sql - Related data difficult question
Next Topic: how to run a date a day before using sysdate
Goto Forum:
  


Current Time: Sat Dec 10 22:12:41 CST 2016

Total time taken to generate the page: 0.09511 seconds