0

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)

4est
  • 2,440
  • 6
  • 25
  • 46
  • That's nothing to do with cron per se. You can do the same thing from the command line first. Is there a reason you want the SQL in the crontab itself, rather than in a script you execute from SQL\*Plus? – Alex Poole Mar 02 '17 at 10:43
  • You can use `-S`ilent command after `sqlplus` as `sqplus -s user/password`. – JSapkota Mar 02 '17 at 10:46
  • I have reason - the script will be executed at a time – 4est Mar 02 '17 at 10:47
  • @ JSapkota: I run with -s, can be data into column somehow? – 4est Mar 02 '17 at 10:51

1 Answers1

1

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.

Alex Poole
  • 161,851
  • 8
  • 150
  • 257
  • it's almost perfect, but I don't know why split one row fro 2 rows? ( I have 12 col) – 4est Mar 02 '17 at 11:25
  • @4est - you probably just need to increase the linesize; I added `lines 200`, adjust that number to match how long a single row of output might ever be. Going too high doesn't really hurt for this. – Alex Poole Mar 02 '17 at 11:28