Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

16 Replies
MVP
MVP

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

Hi Nagendra,

Check this thread

Stored Procedure Parameters SQL

Example:

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

or

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

or

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


Regards,

Jagan.



Not applicable

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

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

MVP
MVP

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

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

AS

  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

AS

  c1 t_cursor;

BEGIN

    OPEN C1 FOR

        SELECT TABLE_NAME FROM user_tables where table_name like p_table;

    RETURN C1;

END;

END pkg1;

/

MCampestrini
Valued Contributor

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

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

Márcio Rodrigo Campestrini
ariel_insua
New Contributor

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

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

MCampestrini
Valued Contributor

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

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

Márcio Rodrigo Campestrini
sureshbaabu
Contributor III

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

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.

MVP
MVP

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

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.

sureshbaabu
Contributor III

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

Hi Jagan,

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

Thanks

Community Browser