5 Replies Latest reply: Jun 8, 2016 11:16 AM by Clever Anjos RSS

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

    Laxmaiah Chowdary

      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-application/

       

       

      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