This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Sep 25, 2006 10:58 AM by 534121 RSS

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

534121 Newbie
Currently Being Moderated
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?
    316993 Pro
    Currently Being Moderated
    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?
    203160 Newbie
    Currently Being Moderated
    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?
    203160 Newbie
    Currently Being Moderated
    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?
    316993 Pro
    Currently Being Moderated
    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?
    203160 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    316993 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    203160 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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