Shell Programming and Scripting

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/scr...rofile_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