5 Replies Latest reply: Jun 8, 2016 10:48 AM by Krishnapriya Arumugam RSS

    Fetching Oracle stored procedure data into QlikView FAILS

    Laxmaiah Chowdary

      Hi Folks,

       

      I am trying to fetch Oracle stored procedure data into QlikView. Basically I want to get p_comment in stored procedure as a QlikView field so that I can take a listbox for that.

       

      I am able to execute the stored procedure through Oracle and it is giving correct results.

       

      Creating Stored Procedure:

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

      create or replace PROCEDURE PROCEDURE5(p_comment out varchar2)

      AS

      BEGIN

      p_comment := 'success';

      END;

       

      Executing Stored Procedure from Oracle:

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

      DECLARE

        p_comment VARCHAR2(10);

      BEGIN

        PROCEDURE5(p_comment);

        DBMS_OUTPUT.PUT_LINE(p_comment);

      END;

       

      Executing stored procedure through QlikView:

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

       

      OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=scuser;Data Source=*******************;Extended Properties=""] (XPassword is cPWNCSJOHLbGCYEHRRMIJ);

       

      SQL

      DECLARE

        p_comment VARCHAR2(10);

      Tab:

      Load *;

      SQL CALL PROCEDURE5(p_comment);

      Capture11.JPG

       

      Capture12.JPG

      I had even tried to take p_comment parameter get assigned with Let and Set statements as below.

       

      OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=scuser;Data Source=*******************;Extended Properties=""] (XPassword is cPWNCSJOHLbGCYEHRRMIJ);

       

      Set p_comment='p_comment'; (or) Let p_comment='p_comment';

       

      Tab1:

      SQL CALL PROCEDURE5(p_comment);

       

      But it is giving below error.

      Capture13.JPG  

      Could somebody please look into this and help me out.

       

      Thanks in advance.

       

      Thank you,

      Laxmaiah Chowdary