Home » Infrastructure » Unix » records returned from sqlplus command coming in a single row
records returned from sqlplus command coming in a single row [message #267487] Thu, 13 September 2007 08:15 Go to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member
Hi All,

In an unix script, I am using sqlplus to retrieve data from an oracle table. The sql statement fetches 4 columns based on a condition and number of records returned might be 1000.

I am using the following code:

`sqlplus<<endofsql -S xxx@yyy/app1
set heading off;
select a,b,c,d from ap.log where b='abc';
exit;
endofsql`

I tried capturing the output returned from this command into a file and found that each record is not stored on a separate line but all records are being stored on a single line with each field separated by a space.

I want to process record by record.

Any inputs on this will be of great help.

Regards,
ssunda.
Re: records returned from sqlplus command coming in a single row [message #267494 is a reply to message #267487] Thu, 13 September 2007 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the execution you have on your screen.
Just that, no more, no comment, just what you've done.

Regards
Miochel
Re: records returned from sqlplus command coming in a single row [message #267502 is a reply to message #267487] Thu, 13 September 2007 09:46 Go to previous messageGo to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member
script:

echo `sqlplus<<endofsql -S xx@yy/ap11
set heading off;
select a,b,c,d from apps.xx where b='donothing';
exit;
endofsql` > temp.txt


$cat temp.txt
$Dev donothing 13-MAR-07 13-MAR-07 Dev donothing 14-MAR-07 14-MAR-07 LDev donothing 15-MAR-07 15-MAR-07 LDev donothing 17-MAR-07 17-MAR-07 LDev donothing 18-MAR-07 18-MAR-07


Re: records returned from sqlplus command coming in a single row [message #267503 is a reply to message #267487] Thu, 13 September 2007 09:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Instead try a variation on the same theme:
sqlplus<<endofsql -S xx@yy/ap11
set heading off;
spool temp.txt
select a,b,c,d from apps.xx where b='donothing';
spool off
exit;
endofsql
cat temp.txt
Re: records returned from sqlplus command coming in a single row [message #267700 is a reply to message #267487] Fri, 14 September 2007 05:17 Go to previous messageGo to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member

Hi anacedent,

I tried with the code give by you(spool). The rows are still being output to a single row.

Regards,
ssunda.
Re: records returned from sqlplus command coming in a single row [message #267714 is a reply to message #267700] Fri, 14 September 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
> sqlplus <<eof -s /
> set head off
> spool t.lst
> select object_id, object_name from all_objects where rownum<=3;
> exit
> eof
        17 FILE$
        53 BOOTSTRAP$
        27 I_COBJ#

3 rows selected.

> cat t.lst
        17 FILE$
        53 BOOTSTRAP$
        27 I_COBJ#

3 rows selected.

Have them in several lines.

Regards
Michel
Re: records returned from sqlplus command coming in a single row [message #267733 is a reply to message #267487] Fri, 14 September 2007 07:51 Go to previous messageGo to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member

Michel, I executed your script. But output is in the following format:

Dev
donothing
13-MAR-07 13-MAR-07 

LDev 
donothing 
15-MAR-07 15-MAR-07 

LDev 

donothing 
17-MAR-07 17-MAR-07


First 2 columns of record are coming in 2 separate lines. 3rd and 4th columns came on single line.

For 3rd record, Between 1st and 2nd columns there is additional new line.

Regards,
ssunda.

Re: records returned from sqlplus command coming in a single row [message #267734 is a reply to message #267733] Fri, 14 September 2007 08:10 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Set your linesize greater the sum of the column widths.
Set recsep off.

Regards
Michel
Previous Topic: use of EOF in scripting
Next Topic: Unix script file creation E-mail error
Goto Forum:
  


Current Time: Sat Dec 10 11:01:02 CST 2016

Total time taken to generate the page: 0.17625 seconds