spooling through shell script

Hi,

I want to spool the output from a .sql file to a .txt file through shell script.

the contents of .sql are:
spool /arboru02/scripts/customer_profile_def.txt
select profile_id ||','|| account_no
from customer_profile_def;
spool off
exit

and shell scrip is like:
#!/bin/sh
sqlplus 'username/pwd@instance'<<!
@/arboru02/scripts/customer_profile_def.sql>/arboru02/scripts/customer_profile_def.txt
exit
!

while executing the script im getting error unable to open file.

And please tell me what should be the path of the .sql and .txt files?

Thanks

You are doing the same thing twice. Either you want "spool" to write directly to a file, or you want sqlplus to write to standard out for it to be redirected (> in the shell script). Doing both might be harmless in this case.

As for why it doesn't work, it's probably because the /scripts/ directory is not writable by you. The output should logically go somewhere else. Try /var/tmp/customer_profile_def.txt

Thanks a lot for your response, the dir is writable by me.

i changed the script to the mentioned one and it worked:

#!/bin/sh

sqlplus username/pwd@instance @/arboru02/scripts/customer\_profile_def.sql

But can you tell me how can i do the vice-versa now that is loading from the .txt file into a table without using sql loader?

That's a COMPLETELY different topic. It depends on the data, the output format, etc. I suggest you repost this one as a new thread, but add specifics like the file format and the fields you want to import into. (If a moderator flags you for this, refer them to this post).

Hi,

I want to spool the output of query to a .txt file but when i am doing that along with the output of the query i am getting query itself and spool off in the output .txt file.

Plz tell me how can i just get the output of the query in the .txt file.

Thanks

Let me understand you correctly: you want to the output of your sql query, but you do not want the query itself in the output file?

ya you are right, i just want the output of the query in the output file.

Thanks

Hi ss,

I'm not that familiar with oracle or sqlplus but if its the line with 'spool off' you don't want (or whatever line you don't want) to be in the output file then you may want to consider manipulating the output file a little like using sed to remove unnecessary lines

You might get by via piping the output to "tail -n +2". Increase 2 to some number if your SQL query takes up multiple lines.

Have you tried using 'SET TERMOUT OFF' in your SQL?

(e.g.)
$ cat mysql.sql
SET FEEDBACK OFF
SET PAGESIZE 0
SET TERMOUT OFF
SPOOL testout.txt
SELECT SYSDATE FROM DUAL
/

From the SQLPLUS documentation:

SET TERMOUT

SET TERMOUT OFF suppresses the display so that
you can spool output from a script without seeing it on the screen.

If both spooling to file and writing to terminal are not required,
use SET TERMOUT OFF in SQL scripts to disable terminal output.

SQL*Plus User's Guide and Reference
Contents