OraFAQ Forum: SQL & PL/SQL » Issue with DBMS

Home » SQL & PL/SQL » SQL & PL/SQL » Issue with DBMS_LOB.SubStr (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issue with DBMS_LOB.SubStr [message #629833]

Tue, 16 December 2014 06:13

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi All,

I'm facing an issue while breaking CLOB to text chunks using DBMS_LOB.SubStr.

Everything works fine if I break them in small chunks eg. 5000 characters. But If I want to break them to larger chunks let us say 15000 characters or greater then I'm not exactly getting exact number of characters returned by SUBSTR function.

Please advise.

CREATE TABLE TEST_CLOB
(
    ClobText     CLOB
) ;

Insert a very large text value in TEST_CLOB table. Let us say about 300,000 characters.

Code Piece 1 :

DECLARE
    l_CLOB    CLOB := EMPTY_CLOB() ;
    ls_Text   VARCHAR2(32767) ;
    ln_Total  NUMBER(15) := 0 ;
BEGIN
    SELECT ClobText
    INTO l_CLOB 
    FROM TEST_CLOB ;

    FOR I IN (
                WITH
                    X as
                    (
                        SELECT DBMS_LOB.GetLength(ClobText) ClobLength, 5000 FixLength
                        FROM TEST_CLOB
                    )
                SELECT LEVEL - 1 Loop_Count, FixLength, ((LEVEL - 1) * FixLength) + 1 RunningValue
                FROM DUAL, x
                CONNECT BY LEVEL <= (CEIL(ClobLength / FixLength) + 1) 
            )
    LOOP
        ls_Text := DBMS_LOB.SubStr(l_CLOB, I.FixLength, I.RunningValue ) ;
        DBMS_OUTPUT.PUT_LINE( 'STRING LENGTH = ' || LENGTH(ls_Text) ) ;
        ln_Total := ln_Total + NVL(LENGTH(ls_Text),0) ;
    END LOOP ;

    DBMS_OUTPUT.PUT_LINE( 'CLOB LENGTH  = ' || DBMS_LOB.GetLength(L_CLOB) ) ;
    DBMS_OUTPUT.PUT_LINE( 'TOTAL LENGTH = ' || ln_Total ) ;

END ;

Code Piece 2 :

DECLARE
    l_CLOB    CLOB := EMPTY_CLOB() ;
    ls_Text   VARCHAR2(32767) ;
    ln_Total  NUMBER(15) := 0 ;
BEGIN
    SELECT ClobText
    INTO l_CLOB 
    FROM TEST_CLOB ;

    FOR I IN (
                WITH
                    X as
                    (
                        SELECT DBMS_LOB.GetLength(ClobText) ClobLength, 32767 FixLength
                        FROM TEST_CLOB
                    )
                SELECT LEVEL - 1 Loop_Count, FixLength, ((LEVEL - 1) * FixLength) + 1 RunningValue
                FROM DUAL, x
                CONNECT BY LEVEL <= (CEIL(ClobLength / FixLength) + 1) 
            )
    LOOP
        ls_Text := DBMS_LOB.SubStr(l_CLOB, I.FixLength, I.RunningValue ) ;
        DBMS_OUTPUT.PUT_LINE( 'STRING LENGTH = ' || LENGTH(ls_Text) ) ;
        ln_Total := ln_Total + NVL(LENGTH(ls_Text),0) ;
    END LOOP ;

    DBMS_OUTPUT.PUT_LINE( 'CLOB LENGTH  = ' || DBMS_LOB.GetLength(L_CLOB) ) ;
    DBMS_OUTPUT.PUT_LINE( 'TOTAL LENGTH = ' || ln_Total ) ;

END ;

Ideally "CLOB LENGTH" and "TOTAL LENGTH" must be same value. But for large chunks there is a mismatch.
Just execute Code Piece 1 and 2 to see the difference.

Thanks & Regards
Manoj

Report message to a moderator

Re: Issue with DBMS_LOB.SubStr [message #629838 is a reply to message #629833]

Tue, 16 December 2014 06:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Just execute Code Piece 1 and 2 to see the difference.

We obviously can't without making our test case so, in the meantime, show us what you get.

Report message to a moderator

Re: Issue with DBMS_LOB.SubStr [message #629883 is a reply to message #629838]

Tue, 16 December 2014 22:12

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Output of Code Piece 1 :

STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 1638
STRING LENGTH = 
CLOB LENGTH  = 266638
TOTAL LENGTH = 266638

Output of Code Piece 2 :

STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 4502
STRING LENGTH = 
CLOB LENGTH  = 266638
TOTAL LENGTH = 204430

Report message to a moderator

Re: Issue with DBMS_LOB.SubStr [message #629893 is a reply to message #629883]

Wed, 17 December 2014 01:54

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No problem for me:

SQL> CREATE TABLE TEST_CLOB
  2  (
  3      ClobText     CLOB
  4  ) ;

Table created.

SQL>
SQL> insert into test_clob values (rpad('A',4000,'A'));

1 row created.

SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL> update test_clob set clobtext = clobtext || rpad('A',4000,'A') || rpad('A',4000,'A') || rpad('A',2638,'A');

1 row updated.

SQL> select length(clobtext) from test_clob;
LENGTH(CLOBTEXT)
----------------
          266638

1 row selected.

SQL> commit;

Commit complete.

SQL>
SQL> DECLARE
  2      l_CLOB    CLOB := EMPTY_CLOB() ;
  3      ls_Text   VARCHAR2(32767) ;
  4      ln_Total  NUMBER(15) := 0 ;
  5  BEGIN
  6      SELECT ClobText
  7      INTO l_CLOB
  8      FROM TEST_CLOB ;
  9
 10      FOR I IN (
 11                  WITH
 12                      X as
 13                      (
 14                          SELECT DBMS_LOB.GetLength(ClobText) ClobLength, 5000 FixLength
 15                          FROM TEST_CLOB
 16                      )
 17                  SELECT LEVEL - 1 Loop_Count, FixLength, ((LEVEL - 1) * FixLength) + 1 RunningValue
 18                  FROM DUAL, x
 19                  CONNECT BY LEVEL <= (CEIL(ClobLength / FixLength) + 1)
 20              )
 21      LOOP
 22          ls_Text := DBMS_LOB.SubStr(l_CLOB, I.FixLength, I.RunningValue ) ;
 23          DBMS_OUTPUT.PUT_LINE( 'STRING LENGTH = ' || LENGTH(ls_Text) ) ;
 24          ln_Total := ln_Total + NVL(LENGTH(ls_Text),0) ;
 25      END LOOP ;
 26
 27      DBMS_OUTPUT.PUT_LINE( 'CLOB LENGTH  = ' || DBMS_LOB.GetLength(L_CLOB) ) ;
 28      DBMS_OUTPUT.PUT_LINE( 'TOTAL LENGTH = ' || ln_Total ) ;
 29
 30  END ;
 31  /
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 1638
STRING LENGTH =
CLOB LENGTH  = 266638
TOTAL LENGTH = 266638

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      l_CLOB    CLOB := EMPTY_CLOB() ;
  3      ls_Text   VARCHAR2(32767) ;
  4      ln_Total  NUMBER(15) := 0 ;
  5  BEGIN
  6      SELECT ClobText
  7      INTO l_CLOB
  8      FROM TEST_CLOB ;
  9
 10      FOR I IN (
 11                  WITH
 12                      X as
 13                      (
 14                          SELECT DBMS_LOB.GetLength(ClobText) ClobLength, 32767 FixLength
 15                          FROM TEST_CLOB
 16                      )
 17                  SELECT LEVEL - 1 Loop_Count, FixLength, ((LEVEL - 1) * FixLength) + 1 RunningValue
 18                  FROM DUAL, x
 19                  CONNECT BY LEVEL <= (CEIL(ClobLength / FixLength) + 1)
 20              )
 21      LOOP
 22          ls_Text := DBMS_LOB.SubStr(l_CLOB, I.FixLength, I.RunningValue ) ;
 23          DBMS_OUTPUT.PUT_LINE( 'STRING LENGTH = ' || LENGTH(ls_Text) ) ;
 24          ln_Total := ln_Total + NVL(LENGTH(ls_Text),0) ;
 25      END LOOP ;
 26
 27      DBMS_OUTPUT.PUT_LINE( 'CLOB LENGTH  = ' || DBMS_LOB.GetLength(L_CLOB) ) ;
 28      DBMS_OUTPUT.PUT_LINE( 'TOTAL LENGTH = ' || ln_Total ) ;
 29
 30  END ;
 31  /
STRING LENGTH = 32767
STRING LENGTH = 32767
STRING LENGTH = 32767
STRING LENGTH = 32767
STRING LENGTH = 32767
STRING LENGTH = 32767
STRING LENGTH = 32767
STRING LENGTH = 32767
STRING LENGTH = 4502
STRING LENGTH =
CLOB LENGTH  = 266638
TOTAL LENGTH = 266638

PL/SQL procedure successfully completed.

As in your case 24991 != 32767, I suspect, if you really use this code, the you have a variable-length character set like AL32UTF8 and have some characters on several bytes.

Report message to a moderator

Re: Issue with DBMS_LOB.SubStr [message #629896 is a reply to message #629893]

Wed, 17 December 2014 02:30

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi Michel,

Earlier I was using SQL Developer to run my scripts. This time I've used SQL *Plus and below is script execution.

There is still some difference in Number of Characters for Script 2. I've populated CLOB with same set of code you posted here.
Please help me to figure out is it some oracle environment related issue or what.

SQL> CREATE TABLE TEST_CLOB
(
  2    3        ClobText     CLOB
  4  ) ;

insert into test_clob values (rpad('A',4000,'A'));

Table created.

SQL> SQL>
update test_clob set clobtext = clobtext || clobtext;

1 row created.

SQL> SQL>
1 row updated.

SQL>
SQL> update test_clob set clobtext = clobtext || clobtext;


1 row updated.

SQL> SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL>
SQL> update test_clob set clobtext = clobtext || clobtext;

1 row updated.

SQL>
SQL> update test_clob set clobtext = clobtext || clobtext;

update test_clob set clobtext = clobtext || clobtext;


1 row updated.

SQL> SQL> update test_clob set clobtext = clobtext || rpad('A',4000,'A') || rpad('A',4000,'A') || rpad('A',2638,'A');

commit;


1 row updated.

SQL> SQL> select length(clobtext) from test_clob;



1 row updated.

SQL> SQL>
Commit complete.

SQL> SQL>
LENGTH(CLOBTEXT)
----------------
          266638

SQL> SQL> SQL>
SQL> set serveroutput on
SQL> DECLARE
    l_CLOB    CLOB := EMPTY_CLOB() ;
  2    3      ls_Text   VARCHAR2(32767) ;
  4      ln_Total  NUMBER(15) := 0 ;
  5  BEGIN
    SELECT ClobText
  6    7      INTO l_CLOB
  8      FROM TEST_CLOB ;

  9   10      FOR I IN (
 11                  WITH
 12                      X as
 13                      (
 14                          SELECT DBMS_LOB.GetLength(ClobText) ClobLength, 5000 FixLength
 15                          FROM TEST_CLOB
 16                      )
 17                  SELECT LEVEL - 1 Loop_Count, FixLength, ((LEVEL - 1) * FixLength) + 1 RunningValue
 18                  FROM DUAL, x
 19                  CONNECT BY LEVEL <= (CEIL(ClobLength / FixLength) + 1)
 20              )
 21      LOOP
        ls_Text := DBMS_LOB.SubStr(l_CLOB, I.FixLength, I.RunningValue ) ;
 22   23          DBMS_OUTPUT.PUT_LINE( 'STRING LENGTH = ' || LENGTH(ls_Text) ) ;
 24          ln_Total := ln_Total + NVL(LENGTH(ls_Text),0) ;
 25      END LOOP ;

    DBMS_OUTPUT.PUT_LINE( 'CLOB LENGTH  = ' || DBMS_LOB.GetLength(L_CLOB) ) ;
 26   27   28      DBMS_OUTPUT.PUT_LINE( 'TOTAL LENGTH = ' || ln_Total ) ;
 29
 30  END ;
 31
 32  /
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 5000
STRING LENGTH = 1638
STRING LENGTH =
CLOB LENGTH  = 266638
TOTAL LENGTH = 266638

PL/SQL procedure successfully completed.

SQL> DECLARE
    l_CLOB    CLOB := EMPTY_CLOB() ;
  2    3      ls_Text   VARCHAR2(32767) ;
  4      ln_Total  NUMBER(15) := 0 ;
  5  BEGIN
  6      SELECT ClobText
    INTO l_CLOB
  7    8      FROM TEST_CLOB ;
  9
 10      FOR I IN (
 11                  WITH
 12                      X as
 13                      (
 14                          SELECT DBMS_LOB.GetLength(ClobText) ClobLength, 32767 FixLength
 15                          FROM TEST_CLOB
 16                      )
 17                  SELECT LEVEL - 1 Loop_Count, FixLength, ((LEVEL - 1) * FixLength) + 1 RunningValue
 18                  FROM DUAL, x
 19                  CONNECT BY LEVEL <= (CEIL(ClobLength / FixLength) + 1)
 20              )
 21      LOOP
 22          ls_Text := DBMS_LOB.SubStr(l_CLOB, I.FixLength, I.RunningValue ) ;
 23          DBMS_OUTPUT.PUT_LINE( 'STRING LENGTH = ' || LENGTH(ls_Text) ) ;
 24          ln_Total := ln_Total + NVL(LENGTH(ls_Text),0) ;
 25      END LOOP ;
 26
 27      DBMS_OUTPUT.PUT_LINE( 'CLOB LENGTH  = ' || DBMS_LOB.GetLength(L_CLOB) ) ;
 28      DBMS_OUTPUT.PUT_LINE( 'TOTAL LENGTH = ' || ln_Total ) ;

 29   30  END ;
 31
 32  /
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 24991
STRING LENGTH = 4502
STRING LENGTH =
CLOB LENGTH  = 266638
TOTAL LENGTH = 204430

PL/SQL procedure successfully completed.

SQL>

Thanks & Regards
Manoj

Report message to a moderator

Re: Issue with DBMS_LOB.SubStr [message #629898 is a reply to message #629896]

Wed, 17 December 2014 02:49

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't know what to say.
What is your database character set?
What is your OS?
Try to do it in another version and/or another OS.
Mine was 11.2.0.1 on Windows XP Pro SP3.
I also did it with 10.2.0.4 and it works.

You can also try to use standard SUBSTR and LENGTH (and LENGTHB) instead of DBMS_LOB ones.

Report message to a moderator

Re: Issue with DBMS_LOB.SubStr [message #629975 is a reply to message #629898]

Thu, 18 December 2014 06:18

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi Michel,

I guess I'm able to figure it out.

NLS_CHARACTERSET=AL32UTF8
DB_BLOCK_SIZE=8192

Since AL32UTF8 is a varying width characterset which means that the code for 1 character can be 1,2,3 or 4 bytes long. The default value of DB_BLOCK_SIZE is 8192 bytes.
It works perfectly find if I break it in 8191 chunks. After that it starts giving me issue.

Although I've not tested it with DB_BLOCK_SIZE of greater than 8K but I hope if DB_BLOCK_SIZE is more than 8 K the range of chunks will also increase. At present I don't have any such environment to test this if you have such environment then please share your findings.

https://community.oracle.com/thread/1000525

Thanks & Regards
Manoj

Report message to a moderator

Re: Issue with DBMS_LOB.SubStr [message #629977 is a reply to message #629975]

Thu, 18 December 2014 06:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The Oracle community topic is about DISTINCT which has nothing to do here.
I don't see the relation of DB_BLOCK_SIZE and DBMS_LOB.LENGTH or the like. Mine is 8KB like yours and it works for me.
I don't really see the relation with the character set (although it is something I suspected) as 'A' in my example and you tested is 1 byte in all and every character sets.

Did you try with the standard SQL functions? I think it is a major test to go ahead.

Report message to a moderator

Previous Topic:	table type indexed by binary integer
Next Topic:	plsql table partitions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 00:52:01 CDT 2024