question

LS2021-2798 avatar image
0 Votes"
LS2021-2798 asked ZoeHui-MSFT commented

SSRS2019 WITH ORACLE STORED PROCEDURE FAILS WITH WRONG NUMBER OF TYPES OF ARGUMENTS

CREATE PROCEDURE TESTLV_PROC (L_CURSOR OUT SYS_REFCURSOR)
IS
BEGIN
OPEN L_CURSOR FOR
SELECT * FROM DW_ORGANIZATION;
END;
/

78475-image.png


sql-server-reporting-services
image.png (41.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the response. This is not connectivity issue. other reports using views works fine.
Issue I see only while using storedproc , and using 32bit client and using OLE DB provider to connect to oracle database .


0 Votes 0 ·

Thanks for your information. I'll do some test locally and you may also post the issue to oracle forums for more suggestion.


0 Votes 0 ·

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @LS2021-2798,

Not familiar with Oracle, so I did some online research.

Several people who are also encountering the issue said that maybe related with the provider.

https://stackoverflow.com/questions/12194311/stored-procedure-call-gives-an-error-pls-00306-ora-06550
https://social.technet.microsoft.com/Forums/en-US/626c9c6c-1c99-4718-9cb1-054a102701cd/ssrs-calling-a-stored-procedure-error-pls00306-wrong-number-or-types-of-arg?forum=sqlreportingservices

To use data from an Oracle database in your report, you must have a dataset that's based on a report data source of type Oracle. This built-in data source type uses the Oracle Data Provider directly and requires an Oracle client software component. oracle-connection-type-ssrs

SSDT runs in 32 bit whereas SSRS runs in 64 bit. Since we are in the same system, we need to install both the bit-ness of Oracle drivers one by one.

Oracle article for your reference: https://www.oracle.com/a/otn/docs/database/connecting-ssrs-to-oracle-adb.pdf

Regards,
Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

What can I do if my transaction log is full?--- Hot issues November

How to convert Profiler trace into a SQL Server table -- Hot issues November



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.