Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik Sense trigger stored procedure Oracle

Hi everybody,

I found a lot of threads about triggering stored procedures on Oracle DB using QlikView.

Is it possible to do the same thing using Qlik Sense?

If so, I think I'm using the wrong syntax because I can't trigger it

Can anyone help me?

Thanks in advance

Best regards,

Nicolò Cogno

6 Replies
agigliotti
Honored Contributor II

Re: Qlik Sense trigger stored procedure Oracle

can you give us more details about your requirements?

Not applicable

Re: Qlik Sense trigger stored procedure Oracle

I'm using this syntax:

     LIB CONNECT TO 'Oracle_Database';

     set data_in = '2017-05-26';

     SQL EXEC TEST_QLIK_SENSE($(data_in));

The procedure should insert data into a table and then I'd like to retrive data from that table, but the problem is that Qlik Sense terminates the load script without errors not running the procedure (in fact the table results empty).

agigliotti
Honored Contributor II

Re: Qlik Sense trigger stored procedure Oracle

the same script works as expected in qlikview ?

Not applicable

Re: Qlik Sense trigger stored procedure Oracle

I'm trying on QlikView with

ODBC CONNECT TO [Qlik-oracle];

SET data_in = '2017-05-25';

SQL {? = CALL DBOWNER.TEST_QLIK($(data_in)) } ;

But I get

SQL##f - SqlState: 07001, ErrorCode: 0, ErrorMsg: [Qlik][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 1.

SQL {? = CALL TEST_QLIK(2017-05-25) }

bbs
Contributor

Re: Qlik Sense trigger stored procedure Oracle

Nicolo,

In QlikView, you will likely need to go the Settings tab in the Script Editor and check the check box for the Open Database in Read and Write Mode in order for the stored proc to run, I seem to recall that needing to be in place in order to run stored procs. 

The problem is I am not aware of similar setting in Sense side to allow this, so if it works in QlikView, I am still not sure it will work in Sense, even in Legacy Mode.  Hope this helps a little. 

The other thing to keep in mind is whether the Oracle driver you are using supports calling of stored procs too, as there could be something there that may need to be tweaked as well potentially, I am no Oracle guru. 

balabhaskarqlik
Honored Contributor

Re: Qlik Sense trigger stored procedure Oracle

Try like this in Qlikview:

Load * ;

SQL

Declare @out int

Declare @out1 int

Exec LMS.dbo.Sp_TestQlikview 1,7,@out Output,@out1 Output

Select @out as Output, @out1 as Output1;

//Try this GRANT EXEC ON dbo.xxxxxxx TO <UserName>