Home » SQL & PL/SQL » SQL & PL/SQL » Insert into CLOB and select
Insert into CLOB and select [message #268463] Tue, 18 September 2007 15:26 Go to next message
tomk
Messages: 17
Registered: March 2006
Junior Member
Hi all,

I have problem with using CLOB datatype. I prepaird table
create table clob_test(
id number,
txt clob default empty_clob()
);

I would like to insert into this table text from file (text length is more than 4000) - in fact it is long sql query, that I want to keep in database. As long as the text is shorter than 4000 I can use simple `insert into`, but for longer values it doesn't work...

Then, how can I do the select query for the clob? I tried simple
select txt from clob_test where id=1
, but I didn't get any results...

Thanks in advance...

(I work with Oracle 10g)
Re: Insert into CLOB and select [message #268468 is a reply to message #268463] Tue, 18 September 2007 15:54 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
If you are going to be working with CLOB's, then you would be well advised to read through the OracleŽ Database Application Developer's Guide - Large Objects.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/toc.htm
Re: Insert into CLOB and select [message #268699 is a reply to message #268468] Wed, 19 September 2007 07:26 Go to previous messageGo to next message
tomk
Messages: 17
Registered: March 2006
Junior Member
Ok, thanks, it was really helpful.

I managed to put long SQL query into the table(about 8000long) as clob.
Now I have would like to execute this query. The code which is presented below works fine - it puts the whole query taken from the clob into output. But how to execute this query?
create table clob_test (
    col_1  varchar2(10), 
    col_2 clob
);


declare

    v_size varchar2(5);
    v_val varchar2(4000);
    v_result varchar2(4000);

begin
    select dbms_lob.getlength(col_2) into v_size from clob_test where col_1 = '3';
    v_size := round(v_size/4000);
    for i in 0 .. v_size loop
    
    select dbms_lob.substr(col_2,4000,i*4000+1) into v_val from clob_test where col_1 = '3';
    dbms_output.put_line(v_val);
    
    end loop;
    
    ---execute immediate v_val into v_result;
--dbms_output.put_line(v_result);
end;
/

Usually I've been doing it by:
execute immediate v_val into v_result;
But this time the query can not be put into the varchar because it is too long.

Is there any way to do this?
Re: Insert into CLOB and select [message #268703 is a reply to message #268699] Wed, 19 September 2007 07:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) In pl/sql the size limit for a varchar2 is 32767, as opposed to the Sql limit of 4000.

2) Just declare v_result as type CLOB. Then you'll be able to select a Clob into it without any trouble.
Re: Insert into CLOB and select [message #268781 is a reply to message #268703] Wed, 19 September 2007 12:46 Go to previous messageGo to next message
tomk
Messages: 17
Registered: March 2006
Junior Member
Quote:
2) Just declare v_result as type CLOB. Then you'll be able to select a Clob into it without any trouble.

I tried to do this like that:
declare

    v_size varchar2(5);
    v_val clob;
    v_result clob;

begin
    select col_2 into v_val from clob_test where col_1 = '3';
--    dbms_output.put_line(v_val);
    
    execute immediate v_val into v_result;
--dbms_output.put_line(v_result);
end;
/

but I get an error:
ORA-06550: line 11, column 23:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored


...
Re: Insert into CLOB and select [message #268805 is a reply to message #268781] Wed, 19 September 2007 15:19 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
See the examples here
http://www.psoug.org/reference/dbms_lob.html
Re: Insert into CLOB and select [message #268925 is a reply to message #268781] Thu, 20 September 2007 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The error you're getting is at line 11, which looks to me like the EXECUTE IMMEDITATE.

Thinking about it, I'm pretty sure that Execute Immediate isn't overloaded to accept CLOBs as input.

Are your queries more than 32767 bytes long? If not, load them into a pl/sql varchar2 and use execute immediate on that.

If they arelonger than that then you could have a problem.
Re: Insert into CLOB and select [message #268932 is a reply to message #268925] Thu, 20 September 2007 02:27 Go to previous message
tomk
Messages: 17
Registered: March 2006
Junior Member
Result of the query is short, only the SQL query is long. I did it as you adviced and it works fine
declare

    v_size varchar2(5);
    v_val clob;
    v_result varchar2(1000);

begin
    select col_2 into v_val from clob_test where col_1 = '3';
    
    execute immediate v_val into v_result;
end;
/


Thanks.
Previous Topic: ORA-06533 While returning collection from function
Next Topic: change in data format by query for data extraction
Goto Forum:
  


Current Time: Sat Dec 03 08:07:22 CST 2016

Total time taken to generate the page: 0.11414 seconds