Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jagan
Luminary Alumni
Luminary Alumni

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
Author

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

maxgro
MVP
MVP

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;

/

Marcio_Campestrini
Specialist
Specialist

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
Anonymous
Not applicable
Author

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

Marcio_Campestrini
Specialist
Specialist

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
Creator III
Creator III

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.

jagan
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III

Hi Jagan,

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

Thanks