0 Replies Latest reply: Jan 15, 2014 10:21 PM by user5778742 RSS

Running JSP on TOMCAT 7.0.50 cannot Oracle 12c on Windows 8.1 returns ORA-12505

user5778742 Newbie
Currently Being Moderated

I have installed and configured and using JSP, Tomcat 7.0.50 , Oracle 12c running Windows 8.1

 

Oracle driver - ojdbc7.jar

 

I am running a basic JSP test which is failing with

Basic Test SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

 

This is weird because I can connect using sqlplus

 

SQL> conn dreamhome/dreamhome@pdborcl

Connected.

 

I can also connect using system to ORCL (as system). Howver PDBORCL is still  failing through TOMCAT

 

JSP Connection call

 

<%@ page import="java.sql.*" %>

Basic Test

 

<%

    Connection conn = null;

    try

    {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:pdborcl", "dreamhome", "dreamhome");

        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM dreamhome_users");

        //Print start of table and column headers out.println("");

        out.println("");

        out.println(" "); //Loop through results of query.

        while(rs.next())

        {

            out.println("");

            out.println("LOGIN_ID USERNAME            PASSWORD" + rs.getString("LOGIN_ID") + "    " + rs.getString("USERNAME") + "    " + rs.getString("PASSWORD") + "");           

        }

    }

 

  

TNSPING

..\product\12.1.0\dbhome_1\NETWORK\ADMIN>tnsping pdborcl

 

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 16-JAN-2

014 05:41:15

 

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

 

Used parameter files:

..\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdborcl)))

 

OK (10 msec)

 

..\product\12.1.0\dbhome_1\NETWORK\ADMIN>

       

       

TNSNAMES.ORA (extract)

 

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

PDBORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdborcl)

    )

  )

 

Database queries

 

SQL> select PDB from v$services;

 

PDB

------------------------------

PDBORCL

CDB$ROOT

CDB$ROOT

CDB$ROOT

CDB$ROOT

 

SQLPLUS example

SQL> conn dreamhome/dreamhome@pdborcl

Connected.

 

A working example

<%

    Connection conn = null;

    try

    {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "system", "password");

        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM dreamhome_users");

        //Print start of table and column headers out.println("");

        out.println("");

        out.println(" "); //Loop through results of query.

        while(rs.next())

 

Basic Test SQLException: ORA-00942: table or view does not exist

 

Failing example

<%

    Connection conn = null;

    try

    {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:pdborcl", "system", "password");

        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM dreamhome_users");

        //Print start of table and column headers out.println("");

        out.println("");

        out.println(" "); //Loop through results of query.

        while(rs.next())

        {

            out.println("");

            out.println("LOGIN_ID USERNAME            PASSWORD" + rs.getString("LOGIN_ID") + "    " + rs.getString("USERNAME") + "    " + rs.getString("PASSWORD") + "");           

        }

    }

 

Basic Test SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

 

TOMCAT config files

 

server.xml extract

<Resource name="jdbc/Oracle12C"
   auth="Container"
   type="oracle.jdbc.pool.OracleDataSource"
   driverClassName="oracle.jdbc.driver.OracleDriver"
   factory="oracle.jdbc.pool.OracleDataSourceFactory"
   url="jdbc:oracle:thin:@localhost:1521:pdborcl"
   user="dreamhome"
   password="dreamhome"
   maxActive="20"
   maxIdle="10"
   maxWait="-1" />

 

Any suggestions?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points