0

I have a Query(Oracle) in Bash like that

RETVAL=`sqlplus -S /nolog <<EOF
         connect $ORCL_USR/$ORCL_PWD@$ORCL_TNS;
        SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
        select trim(Colm1) from $MyTable 
    EXIT;
    EOF`
 Result="Result.txt"
 touch $Result
 echo "$RETVAL" >> "$Result"
 cat $Result

If i just select a column then the result is correct , every column( i just have a column) have a line so.

cat Result.txt
 111
 222
 333

The problem is when i try to query 2 columns or more so in Revalt i put:

  select trim(Colm1),trim(column2) from $MyTable

when i do the Cat $Result.txt the result is:

111
Car
222
Hause
333
Dog

when i would wish

111 Car
222 Hause
333 Dog

So every row in a line for the File.

How can achieve it?

Thanks in Advance , Enrique

Enrique Benito Casado
  • 1,914
  • 1
  • 20
  • 40
  • 1
    What are the column data types in your table? Looks like the output is being wrapped because it is too wide. [Possible duplicate](https://stackoverflow.com/q/3006431/266304) if you just need to `set linesize`.. – Alex Poole Jul 12 '17 at 11:44
  • Hi @AlexPoole I thoung the same Column1 -> nummber , Column2-> Varchar 255 , it seems what you said. I ll try it. – Enrique Benito Casado Jul 12 '17 at 11:59
  • 1
    OK, then try adding `LINESIZE 300` to your `SET` command. Why are you using `trim()` for a number - so it's aligned on the left instead of the right? Using `to_char()` would be more intuitive. – Alex Poole Jul 12 '17 at 12:20
  • Adding set LINESIZE 300 to the SET command ,didn't solve the problem. i just put the trim() cause i wanted to be sure that was not empty whitespace in the database(even if it was a number) . I know it could seen stupied but i was desesperate. – Enrique Benito Casado Jul 12 '17 at 13:48

1 Answers1

1

You can use the spool command to get the desired output. 

sqlplus -S /nolog <<EOF
connect $ORCL_USR/$ORCL_PWD@$ORCL_TNS;
spool filepath/Result.txt
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select trim(Colm1),trim(column2) from $MyTable 
EXIT;
spool off;
EOF
cat filepath/Result.txt
Lohit Gupta
  • 1,045
  • 6
  • 11