Home » SQL & PL/SQL » SQL & PL/SQL » Need procedure to execute CLOB as paramter (oracle 11g)
| Need procedure to execute CLOB as paramter [message #623751] |
Sun, 14 September 2014 01:08  |
 |
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
I have proceudre with CLOB as paramter,but while executing i am getting the error
create or replace procedure test1(doc clob, result in out sys ref cursor)
as
sqlquery clob;
begin
sqlquery := sqlquery || 'select * from (select * from table1 t where id=101 and docid in ('''||doc||''') n )';
open result for sqlquery;
end;
procedure successfully executed;
exec test1('54678,4566,56666',:rt1);
getting errror
pls help in this procedure
|
|
|
|
| Re: Need procedure to execute CLOB as paramter [message #623753 is a reply to message #623751] |
Sun, 14 September 2014 01:48  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It's sys_refcursor, not sys ref cursor.
Since you are passing numeric values in your clob without quotes between them, you need to remove the extra quotes around them in sqlquery.
Your n alias is misplaced. In the example below, I have put it in the right place and prefaced the appropriate things with the aliases n and t.
If your doc parameter is a lengthy clob, then you will need to append using dbms_lob, not just concatenate.
In the future, it would help if you would provide a copy and paste of a complete reproduction, including create table and insert statements, and the error that you receive.
Please see the complete example, including corrected procedure code below.
SCOTT@orcl12c> create table table1
2 (id number,
3 docid number)
4 /
Table created.
SCOTT@orcl12c> insert all
2 into table1 values (101, 54678)
3 into table1 values (101, 4566)
4 into table1 values (101, 56666)
5 into table1 values (101, 101)
6 select * from dual
7 /
4 rows created.
SCOTT@orcl12c> create or replace procedure test1
2 (doc clob,
3 result in out sys_refcursor)
4 as
5 sqlquery clob;
6 begin
7 sqlquery := 'select n.* from (select t.* from table1 t where t.id=101 and t.docid in (';
8 dbms_lob.append (sqlquery, doc);
9 dbms_lob.writeappend (sqlquery, 4,')) n');
10 dbms_output.put_line (sqlquery);
11 open result for sqlquery;
12 end test1;
13 /
Procedure created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> variable rt1 refcursor
SCOTT@orcl12c> exec test1 ('54678,4566,56666', :rt1)
select n.* from (select t.* from table1 t where t.id=101 and t.docid in
(54678,4566,56666)) n
PL/SQL procedure successfully completed.
SCOTT@orcl12c> print rt1
ID DOCID
---------- ----------
101 54678
101 4566
101 56666
3 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need procedure to execute CLOB as paramter [message #623782 is a reply to message #623768] |
Sun, 14 September 2014 12:44 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could use one of many variations of Tom Kyte's str2tbl function to convert the clob to a virtual table that you can use in the dynamic query, as demonstrated below.
SCOTT@orcl12c> create table table1
2 (id number,
3 docid number)
4 /
Table created.
SCOTT@orcl12c> insert all
2 into table1 values (101, 54678)
3 into table1 values (101, 4566)
4 into table1 values (101, 56666)
5 into table1 values (101, 101)
6 select * from dual
7 /
4 rows created.
SCOTT@orcl12c> create or replace function str2tbl
2 (p_str in clob,
3 p_delim in varchar2 default ',')
4 return sys.odcivarchar2list pipelined
5 as
6 l_str clob default p_str || p_delim;
7 l_n number;
8 begin
9 loop
10 l_n := instr (l_str, ',');
11 exit when nvl (l_n, 0) = 0;
12 pipe row (ltrim (rtrim (substr (l_str, 1, l_n - 1))));
13 l_str := ltrim (substr (l_str, l_n + length (p_delim)));
14 end loop;
15 return;
16 end str2tbl;
17 /
Function created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> select * from table (str2tbl ('54678,4566,56666'))
2 /
COLUMN_VALUE
--------------------------------------------------------------------------------
54678
4566
56666
3 rows selected.
SCOTT@orcl12c> create or replace procedure test1
2 (doc clob,
3 result in out sys_refcursor)
4 as
5 sqlquery clob;
6 begin
7 sqlquery :=
8 'select n.*
9 from (select t.*
10 from table1 t
11 where t.id=101
12 and t.docid in
13 (select *
14 from table (str2tbl (:doc)))) n';
15 open result for sqlquery using doc;
16 end test1;
17 /
Procedure created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> variable rt1 refcursor
SCOTT@orcl12c> exec test1 ('54678,4566,56666', :rt1)
PL/SQL procedure successfully completed.
SCOTT@orcl12c> print rt1
ID DOCID
---------- ----------
101 54678
101 4566
101 56666
3 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need procedure to execute CLOB as paramter [message #623811 is a reply to message #623808] |
Mon, 15 September 2014 02:41 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is an example, passing the values as a clob of length greater than 38000, which does not change the length of the query.
SCOTT@orcl12c> create table table1
2 (id number,
3 docid number)
4 /
Table created.
SCOTT@orcl12c> insert all
2 into table1 values (101, 54678)
3 into table1 values (101, 4566)
4 into table1 values (101, 56666)
5 select * from dual
6 /
3 rows created.
SCOTT@orcl12c> create or replace function str2tbl
2 (p_str in clob,
3 p_delim in varchar2 default ',')
4 return sys.odcivarchar2list pipelined
5 as
6 l_str clob default p_str || p_delim;
7 l_n number;
8 begin
9 loop
10 l_n := instr (l_str, ',');
11 exit when nvl (l_n, 0) = 0;
12 pipe row (ltrim (rtrim (substr (l_str, 1, l_n - 1))));
13 l_str := ltrim (substr (l_str, l_n + length (p_delim)));
14 end loop;
15 return;
16 end str2tbl;
17 /
Function created.
SCOTT@orcl12c> create or replace procedure test1
2 (doc clob,
3 result in out sys_refcursor)
4 as
5 sqlquery clob;
6 begin
7 sqlquery :=
8 'select n.*
9 from (select t.*
10 from table1 t
11 where t.id=101
12 and t.docid in
13 (select *
14 from table (str2tbl (:doc)))) n';
15 open result for sqlquery using doc;
16 end test1;
17 /
Procedure created.
SCOTT@orcl12c> variable rt1 refcursor
SCOTT@orcl12c> declare
2 v_clob clob;
3 i number := 0;
4 begin
5 dbms_lob.createtemporary (v_clob, false);
6 dbms_lob.writeappend (v_clob, 17, '54678,4566,56666,');
7 while length (v_clob) <= 38000 loop
8 i := i + 1;
9 dbms_lob.writeappend (v_clob, length (i) + 1, i || ',');
10 end loop;
11 v_clob := rtrim (v_clob, ',');
12 dbms_output.put_line (length (v_clob));
13 test1 (v_clob, :rt1);
14 dbms_lob.freetemporary (v_clob);
15 end;
16 /
38004
PL/SQL procedure successfully completed.
SCOTT@orcl12c> print rt1
ID DOCID
---------- ----------
101 54678
101 4566
101 56666
3 rows selected.
|
|
|
|
| Re: Need procedure to execute CLOB as paramter [message #623812 is a reply to message #623810] |
Mon, 15 September 2014 02:44 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
mvrkr44 wrote on Mon, 15 September 2014 00:35hi ,
I am executing the same query in my test db using TOAD.
I got the below error,if i pass more than 38000 characters in clob
Ora-06550:line1 ,column 20;
PLS-00172:String literal too long
Don't test it in Toad. Test it in SQL*Plus. You need to show how you are generating the 38000 characters in the clob, as that is where the problem lies. If you are actually using a clob, then it is not the length, but how you are creating it. You will need to append to the clob, not concatenate, as I have demonstrated.
|
|
|
|
|
|
| Re: Need procedure to execute CLOB as paramter [message #623814 is a reply to message #623813] |
Mon, 15 September 2014 02:56 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you pass the values as a string literal, then it will be a different query each time, and each one will be stored in the SGA and not re-used.
However, if you pass the values as a clob variable, as I did in my last example, then you will be executing the same query each time, so it will be stored in the SGA only once, so it won't clutter the SGA, and it will be re-used each time you run the query, so each query will be faster than the first run.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need procedure to execute CLOB as paramter [message #623839 is a reply to message #623836] |
Mon, 15 September 2014 05:42 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Sorry Michel.
I formatted my code .
Can i use the code like below,is there any SGA impact?
SCOTT@orcl12c> create or replace procedure test1
2 (doc clob,
3 result in out sys_refcursor)
4 as
5 sqlquery clob;
doc1 clob;
6 begin
doc1 :=doc;
7 sqlquery :=
8 'select n.*
9 from (select t.*
10 from table1 t
11 where t.id=101
12 and t.docid in
13 (select *
14 from table (str2tbl (:doc1)))) n';
15 open result for sqlquery using doc1;
16 end test1;
17 /
[Updated on: Mon, 15 September 2014 05:45] Report message to a moderator |
|
|
|
|
|
| Re: Need procedure to execute CLOB as paramter [message #623843 is a reply to message #623841] |
Mon, 15 September 2014 06:04 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Hi Michel,
Thanks for your suggestion.This time i did the same as you explained
CREATE OR replace PROCEDURE Test1 (doc CLOB,
result IN OUT SYS_REFCURSOR)
AS
sqlquery CLOB;
doc1 CLOB;
BEGIN
doc1 := doc;
sqlquery :=
'select n.*from (select t.* from table1 t where t.id=101
and t.docid in (select * from table (str2tbl (:doc1)))) n';
OPEN result FOR sqlquery USING doc1;
END test1;
/
[Updated on: Mon, 15 September 2014 06:05] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 12:06:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
