2

I have created a sample Oracle 12c PDB (Pluggable Data Base) using the instructions from here. How do I connect to this pluggable database using Hibernate application? I am using a sample Hibernate application from here

I changed the hibernate.cfg.xml file as follows:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>

    <property name="connection.url">jdbc:oracle:thin:@localhost:1521:sys</property>
    <property name="connection.username">sys as sysdba</property>
    <property name="connection.password">helloWORLD12</property>
    <property name="connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="dialect">org.hibernate.dialect.Oracle12cDialect</property>

    <property name="show_sql">true</property>

    <property name="format_sql">true</property>
    <property name="hbm2ddl.auto">create</property>

    <!-- JDBC connection pool (use the built-in) -->
    <property name="connection.pool_size">1</property>
    <property name="current_session_context_class">thread</property>

    <mapping class="net.codejava.hibernate.Book" />

  </session-factory>
</hibernate-configuration>

But I am getting the following error trace when I run the program:

Exception in thread "main" org.hibernate.HibernateException: Error accessing stax stream
    at org.hibernate.boot.cfgxml.internal.JaxbCfgProcessor.unmarshal(JaxbCfgProcessor.java:107)
    at org.hibernate.boot.cfgxml.internal.JaxbCfgProcessor.unmarshal(JaxbCfgProcessor.java:65)
    at org.hibernate.boot.cfgxml.internal.ConfigLoader.loadConfigXmlResource(ConfigLoader.java:57)
    at org.hibernate.boot.registry.StandardServiceRegistryBuilder.configure(StandardServiceRegistryBuilder.java:163)
    at net.codejava.hibernate.BookManager.setup(BookManager.java:23)
    at net.codejava.hibernate.BookManager.main(BookManager.java:100)
Caused by: javax.xml.stream.XMLStreamException: ParseError at [row,col]:[18,6]
Message: The processing instruction target matching "[xX][mM][lL]" is not allowed.
    at com.sun.org.apache.xerces.internal.impl.XMLStreamReaderImpl.next(XMLStreamReaderImpl.java:604)
    at com.sun.xml.internal.stream.XMLEventReaderImpl.peek(XMLEventReaderImpl.java:276)
    at org.hibernate.boot.cfgxml.internal.JaxbCfgProcessor.unmarshal(JaxbCfgProcessor.java:103)
    ... 5 more

Kindly let me know where I am going wrong. There are almost no resources online for using Oracle 12c PDB with Hibernate.

UPDATE 1: I had an extra line of XML code in my config file which lead to the XML parsing error. Now - how do I associate a CDB/PDB with a particular user account, since using the PDB through SYS user is not recommended.

I have a PDB named 'pdb1', and it is associated with sys user account. It is stored at the following location:

D:\app\myusername\virtual\oradata\orcl\pdb1

I created a new user 'c##test' and then created a pdb while logged in to the user 'c##test' using the following command: 

