Setting sql prompt with service name???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Setting sql prompt with service name???

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    How can i set my sql prompt to show me the service I am looged into.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Put this in your login.sql

    Code:
    set termout off
    col user_name new_value user_name
    col db_name new_value db_name
    select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
    set sqlprompt "&user_name@&db_name> "
    set termout on
    set trimspool on
    set trimout on
    set serveroutput on size 1000000
    set timing on
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by marist89
    Put this in your login.sql

    Code:
    set termout off
    col user_name new_value user_name
    col db_name new_value db_name
    select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
    set sqlprompt "&user_name@&db_name> "
    set termout on
    set trimspool on
    set trimout on
    set serveroutput on size 1000000
    set timing on
    This shows me the sid I am connected to.

    Is there a way i can see the service i am conected to.

    I have exact same databases on 2 machines. One is for testing/Development and the other one is production.

    Test/Development is called louise_company and the production one is called company.

    Here is what it shows me

    company@sid7.herold.com> connect company/company@louise_company
    Connected.
    company@sid7.herold.com> connect company/company@company
    Connected.
    company@sid7.herold.com>

    As you can see it shows me the same promt for both the connections.
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  4. #4
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    How about this...
    P1=`/usr/xpg4/bin/id -u -n`
    P2=`hostname`
    PERM=$P1@$P2
    PS1='$PERM@$PWD
    $'; export PS1

    Thanks.
    Thanigaivasan.

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by thanigai
    How about this...
    P1=`/usr/xpg4/bin/id -u -n`
    P2=`hostname`
    PERM=$P1@$P2
    PS1='$PERM@$PWD
    $'; export PS1

    Thanks.
    Thanigaivasan.
    Isnt that for unix....

    I am on windows 2000
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  6. #6
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    I am sorry..
    ...I did not get a chance to work on windows platform...
    Thanks.
    Thanigaivasan.

  7. #7
    Join Date
    Sep 2001
    Posts
    163
    ronnie, this is what I did to get the username and machine name I connected to.

    Add this to the glogin.sql file located in sqlplus/admin:

    set termout off
    spool set_prompt.sql
    select distinct 'set sqlprompt '||user ||'-'|| b.terminal ||'> '
    from v$process b, dual
    where b.terminal is not null;
    spool off
    start set_prompt.sql
    set termout on


    I also created this file that goes in you Bin directory:
    (I named this file CONNECT.SQL)

    set termout off
    CONNECT &1
    start c:\glogin.sql


    What happens is then you select sqlplus, your prompt will look like this:

    SYS-ICEMAN> (I logged in as internal on the a machine named Iceman)

    Now if I want to connect to a different machine, I type
    @connect username/password@machine_name

    and I will get a new sql prompt showing the username and new machine name I just connected to.

    example:

    SYS-ICEMAN>@connect scott/tiger@maverick
    SCOTT-MAVERICK>


  8. #8
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    Try to create your network service name based on your instance name/database name.
    That will solve your purpose.

    Vijay.
    Say No To Plastics

  9. #9
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by ocp2b
    ronnie, this is what I did to get the username and machine name I connected to.

    Add this to the glogin.sql file located in sqlplus/admin:

    set termout off
    spool set_prompt.sql
    select distinct 'set sqlprompt '||user ||'-'|| b.terminal ||'> '
    from v$process b, dual
    where b.terminal is not null;
    spool off
    start set_prompt.sql
    set termout on


    I also created this file that goes in you Bin directory:
    (I named this file CONNECT.SQL)

    set termout off
    CONNECT &1
    start c:\glogin.sql


    What happens is then you select sqlplus, your prompt will look like this:

    SYS-ICEMAN> (I logged in as internal on the a machine named Iceman)

    Now if I want to connect to a different machine, I type
    @connect username/password@machine_name

    and I will get a new sql prompt showing the username and new machine name I just connected to.

    example:

    SYS-ICEMAN>@connect scott/tiger@maverick
    SCOTT-MAVERICK>

    Hi,

    This is what I wanted , but theres a problem here

    If from inside a sqlplus session i try to change my connection the prompt does not change.

    I created the connect.sql also.

    This is whats happenning

    COMPANY-THELMA>connect company/company@louise_company
    Connected.
    COMPANY-THELMA>

    It should show

    COMPANY_LOUISE> as the promt now
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  10. #10
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by marist89
    Put this in your login.sql

    Code:
    set termout off
    col user_name new_value user_name
    col db_name new_value db_name
    select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
    set sqlprompt "&user_name@&db_name> "
    set termout on
    set trimspool on
    set trimout on
    set serveroutput on size 1000000
    set timing on
    In this also the sid does not change if i try to connect to another database from withing a sqlplus session

    company@sid7.herold.com> connect www_dba/biteme2@brutweb
    Connected.
    company@sid7.herold.com>

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

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