QlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracle table

[#1]

Hi Folks,

I'm novice to integrating oracle stored procedure to QlikView. I have below tasks to work on.

1. I want to fetch oracle stored procedure data into QlikView and use that data as fields for list boxes.

2. I want to execute stored procedure from QlikView that inserts records into oracle table .

Task 1:

--------

I tried out Task1. QlikView triggers stored procedure but I 'm not getting table records as QlikView fields.

I have already referred below links but they are not of much help here

https://community.qlikview.com/thread/12849

https://community.qlik.com/thread/3223

https://community.qlik.com/thread/74815

https://community.qlik.com/message/235958#235958

My table is TEST_QV_TABLE and procedure is PROC_TEST_QV_TABLE.

1).

DESC TEST_QV_TABLE;

Name Null Type

----- ---- ------------

ID NUMBER(38)

NAME VARCHAR2(10)

SALES NUMBER(38)

FLAG VARCHAR2(1)

2).

SELECT * FROM TEST_QV_TABLE;

1 John 1000 T

2 KRISH 1200 F

3 scott 1500 T

4 Bob 1600 F

5 JESSY 1800 T

3) Stored Procedure:

-----------------------------

create or replace PROCEDURE PROC_TEST_QV_TABLE

(

p_id IN INTEGER,

p_name in varchar2,

p_sales in number,

p_flag in varchar2

)

AS

v_id number;

v_name varchar2(20);

v_sales NUMBER;

v_flag VARCHAR2(2);

BEGIN

select id,name,sales,flag into v_id, v_name,v_sales,v_flag

FROM TEST_QV_TABLE

where id = p_id

and name = p_name

and sales = p_sales

and flag = p_flag;

end PROC_TEST_QV_TABLE;

4) Calling Stored procedure from QlikView

--------------------------------------------------------

OLEDB CONNECT TO (XPassword is cPWNCSJOHLbGCYEHRRMIJ);

Load *;

SQL CALL PROC_TEST_QV_TABLE(3,'scott',1500,'T');

The QlikView scripts execution goes well but the issue is, it is not giving the parameters in the above procedure call as fields for QlikView.

I have followed the link http://www.c-sharpcorner.com/UploadFile/35fef0/connection-of-stored-procedure-in-qlikview-applicatio...

Task2:

-------

The stored procedure for inserting records into database is follows.

create or replace PROCEDURE PROC_TEST_QV_TABLE

(

p_id IN INTEGER,

p_name in varchar2,

p_sales in number,

p_flag in varchar2

)

AS

v_id number;

v_name varchar2(20);

v_sales NUMBER;

v_flag VARCHAR2(2);

BEGIN

select id,name,sales,flag into v_id, v_name,v_sales,v_flag

FROM TEST_QV_TABLE

where id = p_id

and name = p_name

and sales = p_sales

and flag = p_flag;

INSERT INTO TEST_QV_TABLE(ID,NAME,SALES,FLAG)

VALUES(p_id,p_name,p_sales,p_flag);

COMMIT;

end PROC_TEST_QV_TABLE;

The above procedure gets execute with below QlikView code

OLEDB CONNECT TO (XPassword is cPWNCSJOHLbGCYEHRRMIJ);

Load *;

SQL CALL PROC_TEST_QV_TABLE(15,'Ravis',4500,'T');

The above QlikView script gets executed without any errors but it is not inserting records into TEST_QV_TABLE.

Could some one kindly help me with this.

Thanks in advance.

Regards,

VLC

[#2]

Hello,

Inserting records into database works now.

Stored procedure code:

-----------------------------------

create or replace PROCEDURE PROCEDURE3(no number,sales number,name varchar2, flag varchar2 )

AS

v_no NUMBER;

v_sales NUMBER;

v_name VARCHAR2 (10);

v_flag VARCHAR2 (1);

BEGIN

v_no := no;

v_sales :=sales;

v_name :=name;

v_flag :=flag;

INSERT INTO TEST_QV_TABLE(ID,NAME,SALES,FLAG)

VALUES(v_no,v_name,v_sales,v_flag);

COMMIT;

end;

Execution from QlikView:

-------------------------------------

OLEDB CONNECT TO (XPassword is cPWNCSJOHLbGCYEHRRMIJ);

SQL CALL PROCEDURE3(5,3500,'jpmc','F');

Thanks,

Laxmaiah Chowdary

[Clever_Anjos · Accepted solution]

As it working would you mind closing both threads?

Fetching Oracle stored procedure data into QlikView FAILS

QlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracl...

[#4]

Hi Clever,

Only insertion part is working.

Still fetching records from oracle database through stored procedure to QlikView is not working.

Could you please take a look into it.

Thanks,

Laxmaiah Chowdary

[#5]

Hi,

Finally I have found an answer to this question.

Limitations in QlikView integration with Oracle Stored Procedures:

1. QlikView fetches data only through ODBC connection not OLEDB connection.

2. QlikView fetches data only through ref cursors not  normal procedures.

3. QlikView fetches data only from FUNCTION not Stored Procedures.

So below is updated ones.

FUNCTION:

CREATE OR REPLACE FUNCTION MYFUNC

    RETURN SYS_REFCURSOR

IS

      lv_ret_refcursor  SYS_REFCURSOR;

BEGIN

    OPEN lv_ret_refcursor FOR

    SELECT 'SUCCESS' as result

    FROM DUAL;

    RETURN lv_ret_refcursor;

END ;

QlikView Script:

ODBC CONNECT TO [OracleODBC64;DBQ=dev] (XUserId is JWQfXZNMVbcKGZdT, XPassword is GaZdLSJOHLbGCYEHRRMII);

myfuncreturn:

LOAD *;

SQL

select

{? = CALL MYFUNC };

Output

:

Similarly for functions with input parameters, the sample QlikView is code as follows:

set v_Data=value; //Based on function parameter return type

myfuncreturn:

LOAD *;

SQL

select

{? = CALL my_MYFUNC ($(v_Data))};

Thanks a lot.

Thank you,

Laxmaiah Chowdary

[Clever_Anjos]

Good to read that, I´ve this problem in the past retrieving from Oracle.

Control-D