SQLPLUS Logon Question - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: SQLPLUS Logon Question

  1. #11
    Join Date
    Jun 2000
    Madrid, Spain
    Originally posted by dps
    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.

    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
    *****the above prompt should now be dps_nms@live_dbase> not "dps_cc@test_dbase>" ****

    Hope this clearifies it

    Thanks in advance

    read the post, I put @conn USERNAME@TNS not username/password@tns

    look conn.sql

    have you noticed I have set a SQLPATH variable... do you have that?

  2. #12
    Join Date
    Oct 2001

    Thumbs up

    Thanks Sameer.

    Ok so it seems i'll have to admit defeat.

    I'll just have to accept that having so many instances can be handled by above script(s) but there is a limit to how much a requirement such as mine can be met.


  3. #13
    Join Date
    Oct 2001

    This is getting a bit confusing - maybe we 're on two wave-lengths.

    Firstly, I operate in an NT env not UNIX.

    Secondly, I 'cannot' modify the glogin (or login.sql) file as defined under the SQLPATH.

    From what I gethered looking at your suggestion, you modified you login.sql to include -

    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

    What I have done is, I have a desktop Icon, in it's Target section I have I:\ORANT\BIN\PLUS33W.EXE DPSdatabase_cc/DPSdatabase_cc@dpstest @env.sql (the equivalent of the login.sql if you like)

    This env.sql is in my C:\user\sql\dpstest directory as defined under Start section in for the icon

    This env.sql contains the following -
    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

    Then from your suggestion I understand that you have another sql file - conn.sql? In it you have defined

    connect &1

    undefine &1


    you call it at the prompt using @conn username?????

    Now the sequence of events is as follows -

    1) Launch desktop icon (one mentioned above)
    RESULT: I get a prompt DPSdatabase_cc@dpstest>
    i.e. the test database
    2) At the prompt type the following -
    conn DPSdatabase_cc/DPSdatabase_cc@dpslive
    Enter password: ********

    although global_name does come up as dpslive

    3) At the 'DPSdatabase_cc@dpstest>' prompt for a new sqlplus session type conn DPSdatabase_nms@dpslive
    RESULT: Enter password: ********
    although global_name does come up as dpslive

    4) If I change the contents in my env.sql to that of your suggestion the after launching the icon I get -
    a blinkin' cursor, pressing enter gives me 'SQL>'
    althought he global_name is in fact dpstest which is correct

    5) Then at this prompt call the conn.sql

    ERROR: ORA-03121: no interface driver connected - function not performed

    Warning: You are no longer connected to ORACLE.

    So how can I switch from test to live and vica-versa and get the sqlprompt to reflect the instance??

    In my case for the test database I have the DPSdatabase_cc and DPSdatabase_nms and likewise on live.

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