Why does login.sql run ?
Matt, October 08, 2003 - 4:02 am UTC
Why does login.sql get executed on sqlplus /nolog ?
Is there any way to detect a /nolog and exit the login.sql when it is detected ?
Thanks in advance.
October 08, 2003 - 6:57 am UTC
I have this in my login.sql:
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on
You cannot "exit" a script but you can look at &gname and when set to "idle" just not "do something"
For example, you could have your login.sql be:
set termout off
define gname=idle
column global_name new_value gname
select 'real_login' global_name from global_name;
set termout on
@&gname
that'll either run a script idle.sql (when not connected) or real_login.sql (when connected)
Not be able to login to sql
Jamil, December 11, 2003 - 2:48 am UTC
Dear Sir Tom
I have install the developer 2000 in my workstation,but when I try to connect to SQL
I am getting this message .
SQL*Plus: Release 3.3.2.0.2 - Production on Thu Dec 11 10:30:27 2003
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect descriptor
Enter user-name:
My setting in the Register Editor
NLS_LANG = AMERICAN_AMERICA.AR8MSAWIN
Local jflf
Best Regards
Jamil
December 11, 2003 - 5:54 am UTC
means the sid in the connect descriptor is not the sid of the database you thought you were trying to connect to (eg: tnsnames.ora entry is botched)
Here, I'll show an example using the tns connect string direct -- it shows, if you use a junk SID, you get that error
$ /home/ora9ir2/bin/sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost.localdomain)(Port=1521))(CONNECT_DATA=(SID=garbage)))'
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Dec 11 10:54:38 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12505: TNS:listener could not resolve SID given in connect descriptor
$ /home/ora9ir2/bin/sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost.localdomain)(Port=1521))(CONNECT_DATA=(SID=ora9ir2)))'
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Dec 11 10:54:44 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
scott@ORA9IR2>
so, check your tnsnames.ora, you've entered it wrong (watch out for CASE as well)
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect descriptor
Jamil, December 13, 2003 - 3:01 am UTC
Dear sir
This my TNSNAMES.ORA FILE contains, that is created by SQL NET EASY CONFIGURATION
When change the first SID in this file to SID= SID=ora9ir2 as , you suggest but still not working do I have change the other SID in this file to SID=ora9ir2 or not can make the necessary change to my TNSNAMES.ORA this the original contains that is created by the system .
#This is a SQL*Net Configuration file generated by SQL*Net Easy Configuration.
#Attention: Do not modify this file yourself.
#If you do, your SQL*Net Easy Configuration may not function properly.
Example1.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = Production1)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = SID1)
)
)
Example2.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = spx.world)
(PROTOCOL = SPX)
(Service = Server_lsnr)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
Example3.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = nmp.world)
(PROTOCOL = NMP)
(Server = FinanceServer1)
(Pipe = ORAPIPE)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
Tcp-loopback.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 127.0.0.1)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
Beq-Local.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = beq.world)
(PROTOCOL = BEQ)
(PROGRAM = oracle73)
(ARGV0 = oracle73ORCL)
(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
jedf.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 192.168.0.6)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 192.168.0.6)
(Port = 1526)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
Thank you very much for your help
Best regards
Jamil
December 13, 2003 - 11:52 am UTC
so? i do not know what your SID should be -- sqlnet easy config only does what you tell it to do.
i've told you what is wrong -- you have the wrong sid in there. do you have a dba or anyone that can tell you the proper one to use.
Connection to Sql
Jamil, December 14, 2003 - 5:59 am UTC
Dear Sir
No I do not have dba , I am the dba in my organization , my oracle version as follow :
Oracle9i Enterprise Edition Release 9.2.0.1.0
Can you guide me.
Best Regards
December 14, 2003 - 9:46 am UTC
then you quite simply need to "remember what SID you used when you installed the database"
look in your list of windows services, the SID name will be part of the service name.
ok
Ram, March 09, 2004 - 1:34 am UTC
Dear Tom,
How to get Oracle SID?Does it default to the database_name?
March 09, 2004 - 11:45 am UTC
1* select instance_name from v$instance
ops$tkyte@ORA9IR2> /
INSTANCE_NAME
----------------
ora9ir2
Tom has nerves of steel
Sam, March 19, 2004 - 11:51 pm UTC
I admire not only your expertise, but - even more - your patience! :)
a reader, May 03, 2004 - 8:17 am UTC
jedf.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 192.168.0.6)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 192.168.0.6)
(Port = 1526)
)
)
(CONNECT_DATA = (*SID = ORCL*)
)
)
try *SID_NAME=ORCL*
I was also facing the same problem.
I don't know the reason but it worked in my case.
May 03, 2004 - 8:26 am UTC
you had the service name wrong would be one major reason. lsnrctl services will tell you what services a given listener has. most times, the service name is typed wrong.
patience is a virtue
A reader, May 05, 2004 - 12:59 pm UTC
As sam said, nerves of steel here ;O
Detect if connected in SQL*Plus
Dieter Oberkofler, August 03, 2007 - 9:17 am UTC
Is it possible in a SQL*Plus to detect if we are already connected?
August 05, 2007 - 1:36 pm UTC
select * from dual;
should do it - if not connected, you'll get a message to that effect.
SQL> show user
too...
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> show user
USER is "OPS$TKYTE"
ops$tkyte%ORA10GR2> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2> show user
USER is ""
ops$tkyte%ORA10GR2>
But what about terminating a script
Dieter Oberkofler, September 12, 2007 - 12:26 pm UTC
Tom,
You are right but I was always wondering, if there is a way to quit the execution of a script if we are not yet connected. Something similar to using WHENEVER SQLERROR or WHENEVER OSERROR.
Thank you,
Dieter
Great
Dieter Oberkofler, September 29, 2007 - 5:45 pm UTC
It would be great if you could have a look at my last comment on the sql*plus issue. Thank you.
October 03, 2007 - 2:01 pm UTC
there is not. you get an SP error (sqlplus error) not a database, not an OS error.