ora-12203 can't connect from form builder to local database

[Jacek]

hello,
I have database 9i and developer 6i installed on my computer.
when trying to connect from form builder to database I get
ORA-12203:TNS:unable to connect to destination
SQL Plus, isqlplus work fine.
here are some of the configuration files:

$ORACLE_HOME = E:\oracle
$DEV_HOME=E:\dev
$SID=jac
$GLOBAL_DBNAME=jac
-----------------------------------------------------
$ORACLE_HOME\network\admin\listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

# (DESCRIPTION =
# (PROTOCOL_STACK =
# (PRESENTATION = GIOP)
# (SESSION = RAW)
# )
# (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2481))
# )
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = jac)
(ORACLE_HOME = E:\oracle)
(SID_NAME = jac)
)
)

LOG_FILE_LISTENER = lsnr
LOG_DIRECTORY_LISTENER = E:\oracle\network\log

------------------------------------------------------------
$ORACLE_HOME\network\admin\sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
names.default_domain = world
names.default_zone = world
names.directory_path= (TNSNAMES, HOSTNAME, ONAMES)

------------------------------------------------------------
$ORACLE_HOME\network\admin\tnsnames.ora

jac.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jac)
(SID = jac)
)
)

#INST1_HTTP =
# (DESCRIPTION =
# (ADDRESS_LIST =
# (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
# )
# (CONNECT_DATA =
# (SERVER = SHARED)
# (SERVICE_NAME = MODOSE)
# (PRESENTATION = http://HRService)
# )
# )

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

---------------------------------------------------------------
$DEV_HOME\net80\admin\sqlnet.ora

NAMES.DEFAULT_ZONE = world
NAMES.DEFAULT_DOMAIN = world
#SQLNET.EXPIRE_TIME = 0
#SQLNET.ENCRYPTION_SERVER = requested
#SQLNET.ENCRYPTION_CLIENT = requested
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)

-------------------------------------------------------------
$DEV_HOME\net80\admin\tnsnames.ora

JAC.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1526))
)
(CONNECT_DATA = (SID = jac))
)

--------
here are also logs:

---------------------------------------------------------
$DEV_HOME\forms60\sqlnet.log:

Fatal NI connect error 12203, connecting to:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=152
6))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=j
ac)(CID=(PROGRAM=E:\dev\BIN\ifbld60.EXE)(HOST=MOJA)(USER=jacek))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 8.0.6.0.0 - Production
Time: 03-STY-04 23:06:33
Tracing not turned on.
Tns error struct:
nr err code: 12203
TNS-12203: TNS:unable to connect to destination
ns main err code: 12538
TNS-12538: TNS:no such protocol adapter
ns secondary err code: 12560
nt main err code: 508
TNS-00508: No such protocol adapter
nt secondary err code: 0
nt OS err code: 0

---------------------------------------
lsnrctl status gives:
Service "PLSExtProc" has a number of instances 1.
Instancja "PLSExtProc", state UNKNOWN
Service "jac" has a number of instances 2.
Instancja "jac", state UNKNOWN
Instancja "jac", state READY
Service "jacXDB" has a number of instances 1.
Instancja "jac", state READY


I will be very greatful for any help.

--

best regards,
jacek

[Hans Forbrich]

Jacek wrote: hello, I have database 9i and developer 6i installed on my computer. when trying to connect from form builder to database I get ORA-12203:TNS:unable to connect to destination SQL Plus, isqlplus work fine. here are some of the configuration files:

Many people install a database server and various client software into
different ORACLE_HOMEs (usually because it's necessary). Then they set
up the Oracle Networking stuff for the ORACLE_HOME related to the
server, but totally forget that the client software has a completely
independant set of Oracle Networking configuration in the client's
ORACLE_HOME.

Please check that ALL of your $ORACLE_HOME/network/admin/tnsnames.ora
files point to the server. Apparently the one for your server already
does. Suggest you verify same for the developer kit.

Please note that LISTENER.ORA is for the receiver's (database server)
side, whereas TNSNAMES.ORA is for the sender's (client's) side.

Finally, please note that even when they are installed with the server,
SQL*Plus and iSQL*Plus are "client side" utilities. This is the cause
for much confusion.

HTH
/Hans

[Jacek]

> Many people install a database server and various client software into different ORACLE_HOMEs (usually because it's necessary). Then they set up the Oracle Networking stuff for the ORACLE_HOME related to the server, but totally forget that the client software has a completely independant set of Oracle Networking configuration in the client's ORACLE_HOME.

I configured files mentioned earlier using some short how-to especially for
it, but not for 9i and XP Professional (8i and 98), but it deosn't work. I
looked in Oracle9i Net Services Administrator's Guide.pdf and Oracle9i Net
Services Reference Guide.pdf, I tried google tips, I've been trying to
solve this problem for 3 days now and I'm beginning to feel frustrated:(

what is the most freaky thing is that when I run from developer Net8 Easy
Config (E:\dev\BIN\N8SW.EXE), change in the first window network
configuration directory from E:\oracle\NET80\ADMIN\ to E:\dev\NET80\ADMIN\
and then tested the service it worked fine!!!
so maybe it's something about editing registry entries....

when I do C:\Documents and Settings\jacek>e:\oracle\bin\TNSPING.EXE jac
Using
E:\oracle\network\admin\sqlnet.ora
using TNSNAMES adapter for this alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = 127.0.0.1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP
)(HOST = 127.0.0.1)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = jac)
(SID = jac)))
OK (110 ms)

but when trying C:\Documents and Settings\jacek>e:\dev\BIN\TNSPING80.EXE
jac
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))
TNS-12538: TNS:no such protocol adapter

--
best regards, jacek