loading CLOB column using function

[Baldwin]

Hi,

I am a newbie of sql loader. Everything seems to be fine until I hit a
block road - the CLOB column type. I want to load data into the clob
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 the sql loader.

SQL*Loader-309: No SQL string allowed as part of "DATA" field
specification

DATA is my CLOB type column.

Almost all the references are suggesting to use a file to load data on
CLOB column 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

[DA Morgan]

Without posting the control file no help is possible.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Baldwin]

On Nov 30, 10:32 pm, DA Morgan <damor...@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...@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.

[DA Morgan]

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

SQL>

More information is required ... not much here makes sense.

--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington

damo...@x.washington.edu (replace x with u to respond)

[Baldwin]

> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide 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.
---------------------------

LOAD DATA
INFILE temp.csv'
REPLACE
INTO TABLE table1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(

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
begin

return 'here is my content!';
end GetContent;

---------------------------------
here is the script to create the table
create table table1 (
ID number,
content CLOB
)

baldwin