Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PL/SQL Error: String buffer too small??

Posted on 2009-05-20
4
Medium Priority
?
989 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
  • 3
4 Comments
 
LVL 61

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

877 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