Oracle Error ORA-06512

Question

Just can t figure out why it gives me ORA-06512 Error  PROCEDURE PX pNum INT pIdM INT pCv VARCHAR2 pSup FLOAT  AS     vSOME EX EXCEPTION   BEGIN      IF   pNum  lt  12  OR  pNum  gt  14   THEN              RAISE vSOME EX      ELSE           EXECUTE IMMEDIATE   INSERT INTO M   pNum   GR  CV  SUP  IDM   pNum     VALUES    pCv        pSup        pIdM           END IF  END PX    The structure base for the table where the insert is made   CREATE TABLE  DB   M12GR         IDM12GR  NUMBER 10 0  NOT NULL ENABLE        CV  VARCHAR 5  NOT NULL ENABLE        SUP  FLOAT 126  NOT NULL ENABLE        IDM12  NUMBER 10 0  NOT NULL ENABLE        CONSTRAINT  PRIMARY 30  PRIMARY KEY   IDM12GR        CONSTRAINT  M12SUELORM12  FOREIGN KEY   IDM12   REFERENCES  DB   M12    IDM12   ENABLE

User · Answer

The variable pCv is of type VARCHAR2 so when you concat the insert you aren't putting it inside single quotes:

 EXECUTE IMMEDIATE  'INSERT INTO M'||pNum||'GR (CV, SUP, IDM'||pNum||') VALUES('''||pCv||''', '||pSup||', '||pIdM||')';

Additionally the error ORA-06512 raise when you are trying to insert a value too large in a column. Check the definiton of the table M_pNum_GR and the parameters that you are sending. Just for clarify if you try to insert the value 100 on a NUMERIC(2) field the error will raise.

User · Answer

ORA-06512 is part of the error stack   It gives us the line number where the exception occurred  but not the cause of the exception   That is usually indicated in the rest of the stack  which you have still not posted      In a comment you said      still  the error comes when pNum is not between 12 and 14  when pNum   is between 12 and 14 it does not fail    Well  your code does this   IF   pNum  lt  12  OR  pNum  gt  14   THEN          RAISE vSOME EX    That is  it raises an exception when pNum is not between 12 and 14    So does the rest of the error stack include this line      ORA-06510  PL SQL  unhandled user-defined exception   If so  all you need to do is add an exception block to handle the error   Perhaps   PROCEDURE PX pNum INT pIdM INT pCv VARCHAR2 pSup FLOAT  AS     vSOME EX EXCEPTION   BEGIN      IF   pNum  lt  12  OR  pNum  gt  14   THEN              RAISE vSOME EX      ELSE           EXECUTE IMMEDIATE   INSERT INTO M   pNum   GR  CV  SUP  IDM   pNum     VALUES    pCv        pSup        pIdM           END IF  exception     when vsome ex then          raise application error -20000                                     This is not a valid table   M   pNum   GR     END PX    The documentation covers handling PL SQL exceptions in depth      Find out more

User · Answer

I also had the same error  In my case reason was I have created a update trigger on a table and under that trigger I am again updating the same table  And when I have removed the update statement from the trigger my problem has been resolved

[oracle] Oracle Error ORA-06512

Examples related to oracle

Examples related to stored-procedures

Examples related to plsql

Examples related to ora-06512