Solved

PL/SQL Error: String buffer too small??

Posted on 2009-05-20
4
984 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:Huseyin KAHRAMAN
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Salesforce.com’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

680 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