ORA-00984: column not allowed here
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: ORA-00984: column not allowed here

  1. #1
    Join Date
    Oct 2010
    Posts
    7

    ORA-00984: column not allowed here

    Hello,

    While inserting record into database table through shell script,

    INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
    VALUES($EMPNAME,$EMPID,TO_DATE($EMPBDATE,'YYYY-MM-DD'));

    I am getting below error
    SQL> TO_DATE (20100913,"YYYY-MM-DD")
    *
    ERROR at line 8:
    ORA-00984: column not allowed here

    Please help me out.
    Thanks in advance.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    What's EMPBDATE column datatype, is it DATE or is it VARCHAR2?

    Would you mind in posting desc of TBL1?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2010
    Posts
    7
    here are the datatypes of table column,

    EMPNAME VARCHAR2(6)
    EMPID VARCHAR2(10)
    MPBDATE DATE

    and I had altered my default date format as,
    alter session NLS_DATE_FORMAT "YYYY-MM-DD";

  4. #4
    Join Date
    Oct 2010
    Posts
    7
    here are the datatypes of table column,

    EMPNAME VARCHAR2(6)
    EMPID VARCHAR2(10)
    EMPBDATE DATE

    and I had altered my default date format as,
    alter session set nls_date_format = 'yyyy-mm-dd';

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Syntax to insert date column is incorrect, '20100913' in not in the 'YYYY-MM-DD' format but in the 'YYYYMMDD' format, like...

    Code:
    SQL> create table TBL1 (EMPNAME VARCHAR2(6)
      2  ,EMPID VARCHAR2(10)
      3  ,MPBDATE DATE);
    
    Table created.
    
    SQL> insert into TBL1 columns(EMPNAME,EMPID,MPBDATE)
      2  values('Joe',2,to_date('20100913','yyyymmdd'));
    
    1 row created.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Oct 2010
    Posts
    7
    But using that format also I am getting below error,

    SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 TO_DATE (20100913,"YYYYMMDD")
    *
    ERROR at line 10:
    ORA-06550: line 10, column 23:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 3, column 1:
    PL/SQL: SQL Statement ignored

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please show us the whole session, like I did.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Oct 2010
    Posts
    7
    I had written a oracle procedure in shell script.
    In shell script I am reading each line of file and checking 7th field of line if it is "1" then copying EMPNAME,EMPID and EMPBDATE from input file to insert into database table.
    Here is procedure,
    INS_REC=$(sqlplus scott/tiger@EMPLOYEE<< EOF
    set head off
    set feed off
    set serveroutput on
    INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
    VALUES($EMPNAME,$EMPID,TO_DATE($EMPBDATE,'YYYY-MM-DD'));
    end;
    /
    EOF)
    TBL_ERR=$(echo "$INS_REC" | grep -e "SP2-" -e "ORA-")
    echo $TBL_ERR >> $LOGFILE
    Thanks in advance.

  9. #9
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool Needs quotes

    Quote Originally Posted by PinkRoses View Post
    I had written a oracle procedure in shell script.
    In shell script I am reading each line of file and checking 7th field of line if it is "1" then copying EMPNAME,EMPID and EMPBDATE from input file to insert into database table.
    Here is procedure,

    Thanks in advance.
    Try this (notice the quotes):
    Code:
    INS_REC=$(sqlplus scott/tiger@EMPLOYEE<< EOF
    set head off feed off
    set serveroutput on
    INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
         VALUES ('$EMPNAME','$EMPID',TO_DATE('$EMPBDATE','YYYY-MM-DD'));
    /
    EOF)
    TBL_ERR=$(echo "$INS_REC" | grep -e "SP2-" -e "ORA-")
    echo $TBL_ERR >> $LOGFILE
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  10. #10
    Join Date
    Oct 2010
    Posts
    7
    Using single quotes ' ' I am not able to get any value of variable and the error for column not allowed here is still coming as,

    SQL> TO_DATE ("$DATEGEN","YYYYMMDD")
    *
    ERROR at line 8:
    ORA-00984: column not allowed here

    Please help me out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width