topic can QlikView load data from an oracle stored procedure ? in QlikView

can QlikView load data from an oracle stored procedure ?

Fri, 01 Jul 2011 12:30:02 GMT

Is anyone able to get a record set from an oracle stored proc with an output type of refcursor ?

If you are, please share.

I have a very simple procedure. It takes two in params of string type and one out param of refcursor type.

The issue seems to be around the bind variable for the output cursor.

I have tried every load statement and argument list combination that I can think of:

LOAD *;

SQL CALL sp_name('string1','string2', what-goes-here ?)

mostly, I get the "wrong number or type of argument" error.

Thanks for any thoughts.

suniljain — Sat, 02 Jul 2011 06:39:32 GMT

you can call store procedure from qlikview.

if your data volume is large , i suggest you to implement same logic in qlikview instead of calling stored procedure.

Sun, 03 Jul 2011 11:01:43 GMT

thanks Sunil. sql is very easy but oracle stored procs are proving difficult. it may be that qv cannot do it.

Do you have an example of calling an Oracle stored proc that returns a records set ?

Anonymous — Fri, 30 Sep 2011 14:48:43 GMT

Tod,

Did you finally found out what is the right syntax to call the stored procedure on Oracle?

Fri, 30 Sep 2011 14:54:18 GMT

Sorry, no.

SunilChauhan — Fri, 30 Sep 2011 14:57:21 GMT

use below

LOAD *;

select

schemaname.SQL CALL sp_name('string1','string2') as output

from schemaname;

Fri, 30 Sep 2011 15:10:02 GMT

thanks sunil,

are you actually using that successfully to load data from an oracle cursor ?

unless i'm misunderstnading your references to schemaname, I do not think that will work.

when calling an oracle SP, you need to set an output variable for the cursor. there is no "from" in an exec call to an oracle SP.

Please let me know if I'm missing something

Anonymous — Fri, 30 Sep 2011 15:24:09 GMT

I did try Sunil code and got:

"SQL##f - SqlState: S1000, ErrorCode: 923, ErrorMsg: [Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected" as Tod suggested.

What now?