The following is the exact implementation from https://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pipelined_example.htm#CHDHDHEE
Let's assume we have the following table deifinition:
CREATE TABLE StockTable (
ticker VARCHAR(4),
openprice NUMBER,
closeprice NUMBER
);
Then, we've got the following types (A basic type as object and a type as table of it):
CREATE TYPE TickerType AS OBJECT
(
ticker VARCHAR2(4),
PriceType VARCHAR2(1),
price NUMBER
);
/
CREATE TYPE TickerTypeSet AS TABLE OF TickerType;
Also, we have defined an ODCI type with its implementation in both SQL and Java Stored Procedure within database:
SQL:
CREATE OR REPLACE TYPE StockPivotImpl AS OBJECT
(
key INTEGER,
STATIC FUNCTION ODCITableStart(sctx OUT StockPivotImpl, cur SYS_REFCURSOR)
RETURN NUMBER
AS LANGUAGE JAVA
NAME 'StockPivotImpl.ODCITableStart(oracle.sql.STRUCT[], java.sql.ResultSet) return java.math.BigDecimal',
MEMBER FUNCTION ODCITableFetch(self IN OUT StockPivotImpl, nrows IN NUMBER,
outSet OUT TickerTypeSet) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'StockPivotImpl.ODCITableFetch(java.math.BigDecimal, oracle.sql.ARRAY[]) return java.math.BigDecimal',
MEMBER FUNCTION ODCITableClose(self IN StockPivotImpl) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'StockPivotImpl.ODCITableClose() return java.math.BigDecimal'
);
/
Java Stored Procedure:
import java.io.*;
import java.util.*;
import oracle.sql.*;
import java.sql.*;
import java.math.BigDecimal;
import oracle.CartridgeServices.*;
// stored context type
public class StoredCtx
{
ResultSet rset;
public StoredCtx(ResultSet rs) { rset=rs; }
}
// implementation type
public class StockPivotImpl implements SQLData
{
private BigDecimal key;
final static BigDecimal SUCCESS = new BigDecimal(0);
final static BigDecimal ERROR = new BigDecimal(1);
// Implement SQLData interface.
String sql_type;
public String getSQLTypeName() throws SQLException
{
return sql_type;
}
public void readSQL(SQLInput stream, String typeName) throws SQLException
{
sql_type = typeName;
key = stream.readBigDecimal();
}
public void writeSQL(SQLOutput stream) throws SQLException
{
stream.writeBigDecimal(key);
}
// type methods implementing ODCITable interface
static public BigDecimal ODCITableStart(STRUCT[] sctx,ResultSet rset)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
// create a stored context and store the result set in it
StoredCtx ctx=new StoredCtx(rset);
// register stored context with cartridge services
int key;
try {
key = ContextManager.setContext(ctx);
} catch (CountException ce) {
return ERROR;
}
// create a StockPivotImpl instance and store the key in it
Object[] impAttr = new Object[1];
impAttr[0] = new BigDecimal(key);
StructDescriptor sd = new StructDescriptor("STOCKPIVOTIMPL",conn);
sctx[0] = new STRUCT(sd,conn,impAttr);
return SUCCESS;
}
public BigDecimal ODCITableFetch(BigDecimal nrows, ARRAY[] outSet)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
// retrieve stored context using the key
StoredCtx ctx;
try {
ctx=(StoredCtx)ContextManager.getContext(key.intValue());
} catch (InvalidKeyException ik ) {
return ERROR;
}
// get the nrows parameter, but return up to 10 rows
int nrowsval = nrows.intValue();
if (nrowsval>10) nrowsval=10;
// create a vector for the fetched rows
Vector v = new Vector(nrowsval);
int i=0;
StructDescriptor outDesc =
StructDescriptor.createDescriptor("TICKERTYPE", conn);
Object[] out_attr = new Object[3];
while(nrowsval>0 && ctx.rset.next()){
out_attr[0] = (Object)ctx.rset.getString(1);
out_attr[1] = (Object)new String("O");
out_attr[2] = (Object)new BigDecimal(ctx.rset.getFloat(2));
v.add((Object)new STRUCT(outDesc, conn, out_attr));
out_attr[1] = (Object)new String("C");
out_attr[2] = (Object)new BigDecimal(ctx.rset.getFloat(3));
v.add((Object)new STRUCT(outDesc, conn, out_attr));
i+=2;
nrowsval-=2;
}
// return if no rows found
if(i==0) return SUCCESS;
// create the output ARRAY using the vector
Object out_arr[] = v.toArray();
ArrayDescriptor ad = new ArrayDescriptor("TICKERTYPESET",conn);
outSet[0] = new ARRAY(ad,conn,out_arr);
return SUCCESS;
}
public BigDecimal ODCITableClose() throws SQLException {
// retrieve stored context using the key, and remove from ContextManager
StoredCtx ctx;
try {
ctx=(StoredCtx)ContextManager.clearContext(key.intValue());
} catch (InvalidKeyException ik ) {
return ERROR;
}
// close the result set
Statement stmt = ctx.rset.getStatement();
ctx.rset.close();
if(stmt!=null) stmt.close();
return SUCCESS;
}
}
After all of this, in order to have a pipelined function that could be used to create a table of the aforementioned table type, we need to have a ref cursor and the pipelined function itself. I've put the ref cursor into a package.
-- Define the ref cursor type
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;
END refcur_pkg;
/
-- Create table function
CREATE OR REPLACE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED USING StockPivotImpl;
/
My question is just how can I call the StockPivot function in order to execute the JSP and display a table of the mentioned type.
I am expecting to get a result from a simple select which emulates a table:
-----------------------------
| column1 | column2 | ... |
|---------------------------|
| row_data1| row_data2| ... |
| --------------------------|
|___________________________|
I have tried to call the function like this, but it isn't working from obvious reasons (we need a ref cursor as a parameter):
SELECT * FROM TABLE(StockPivot());
Thank you in advance.
Option 1 Create function to return strong ref cursor.
create or replace function getCursor return refcur_pkg.refcur_t is
c_tmp refcur_pkg.refcur_t;
begin
open c_tmp for select * from StockTable;
return c_tmp;
end;
SELECT * FROM TABLE(StockPivot(getCursor()));
Option 2. You can try cursor expression it also should work. Cursor expression.
SELECT * FROM TABLE(StockPivot(Cursor(select * from StockTable)));