PDA

View Full Version : Oracle Stored procs



GMO
10-16-2006, 02:15 AM
Jasper reports apparently cannot handle Oracles Stored Proc's because of the cursor. I there a report writer that works with Pentaho that can handle this?

Thanks in advance.

mbatchelor
10-16-2006, 06:01 AM
Pentaho currently doesn't support stored procedures/stored functions as data sources.

Although it sounds trivial, there are important cross-database considerations when supporting stored functions and procedures. It's somewhere in the development roadmap, but I don't know where it falls priority-wise right now.

We're always interested in contributions from the community. If you're going to implement it, bear the following in mind:

a- Database differences must be accommodated. For example, MS SQL Server and DB2 for example return the results of queries from store procedures, not stored functions. Oracle returns the results of queries from stored functions. The difference is subtle, but important as the syntax in Java for calling them is different.



Oracle: CallableStatement cstmt =
con.prepareCall( "{ ? = call somefunc( ?, ?, ? ) }" );

MSSQL/DB2:
CallableStatement cstmt =
con.prepareCall( "{ call someproc( ?, ?, ? ) }" );



In Oracle, the first bound parameter will end up having the result set in it. In MSSQL/DB2, it gets returned as a result of executing executeQuery().

b- Each database has different requirements for IN parameters, OUT parameters, and INOUT parameters. So, the XML specification in the action sequence needs to be sufficently rubust to be able to specify the parameter calling style.

c- Each bound parameter needs to be bound to a data type. So the XML specification in the action sequence needs to be sufficiently robust to be able to specify the parameter types.

d- For full support of stored functions and procedures, some executions will return result sets (rows and columns), other will return a string (either as a return parameter like "? = call func()" ) or as an OUT/INOUT parameter. These will need to be able to be bound to discrete action outputs. So, an action sequence output could be bound to:

a- A stored function return result (the '?' in "? = call func()")
b- A stored procedure OUT parameter (the '?' in "call sproc(?)")
c- A stored procedure INOUT parameter (the '?' in "call sproc(?)")

JPBowden
10-16-2006, 06:35 AM
For an Oracle-specific alternative, can you copy your stored proc to make a pipelined function?

create or replace type myScalarType as object
( a int,
b date,
c varchar2(25)
);

create or replace type myTableType as table of myScalarType;

create or replace function f pipelined is
begin
for i in 1 .. 5
loop
pipe row ( myScalarType( i, sysdate+i, 'row ' || i) );
end loop;
return;
end;

I tested both Jasper and JFreeReports with a "select * from table(f())" and had no problems.

Jim

mbatchelor
10-16-2006, 06:58 AM
Great suggestion Jim!

Thanks for the help.