topic Re: Sqlplus_Oracle8i in Operating System - HP-UX

Sqlplus_Oracle8i

Arun_39 — Fri, 11 Mar 2005 04:18:49 GMT

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 ?

Re: Sqlplus_Oracle8i

Keith Bryson — Fri, 11 Mar 2005 04:28:32 GMT

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

Re: Sqlplus_Oracle8i

Mobeen_1 — Fri, 11 Mar 2005 04:38:36 GMT

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

Re: Sqlplus_Oracle8i

Arun_39 — Fri, 11 Mar 2005 04:46:01 GMT

Hi Mobeen,

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

Re: Sqlplus_Oracle8i

Yogeeraj_1 — Fri, 11 Mar 2005 04:48:39 GMT

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

Re: Sqlplus_Oracle8i

Yogeeraj_1 — Fri, 11 Mar 2005 04:51:15 GMT

hi again,

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

regards
yogeeraj

Re: Sqlplus_Oracle8i

Mobeen_1 — Fri, 11 Mar 2005 04:52:52 GMT

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

Re: Sqlplus_Oracle8i

Keith Bryson — Fri, 11 Mar 2005 04:53:02 GMT

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

Re: Sqlplus_Oracle8i

Peter Godron — Fri, 11 Mar 2005 04:54:51 GMT

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

Re: Sqlplus_Oracle8i

Arun_39 — Fri, 11 Mar 2005 05:04:34 GMT

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 ?

Re: Sqlplus_Oracle8i

Arun_39 — Fri, 11 Mar 2005 05:28:57 GMT

hi Bryson,
urs is working fine.

i need the output as align format.

Re: Sqlplus_Oracle8i

Yogeeraj_1 — Fri, 11 Mar 2005 06:05:07 GMT

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

Re: Sqlplus_Oracle8i

Keith Bryson — Fri, 11 Mar 2005 07:02:08 GMT

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