Archive for November, 2012

How to convert reference cursor to oracle pipelined table data results


Problem: Converting from reference cursor to oracle pipelined results

Solution: Package body declaration is shown here, it is assumed that the specification is already taken care to declare the pipelined record datastructure, if not please look into my other posts on how to declare the specification part of it at

https://javatoj2ee.wordpress.com/2012/08/30/oracle-pipelined-table-functions/

function get_customer_data

(firstName IN VARCHAR2 DEFAULT NULL, lastName in VARCHAR2 DEFAULT NULL)

return customerpipelineddata PIPELINED AS

output_record                 customerpipelineddata;

v_customerdetails           ref_cursor_type;

–select statement goes here

open v_customerdetails for (select statement query)

–Below is the part needed to have reference cursor data into pipelined data

loop
fetch v_customerdetails_cursor bulk collect into output_record limit batchsize;
for i in 1 .. output_record.count() loop
pipe row (output_record(i));
end loop;
exit when output_record.count < batchsize;
end loop;

end get_customer_data;

here customerpipelineddata  is the actual pipelined data record, a set of records of a particular data structure like

TYPE customerpipelinedrecord  IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

TYPE customerpipelineddata  IS TABLE OF customerpipelinedrecord;

Hope this helps….Please do not forget to like or post your comments 🙂

Leave a comment

Convert oracle result set to XML using


The OracleXMLQuery class generates XML given an SQL query.

getXMLSchema()

Description

This methods generates the XML Schema(s) corresponding to the specified query; returns the XML Schema(s).

Syntax

public org.w3c.dom.Document[] getXMLSchema();

getXMLString()

Description

Transforms the object-relational data, specified in the constructor, into a XML document. Returns the string representation of the XML document. The options are described in the following table.

Syntax Description
public String getXMLString(); Takes no arguments.
public String getXMLString(int metaType); The metaType argument is used to specify the type of XML metadata the XSU is to generate along with the XML.
public String getXMLString(org.w3c.dom.Node root); If not NULL, the root argument, is considered the root element of the XML doc.
public String getXMLString(org.w3c.dom.Node root,int metaType); If not NULL, the root argument is considered the root element of the XML doc. The metaType argument is used to specify the type of XML metadata the XSU is to generate along with the XML.

Following snippet converts oracle result set to XML WHERE cs is a valid callable statement object and rs is the jdbc result set object

import oracle.xml.sql.query.OracleXMLQuery;

OracleXMLQuery qry = new OracleXMLQuery(cs.getConnection(),rs); // prepare Query class

qry.useLowerCaseTagNames();
qry.setRowsetTag(“students”);
qry.setRowTag(“student”);
qry.setRaiseNoRowsException(true);
qry.setRaiseException(true);
str = qry.getXMLString();

Above snippet will create XML in the following format

<students>

<student>

<firstname>John</firstName>

<id>2323</id>

</student>

<student>

<firstname>Mary</firstName>

<id>343</id>

</student>

</students>

we will see in the next tutorial on how to transform this generated XML to user defined XML schema definition using XSLT

Leave a comment