create pluggable database pdb3 admin user pdb_admin3 identified by helloWORLD12 
file_name_convert=('D:\app\myusername\virtual\oradata\orcl\pdbseed\', 
'D:\app\myusername\virtual\oradata\test\pdb3\');

'pdb3' is created successfully, but it is not getting associated to the user 'c##test'.

The error trace I am getting now is as follows: https://pastebin.com/skVMLkqT

pikaraider
  • 187
  • 1
  • 14
  • The error is an XML parsing error, not an Oracle error. You need to check the format of the XML file. Also: do you really have a PDB named `sys`? And why are you using the SYS account from your application? That is a really, really bad idea. You should create a regular user and use that from your application. Do **NOT** use SYS (or SYSTEM) for your application tables. –  Sep 11 '18 at 05:41
  • I have a PDB named 'pdb1', and it is associated with sys user account. It is stored at the following location: D:\app\myusername\virtual\oradata\orcl\pdb1. I created a new user 'c##test' and then created a pdb while logged in to the user 'c##test' using the following command: create pluggable database pdb3 admin user pdb_admin3 identified by helloWORLD12 file_name_convert=('D:\app\myusername\virtual\oradata\orcl\pdbseed\', 'D:\app\myusername\virtual\oradata\test\pdb3\'); 'pdb3' is created successfully, but it is not getting associated to the user 'c##test'. Why so? – pikaraider Sep 11 '18 at 07:05
  • That is a completely different question and has nothing to do with the parsing error of your Hibernate configuration file –  Sep 11 '18 at 07:08
  • @a_horse_with_no_name XML parsing is not the issue anymore. Actually I had an extra line of XML code and it was leading to the parsing error. Can you please tell me how do I create a pdb associated with a specific user. – pikaraider Sep 11 '18 at 07:14
  • Then you should update your question and include the real error message you get. –  Sep 11 '18 at 07:23

1 Answers1

2

The issue is with the syntax you are using. you are using :SID instead of /SERVICE_NAME , so make sure to change this line as :

<property name="connection.url">jdbc:oracle:thin:@localhost:1521/sys</property>

to see which services available, execute lsnrctl service.

Please also note that you are trying to use sys account which is a the same as root. to avoid future problems, it's best practice to create a new account an use that instead.

according to your stack trace you missed oracle driver dependency:

ClassNotFoundException: Could not load requested class : oracle.jdbc.OracleDriver

you should add this dependecy to your pom.xml file,Please take these steps for that:

1- Download ojdbc8.jar from oracle site:

https://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html

2- Install ojdbc8 to your local maven repository:

  • specify you path instead of Path/to/your/

    mvn install:install-file -Dfile={Path/to/your/ojdbc8.jar} DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=12.2.0.1 -Dpackaging=jar

3-Add Dependency to Pom.xml

<!-- ORACLE database driver -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>12.2.0.1</version>
        </dependency>
MohammadReza Alagheband
  • 2,110
  • 13
  • 19
  • If you use an URL with a service name it should use `@//localhost:1521` instead of `@localhost` (which is the old deprecated URL format) - but that is not the root cause of this problem. The driver never makes a connection as Hibernate fails to parse the XML file. –  Sep 11 '18 at 06:18
  • @MohammadRezaAlagheband, I am not able to associate cdb/pdb with a user account. How do I do that? I have updated my question. – pikaraider Sep 11 '18 at 09:05
  • 1
    @pikaraider I updated my answer , let me know if it works or not because you may need to use other version of ojdbc – MohammadReza Alagheband Sep 11 '18 at 09:25
  • I am getting the following error when I add ojdbc 8: Failure to find com.github.noraui:ojdbc8:jar:12.2.0.1 in https://repo.maven.apache.org/maven2 was cached in the local repository, resolution will not be reattempted until the update interval of central has elapsed or updates are forced – pikaraider Sep 11 '18 at 09:46
  • 1
    @pikaraider no problem, I update my answer to manually install it. please recheck – MohammadReza Alagheband Sep 11 '18 at 10:08
  • @MohammadRezaAlagheband, I am not able to download it manually too. I am getting the following error: "Bad Request. The request could not be understood by server due to malformed syntax." I tried downloading the jar from https://jar-download.com/artifacts/com.github.noraui/ojdbc8/12.2.0.1/source-code. Here also, I am getting an error. Kindly look into this. – pikaraider Sep 11 '18 at 10:14
  • download from here https://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html – MohammadReza Alagheband Sep 11 '18 at 10:17
  • if you are not able to download from oracle site take it from here https://drive.google.com/file/d/1uKxpw_TrXFye4zGpQHltGbf8XGj-GV1v/view?usp=sharing – MohammadReza Alagheband Sep 11 '18 at 10:21
  • @MohammadRezaAlagheband, I was able to download ojdbc8 alright. But program is giving the below error now: https://pastebin.com/DCSajh42 My hibernate.cfg.xml file is as follows: https://pastebin.com/sU6QaE4J I think the issue is with hibernate XML configuration. I have got some things wrong but I don't know where is it wrong. Can you clarify connection.url property? I added a user but custom pdb is not getting associated with it. Thanks. – pikaraider Sep 11 '18 at 11:02
  • try this jdbc:oracle:thin:@//localhost:1521/orcl – MohammadReza Alagheband Sep 11 '18 at 11:29
  • Getting the same error, @MohammadRezaAlagheband . Please look into the error log: "Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor" – pikaraider Sep 11 '18 at 14:03
  • lsnrctl service <-exectute and check if your requsting service is available – MohammadReza Alagheband Sep 11 '18 at 14:08
  • 1
    also check https://stackoverflow.com/questions/10786782/ora-12514-tnslistener-does-not-currently-know-of-service-requested-in-connect-d – MohammadReza Alagheband Sep 11 '18 at 14:09
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179823/discussion-between-pikaraider-and-mohammadreza-alagheband). – pikaraider Sep 11 '18 at 14:18
  • Output of lsnrctl service: Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MAHEIYER-LAP)(PORT=1521))) TNS-12546: TNS:permission denied TNS-12560: TNS:protocol adapter error TNS-00516: Permission denied 64-bit Windows Error: 13: Permission denied My tnsnames.ora: pastebin.com/jcHWdsHb My listener.ora: pastebin.com/Y8Fv2cv7 I am using windows 10. Kindly guide. – pikaraider Sep 11 '18 at 15:31
  • @pikaraider, I am co-creator of com.github.noraui, this jar file is deprecated, please use this sample: github.com/sgrillon14/MavenSampleOracleJdbc – Stéphane GRILLON Oct 04 '18 at 09:39