PL/SQL ORA-06512 Error

in Oracle Database; Hi friends this is a very simple procedure but for some reason it's giving the dreaded ORA-06512 Error. Please advise. ...

+ Post Reply + Post New Topic
Results 1 to 7 of 7
  1. #1
    ericzutter's Avatar
    ericzutter is offline Junior Member ericzutter is on a distinguished road
    Join Date
    07 Apr 2009
    Posts
    7
    Document Uploads
    0

    Helpful? Yes No

    PL/SQL ORA-06512 Error

    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.    Club-Oracle Complementary E-Books and Magazines
    Get your free Complementary Copy of Oracle Magazine

    You can also browse the Free Magazines and E-Books section to see the complete list of free magazines, e-books and Whitepapers.

  3. #2
    tyro's Avatar
    tyro is offline Forum Genius tyro is on a distinguished road
    Join Date
    20 Aug 2008
    Location
    India
    Posts
    362
    Document Uploads
    0

    Helpful? Yes No

    Re: PL/SQL ORA-06512 Error

    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.

  4. #3
    rajavu's Avatar
    rajavu is offline Forum Genius rajavu is on a distinguished road
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    482
    Document Uploads
    0

    Helpful? Yes No

    Re: PL/SQL ORA-06512 Error

    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.

    Raj.

  5. #4
    ericzutter's Avatar
    ericzutter is offline Junior Member ericzutter is on a distinguished road
    Join Date
    07 Apr 2009
    Posts
    7
    Document Uploads
    0

    Helpful? Yes No

    Re: PL/SQL ORA-06512 Error

    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?

  6. #5
    rajavu's Avatar
    rajavu is offline Forum Genius rajavu is on a distinguished road
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    482
    Document Uploads
    0

    Helpful? Yes No

    Re: PL/SQL ORA-06512 Error

    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.

    Raj.

  7. #6
    halim's Avatar
    halim is offline Junior Member halim is on a distinguished road
    Join Date
    22 Apr 2009
    Location
    dhaka
    Posts
    4
    Document Uploads
    0

    Helpful? Yes No

    Re: PL/SQL ORA-06512 Error

    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 :
    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

  8. #7
    rajavu's Avatar
    rajavu is offline Forum Genius rajavu is on a distinguished road
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    482
    Document Uploads
    0

    Helpful? Yes No

    Re: PL/SQL ORA-06512 Error

    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>

    Raj.

Similar Threads

  1. Error while starting up Oracle database, I got the error "ORA-01194"
    By venkat09102007 in forum Security, Backup and Recovery
    Replies: 3
    Last Post: 07-25-2010, 05:00 PM
  2. ORA-00971 and ORA-06512 errors
    By charlichaplin in forum SQL PL/SQL
    Replies: 12
    Last Post: 04-20-2010, 06:24 AM
  3. Oracle ORA-06512 error
    By mohammedalthaf in forum SQL PL/SQL
    Replies: 1
    Last Post: 01-12-2010, 12:07 PM
  4. Toad error - Can't initialize OCI. Error-1
    By cathyyankeefan in forum Other Development Tools
    Replies: 3
    Last Post: 07-17-2009, 05:58 AM