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

[#1]

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

[jagan]

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.

[#3]

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]

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]

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

[Anonymous]

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

[Marcio_Campestrini]

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

[sureshbaabu]

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]

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]

Hi Jagan,

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

Thanks