Re: loading CLOB column using function

From: Baldwin <ibaldwinjr_at_gmail.com>
Date: Mon, 3 Dec 2007 11:18:05 -0800 (PST)
Message-ID: <ef1a14f6-d916-4074-8b01-c4800cb01fbb_at_e4g2000hsg.googlegroups.com>


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
>
> SQL>
>
> 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.



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 Received on Mon Dec 03 2007 - 20:18:05 CET

Original text of this message