connecting to table to extract multiple rows into file from unix script

Shell Programming and Scripting
1

I need to extract the data from oracle table and written the below code.
But it is not working.There is some problem with the query and output is shown is No rows selected" . If I run the same query from sql developer there is my required output.

And if I run the shell script with simple sql query like "select * from emp" it is working.

ORACLE_SID=fpql;export ORACLE_SID
ORACLE_BASE=/dboracle/orabase ;export ORACLE_BASE
ORACLE_HOME=/dboracle/orabase/product/9.0.4.1_abc01i ;export ORACLE_HOME

ORA_USERNAME=abc@abc
ORA_PASSWD=abc
$ORACLE_HOME/bin/sqlplus -s $ORA_USERNAME/$ORA_PASSWD  << EOFSQL 
spool jobs123.dat           # for sending te jobs to te file jobs123
select * from process_histories WHERE pr_code='XYZ_job' and processed_date like '04-JAN-12';
spool off;
exit;
EOFSQL

Could you please have a look and share your views and any code change in the script.

Regards,
Giridhar

2 
... << EOFSQL
...
EOFSQL

Note that the here document delimiters (the two EOFSQL in your case) should be identical (trailing white space characters included).

Please post the exact output/error message that you're getting.

3

This is not the correct syntax for a comment in Oracle.

/* for sending the jobs to the file jobs123 */
spool jobs123.dat

Worth checking the directory where you expect to find jobs123.dat in case you have a similar file but with a very long name!

1 Like
4

The space is unexceptional, try the below one.

ORACLE_SID=fpql;export ORACLE_SID
ORACLE_BASE=/dboracle/orabase ;export ORACLE_BASE
ORACLE_HOME=/dboracle/orabase/product/9.0.4.1_abc01i ;export ORACLE_HOME

ORA_USERNAME=abc@abc
ORA_PASSWD=abc
$ORACLE_HOME/bin/sqlplus -s $ORA_USERNAME/$ORA_PASSWD <<EOFSQL 
spool jobs123.dat           # for sending te jobs to te file jobs123
select * from process_histories WHERE pr_code='XYZ_job' and processed_date like '04-JAN-12';
spool off;
EOFSQL
5

@Rksiva
The extra space character before the "<<" is not a problem.

@giridhar276
Please post what Operating System and version you have and what Shell your are using.

I have come across Shells which expand the asterisk character in a "here" document.
If you get a list of files from this test script, you need to protect the asterisk.

cat << EOF
Unprotected
*
Protected
\*
EOF

The normal output from a Posix Shell should be:
./scriptname
Unprotected
*
Protected
\*
6

Good catch! This will definitely cause an error.
For comments that don't span multiple lines, you could use also double dash (--).

7

Or even the old way.

REMARK A single line of comment in an Oracle SQL Plus program
8

Yes, or even with the shorter rem :slight_smile: