OraFAQ Forum: Unix » records returned from sqlplus command coming in a single row

Home » Infrastructure » Unix » records returned from sqlplus command coming in a single row

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

records returned from sqlplus command coming in a single row [message #267487]

Thu, 13 September 2007 08:15

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.

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Set your linesize greater the sum of the column widths.
Set recsep off.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 22:36:12 CST 2024