Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error ORA-06502 in DBMS_LOB.READ

Posted on 2011-03-23
13
Medium Priority
?
2,596 Views
Last Modified: 2012-05-11
I am generating XML by use of DBMS_XMLGEN.getxml pakage and the output i am storing into one CLOB. Then i am reading the value from the CLOB by use of DBMS_LOB.READ for every 32767 count of data. Below is my code. My Total LOB length is 450755. In the loop, When it reaches 393205, i am getting the below error.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Please let me know what may be the problem

Thanks
DECLARE
    v_xml_clob     CLOB := EMPTY_CLOB();

    v_lob_length   INTEGER;
    v_index        INTEGER := 1;
    v_read_cnt     INTEGER;
    v_chunk        VARCHAR2(32767);

BEGIN
    o_ret_code := 0;

    SELECT  DBMS_XMLGEN.getxml ('SELECT OBJECT_NAME, OBJECT_TYPE FROM DBS_OBJECTS')
        INTO    v_xml_clob
        FROM    DUAL;

    v_lob_length  := NVL(DBMS_LOB.getlength(v_xml_clob),0);
    DBMS_OUTPUT.PUT_LINE('LOB Length : '||v_lob_length);

    v_index       := 1;

    -- Start to read the data from CLOB object
    WHILE v_index <= v_lob_length
    LOOP
        DBMS_OUTPUT.PUT_LINE('IN');
        v_read_cnt   := 32767;
        DBMS_LOB.read (
                    v_xml_clob,
                    v_read_cnt,
                    v_index,
                    v_chunk
                    );

        -- USe fnd_file.put function to place the XML data into concurrent view output
        fnd_file.put(fnd_file.output,v_chunk);
        DBMS_OUTPUT.PUT_LINE('v_index Start: '||v_index);
        v_index := v_index + v_read_cnt;
        DBMS_OUTPUT.PUT_LINE('v_index End: '||v_index);
    END LOOP;

EXCEPTION
WHEN OTHERS
THEN
    ROLLBACK;
    NULL;
END;

Open in new window

0
Comment
[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
  • 6
  • 5
  • 2
13 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35197812
What version or Oracle are you using?

Can you use clob2file?
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xslpro.htm
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 35197899
i am using Oracle 10.2.0.4.0
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35197946
and, Can you use clob2file?  That was a 10.2 link I posted.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 35198357
yes i am trying now. mean while can you please tell me why the loop process is stopped once the read count reaches to 393205. I am not able to get the things here.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35198447
>>can you please tell me why the loop process is stopped

Sorry, I cannot.  It looks like you only posted a subset of the code so I cannot begin to test on my system.

If you can post stand-alone code I can run on my side, I can test with 10.2.0.3 and see if I can reproduce the error and if so, come up with a work-around.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 1000 total points
ID: 35198482
Could it be some sort of character set conversion?  If something in the data is being converted to a multibyte character set, then the 32767 limit would overflow.

This is from the doc:

When calling DBMS_LOB.READ from the client (for example, in a BEGIN/END block from within SQL*Plus), the returned buffer contains data in the client's character set. The database converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 35198894
So is there any way to overcome this conversion problem?
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 35198959
And also it is working when i process the small volume of data. The problem is from only large volume of data
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 35198974
If it is a conversion issue, just cut the size of your 'chunk' in half:

v_read_cnt   := 16383;
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 35199216
hi slightwv,

it is over. Thanks the issue is fixed now after i changed v_read_cnt   := 16383. But How it is possible? My v_chunk accepts till 32767 buffer. That' what i am reading every 32767 data from LOB.

Please explain me clearly.

Thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35199246
As johnsone pointed out (and correctly), it is probably a characterset conversion error.

The issue is when reading a character form the database it can get 'expanded' into two bytes.  The chunk buffer can hold 32767 characters so if you read 16383 multi-byte characters from the database, it takes up twice the room (the 32766, one left over from rounding).
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35199736
Seeing as how the smaller chunk worked, I would bet on the character set conversion.  Based on how far you got through the data, you probably don't have a lot of strange characters, so going to half on the chunk size, while definitely the safe way to do it, probably wasn't necessary.  You could probably bump the size up to more like 30,000, but to prevent it from happening again, half (as slightwv suggested) is the best route.

The other way to do it would be to make sure that the character set on the client is the same as the server.  This would be an alter statement or an environment variable change.  However, if you want to account for every situation, as you cannot control every client, the way you are doing it now is probably best.
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 35468118
GOOD ONE
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

670 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