topic Calling Oracle Stored Procedure in Load Script Does Not Return Data in QlikView

Calling Oracle Stored Procedure in Load Script Does Not Return Data

Tue, 21 Feb 2017 22:55:15 GMT

I have a stored procedure that returns a ref cursor. I can execute the stored procedure in Toad and it returns results. If I call this from my QlikView load script, I get an error regardless of the syntax I use. I read on one of the posts that you can only return a result set from Oracle functions, not stored procedures, in QlikView.

So, I created a function that calls the stored procedure and returns the Ref Cursor. I can successfully call this from Toad and I get a result set. If I call it from QlikView, it appears to run, but I never get any result. In other words, there are no tables.

Here is the syntax that I'm using...

OLEDB CONNECT TO [my connect string.....](mode is write);

TheTable:

LOAD

SQL SELECT {? = call TheSchema.TheFunctionName ('somevalue', TO_DATE ('31-DEC-16', 'DD-MON-RR'))};

(I also have the flag set to open databases in read/write mode.)

This runs, but it never produces any data. I've tried all of the other examples posted in QlikCommunity, but none of them work either.

Does anyone know how to return a result set into a QlikView table from an Oracle Stored Procedure?

Thanks!

Re: Calling Oracle Stored Procedure in Load Script Does Not Return Data

maxgro — Tue, 21 Feb 2017 23:22:44 GMT

Here I post an answer

Re: How to trigger PL/SQl Stored Procedures from Qlikview Application???

Re: Calling Oracle Stored Procedure in Load Script Does Not Return Data

Wed, 22 Feb 2017 05:30:26 GMT

You'll notice that what I'm doing is identical to what you're doing. I'm not getting anything back. Is is because I'm using OLDDB and not ODBC?

It would be great if someone from Qlik could weigh in here. It seems really Odd that QlikView can't call Oracle stored procedures.

Re: Calling Oracle Stored Procedure in Load Script Does Not Return Data

maxgro — Wed, 22 Feb 2017 06:45:10 GMT

You'll notice that what I'm doing is identical to what you're doing. I'm not getting anything back. Is is because I'm using OLDDB and not ODBC?

In my old post (2014) I wrote

"these 2 works on my oracle (only with odbc, not with oledb)"

I think because I tried oledb and odbc but only odbc worked in my environment..