1748151 Members
3504 Online
108758 Solutions
New Discussion юеВ

Re: Sqlplus_Oracle8i

 
Arun_39
New Member

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 12
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
New Member

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
New Member

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 ?