How to resolve ORA-22828?

Posted on 2013-10-03
Medium Priority
Last Modified: 2013-10-04
can you please help me resolve ora-22828? reading from a table clob into a variable clob in pl/sql?
when i try this i get the following error:

    lv_table7 clob;
    lv_userid varchar2(100) := 'ABCD390;EFGH794';
    dbms_output.put_line ('lv '||lv_userid);  
    SELECT  A.GET_TABLE(7, 301, 104, lv_userid)
    INTO    lv_table7
    FROM    schema1.table1 WHERE ROWNUM = 1;
    dbms_output.put_line ('T '||lv_table7);  
    select user_instance into lv_table7 from schema1.table1 WHERE user_id = lv_userid;
    dbms_output.put_line ('T '||lv_table7);
exception when others then
    dbms_output.put_line ('E '||sqlcode||sqlerrm);  
lv ABCD390;EFGH794
E -6502ORA-06502: PL/SQL: numeric or value error
ORA-22828: input pattern or replacement parameters exceed 32K size limit
Question by:Rao_S
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 39544806
What is your exact Oracle version (all 4 numbers please, like

I believe the issue is the second:
dbms_output.put_line ('T '||lv_table7);

There is a string concatenation in there that is probably causing the issue.

Try just the clob:
dbms_output.put_line (lv_table7);

Author Closing Comment

ID: 39545976
i tried without the string concatenation, it did not work, so instead parsed out the text in 30K chunks...and that worked..

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

627 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