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?

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;
  • 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. – User123 Apr 24 '18 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) – Romeo Ninov Apr 24 '18 at 7:39
  • And try to add in sqlplus command the SID like /sqlplus -s user_name/passwd@SID @effective_date.sql ... – Romeo Ninov Apr 24 '18 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.