5 Replies Latest reply: Oct 3, 2013 6:09 AM by padders RSS

    Using sqlerrm directly on insert statement

    user13325846

      Hi,

      Given below a block of code, this code compiled successfully on one DB but returns error on other.

      DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB.

       

      declare

         l_procedure_name CONSTANT VARCHAR2(100) := 'copy_device_status_tables';

         l_procedure_id   CONSTANT INTEGER := 301;

         l_step_name            VARCHAR2(4000);

         x number;

      begin

         select 1 into x from dual;

      EXCEPTION

        WHEN OTHERS THEN

          INSERT INTO aerbill_traffic.procedure_status(procedure_id,procedure_name,step_description,process_time, status, procedure_status)

          VALUES (l_procedure_id,l_procedure_name,substr(l_step_name || '; ' || SQLERRM,1,200), SYSDATE, 1,'Failed');

        COMMIT;

      end;

       

       

      Error report:

      ORA-06550: line 12, column 75:

      PL/SQL: ORA-00984: column not allowed here

      ORA-06550: line 11, column 5:

      PL/SQL: SQL Statement ignored

      06550. 00000 -  "line %s, column %s:\n%s"

      *Cause:    Usually a PL/SQL compilation error.

      *Action:


      Regards