Solved

Oracle clob

Posted on 2006-06-16
12
5,308 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now