Oracle Query return the whole row in a line bash

Question

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

What are the column data types in your table? Looks like the output is being wrapped because it is too wide. Possible duplicate if you just need to set linesize.. — Alex Poole
– Alex Poole, Commented Jul 12, 2017 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
– Enrique Benito Casado, Commented Jul 12, 2017 at 11:59
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
– Alex Poole, Commented Jul 12, 2017 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
– Enrique Benito Casado, Commented Jul 12, 2017 at 13:48

Lohit Gupta · Accepted Answer · 2017-07-12 11:46:09Z

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

answered Jul 12, 2017 at 11:46

Lohit Gupta

1,0816 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Enrique Benito Casado Over a year ago

Hi @Lohit thanks for the idea but with Spool i have exactly the same result.

Lohit Gupta Over a year ago

@Enrique check the column width of your columns and set linesize accordingly. I think linesize is exceeding the column width.

Enrique Benito Casado Over a year ago

Hi @Lohit i did it but it didnt get better . :/

Collectives™ on Stack Overflow

Oracle Query return the whole row in a line bash

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related