extract clob into a string

[diesel]

Hi

I am trying to create a script to use to populate a "new database" with the existing database table data.
The problem is the table has a clob column and I am experiencing problems.
Here is the sql command that worked before I changed the column(Function) in the table to clob.

select '#beginsql begin sp_InsertTaskFunction('''||TaskName||''','''||Function||''','''||Description||'''); end; #endsql' a from tasks;

I then use the output from this command to create a script which I run to populate the tasks table in future for a "new db".

sp_InsertTaskFunction is the stored proc that is run with the TaskName, Function,Description parameters as input.

It used to work fine untill I changed Function column to CLOB datatype.

Now I can't run the sql command anymore.
Error : inconsistent datatypes
I know the problem is that it expects a varchar/string when concatenating to the rest of the string.
How do i work around this or convert clob data easily in PL/SQL to string data.

Thanks

 

[D.]

Try CASTing the CLOB to be a VARCHAR2 in the query ...

eg.

SELECT CAST(function AS VARCHAR2(100)) a
FROM tasks

Let us know how you get on.

D.

 

[diesel]

The reason I changed the function column to CLOB is because of the limitation of the varchar2 to 4000 characters.
Even if I cast the output it will only return max of 4000 characters right?

A normal "select function from tasks" works fine.

The problem is that when I try to concatenate the output to a string as in the select statement I sent to you, it bombs out with the error "inconsistent datatypes".