Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Substring behavior with CLOB

746835Mar 24 2010 — edited Mar 24 2010
Hi,
we have this code
DECLARE 
    v_cCLOB     CLOB := 'TEST';
    v_cVARCHAR  VARCHAR2(500) := 'TEST';
    v_cCHAR     CHAR(500) := 'TEST';
BEGIN
    
--Trying to get substring beyond the actual amount of text    
--CLOB TEST
    IF NVL(SUBSTR(v_cCLOB, 376, 1), ' ') = ' ' THEN
        dbms_output.put_line('YES');
    ELSE
        dbms_output.put_line('NO');
    END IF;
    
    --VARCHAR TEST
    IF NVL(SUBSTR(v_cVARCHAR, 376, 1), ' ') = ' ' THEN
        dbms_output.put_line('YES');
    ELSE
        dbms_output.put_line('NO');
    END IF;
    
    --CHAR TEST
    IF NVL(SUBSTR(v_cCHAR, 376, 1), ' ') = ' ' THEN
        dbms_output.put_line('YES');
    ELSE
        dbms_output.put_line('NO');
    END IF;    
END;
 
we're expecting to get three YES as the result,
however we got NO, YES, YES for the result

Can we know why we got NO for the CLOB? what is behind the CLOB? Is there any particular value stored there?

thanks
This post has been answered by MichaelS on Mar 24 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 21 2010
Added on Mar 24 2010
6 comments
1,518 views