0

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?

0

1 Answer 1

1

With sqlplus you should use different approach to the shell variables. Your command should become:

/app/oracle/product/10.2.0/bin/sqlplus -s user_name/passwd @effective_date.sql "$Last_Month" "$first_date" "$last_date" 

and your sql script should be:

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_'&1';

select * from table_1
where effective_date between to_date('&2','dd-mm-yyyy') and to_date('&3','dd-mm-yyyy');

spool off;
exit;
3
  • Thanks for the help, but after executing the shell, it's asking for the values, like Enter value for 3:, Enter value for 2:, Enter value for 1:....i don't want the prompt to be displayed.
    – ashish_k
    Apr 24, 2018 at 7:35
  • Are variables set before execution? Do they have any special symbols inside? And you can try to put them in double quotes (see edited answer) Apr 24, 2018 at 7:39
  • And try to add in sqlplus command the SID like /sqlplus -s user_name/passwd@SID @effective_date.sql ... Apr 24, 2018 at 7:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .