Skip to Content
avatar image
Former Member

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 24, 2008 at 07:56 AM

    Klaus

    Nice to know more people are trying to work with Oracle stored procedures.

    First off, no sorry I don't know if VC can handle ref cursor. Although I guess the first step would be to get a stored procedure to return a standard varchar or number parameter.

    I have also tried to deploy new drivers and then using JDBC system with this. I cannot get these systems working in portal. Under which node in J2EE admin have you deployed your new driver?

    I successfully get to call my procedures and pass values to the in parameters using the standard JDBC system setup in portal. I thus get the procedures to do everything they should, except I don't get anything back though the out parameters.

    I am running a customer message now and will update along the way.

    Please supply more info if you find out more on your side too.

    Henning Strand

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Thanks, Klaus. I was also having trouble with the deployment via J2EE admin. My references (other than for bi-jdbc) also seemed to disappear into a black hole. I will try your approach.

      Henning