1 2 Previous Next 19 Replies Latest reply: Sep 25, 2006 12:58 PM by 534121 RSS

    INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?

    534121
      Hi,

      There is a procedure, in its exception I have used
      INSERT INTO TERR VALUES (SQLCODE||SQLERRM, SYSDATE);

      This is to capture the exceptions in TERR table (ERR_DESC VARCHAR2(3000), ERR_TIME DATE).

      This procedure compiles sccuessfully in database A, but throws error "ORA-00984: column not allowed here"

      I learnt that SQLCODE,SQLERRM doesn't work directly in a SQL. We have to use variables to store them.

      My Question is, why the procedure (with same code) compiles successfully in database A.

      Is there any set-up that has been missed in database B (OR)
      is there any post-db creation scripts that have been missed out in database A (where it is successful)?

      Procedure goes like this:-
      CREATE PROCEDURE PRO_DATA
      (vval1 IN NUMBER, vval2 IN VARCHAR2....vval5 IN DATE) IS
      BEGIN
      INSERT INTO TBL_INVALID (col1, col2, ...col5)
      VALUES (vval1,vval2,...val5);
      COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
      INSERT INTO TERR (ERROR_DESC, ERROR_TIME)
      VALUES (SQLCODE || SQLERRM, SYSDATE);
      COMMIT;
      END PRO_DATA;
        • 1. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
          orawarebyte
          do you INSERT at sql in this way???
          SQL> INSERT INTO dept (deptno) VALUES (emp.deptno);
          INSERT INTO dept (deptno) VALUES (emp.deptno)
                                                *
          ERROR at line 1:
          ORA-00984: column not allowed here
          First capture these code in local variable then reference these columns in yours INSERT statment.

          Dont use Commit indside procedure its not the job of procedure to commit.
          SQL> DROP TABLE tbl_invalid
            2  /
          
          Table dropped.
          
          SQL> CREATE TABLE tbl_invalid (col1  NUMBER,col2  VARCHAR2(50))
            2  /
          
          Table created.
          
          
          SQL> CREATE OR REPLACE PROCEDURE PRO_DATA IS
            2  a  NUMBER;
            3  b  VARCHAR2(50);
            4  C  VARCHAR2(50);
            5  BEGIN
            6   a:=1/0;
            7  EXCEPTION
            8  WHEN OTHERS THEN
            9  a:=sqlcode;
           10  b:=sqlerrm;
           11  INSERT INTO tbl_invalid (col1,col2) VALUES (a,b);
           12  END PRO_DATA; 
           13  .
          SQL> /
          
          Procedure created.
          
          SQL> SELECT * FROM tbl_invalid;
          
          no rows selected
          
          SQL> EXECUTE pro_data;
          
          PL/SQL procedure successfully completed.
          
          SQL> SELECT * FROM tbl_invalid;
          
                COL1 COL2
          ---------- --------------------------------------------------
               -1476 ORA-01476: divisor is equal to zero
          
          SQL> 
          Khurram
          • 2. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
            Dmytro Dekhtyaryuk
            BTW, you write in declarartion wal5, and in insert val5.
            It is obviously typo, but it is much better, if you show complete code
            of procedure with copy-paste.

            To your question:
            please post with copy-paste sqlplus-session in Database A with text of
            procedure and compilation
            I want to see the message "... successfully completed" :-)

            Regards
            Dmytro
            • 3. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
              Dmytro Dekhtyaryuk
              do you INSERT at sql in this way???
              SQL> INSERT INTO dept (deptno) VALUES (emp.deptno);
              INSERT INTO dept (deptno) VALUES (emp.deptno)
              *
              t allowed here
              Khurram
              Why ? He used sqlcode and sqlerrm inproperly, as he already said.
              It is impossible to use these variables in INSERT directly , only via local
              variables.

              Regards
              Dmytro
              • 4. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                orawarebyte
                Why ? He used sqlcode and sqlerrm inproperly, as he
                already said.
                It is impossible to use these variables in INSERT
                directly , only via local
                variables.

                Regards
                Dmytro
                I told him you cannot use SQLCODE or SQLERRM function directly in SQL statement.

                Like
                SQL> INSERT INTO tbl_invalid (a) VALUES (scott.empno);
                INSERT INTO tbl_invalid (a) VALUES (scott.empno)
                                                          *
                ERROR at line 1:
                ORA-00984: column not allowed here
                You cant use column name INSIDE sql INSERT values clause directly unless
                SQL> INSERT INTO tbl_invalid (col1) SELECT empno FROM scott.emp;
                
                17 rows created.
                So its not documented anywhere why cant we use SQLCODE and SQLERRM function inside sql.

                Khurram
                • 5. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                  Dmytro Dekhtyaryuk
                  PL/SQL User's Guide and Reference
                  Release 2 (9.2)
                  Part Number A96624-01

                  Retrieving the Error Code and Error Message: SQLCODE and SQLERRM
                  --------------------------------------------------------------------------------------------------------------------
                  You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement,
                  --------------------------------------------------------------------------------------------------------------------

                  But just wait on OPs answer. It seemed to me, that he could overcome this
                  limitation "in database A" :-)

                  Regards
                  Dmytro
                  • 6. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                    27876
                    So its not documented anywhere why cant we use SQLCODE and SQLERRM function inside sql.
                    The use of SQLCODE and SQLERRM directly in a SQL statement is unsafe since as part of the statement execution, Oracle will need to reset the values of these variables whether the statement succeeds or fails.
                    • 7. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                      orawarebyte
                      The use of SQLCODE and SQLERRM directly in a SQL
                      statement is unsafe since as part of the statement
                      execution, Oracle will need to reset the values of
                      these variables whether the statement succeeds or
                      fails.
                      Thanx Kamal thats i wana try to tell (which i dont know at all) and i didnt see anywhere in ORACLE doc WHY??

                      Kamal still not clear if you give me some more detail i would be obliged for you.

                      Is it yours assupmtion or its black and white some where else please gimme link for this.

                      Khurram

                      Khurram
                      • 8. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                        534121
                        Few of the OPs were interested in copy+paste of the code that's working fine in db-A but giving compilation errors in db-B.

                        DATABASE-A>>>

                        SQL> CREATE TABLE X_TBL_ERRORS(ERROR_DESC VARCHAR2(1000), ERROR_TIME DATE);
                        Table created.

                        SQL> CREATE TABLE X_TBL_TEST(SEQ_NUM NUMBER, LOAD_DATE DATE, ERROR_DESC VARCHAR2(1000));
                        Table created.

                        SQL> CREATE OR REPLACE PROCEDURE X_PRO_TEST(vn_seq IN NUMBER, vd_loaddate IN DATE, vv_errdesc IN VARCHAR2) IS
                        BEGIN
                        INSERT INTO X_TBL_TEST (SEQ_NUM, LOAD_DATE, ERROR_DESC)
                        VALUES (vn_seq, vd_loaddate, vv_errdesc);
                        COMMIT;

                        EXCEPTION
                        WHEN OTHERS THEN
                        INSERT INTO X_TBL_ERRORS(ERROR_DESC, ERROR_TIME)
                        VALUES (SQLCODE||SQLERRM, SYSDATE);
                        COMMIT;
                        END X_PRO_TEST;
                        /
                        Procedure created.

                        SQL> ALTER PROCEDURE X_PRO_TEST COMPILE;
                        Procedure altered.

                        Guyz you are right. I too have read the Oracle Docmn which says, SQLCODE and SQLERRM doesn't go alongwith INSERT statement directly.

                        I ran catproc.sql thinking it would rectify db-A (and start throwing the same ORA984 error, as its rightly doing in db-B) but in vain.

                        Now OPs plz help me in resolving this trivia.
                        • 9. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                          Dmytro Dekhtyaryuk
                          I think, that Khurram helped you already.

                          BTW: OP means Original Poster (in this thread - You :-) )

                          Regards
                          Dmytro
                          • 10. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                            45878
                            Just to be safe, I think you must rollback affter some "error" occurred on a DML.
                            DECLARE
                            vErrMsg VARCHAR2(1000);

                            BEGIN
                            INSERT INTO X_TBL_TEST (SEQ_NUM, LOAD_DATE, ERROR_DESC)
                            VALUES (vn_seq, vd_loaddate, vv_errdesc);
                            COMMIT;

                            EXCEPTION
                            WHEN OTHERS THEN
                            -- Otherwise tries to eventually COMMIT again
                              ROLLBACK; 
                            -- As others have mentioned earlier....
                            vErrMsg := SUBSTR(SQLCODE||' '||SQLERRM,1,1000); 
                                 INSERT INTO X_TBL_ERRORS(ERROR_DESC, ERROR_TIME)
                                       VALUES (vErrMsg, SYSDATE);
                              COMMIT;
                            END X_PRO_TEST;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                            • 11. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                              27876
                              ran catproc.sql thinking it would rectify db-A (and start throwing the same ORA984 error, as its rightly doing in db-B) but in vain.
                              You need to be more specific. What are the exact versions of these two databases "A" and "B"?
                              How updated are these databases with the latest updates for there versions?
                              • 12. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                                534121
                                Sorry, I thought OP is for Oracle Pros and not Original Poster.

                                Dmytro: Did u see my last posting, it has got the messg you were looking for. I would have uploaded the screenshot but this site doesn't allows to do so.

                                Kamal: Both database A and B are on 9.2.0.6

                                Guyz....Any clues???
                                • 13. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                                  27876
                                  What is the output of the following from both databases:
                                  SQ> select comp_name, version, status from dba_registry ;
                                  ?
                                  • 14. Re: INSERT INTO ..VALUES (SQLERRM, SQLCODE, SYSDATE) doesn't work?
                                    534121
                                    Kamal:
                                    Database A (where the pkg succesfully compiles)
                                    SQL> select comp_name, version, status from dba_registry ;
                                    COMP_NAME ==> VERSION ==> STATUS
                                    Oracle9i Catalog Views ==> 9.2.0.6.0 ==> VALID
                                    Oracle9i Packages and Types ==> 9.2.0.6.0 ==> VALID
                                    JServer JAVA Virtual Machine ==> 9.2.0.6.0 ==> VALID
                                    Oracle9i Java Packages ==> 9.2.0.6.0 ==> VALID

                                    Database B (where the pkg gives compilation error)
                                    SQL> select comp_name, version, status from dba_registry ;
                                    COMP_NAME ==> VERSION ==> STATUS
                                    Oracle9i Catalog Views ==> 9.2.0.6.0 ==> VALID
                                    Oracle9i Packages and Types ==> 9.2.0.6.0 ==> VALID
                                    1 2 Previous Next