I've written a shell script in which i've defined variables : Last_Month
,last_date
and first_date
and i'm trying to execute the below sql from the shell :
/app/oracle/product/10.2.0/bin/sqlplus -s user_name/passwd @effective_date.sql
contents of effective_date.sql
:
set heading off
set feedback off
set verify off
set pagesize 50000
set trimspool on
set wrap off
set newpage none
set linesize 2000
spool /home/dir/file_${Last_Month};
select * from table_1
where effective_date between to_date("$first_date",'dd-mm-yyyy') and to_date("$last_date",'dd-mm-yyyy');
spool off;
exit;
but on executing the shell, i'm getting below error:
SP2-0332: Cannot create spool file.
where effective_date between to_date("$first_date",'dd-mm-yyyy') and to_date("$last_date",'dd-mm-yyyy')
*
ERROR at line 2:
ORA-00904: "$last_date": invalid identifier
not spooling currently
Any help?