Fetching Oracle stored procedure data into QlikView FAILS

[#1]

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);

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.

  

Could somebody please look into this and help me out.

Thanks in advance.

Thank you,

Laxmaiah Chowdary

[#2 · Accepted solution]

Hi,

I have come to know from Programming Features in PL/SQL in TimesTen (Using bind variables from an application) that p_comment has to be declared as a bind variable since bind variables in an application correspond to parameters declared in a procedure. Also with in the procedure call, I have passed p_comment as a bind variable.

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 SCUSER.PROCEDURE5(:$(p_comment));

Now I do not see any error in QlikView script execution but not able to fetch p_comment into QlikView as a field.

Can somebody kindly help me with this.

Thanks in advance.

Thank you,

Laxmaiah Chowdary

[#3]

Hi,

I had even tried with below code. But it did not work out for me with Oracle.

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(30)

call SCUSER.PROCEDURE5(:(p_comment)) output

select :(p_comment) as p_comment from dual;

Tab:

LOAD *;

SQL

select '$(p_comment)' AS myField

from dual;

I had referred To pass parameters from QlikView application to a stored Procedure link bmw  response.

Can some one please help me out.

Thanks in advance.

Thank you,

Laxmaiah Chowdary

[#4]

Hello vikasmahajanvsaikrishna@vsaikrishna‌chauhans85‌nagen1908‌QVKishore

I have seen that you guys tried fetching oracle stored procedure data to QlikView.

Could you please kindly take a look at this thread and help me out.

Thanks in advance.

Thank you,

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

[krishnacbe]

Thanks Laxman for sharing the details.