CODEWITHSUNDEEP
linuxoracle-databasesavecronsqlplus
4est
4est

Reputation: 3168

Change output format into SQLplus using Crontab

I'm using crontab to run SQL script. Below is my script:

#!/bin/sh
export ORACLE_HOME=/opt/xxx/oracle/client
date=$(date +%d.%m.%y-%T)
echo "select col1, col2 from table1;" |/opt/xxx/oracle/client/bin/sqlplus "abc/abc@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=111.0.0.1)(Port=9999))(CONNECT_DATA=(SID=sidX)))" > /usr/users/tuser/temp/file.$date.txt

Format of the file contains a lof o unnecessary data like:

enter image description here

How to export just result of query with column headers? (desirable is csv format)

Upvotes: 0

Views: 403

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

You can include SQL*Plus set commands in your echoed string, but will also need to embed new lines; switching from echo to printf might make that simpler/cleaer.

You can then concatenate your columns with a comma to get CSV output:

printf "set pages 0 lines 200 trimout on tab off feedback off\nselect col1||','||col2 from table1;exit" |  /opt/....

I've also increased the line size based on your comment. You can read more about the settings available, and formatting in general.

If your columns include strings that might contain commas, you can enclose their values in double-quotes, which would avoid them being misinterpreted as extra columns by Excel or other tools.

If you want CSV headers as well you can have a dummy query to get them, or use the prompt command to add the fixed text:

printf "set pages 0 lines 200 trimout on tab off feedback off\nprompt COL1,COL2\nselect col1||','||col2 from table1;exit" |  /opt/....

And you can use the -s flag to make SQL*Plus suppress its banners:

... exit" | /opt/xxx/oracle/client/bin/sqlplus -s "abc/abc@..."

Incidentally, if you can switch from using SID to service name, you could use shorted 'easy connect' syntax instead of the full TNS connect descriptor.

Quick demo with a simple query; if I do this from the command line (split onto two lines just to prevent scrolling):

printf "set pages 0 feedback off\nprompt Col1,Col2\nselect dummy||','||dummy from dual;" |\
  /path/to/sqlplus -s myuser/mypass@//myhost:myport/myservicename

the entire output I get is:

Col1,Col2
X,X

I would seriously consider putting at least the SQL commands into a .sql script file and executing that from SQL*Plus; and probably putting the entire command (call to sqlplus that executes the script) into a shell script - and you can then just call the shell script from cron.

Upvotes: 1

Related Questions