ORA-00984: column not allowed here

[PinkRoses]

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.

[PAVB]

What's EMPBDATE column datatype, is it DATE or is it VARCHAR2?

Would you mind in posting desc of TBL1?

[PinkRoses]

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";

[PinkRoses]

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';

[PAVB]

Syntax to insert date column is incorrect, '20100913' in not in the 'YYYY-MM-DD' format but in the 'YYYYMMDD' format, like...

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.

[PinkRoses]

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

[PAVB]

Please show us the whole session, like I did.

[PinkRoses]

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.

[LKBrwn_DBA]

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):

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

[PinkRoses]

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.