40783

Accessing the Custom Object Return type from ojdbc6 JDBC Thin Drivers

Question:

I'm writing some JDBC code which calls a Oracle 11g PL/SQL procdedure which has a Custom Object return type. I can get the code to call the procedure, but how do I access the returned Custom Object to obtain it's contained values?. An example of my code calling the procedure is below:

PLSQL Code:

Procedure GetDataSummary (p_my_key IN KEYS.MY_KEY%TYPE, p_recordset OUT data_summary_tab, p_status OUT VARCHAR2);

Java Code:

String query = "begin manageroleviewdata.getdatasummary(?, ?, ?); end;"); CallableStatement stmt = conn.prepareCall(query); // Single IN parameter stmt.setInt(1, 83); // Two OUT parameters, one a Custom Object, the other a VARCHAR stmt.registerOutParameter(2, OracleTypes.ARRAY, "DATA_SUMMARY_TAB"); stmt.registerOutParameter(3, OracleTypes.VARCHAR); stmt.execute(stmt);

How do I get the result back fron this?

Answer1:

We've cracked it:

oracle.sql.ARRAY result2 = (oracle.sql.ARRAY) stmt.getObject(2); ResultSet rs = result2.getResultSet(); oracle.sql.STRUCT elements = (oracle.sql.STRUCT) rs.getObject(2); String result = null; if (elements != null) { Object[] objs = elements.getAttributes(); result = objs[2]; } System.out.println("Result: " + result);

In our case this prints the third element in our Custom Object type.

Recommend

  • Google Maps API for JavaScript isn't working properly when used in an AIR Android Project withi
  • Getting “No query executer factory registered for the 'plsql' language” Exception from Jav
  • function that return data type that can be used in SQL(oracle)
  • Failed to create creation time in APEX report
  • Checking if the “system” falls between two dates in SQL
  • How to escape colon (:) character while executing native SQL queries against an Informix database us
  • INSERT EXEC Statement cannot be nested [duplicate]
  • iOS Localization Doesn't Work with More Than 63 Files
  • Get used tables from sql query [duplicate]
  • why calling cd shell command through system() or execvp() from a child process won't work?
  • pillow imaging ImportError
  • Array with custom indexes in Ionic2
  • How to test if a URL from an Eclipse bundle is a directory?
  • ActiveRecord query for a count of new users by day
  • Set the selected item in dropdownlist in MVC3
  • How to create a file in java without a extension
  • Spring Data JPA custom method causing PropertyReferenceException
  • QLineEdit password safety
  • Splitting given String into two variables - php
  • What is Eclipse's Declaration View used for?
  • Counter field in MS Access, how to generate?
  • Possible to stop flickering java tooltip in heavyweight mode?
  • Check if a string to interpolate provides expected placeholders
  • sending/ receiving email in Java
  • How to set my toolbar fixed while scrolling android
  • Javascript + PHP Encryption with pidCrypt
  • Convert array of 8 bytes to signed long in C++
  • Why winpcap requires both .lib and .dll to run?
  • AT Commands to Send SMS not working in Windows 8.1
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Acquiring multiple attributes from .xml file in c#
  • Understanding cpu registers
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • apache spark aggregate function using min value
  • How can I remove ASP.NET Designer.cs files?
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Sorting a 2D array using the second column C++
  • java string with new operator and a literal