Solved

Oracle clob

Posted on 2006-06-16
12
5,387 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Independent Software Vendors: 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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

691 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