PL/SQL ORA-06512 Error

[ericzutter]

Hi friends

this is a very simple procedure but for some reason it's giving the dreaded ORA-06512 Error. Please advise.

CREATE OR REPLACE PROCEDURE getprojectdescription (
   v_project_id            IN       NUMBER,
   v_project_description   OUT      VARCHAR2
)
AS
BEGIN
   SELECT project_description
     INTO v_project_description
     FROM projects
    WHERE project_id = v_project_id;
END;
/

Thanks

[tyro]

your procedure looks fine to me, could you post the error code and the desc table for projects. Seems like you might have a column length problem with the data that your query is returning.

[rajavu]

The ORA-06512 error itself does not indicate the actual error . It normally indicates the line number at which the oracle PL/SQL code has caused an error . There will be another main error occurred in your process and that error happened in the line number as mentioned in ORA-06512 message description.

As tyro assumed, it could be data type length issue with the OUT variable v_project_description (in comparison with length of column value project_description) while calling the procedure. please check it.

[ericzutter]

Thanks for your replies guys

yes the actual error is

ORA-06502: PL/SQL: numeric OR value error: character string buffer too small

Any suggestions?

[rajavu]

The reason is already mentioned.

The data type length of v_project_description is less than the actual Data type length of the column projects.project_description.

Increase the data type length of v_project_description while calling the procedure.

[halim]

Here I want to say someting (even if this is one year old post) may be some people search by "ORA-06512"

If there is an exception handle this can be easily identified the problem
like

SQL> SET SERVEROUTPUT ON
SQL> ed
Wrote file afiedt.buf
 
  1  declare
  2  s_emp varchar2(3);
  3  begin
  4  select first_name
  5  into s_emp
  6  from employees
  7  where employee_id =200;
  8  exception
  9  when others then
 10  ---DBMS_OUTPUT.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK );
 11  DBMS_OUTPUT.put_line(sqlerrm);
 12  ---DBMS_OUTPUT.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
 13* end ;
SQL> /
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
 
PL/SQL procedure successfully completed.

Regards
Halim

[rajavu]

Exception VALUE_ERROR is best suitable for this scenario.

SQL> -- Captures Datatype mismatches
SQL> DECLARE
   vrbl NUMBER(10) ;
BEGIN
   vrbl :='ABCD';
   DBMS_OUTPUT.PUT_LINE(vrbl);
EXCEPTION
   WHEN VALUE_ERROR THEN
     DBMS_OUTPUT.put_line('Err01 : '||sqlerrm);
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Others');
END;  2 3 4 5 6 7 8 9 10 11
 12 /
Err01 : ORA-06502: PL/SQL: numeric OR value error: character TO number
conversion error
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> -- Captures Variable size mismatches
SQL> DECLARE
   vrbl VARCHAR2(5) ;
BEGIN
   vrbl :='RAJUVAN';
   DBMS_OUTPUT.PUT_LINE(vrbl);
EXCEPTION
   WHEN VALUE_ERROR THEN
     DBMS_OUTPUT.put_line('Err02 : '||sqlerrm);
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Others');
END;  2 3 4 5 6 7 8 9 10 11
 12 /
Err02 : ORA-06502: PL/SQL: numeric OR value error: character string buffer too
small
 
PL/SQL procedure successfully completed.
 
SQL>