Update:
I resolved the service name issue. At some point I put in the right combo of parameters (or maybe I omitted some) and this message was displayed:
The following key/value is missing: -s db_sid OR -sn db_service_name
The -sn option isn't in the docs as far as I can tell.

In my case, I replace the -s GDSRV with -sn pdborcl.

Now I'm getting all sorts of errors:  
Connecting to Oracle Database using...  
localhost, 1521, null pdborcl, GISD, ********, shapes, tl_2013_us_state, null, 0  
Dropping old table...  
   Table not previously created.  
Creating new table...  
java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype  

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Record #1 not converted.  
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Record #2 not converted.  
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

...  

Record #56 not converted.
56 record(s) not converted.
0 record(s) converted.
Done.

Original Question:

I am attempting to use the shapefile converter in Oracle12c and I'm not having much success and I think it is due to the container database (CDB) vs pluggable database (PDB). I need to somehow specify my PDB: pdborcl

If I provide the SID, I get an invalid username/password....as expected.

C:\temp>java oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s GDSRV -u GISD -d gisd -t shapes -f tl_2013_us_state -r 0 -g geom

host: localhost
port: 1521
sid: GDSRV
db_username: GISD
db_password: ********
db_tablename: shapes
shapefile_name: tl_2013_us_state
SRID: 0
db_geometry_column: geom
Connecting to Oracle Database using...
localhost, 1521, GDSRV null, GISD, ********, shapes, tl_2013_us_state, null, 0
java.sql.SQLException: ORA-01017: invalid username/password; logon denied

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:392)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:385)
        at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:938)
        at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:480)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:416)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:825)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:596)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at oracle.spatial.util.SampleShapefileToJGeomFeature.main(SampleShapefileToJGeomFeature.java:341)
Exception in thread "main" java.lang.NullPointerException
        at oracle.spatial.util.SampleShapefileToJGeomFeature.main(SampleShapefileToJGeomFeature.java:345)

From SQLPLUS, I would use the PDB name instead to connect and I'm in. How do you connect to the PDB when calling oracle.spatial.util.SampleShapefileToJGeomFeature?

java oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s pdborcl -u GISD -d gisd -t shapes -f tl_2013_us_state -r 0 -g geom

host: localhost
port: 1521
sid: pdborcl
db_username: GISD
db_password: ********
db_tablename: shapes
shapefile_name: tl_2013_us_state
SRID: 0
db_geometry_column: geom
Connecting to Oracle Database using...
localhost, 1521, pdborcl null, GISD, ********, shapes, tl_2013_us_state, null, 0
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at oracle.spatial.util.SampleShapefileToJGeomFeature.main(SampleShapefileToJGeomFeature.java:341)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

        at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:272)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:263)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486)

Here's the docs on the 12c converter: http://docs.oracle.com/cd/E16655_01/appdev.121/e17896/sdo_shapefile_converter.htm#SPATL1427

up vote 1 down vote accepted

I'm almost certain Oracle didn't update the docs for 12c.

I'm going to list out a few things that I got hung up on.

  1. For a pluggable database, you provide the name as the service name with the -sn switch.

  2. The docs has ojdbc5.jar in the class path. I didn't have this file in 12c, but I did have a ojdbc7.jar and that works.

  3. As for the actual issue that I reference in my question: my database was jacked. A large number of SDO views were broken which caused just about anything with SDO to break. I installed a new database, and did some checking...everything was good.

    The only thing I did different in the first database was to execute the sdoupggeom.sql to increase the size of the ordinate arrays in order to support very large geoemtries.

    A.4 Increasing the Size of Ordinate Arrays to Support Very Large Geometries

    So it appears that this script did some bad things to my database.

Here's an example of a command that worked in Oracle 12c:

C:\temp>java -cp C:\app\orcl\product\12.1.0\dbhomeTest\jdbc\lib\ojdbc7.jar;C:\app\orcl\product\12.1.0\dbhomeTest\md\jlib\sdoutl.jar;C:\app\orcl\product\12.1.0\dbhomeTest\md\jlib\sd
oapi.jar oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1523 -sn pdborclt -u GISD -d gisd -t shapes -f tl_2013_us_state -r 0 -g geom
host: localhost
port: 1523
service_name: pdborclt
db_username: GISD
db_password: ********
db_tablename: shapes
shapefile_name: tl_2013_us_state
SRID: 0
db_geometry_column: geom
Connecting to Oracle Database using...
localhost, 1523, null pdborclt, GISD, ********, shapes, tl_2013_us_state, null, 0
Dropping old table...
   Table not previously created.
Creating new table...
Converting record #10 of 56
Converting record #20 of 56
Converting record #30 of 56
Converting record #40 of 56
Converting record #50 of 56
56 record(s) converted.
Done.
  • Can you confirm this: (1) you ran the load successfully on 12c. (2) you enlarged the geometry type (3) you ran the exact same load again on the same database, and now it fails ? If this is true then there is some sort of incompatibility with the enlarged geometries and 12c. Please report that as a bug to Oracle. Make sure to provide a reproducible example. – Albert Godfrind Nov 8 '14 at 17:12

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.