ora 22275 [message #425315] |
Thu, 08 October 2009 10:43  |
tiagocscoelho
Messages: 3 Registered: October 2009 Location: madrid
|
Junior Member |
|
|
Hi!
I' ve this procedure
CREATE OR REPLACE
procedure test_clob ( l_clob OUT cLOB )is
v_str varchar2(30000);
begin
v_str := rpad('x',30000,'x') ;
dbms_lob.createtemporary(l_clob, TRUE, dbms_lob.session);
dbms_lob.write(l_clob, 1, length(v_str), v_str);
dbms_lob.freetemporary(l_clob);
end;
to execute it i've this code
DECLARE
L_CLOB CLOB;
BEGIN
TEST_CLOB(
L_CLOB => L_CLOB
);
DBMS_OUTPUT.PUT_LINE('L_CLOB = ' || L_CLOB);
END;
and I receive the error ora-22275 invalid lob locator.
but if i delete the line dbms i'm able to execute the procedure!
My question is, how can i see the value of the l_clob variable when i execute it?
thanks
|
|
|
|
|
Re: ora 22275 [message #425328 is a reply to message #425315] |
Thu, 08 October 2009 11:26  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Problem is you freed the clob before you returned it and thus the returned pointer is empty (uninitialized). See what happens when we remove the DBMS_LOB.freetemporary call.
SQL> CREATE OR REPLACE PROCEDURE test_clob (
2 l_clob OUT CLOB
3 ) IS
4 v_str VARCHAR2 ( 30000 );
5 BEGIN
6 v_str := RPAD (
7 'x'
8 , 30000
9 , 'x'
10 );
11 DBMS_LOB.createtemporary (
12 l_clob
13 , TRUE
14 , DBMS_LOB.SESSION
15 );
16 DBMS_LOB.WRITE (
17 l_clob
18 , 1
19 , LENGTH ( v_str )
20 , v_str
21 );
22 -- DBMS_LOB.freetemporary ( l_clob );
23 END;
24 /
Procedure created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 l_clob CLOB;
3 BEGIN
4 test_clob ( l_clob => l_clob );
5 DBMS_OUTPUT.put_line ('L_CLOB = ' || l_clob );
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 5
Now the clob is good, a clob pointer was actually returned by your call. Thus dbms_output is free to chock on the data.
Good luck, Kevin
|
|
|