Sqlplus Specify connection string on command line

[blakmk]

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?

[pando]

create a file called login.sql and put it in your SQLPATH

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

[davey23uk]

sqlplus
'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))(CONNECT_DATA=(SERVIC E_NAME=ora9i)))'

[pando]

hm I confused the question hehe

[Partha]

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

[blakmk]

Cheers that connect string worked nicely. Handy for testing out jdbc urls

[marist89]

Originally posted by pando
hm I confused the question hehe

Right answer, wrong question