Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle clob

Posted on 2006-06-16
12
Medium Priority
?
5,495 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 172 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 164 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 164 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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

610 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