SQLPLUS Logon Question
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SQLPLUS Logon Question

  1. #1
    Join Date
    Oct 2001
    Posts
    59

    Unhappy

    I currenty have a script saved in my sql directory

    prompt Note - you are logged on as DPS
    set linesize 1000
    set pagesize 40
    set sqlp 'DPSdatabase> '
    set serveroutput on
    set trims on
    alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

    In the icon I have setup in desktop I have the command line pointing to this sql script.

    The issue is that whilst in my sqlplus session with my prompt as
    DPSdatabase>

    if I choose to connect to another database, my prompt remains as DPSdatabase>

    How can I get around this???

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hi

    "sqlp" is nothing but just to set the **prompt**.. Even though you change the database connection without closing SQL*Plus sesion it will remain same for the different database...

    I think you are looking for dynamic prompt which will tell yu to which database you are connected? Is this what u want to do?

    Sameer

  3. #3
    Join Date
    Oct 2001
    Posts
    59

    Unhappy SQLPLUS Login..

    Thanks for responding Sameer.

    Yes you are absolutly right - when I connect to a different instance ( say for example there are two instances - DPS database_cc (employee system) and DPSdatabase_nms (departments system). These can be either in Test database or the Live database - ie. dpsTest or dpsLive) ....

    Say I logon (via Icon sitting on desktop) which points to the script which logs me in to the DPS database_cc, so the prompt in sqlplus session will be say DPS database@Test>

    Now I connect as DPSdatabase_nms/passwd@dpsTest..

    .....I want the sql prompt to show this and perhaps even throw in a message saying : "you are now connected to DPSdatabase_nms"

    I actually am using the following script - so disregard the above

    column global_name new_value gname
    set termout off


    select lower(user) || '@' ||
    decode(global_name, 'dpsTest.WORLD', 'DPSdatabase_cc', 'dpsLive.WORLD',
    'DPSdatabase_cc', global_name) global_name
    from global_name;
    set sqlprompt '&gname> '
    set termout on

    set underline off

    What can you or anyone suggest please

  4. #4
    Join Date
    May 2002
    Posts
    37
    You can place your code in glogin.sql (in $ORACLE_HOME/plusXX), but this will only take care of your initial SQL*Plus login. It won't work with 'connect user/passwd'
    I have another file 'db.sql' in SQLPATH so I can always run it as

    SQL> @db

    to update my SQL prompt

    regards.

  5. #5
    Join Date
    Oct 2001
    Posts
    59

    Question

    ........Dave I do not have privleges to modify this sql file.

    Any other suggestions how I can dynamically achieve my objective?????????

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    oracle@simp:~> echo $SQLPATH
    /opt/oracle/scripts/DBA
    oracle@simp:~> cd $SQLPATH
    oracle@simp:~/scripts/DBA> cat login.sql
    set linesize 140
    set pagesize 20
    set termout off
    set feed off
    column sqlprompt new_value prompt
    column uname new_value username
    
    select user||userenv('sessionid') uname
    from dual;
    
    select lower(user)||'@'||global_name||'>' sqlprompt
    from global_name;
    
    set editfile /tmp/&username.buf
    set sqlprompt &prompt
    
    set termout on
    set feed on
    
    
    oracle@simp:~/scripts/DBA> sqlplus internal
    
    SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jul 26 09:31:03 2002
    
    (c) Copyright 2000 Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - Production
    
    sys@SIMP>

  7. #7
    Join Date
    Oct 2001
    Posts
    59

    Unhappy

    Pando,

    Thanks for the response, but........

    you did not include in your output the bit where you then re-connect to another instance. In doing so this would be reflected by your sqlprompt.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    cat conn.sql
    
    connect &1
    undefine &1
    @login.sql
    
    
    sys@SIMP> @conn.sql system
    
    system@SIMP>

  9. #9
    Join Date
    Oct 2001
    Posts
    59
    Unfortunately the login script when used by my desktop icon takes while to work and then starts up with the SQL>

    your second peice prompts me for a value but then disconnects from oracle.....

    The script which I have shown above - the one which is currently picked up by my sqlplus desktop icon - does the job I want so far as in that the prompt tells me which dbase eg.

    dps_cc@test_dbase>
    Above the uname would be dps_cc and passwd dps_cc

    then I do the following : -

    dps_cc@test_dbase> connect dps_nms@live_dbase
    Enter password: dps_nms
    Connected.
    dps_cc@test_dbase>
    *****the above prompt should now be dps_nms@live_dbase> not "dps_cc@test_dbase>" ****

    Hope this clearifies it

    Thanks in advance


  10. #10
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by dps

    dps_cc@test_dbase>
    Above the uname would be dps_cc and passwd dps_cc

    then I do the following : -

    dps_cc@test_dbase> connect dps_nms@live_dbase
    Enter password: dps_nms
    Connected.
    dps_cc@test_dbase>
    *****the above prompt should now be dps_nms@live_dbase> not "dps_cc@test_dbase>" ****
    If you change the database connection in the same SQL*Plus session (WITHOUT CLOSING SQL*PLUS AND SWITCH TO NEW DATABASE USING connect) as you are doing above, then setting of dynamic prompt will be defficult..

    execute your .SQL file again to set the prompt .. which will again execute sql statement to get the current instance name for the database ....

    otherwise on new connection the prompt will remain same . i.e instance name of old database...

    Don't hardcode username/password in login sql file..

    Pass following sql file to SQL*Plus .. SQL*Plus will prompt you for username and password.. but after login it will set the prompt.

    login.sql
    -----------------------------------------------------
    SET TERMOUT OFF
    COLUMN X NEW_VALUE Y
    SELECT global_name X FROM global_name;
    SET SQLPROMPT '&Y SQL>'
    SET TERMOUT ON
    -----------------------------------------------------

    To connect to new database close the SQL*Plus and open it again.. Now supply username and password for live database... and now it will run this sql file again and will set the promt for live database.


    Sameer

    [Edited by Sameer on 07-26-2002 at 09:23 AM]

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