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

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

Wed, 25 Jun 2014 00:25:04 GMT

Hi All,

My requirement is to trigger PL/SQL Stored Procedure on Oracle DB. On successful completion of this Stored Procedure I need to reload my Application. Please post your suggestions and approach for resolving this.

Thanks,

Nag

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

jagan — Wed, 25 Jun 2014 01:41:56 GMT

Hi Nagendra,

Check this thread

Stored Procedure Parameters SQL

Example:

sql exec ('[ServerName].[SchemaName].[dbo].[storedProcedureName] $(ParameterName) ' ) ; -- With one parameter

sql exec ('[dbo].[storedProcedureName] $(ParameterName) ' ) ;-- With one parameter

sql exec ('[dbo].[storedProcedureName]') -- without parameters

Regards,

Jagan.

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

Wed, 25 Jun 2014 17:13:46 GMT

Hi Jagan,

Thanks for the reply.

When I use the below script :

ODBC CONNECT32 TO [C2P;DBQ=c2p] (XUserId is XXXXXXXXXX, XPassword is XXXXXXXXXX);

sql exec ('c3tool.c3_inserts_prd');

And try to reload my Qlikview document. It's throwing the below error:

[Oracle][ODBC]Syntax error or access violation.

Thanks,

Nagendra

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

maxgro — Wed, 25 Jun 2014 18:20:51 GMT

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

ODBC CONNECT32 TO [DEV11ORACLE;DBQ=DEV11] (XUserId is xxxxxxx, XPassword is xxxxxxxxxxxxx);

set vTable='%E%';

Table:

LOAD *;

SQL {? = CALL f_get_tables() } ;

//SQL {? = CALL pkg1.f_get_tables2('$(vTable)') } ;

this is the oracle function

CREATE OR REPLACE function TEST.f_get_tables return sys_refcursor

c1 sys_refcursor;

BEGIN

OPEN C1 FOR

SELECT TABLE_NAME FROM user_tables;

RETURN C1;

END;

and this is the package

CREATE OR REPLACE PACKAGE TEST.pkg1 AS

TYPE t_cursor IS REF CURSOR;

function f_get_tables2 (p_table varchar2) return t_cursor;

END pkg1;

CREATE OR REPLACE PACKAGE BODY TEST.pkg1 AS

function f_get_tables2 (p_table varchar2) return t_cursor

c1 t_cursor;

BEGIN

OPEN C1 FOR

SELECT TABLE_NAME FROM user_tables where table_name like p_table;

RETURN C1;

END;

END pkg1;

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

Marcio_Campestrini — Wed, 25 Jun 2014 18:43:38 GMT

Hi Nagendra

I solved this issue using webservices to trigger procedures. I call the webservice and wait the return. The webservice does all the job. Look an example for a help at http://community.qlik.com/docs/DOC-3105

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

Anonymous — Wed, 03 Dec 2014 13:23:14 GMT

Hi Marcio, can you post an example about refcursors and webservices?

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

Marcio_Campestrini — Fri, 05 Dec 2014 12:13:56 GMT

Hi.

Here is an application I used to test webservices.

To sse how I did it, see the code under "Macros" text object.

Márcio

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

sureshbaabu — Fri, 09 Jan 2015 05:34:41 GMT

Hello,

I'm facing the same issue. I'm trying to execute a stored procedure without Parameters (this proc will insert a entry to table)

Error message:

SQL##f - SqlState: 37000, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Syntax error or access violation.

my script :

ODBC CONNECT TO [DB]:

SQL EXEC Schemaname.Procname;

If you could help me with any suggestions, that would be really great.

Thanks.

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

jagan — Fri, 09 Jan 2015 05:47:45 GMT

Hi Suresh,

This is the syntax for SQL Server, you convert this to Oracle.

My be try

SQL EXECUTE Schemaname.Procname;

Hope this helps you.

Regards,

jagan.

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

sureshbaabu — Fri, 09 Jan 2015 06:05:29 GMT

Hi Jagan,

I tried that aswell, but continues to throw the same error.

Thanks

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

jagan — Fri, 09 Jan 2015 06:44:08 GMT

Hi Suresh,

Is that Stored procedure working in Oracle?

What you are doing in the Stored Procedure? Are you using select query?

Regards,

Jagan.

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

sureshbaabu — Fri, 09 Jan 2015 07:03:34 GMT

Jagan,

please find the comments below:

1)Yes, the stored procedure runs perfectly in oracle.

2) The Proc will just insert an entry to a table. I have granted permission to execute this PROC on different schema.

create or replace procedure Procname as

begin

insert into dummy values (1,’abc’);

commit;

end;

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

Thu, 02 Jun 2016 14:09:59 GMT

Hi Nagendra,

Thank you for the discussion!!.

I am facing issues the same issue in integrating oracle stored procedure data with QlikView.

I have enabled all the settings in QlikView.

But I am not able to fetch Oracle stored procedure data into QlikView as well as insert records into Oracle table through stored procedure.

I have posted the issue in forum atQlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracle table

Could you please take a look at and advise for corrections.

Thanks a lot in advance.

Regards,

Laxmaiah Chowdary

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

Thu, 02 Jun 2016 14:20:08 GMT

Hi Maxgro,

Thank you for the inputs!!.

Could you please help in integrating oracle stored procedure data with QlikView.

I have tried all the above changes.

But I am not able to fetch Oracle stored procedure data into QlikView as well as insert records into Oracle table through stored procedure.

I have posted the issue in forum atQlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracle table

Could you please take a look at and advise for corrections.

Thanks a lot in advance.

Regards,

Laxmaiah Chowdary

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

Marcio_Campestrini — Mon, 31 Oct 2016 18:54:30 GMT

Have you solved your problem? If you did, please choose the correct answer and help us to keep community focused.

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

Anonymous — Tue, 01 Nov 2016 14:31:02 GMT

If you have a PL/SQL stored procedure with a ref cursor out parameter you CANNOT call it directly from QlikView and get your ref cursor results as the input data for QlikView.

You CAN call a PL/SQL stored function with a return type of ref cursor. But you must call it through ODBC not OLEDB exactly as highlighted by Massimo Grossi above.

You could simply wrap your PL/SQL stored procedure in a PL/SQL function to achieve what you are looking for.

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

Marcio_Campestrini — Mon, 29 May 2017 14:11:12 GMT

Got you issue solved? Please mark helpful/correct answers.