How to expose a SQL query using Oracle AS Jpublisher

Asked By 0 points N/A Posted on -

I would like to know , how to use expose SQL query using a Oracle AS Jpublisher. Kindly provide me a step to step guide with a working example code.

Answered By 0 points N/A #124411

How to expose a SQL query using Oracle AS Jpublisher


This can be perform using the JPublisher utility available in Oracle Application server.

Below are the sequence of step and a example on how to expose a SQL statement as webservice.

1) Create a Oracle Object Type using the below SQL in the oracle DB.

create or replace type address as object
 street varchar2(50),
 city varchar2(30),
 state varchar2(2),
 zip varchar2(10),
 map member function get_address return varchar2 )
not final;

2) create the oracle objects body.

create or replace type body address
map member function get_address
return varchar2
l_address varchar2(200);
l_address :=
street || ' ' ||
city || ' ' ||
state || ' ' ||
return l_address;

3) Create a oracle table that hold the objects in a table.

create table address_table of address

4) Now insert data into the above created table using the PLSQL code.

l_address address;
l_address := address(
'Mountain View', 'CA', '94055' );
insert into address_table values ( l_address );
dbms_output.put_line ( l_address.get_address() );

5) now create a Java wrapper class using the Oracle JPublisher using the below command.

jpub -user=USERNAME/PASSWORD -methods=none -builtintypes=jdbc -numbertypes=objectjdbc -usertypes=jdbc -sql address:Address:MyAddress -package=sample.jpubs

THis will create a Java Interface class called MyAddress. This class belongs to the object Sample.jpubs

6) We will now add the method called getAddress() to the generated file,
which in turn invokes the get_address() method of the address object type:
public String getAddress( Connection connection )
throws SQLException
String getAddressStmt =
"begin ? := " + getSQLTypeName()+".get_address( ? ); end;";
CallableStatement cstmt = null;
cstmt = connection.prepareCall ( getAddressStmt );
cstmt.registerOutParameter ( 1, OracleTypes.VARCHAR );
// pass the second parameter corresponding to the
// implicit parameter "self".
cstmt.setObject( 2, this );
String address = (String) cstmt.getObject( 1 );
return address;
JDBCUtil.close ( cstmt );
We need to import the classes book.util.JDBCUtil and java.sql.CallableStatement
for the MyAddress class after adding the previous method, in order to compile the file
We pass a Connection object in the wrapper method getAddress(). This is required to
execute the object’s get_address() method after connecting to the database.

import java.util.Map;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import book.util.JDBCUtil;
import book.ch10.jpub.MyAddress;
public class DemoSQLData
public static void main(String[] args) throws Exception
Connection connection = null;
connection = JDBCUtil.getConnection(
"benchmark", "benchmark", "ora10g" );
Map myMap = connection.getTypeMap();

Class.forName( MyAddress.class.getName() ) );
_demoSelect( connection );
JDBCUtil.close ( connection );

private static void _demoSelect( Connection connection )
throws SQLException
PreparedStatement pstmt = null;
ResultSet rset = null;
String selectStmt = "select value(a) from address_table a";
pstmt = connection.prepareStatement ( selectStmt );
rset = pstmt.executeQuery();
while ( )
MyAddress address = (MyAddress) rset.getObject(1);
System.out.println( address.getAddress( connection ) );
JDBCUtil.close( rset );
JDBCUtil.close( pstmt );


This complete the creation of the Java Wrapper class which we can use in normal Java web service which exposes SQL statement
select * from  address_table

Login/Register to Answer

Related Questions