On Dec 3, 2:02 pm, DA Morgan <damor...@[EMAIL PROTECTED]
> wrote:
> Baldwin wrote:
> > On Nov 30, 10:32 pm, DA Morgan <damor...@[EMAIL PROTECTED]
> 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 Wa****ngton
> >> damor...@[EMAIL PROTECTED]
(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
>
> SQL>
>
> More information is required ... not much here makes sense.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Wa****ngton
> damor...@[EMAIL PROTECTED]
(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.
---------------------------
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
|
