question

coreman avatar image
coreman asked

Anyone use OPENQUERY() to call oracle 12c procs w/ SYS_REFCURSOR or dbms_sql.return_result()?

So Oracle 12c stored procs can now be built to return result sets "implicitly". Up to now, one would make a stored procedure with one or more output parameters of type SYS_REFCURSOR).
External apps like SSRS can "see" that output parameter and swoosh through the records in the REFCURSOR into the report's dataset(s). SSRS certainly can handle this invisibly. It is also the way to do it programmatically outside of the SQL realm.
But now one can set up an Oracle stored proc (in 12c) to "implicitly" return a result set. (the how is easily found in the Oracle docs).

Some background:

the server is SQL Server 2016 Standard, with SSRS 2016 Standard installed and working fine on it too. The Oracle client has been set up and is working. The SQL server has the Oracle 12c release 2 client, OLEDB and .Net bits installed. And TNSNAMES.ora has been set up.

A linked server to the Oracle server has been created and otherwise works fine. doing a query like this:

select OPENQUERY(MyLinkedServer, 'select 1 from dual')

works fine.

Those things out of the way...

Poor me has to fake doing "data driven reports" in our non-Enterprise SSRS. Our use case is to determine if a report has data, and not invoke the subscription if it doesn't.

One way we've been doing this is to create a T-SQL stored proc that then executes the guts of the stored procedure from Oracle, thru a OPENQUERY() query. Then we can determine whether to invoke the actual report or not if it DOES have records (@@rowcount > 0) by then doing our own call to ReportServer.dbo.AddEvent() and a given SubscriptionID in that proc.

Has anyone figured out how to either call an oracle stored proc from TSQL via OpenQuery() that has an explicit OUT SYS_REFCURSOR parameter?
Can SQL Server 2016 consume the implicit result set returned via DBMS_SQL.RETURN_RESULT() in a stored procedure, or is this only good for newer versions of SQL Server and/or SSMS?

openquery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
coreman avatar image
coreman answered

On a related note, this old link seemed like it was almost there... OLD... use OPENQUERY for Oracle procs. It set up using an explicit table type, in an Oracle pl/sql package. But didn't explicitly call out using a declared refcursor that way, either.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.