• About

On Technology

~ SOA and Integration blog

On Technology

Category Archives: Database

Call Oracle PL/SQL procedure with XMLType from Java using JDBC

11 Friday Mar 2011

Posted by Padmarag Lokhande in Database, Integration, Java, Oracle

≈ 2 Comments

Tags

jdbc, oracle, oracleaq, plsql, stored procedure, xmltype

Recently I needed to call a stored procedure which had Oracle’s XMLType as IN and OUT parameters.

The first thing to do is add xdb.jar and xmlparserv2.jar file to your application lib. These contain the required class files for Oracle XML API. the jars can be found under your installation of oracle client lib folders. Also don’t forget to add oracle jdbc driver files – ojdbc6 or ojdbc14.

The code I setup was this –

XMLType reqInXml;
XMLType reqOutXML;
String atpInStr = "";
OpResponse output = null;

try {
   reqInStr = jaxbMarshalRequestToString(input);
   System.out.println("Input : " + reqInStr);
} catch (JAXBException ex) {
   logger.log(Level.SEVERE, null, ex);
} catch (IOException ex) {
   logger.log(Level.SEVERE, null, ex);
}

//input.getHeader().setNotes("THIS VALUE RETURNED BY FACADE : " + XXDS);
try {
   Connection con = XXDS.getConnection();
   //The IN parameter for stored proc is Oracle XDB XMLType
   reqInXml = XMLType.createXML(con, reqInStr);
   OracleCallableStatement stmt = (OracleCallableStatement) con.prepareCall("call DEMO_PROC.ProcessXMLRequest(?, ?, ?, ?)");
   stmt.setObject(1, reqInXml);

   //set out parameters
   stmt.registerOutParameter (2, OracleTypes.OPAQUE,"SYS.XMLTYPE");
   stmt.registerOutParameter(3, Types.INTEGER);
   stmt.registerOutParameter(4, Types.VARCHAR);

   stmt.executeQuery();
   int resultCode = stmt.getInt(3);
   String resultMsg = stmt.getString(4);
   System.out.println("result code : " + resultCode);
   System.out.println("result msg : " + resultMsg);
   if (resultCode == 101 || resultCode == 100){
      reqOutXML = XMLType.createXML(stmt.getOPAQUE(2));
      System.out.println("Output from ERP :" + reqOutXML.getStringVal());
      output = jaxbUnmarshalFromString(reqOutXML.getStringVal());
   }
}
....

This gave me “java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.ConnectionHolder40 cannot
be cast to oracle.jdbc.OracleConnection” Exception. The connection was being returned from GlassFish JDBC ConnectionPool and was instance of OracleConnectionPooldataSource.
The FIX proved tricky, but in the end it was simple –
Do this –

OracleConnection oraCon = con.unwrap(OracleConnection.class);
//The IN parameter for stored proc is Oracle XDB XMLType
atpInXml = XMLType.createXML(oraCon, atpInStr);

Pass the cast instance of OracleConnection to the XMLType API.

Advertisement

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Database Diff / Database Change Management

17 Tuesday Aug 2010

Posted by Padmarag Lokhande in Database

≈ Leave a comment

Tags

database, oracle

Recently we had a situation when two developers were working on same DB package. Delay in submission of deployment request by one dev resulted in package compilation error as a dependent Type was missing.
I was looking for solution that may help us avoid such situations in future.

Using SVN is one option; other is use of Database Change Management / Database Diff
Some tools for that are –

1) Oracle Change Management Pack
From the docs –
There are three core capabilities of Change Management that allow developers and database administrators to manage changes in database environments:
• Baseline – A point in time of the definition of the database and its associated database objects.
• Comparison – A complete list of differences between a baseline or a database and another baseline or a database.
• Synchronization – The process of promoting changes from a database definition capture in a baseline or from a database to a target database

It allows us to take a baseline(snapshot) at a fixed time and then later we can see how the DB schema and objects have changed. The CMP can also generate DDL scripts, though I am not sure we would want to use it.

Details
• http://download-east.oracle.com/docs/cd/B19306_01/em.102/b31949/change_management.htm
•http://www.oracle.com/technology/products/oem/pdf/change-management-pack-11g-datasheet.pdf

2) PL/SQL Developer Compare User Objects feature
This is available from Tools -> Compare User Objects

3) Oracle SQL Developer Database Diff feature
This is available from Tools -> Database diff
http://www.oracle.com/technology/products/database/sql_developer/files/what_is_sqldev.html#copy See “Schema Copy and Compare”

#1 looks to be most versatile and flexible but DBA rights may be necessary.
#2 & 3 can be used by any developer. I think Oracle SQL Developer is easier and provides more options.

Using any of the above option can help in –
1) Identifying the changed objects and may also serve as a Check List before requesting deployment.

2) The developers concerned can take ownership of specific changed objects.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • April 2020
  • February 2019
  • April 2018
  • July 2015
  • July 2013
  • October 2012
  • June 2012
  • May 2012
  • September 2011
  • April 2011
  • March 2011
  • December 2010
  • August 2010

Categories

  • Camel
  • Database
  • Devops
    • Amazon AWS
    • Docker
    • Kubernetes
  • Integration
  • Java
  • JMS
  • MuleSoft
  • Oracle
  • Siebel
  • SOA
    • BPEL
    • REST
  • Uncategorized
  • Zapier

Meta

  • Register
  • Log in

Create a free website or blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • On Technology
    • Already have a WordPress.com account? Log in now.
    • On Technology
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
%d bloggers like this: