| Insert into CLOB and select [message #268463] |
Tue, 18 September 2007 15:26  |
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
Thanks in advance...
(I work with Oracle 10g)
|
|
|
|
|
|
| Re: Insert into CLOB and select [message #268699 is a reply to message #268468] |
Wed, 19 September 2007 07:26  |
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;
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 |
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 |
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 #268925 is a reply to message #268781] |
Thu, 20 September 2007 02:14 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
