Re: login.sql hangs

In article <1129241825.035941.290780_at_o13g2000cwo.googlegroups.com>, PHernandez says...
>
>EHllo!
>
>I have honestly stolen the following code from Mr. Kyte
>
>column global_name new_value gname
>set termout off
>select lower(user) || '@' || global_name as global_name from
>global_name;
>set sqlprompt '&gname> '
>set termout on
>

if you alter that to be:

define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name   from (select global_name, instr(global_name,'.') dot from global_name ); set sqlprompt '&gname> '

the DEFINE is key - if gname has a default definition, sqlplus won't prompt you for the value when the query doesn't fill it in.

The prompt will be

idle>

when the database isn't open.

>and have it in login.sql, which is nice. If the instance is idle it
>hangs (throws ORACLE NOT AVAILABLE without termout off) and one have to
>press enter go get by.
>
>I can live with that.
>
>However, I want my homebase computer to start Oracle at boot which
>can't be done if the login.sql is there. I got around it by unsetting
>SQLPATH in dbstart and dbshut, but not so nice. I also tried to wrap
>the select in an exception-block, but well- oracle is not available...
>
>Someone knows how to get it right?
>
>--ph
>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation