Re: loading CLOB column using function
Date: Mon, 3 Dec 2007 11:18:05 -0800 (PST)
On Dec 3, 2:02 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Baldwin wrote:
> > On Nov 30, 10:32 pm, DA Morgan <damor..._at_psoug.org> wrote:
> >> Baldwin wrote:
> >>> Hi,
> >>> I am a newbie ofsqlloader. Everything seems to be fine until I hit a
> >>> block road - theCLOBcolumn type. I want to load data into theclob
> >>> column using a stored function. I need to do some manipulation on the
> >>> data before it gets saved to that column. But I got this error when I
> >>> run thesqlloader.
> >>> SQL*Loader-309: NoSQLstring allowed as part of "DATA" field
> >>> specification
> >>> DATA is myCLOBtype column.
> >>> Almost all the references are suggesting to use a file to load data on
> >>> CLOBcolumn but what I want to use a function in which it generates
> >>> the content to be saved into the column.
> >>> Any help is greatly appreciated.
> >>> Baldwin
> >>> MISICompany
> >> Without posting the control file no help is possible.
> >> --
> >> Daniel A. Morgan
> >> Oracle Ace Director & Instructor
> >> University of Washington
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
> >> - Show quoted text -
> > here is the control file
> > LOAD DATA
> > INFILE 'temp.csv'
> > REPLACE
> > INTO TABLE table1
> > FIELDS TERMINATED BY ','
> > OPTIONALLY ENCLOSED BY '"'
> > TRAILING NULLCOLS
> > (
> > Index FILLER position(1:2),
> > param1 BOUNDFILLER,
> > param2 BOUNDFILLER,
> > "DATA" "GetContent(:param1,:param2)"
> > )
> > I hope this helps.
> A column named "INDEX"? Another named "DATA"? Should we put a suicide
> watch on you? Not one of your column name corresponds with naming rules.
> Give some serious consideration to changing them to something meaningful.
> Perhaps I am missing something about your demo but I get stuck
> trying to build the table into which it appears you are trying
> to load this:
> SQL> create table table1 (
> 2 index VARCHAR2(5));
> index VARCHAR2(5))
> ERROR at line 2:
> ORA-00904: : invalid identifier
> More information is required ... not much here makes sense.
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
> - Show quoted text -
sorry, thats not my actual table and control file. i cant post it in
here coz it's huge.
i am jsut trying to simulate what i have.
INTO TABLE table1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ID EXPRESSION "someidseq.nextval", param1 BOUNDFILLER, param2 BOUNDFILLER, content "GetContent(:param1, :param2, :content)")
here is the temp.csv
A, AA, test
B, BB, testagain
here is a stored function:
create or replace function GetContent(param1 varchar2,
param2 varchar2, param3 varchar2)
return varchar2 is
return 'here is my content!';
here is the script to create the table
create table table1 (
baldwin Received on Mon Dec 03 2007 - 20:18:05 CET