LOB question

From: Jonathan Gennick <listmail_at_gennick.com>
Date: Wed, 29 May 2002 14:33:40 -0800
Message-ID: <F001.0046EECD.20020529143340@fatcity.com>

I'm very courious about some PL/SQL behavior with respect to LOBs. In PL/SQL, in Oracle9i, I can even do the following:

DECLARE
   directions CLOB;
BEGIN
   directions := EMPTY_CLOB();
   directions := 'M-28 to Munising, H-58 to Sand Point Road, '

              || 'Sand Point Road to visitor center, walk to falls.'; END; This really floored me, because I didn't expect to be able to assign a value to a CLOB variable without first selecting a LOB locator from the database. In fact, I can remove the call to EMPTY_CLOB, and the code still works! My first thought was that PL/SQL was implicitly creating a temporary LOB, but that doesn't appear to be the case.

I went on to write the following code. My call to EMPTY_CLOB() works, but my call to GETLENGTH fails.

SQL> DECLARE
  2 directions CLOB;
  3 BEGIN

  4     --Check to see whether directions is NULL
  5     IF directions IS NULL THEN
  6        DBMS_OUTPUT.PUT_LINE('directions is NULL');
  7     END IF;
  8     
  9     directions := EMPTY_CLOB();
 10     --DBMS_LOB.CREATETEMPORARY(directions, TRUE);
 11  
 12     --Verify that we now have an empty LOB
 13     IF DBMS_LOB.GETLENGTH(directions) = 0 THEN
 14        DBMS_OUTPUT.PUT_LINE('directions is empty');
 15         END IF;

 16 END;
 17 /
directions is NULL
DECLARE
*
ERROR at line 1:

ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 512
ORA-06512: at line 13

If I explicitly create a temporary LOB (below), then everything works. So I must not be getting a temporary LOB in my example above. Nor can I be getting a permenant LOB. So what the h--- am I getting?

SQL> DECLARE
  2 directions CLOB;
  3 BEGIN

  4     --Check to see whether directions is NULL
  5     IF directions IS NULL THEN
  6        DBMS_OUTPUT.PUT_LINE('directions is NULL');
  7     END IF;
  8     
  9     --directions := EMPTY_CLOB();
 10     DBMS_LOB.CREATETEMPORARY(directions, TRUE);
 11  
 12     --Verify that we now have an empty LOB
 13     IF DBMS_LOB.GETLENGTH(directions) = 0 THEN
 14        DBMS_OUTPUT.PUT_LINE('directions is empty');
 15         END IF;

 16 END;
 17 /
directions is NULL
directions is empty

Best regards,

Jonathan Gennick --- Brighten the corner where you are mailto:jonathan_at_gennick.com * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jonathan Gennick
  INET: listmail_at_gennick.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed May 29 2002 - 17:33:40 CDT