Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

VC 7.0 Oracle stored procedures resultset with ref cursor

Can VC (we are on NW7 SP13) handle Oracle's datatype ref cursor - which is the standard solution in Oracle to return result sets - as the return value of a stored procedure?

When testing a data service in the VC story board based upon a simple Oracle function like:

create or replace package pkg_dev

is

type t_cursor is ref cursor;

end;

create or replace function vc_stub return pkg_dev.t_cursor

as

l_cursor pkg_dev.t_cursor;

begin

open l_cursor for select ename from emp;

return l_cursor;

end;

(just as example - I know that could be easily retrieved using the BI JDBC connector framework and accessing tables / views)

I am always running in the "portal request failed ( Could not execute Stored Procedure)" error - so I am not able to use the "add fields" function to bind the output.

The defaulttrace contains entries like:

Text: com.sap.portal.vc.HTMLBRunTime

[EXCEPTION]

com.sapportals.connector.execution.ExecutionException: Could not execute stored procedure

....

Caused by: java.sql.SQLException: Invalid column type

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

We deployed Oracle's own jdbc-driver for Oracle 10g. Using that driver and a portal jdbc connector framework entry the stored procedures of the Oracle database user mapped to the portal user are discovered and available in the "Find Data Services" section.

Former Member
Not what you were looking for? View more on this topic or Ask a question