Solved

Oracle clob

Posted on 2006-06-16
12
5,361 Views
Last Modified: 2012-05-05
Hi,

I have oracle clob column and I need to check if there is any data in it or not.
I tried DBMS_LOB.GETLENGTH(:NEW.field1)=0, its giving
SQL error code: 22275
Error message: ORA-22275: invalid LOB locator specified

Regards,
0
Comment
Question by:PraKash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 43 total points
ID: 16922956
Can you post more code...

You need to initialized your CLOB using the EMPTY_CLOB() function, which returns an empty LOB locator that can be used to initialize a LOB variable. At this point the CLOB is not populated with data but the locator is known
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 41 total points
ID: 16923376
ORA-22275:      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.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16923381
look at (4), are you trying to update clob field in your trigger body?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:PraKash
ID: 16924186
I am not trying to update the clob value. I just want to know the clob column is populateed or not.
paquicuba, can you give me an example of initializing with empty_clob() and the finding out if it is populated or not?
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16926609
using sql
----------

select id from TableA where clob_col is null;
or
select id from TableA where clob_col is not null and getlength(clob_col)=0;

in PL/SQL
----------

declare
c1 clob
begin
if c1 is null
then
c1 := empty_clob();
end if;
end;
/
0
 

Author Comment

by:PraKash
ID: 16935567
Mohan,

I am using it in a trigger trying to find out if the clob field is empty or populated.
dbms_log.getlength keeps giving me the error that I had mentioned before.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16935624
>> DBMS_LOB.GETLENGTH(:NEW.field1)
     maybe your problem is that :NEW.field1.  field1 might not be initialized yet this time.
     I don'th have time to test it now but it might be the problem..      

      I guess you are doing the before update trigger, try to do it using after trigger.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16935926
Try

If :NEW.colb_col is not null
THEN
...........
END IF;
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16939028
If you want to know if the column you're updating is poplated or not you need to test DBMS_LOB.GETLENGTH(:OLD.field1) IS NULL  OR  OLD:field1 IS NULL instead of DBMS_LOB.GETLENGTH(:NEW.field1)=0

If you want to check when an empty clob is updated with a null value then you need: DBMS_LOB.GETLENGTH(:NEW.field1) IS NULL OR :NEW.field1 IS NULL
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 41 total points
ID: 16939488
See the results from the following code

SQL> declare
  2  c1 clob;
  3  begin
  4  if c1 is null
  5  then
  6  dbms_output.put_line('c1 is null');
  7  end if;
  8  if dbms_lob.getlength(c1)=0
  9  then
 10  dbms_output.put_line('Length of c1 = 0');
 11  end if;
 12  end;
 13  /
c1 is null

PL/SQL procedure successfully completed.

SQL>
SQL>SQL> ed
Wrote file afiedt.buf

  1  declare
  2  c1 clob := empty_clob();
  3  begin
  4  if c1 is null
  5  then
  6  dbms_output.put_line('c1 is null');
  7  end if;
  8  if dbms_lob.getlength(c1)=0
  9  then
 10  dbms_output.put_line('Length of c1 = 0');
 11  end if;
 12* end;
SQL> /
declare
*
ERROR at line 1:
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 541
ORA-06512: at line 8

SQL>
SQL> ed
Wrote file afiedt.buf

  1  declare
  2  c1 clob := empty_clob();
  3  begin
  4  if c1 is null
  5  then
  6  dbms_output.put_line('c1 is null');
  7  end if;
  8  --if dbms_lob.getlength(c1)=0
  9  --then
 10  --dbms_output.put_line('Length of c1 = 0');
 11  --end if;
 12* end;
SQL>

PL/SQL procedure successfully completed.



if c1 is null is a valid command to check whether an LOB is null.  If an LOB is initialized using empty_clob then dbms_lob.getlength will throw an error.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20294469
Forced accept.

Computer101
EE Admin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question