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)
strRETURN VARCHAR2 (10000) := '';
v_rows_processed_1 INTEGER := 0;
v_rows_processed_2 INTEGER := 0;
v_rows_processed_3 INTEGER := 0;
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 || ');';
strRETURN := strRETURN || 'USERPROFILE: USERID (' || v_rows_processed_1 || '), MANAGER (' || v_rows_processed_2 || '),MANAGER2(' || v_rows_processed_3 || ');';