Fail to open Information link with Oracle Stored procedure Error - Failed to execute query: Unsupported JDBC data type in query result: REFCURSOR

Fail to open Information link with Oracle Stored procedure Error - Failed to execute query: Unsupported JDBC data type in query result: REFCURSOR

book

Article ID: KB0075482

calendar_today

Updated On:

Products Versions
Spotfire Analyst All

Description

Fail to  open Information link with Oracle Stored procedure
==================
 Failed to execute query: Unsupported JDBC data type in query result: REFCURSOR
==================
 

Issue/Introduction

Fail to open Information link with Oracle Stored procedure Error - Failed to execute query: Unsupported JDBC data type in query result: REFCURSOR

Environment

All Supported OS

Resolution

The above error is generally caused if the Stored Procedure syntax is incorrect.

e.g. Incorrect Syntax - The stored procedure is missing data type declaration. 
================================

Create function functionName(val1, val2) return SYS_REFCURSOR;
Begin
  Open aaaa for   Select * From dual;
Return aaaa;
End functionName;
============================


Below is the correct syntax with valid datatypes
============================

Create or replace function functionName(val in varchar2,val2 in varchar2) return SYS_REFCURSOR AS aaaa SYS_REFCURSOR;
Begin
Open aaaa for Select * From dual;
Return aaaa;
End ;
=============================