Solved

PL/SQL Error: String buffer too small??

Posted on 2009-05-20
4
986 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:katiep23
[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
  • 3
4 Comments
 
LVL 56

Expert Comment

by:HainKurt
ID: 24434839
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
 

Author Comment

by:katiep23
ID: 24435790
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
 

Author Comment

by:katiep23
ID: 24435956
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
 

Accepted Solution

by:
katiep23 earned 0 total points
ID: 24436159
Crap.  VARCHAR2 has a length limit of 4000 characters regardless of what I specify.  That's my problem :).  Didn't realize that limit.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

630 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