Solved

PL/SQL Error: String buffer too small??

Posted on 2009-05-20
4
980 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 51

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now