Setting SQL Prompt within a script

[Horace]

Hi Guys,

Is there an easy way to set the Sql Prompt within a script. E.g

code.sql
--------

set heading on
set verify off

SET SQLP .............(select username from user_users


p.s. that should be semi-colon, right bracket, not a flamin smiley!!

[Scorby]

col username new_value username

select username||'> ' username from user_users;

set sqlprompt "&username"

[Horace]

Brillliant! Thankyou.

[dbtoo]

-- this sql can be usefull when using multi-server environments with many databases on each server.
-- all users who access this script must have access to v$database and v$session, otherwise, it will fail.
-- it will also fail when using sqlplus on an automatic bootup of the databases, i.e., not a good thing
-- to put into glogin.sql
-- it will also not reflect the correct information if you use remote connection from the current session,
-- in this case create a sql file called connect.sql and use '@connect user@instance' rather than 'connect user@instance'
-- this concept was adapted from another forum, I don't recall which - maybe asktom or tek-tips, I don't recall.
-- assumes unix
set time on
col user_id new_value user_id
col user_name new_value user_name
select user_id, lower(username) user_name from user_users where username = user;
col db_name new_value db_name
col dbn new_value dbn
select distinct(machine) as dbn from v$session where type='BACKGROUND'
and program like '%PMON%';
select name db_name from v$database;
set sqlprompt "&db_name@&dbn:SQL> "