Setting SQL Prompt within a script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Setting SQL Prompt within a script

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Setting SQL Prompt within a script

    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!!

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    col username new_value username

    select username||'> ' username from user_users;

    set sqlprompt "&username"

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Brillliant! Thankyou.

  4. #4
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    -- 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> "

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width