SQLPLUS query in Unix script

Shell Programming and Scripting
1

Hi,

I am using sqlplus query to get results in a csv format in unix. I am using ksh, and below is the query.

echo "select r.num|| ',' || p.path ||',"' || r.issue_description ||'",' ||p.timestamp from events r, messagepath p;">> $QUERY_FILE
 
sqlplus -s $LOGIN @ $QUERY_FILE>>$OUTFILE

The query runs fine and creates OUTFILE. The problem is instead of value of r.issue_description coming in the results, it is being outputted as it is. The output is like below.

123,temp.node.leaf, ||r.issue_description||,20100301
126,temp.node1.node2.leaf, ||r.issue_description||,20100301
236,temp.node.node3.leaf, ||r.issue_description||,20100301
589,temp.node.node4.leaf, ||r.issue_description||,20100301

The same query runs fine when ran against sql developer. I am assuming its something to do with the unix single and double quotes. Please can someone help.

2

Try to remove the double quotes inside the string:

echo "select r.num|| ',' || p.path ||',' || r.issue_description ||',' ||p.timestamp from events r, messagepath p;" >> $QUERY_FILE
3

Hi,

Earlier the query was same without the double quotes and works fine. But the problem is, since the issue_discription has commas in its value, while opening the csv file, the formatting is all lost. Hence I included the quotes to enclose the text value as should be done normally while running any DB query.

---------- Post updated at 05:06 AM ---------- Previous update was at 04:28 AM ----------

Hi, I got the issue. Just use the escape character and it works!!

echo "select r.num|| ',' || p.path ||',\"' || r.issue_description ||'\",' ||p.timestamp from events r, messagepath p;">> $QUERY_FILE
 
sqlplus -s $LOGIN @ $QUERY_FILE>>$OUTFILE

Thanks all.