Constructing a string upto 20000 characters in PL/SQL select statement

I was trying to concatenate 3 columns (2 of type varchar2 and one of type CLOB) in a PL/SQL procedure. When I execute the procedure I was getting ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversio.

Please help me in this regard.

CREATE OR REPLACE PROCEDURE STR_T (
		v_no			IN  VARCHAR2,
		v_res			OUT REF CURSOR 
AS
 
BEGIN
 
 		  OPEN v_res FOR
		SELECT i_no || '^' || rlse_no || '^' || TO_CHAR(n_txt) || ' ^' AS v_str
		  FROM iv
		 WHERE i_no			= v_no;
     
END STR_T;
/

Open in new window

Oracle Database

Last Comment

Sean Stuber

8/22/2022 - Mon

Sean Stuber

7/16/2008

you can't.

sql varchar2 fields can't be longer than 4000 characters.

You can use a clob to get larger strings, but you can't build it through concatenation like that.

Sean Stuber

7/16/2008

you can use dbms_lob.append to add your fields to your clob field, but you can't do that in sql, only in pl/sql

rajasekhar_b

7/16/2008

ASKER

I believe that in PL/SQL the varchar2 type will support upto 32k. So, I am trying to build a string which is less than 32k.

Additional info: i_no VARCHAR2(10), rlse_no VARCHAR2(6) and n_txt CLOB.
The n_txt column having 15343 characters in it.

Also, I have used DBMS_LOB.SUBSTR() function to convert the CLOB into VARCHAR2 and then trying to concatenate with other two columns. This is also giving the same error.

Your help has saved me hundreds of hours of internet surfing.

fblack61

ASKER CERTIFIED SOLUTION

Sean Stuber

7/16/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

rajasekhar_b

7/16/2008

ASKER

Thanks for the update,

I think that might be true. I tried the following block which is taking two strings of 4000 charecters and 2000 characters, and to concatinate those two strings then assaign to another string.

DECLARE
      v_str1            VARCHAR2(20000);
      v_str2            VARCHAR2(5000);

      v_str3            VARCHAR2(30000);

BEGIN

      SELECT TO_CHAR(SUBSTR(N_TEXT,1,4000))
       INTO v_str1
       FROM IV
       WHERE inst_no = 'A110028863'
       AND LENGTH(NOTE_TEXT) > 5000
       AND ROWNUM       < 2;

      DBMS_OUTPUT.PUT_LINE('length : ' || LENGTH(v_str1));

       v_str2      := SUBSTR(v_str1,1,2000);

SELECT v_str1      || '^' || v_str2
INTO v_str3
FROM DUAL;

END;
/

This is failing with ORA-01489: result of string concatenation is too long error.

Thanks,

rajasekhar_b

7/16/2008

ASKER

Thanks for your quick response.

Sean Stuber

7/16/2008

you're welcome, but why only a B grade?

Please don't accept an answer prematurely and then give a penalty without giving the volunteers (in this case, me) a chance to provide whatever additional info you were looking for.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Sean Stuber

7/16/2008

and yes you will get an error doing that.

However....
try this...

I removed the sql from the v_str3 assignment. pure pl/sql and it should work.

Note, you should avoid select from dual as a means of assignment too. just do x := y;

DECLARE
      v_str1            VARCHAR2(20000);
      v_str2            VARCHAR2(5000);
      
      v_str3            VARCHAR2(30000);
 
BEGIN
 
      SELECT TO_CHAR(SUBSTR(N_TEXT,1,4000))
        INTO v_str1
        FROM IV
       WHERE inst_no = 'A110028863'
         AND LENGTH(NOTE_TEXT) > 5000
         AND ROWNUM       < 2;
      
      DBMS_OUTPUT.PUT_LINE('length : ' || LENGTH(v_str1));
      
       v_str2      := SUBSTR(v_str1,1,2000);
       
   v_str3 := v_str1      || '^' || v_str2;
 
END;
/

Open in new window