1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL/SQL ORA-06512 Error

Discussion in 'SQL PL/SQL' started by ericzutter, Apr 18, 2009.

  1. ericzutter

    ericzutter Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi friends

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

    Code (SQL):
    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
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    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.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    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.
     
  4. ericzutter

    ericzutter Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for your replies guys

    yes the actual error is
    Code (SQL):
    ORA-06502: PL/SQL: NUMERIC OR VALUE error: CHARACTER string buffer too small
    Any suggestions?
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    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.
     
  6. halim

    halim Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    dhaka
    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

    Code (Text):
     
    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
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Exception VALUE_ERROR is best suitable for this scenario.

    Code (SQL):
    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>
     
     
  8. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    ORA-06512 error message indicates the line number in the PLSQL code that the error resulted. Usually ORA-06512 is accompanied by another line and error which indicates the actual error. Like for example, the first line of the error message like ORA-06502 may indicate the actual error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at which line number of the PLSQL code.