Oracle clob

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,
PraKashAsked:
Who is Participating?
 
paquicubaCommented:
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
 
actonwangCommented:
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
 
actonwangCommented:
look at (4), are you trying to update clob field in your trigger body?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
PraKashAuthor Commented:
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
 
MohanKNairCommented:
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
 
PraKashAuthor Commented:
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
 
actonwangCommented:
>> 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
 
MohanKNairCommented:
Try

If :NEW.colb_col is not null
THEN
...........
END IF;
0
 
paquicubaCommented:
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
 
MohanKNairCommented:
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.