DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Sqlplus Specify connection string on command line

  1. #1
    Join Date
    Oct 2002
    Location
    london
    Posts
    41

    Sqlplus Specify connection string on command line

    I seem to remember reading about a way of being able to specify the entire connection string on the command line when using sqlplus. Ie you can give the client the details of a database that isn't in the tnsnames.ora file, or force it to use a different port.

    Does anyone know the syntax for this?
    OCP 8i DBA

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    create a file called login.sql and put it in your SQLPATH

    Code:
    set line 180
    set pagesize 40
    set termout off
    
    column gname new_value gname
    variable server varchar2(64)
    
    declare
      l_role varchar2(36);
    begin
      select role
        into l_role
        from session_roles
       where role='DBA';
      if l_role = 'DBA'
      then
        select '-' || upper(host_name)
          into :server
          from v$instance;
      else
        :server := '';
      end if;
    end;
    /
       
    select
      lower(user) || '@' ||
      substr(global_name, 1, decode(instr(global_name, '.', -1, 1), 0, length(global_name) + 1, instr(global_name, '.', -1, 1) ) - 1) || :server gname
    from global_name;
    
    set sqlprompt '&gname>'
    
    set termout on

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    sqlplus
    'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))(CONNECT_DATA=(SERVIC E_NAME=ora9i)))'

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hm I confused the question hehe

  5. #5
    Join Date
    May 2003
    Location
    hyderabad, India
    Posts
    10
    i think u r talking bout Oracle Internet Directory(OID) concept(Oracle Directory Services), where the sqlnet.ora has to be configured and the authentication is done at the server. here the tnsnames.ora is not required to be configured. it is useful where the number of databases are high and configuring each client's tnsnames.ora everytime a database is added to the server is not advisable.

    it is a guess.

    Partha

  6. #6
    Join Date
    Oct 2002
    Location
    london
    Posts
    41
    Cheers that connect string worked nicely. Handy for testing out jdbc urls
    OCP 8i DBA

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pando
    hm I confused the question hehe
    Right answer, wrong question
    Jeff Hunter

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