PL/SQL Error: String buffer too small??

I have a procedure that I am using to update a bunch of tables (converting userid's) in a database that is working just fine.  And I was logging all the tables that were being updated as I went & saving them into a very large string which was the output of my procedure.

However, I've now decided that it would be nice to also include the number of rows updated in each table & this is giving me fits.  I am relatively new to PL/SQL & was just updating previous code with this procedure.  The error I get is: ORA-06502: PL/SQL: numeric or value error: character string buffer too small.  I have tried increasing the size of the string variable, but that wasn't the issue.

I am going to copy & paste the beginning of the procedure below.  The last line is the problem area I believe & I have some comments I will put there.  Maybe an issue trying to store an integer into a string?  I'm hoping this is something simple that I am missing because of my inexperience. Thanks.
PROCEDURE USERPROFILE_UPDATEUSERID(pold_user_id IN VARCHAR, pnew_user_id IN VARCHAR, presult OUT VARCHAR)
AS
strRETURN VARCHAR2 (10000) := '';
v_rows_processed_1 INTEGER := 0;
v_rows_processed_2 INTEGER := 0;
v_rows_processed_3 INTEGER := 0;
BEGIN
 
UPDATE USERPROFILE SET USERID = pnew_user_id WHERE USERID = RPAD(pold_user_id, 32); 
v_rows_processed_1 := SQL%ROWCOUNT;
UPDATE USERPROFILE SET MANAGER = pnew_user_id WHERE MANAGER = RPAD(pold_user_id, 32);
v_rows_processed_2 := SQL%ROWCOUNT;
UPDATE USERPROFILE SET MANAGER2 = pnew_user_id WHERE MANAGER2 = pold_user_id; 
v_rows_processed_3 := SQL%ROWCOUNT;
 
--This one below works:
strRETURN := strRETURN || 'USERPROFILE: USERID (' || v_rows_processed_1 || ');';
 
--This doesn't:
strRETURN := strRETURN || 'USERPROFILE: USERID (' || v_rows_processed_1 || '), MANAGER (' || v_rows_processed_2 || '),MANAGER2(' || v_rows_processed_3 || ');';

Open in new window

katiep23Asked:
Who is Participating?
 
katiep23Author Commented:
Crap.  VARCHAR2 has a length limit of 4000 characters regardless of what I specify.  That's my problem :).  Didn't realize that limit.
0
 
HainKurtSr. System AnalystCommented:
there is no reason for this not to run :) buy you can replace this line

--This doesn't:
strRETURN := strRETURN || 'USERPROFILE: USERID (' || v_rows_processed_1 || '), MANAGER (' || v_rows_processed_2 || '),MANAGER2(' || v_rows_processed_3 || ');';

with

--This doesn't:
strRETURN := 'USERPROFILE: USERID (' || v_rows_processed_1 || '), MANAGER (' || v_rows_processed_2 || '), MANAGER2(' || v_rows_processed_3 || ');';

also

UPDATE USERPROFILE SET MANAGER2 = pnew_user_id WHERE MANAGER2 = pold_user_id;

looks like it should be:

UPDATE USERPROFILE SET MANAGER2 = pnew_user_id WHERE MANAGER2 = RPAD(pold_user_id, 32);

the result should be like

USERPROFILE: USERID (123456), MANAGER (123456), MANAGER2(123456);

which is 66 character in length... you do not need 10000... problem is somewhere else...

also try VARCHAR2 in out parameter

PROCEDURE USERPROFILE_UPDATEUSERID(pold_user_id IN VARCHAR, pnew_user_id IN VARCHAR, presult OUT VARCHAR2)

0
 
katiep23Author Commented:
So if 6 rows are processed,SQL%ROWCOUNT would return 123456 instead of just 6??  If so, that is definitely my problem as there are many other tables updating after this with possibly many records in them.
0
 
katiep23Author Commented:
If I comment about half the comments, it works fine.  But when I count the total characters, it's nowhere near the 10,000 limit.
0
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.

All Courses

From novice to tech pro — start learning today.