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

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

On Nov 30, 10:32 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
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(aram1,aram2)"
)

I hope this helps.

Baldwin wrote:
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
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Dec 3, 2:02 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
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(aram1, aram2, :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