Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Sqlplus_Oracle8i

Arun_39
Occasional Visitor

Sqlplus_Oracle8i

SELECT '-',banner from v$version WHERE banner LIKE '% 8.%';

- Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
- PL/SQL Release 8.1.7.4.0 - Production
- TNS for Solaris: Version 8.1.7.4.0 - Production

but i need the output as

Oracle8i Enterprise Edition Release 8.1.7.4.0
PL/SQL Release 8.1.7.4.0
TNS for Solaris: Version 8.1.7.4.0
What i have to do ?
12 REPLIES
Keith Bryson
Honored Contributor

Re: Sqlplus_Oracle8i

If you can get the original output to a Unix file (/tmp/output for example) try:

cat /tmp/output | awk -F"-" {'print $2'} | cut -c2-

I don't know of a way in SQL - someone else may though.

Keith
Arse-cover at all costs
Mobeen_1
Esteemed Contributor

Re: Sqlplus_Oracle8i

Arun,
I don't know much about Sqlplus, but on Sybase ASE query we could achieve this.

Why don't you look into the sql guide and see if there is some string manipulation that can be done in the query using sqlplus.

Within the query we would output to a string and then do string manipulation before outputting..all in one sql statement

rgds
Mobeen
Arun_39
Occasional Visitor

Re: Sqlplus_Oracle8i

Hi Mobeen,

I am searching the same for last one hour.
but i didnt get.
Help me, if u have time.
Yogeeraj_1
Honored Contributor

Re: Sqlplus_Oracle8i

hi,

try either of:

select replace(banner,' - Production','') from v$version
where banner LIKE '% 8.%'

or:
select replace(replace(banner,' - Production',''),'Production','') from v$version
where banner LIKE '% 8.%'


hope this helps!
regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Sqlplus_Oracle8i

hi again,

why did you include '-' to your sql statement?


regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Mobeen_1
Esteemed Contributor

Re: Sqlplus_Oracle8i

Yogee,
You are the man !! No wonder you have a crown :)

I think replace should do the trick :), i know all these SQLs subscribe to ANSI and i should have figured that out ...

Unfortunately i have nothing to test ...

regards
Mobeen
Keith Bryson
Honored Contributor

Re: Sqlplus_Oracle8i

Apparently, talking to our DBAs here, you can do:

spool /tmp/output
run your SELECT statement
spool off

Then at Unix:

cat /tmp/output | awk -F"-" {'print $2'} | cut -c2-

HTH - Keith
Arse-cover at all costs
Peter Godron
Honored Contributor

Re: Sqlplus_Oracle8i

Arun,
Yogeeraj's sqlplus will work.
Just be aware of the 64bit addition in the banner, if you ever convert to 64bit.
Regards
Arun_39
Occasional Visitor

Re: Sqlplus_Oracle8i

hi,

it's working fine.
is it possible to print like this

Oracle8i Enterprise Edition Release 8.1.7.4.0
PL/SQL Release 8.1.7.4.0
TNS for Solaris: Version 8.1.7.4.0

that is like table format.


i.e Align format ?

Arun_39
Occasional Visitor

Re: Sqlplus_Oracle8i

hi Bryson,
urs is working fine.

i need the output as align format.
Yogeeraj_1
Honored Contributor

Re: Sqlplus_Oracle8i

hi,

you can use chr(9)


for instance, one easy way would be, assuming the version is always 8.1.7.4.0, you can do a replace of 8.1.7.4.0 with chr(9)||'8.1.7.4.0'...


nb. chr(9) = tab character

hope this helps!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Keith Bryson
Honored Contributor

Re: Sqlplus_Oracle8i

This works - but it's dirty!!! If your version number changes, it'll stop working:

cat /tmp/output | awk -F"-" {'print $2'} | cut -c2- | awk -F"8.1" {'print $1"\t8.1"$2'}

Keith
Arse-cover at all costs