Home » SQL & PL/SQL » SQL & PL/SQL » ora 22275 (oracle 10.2.0.1.0)
ora 22275 [message #425315] Thu, 08 October 2009 10:43 Go to next message
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 #425317 is a reply to message #425315] Thu, 08 October 2009 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
22275, 00000, "invalid LOB locator specified"
// *Cause:  There are several causes:  (1) the LOB locator was never
//          initialized; (2) the locator is for a BFILE and the routine
//          expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
//          BLOB/CLOB/NCLOB and the routine expects a BFILE locator;
//          (4) trying to update the LOB in a trigger body -- LOBs in
//          trigger bodies are read only; (5) the locator is for a 
//          BFILE/BLOB and the routine expects a CLOB/NCLOB locator;
//          (6) the locator is for a CLOB/NCLOB and the routine expects 
//          a BFILE/BLOB locator;
// *Action: For (1), initialize the LOB locator by selecting into the locator
//          variable or by setting the LOB locator to empty.  For (2),(3),
//          (5) and (6)pass the correct type of locator into the routine.  
//          For (4), remove the trigger body code that updates the LOB value.
Re: ora 22275 [message #425325 is a reply to message #425315] Thu, 08 October 2009 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but if i delete the line dbms i'm able to execute the procedure!

If you delete the lines containing dbms it rests about nothing.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: ora 22275 [message #425328 is a reply to message #425315] Thu, 08 October 2009 11:26 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Initializing varray inside another varray
Next Topic: Oracle Package hangs while compiling (2)
Goto Forum:
  


Current Time: Sun Dec 11 02:17:53 CST 2016

Total time taken to generate the page: 0.11794 seconds