topic Re: Fetching Oracle stored procedure data into QlikView FAILS in QlikView

Fetching Oracle stored procedure data into QlikView FAILS

Fri, 03 Jun 2016 12:39:47 GMT

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)

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

Re: Fetching Oracle stored procedure data into QlikView FAILS

Sat, 04 Jun 2016 13:11:04 GMT

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

Re: Fetching Oracle stored procedure data into QlikView FAILS

Mon, 06 Jun 2016 07:41:32 GMT

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

Re: Fetching Oracle stored procedure data into QlikView FAILS

Mon, 06 Jun 2016 07:48:01 GMT

Hello vikasmahajan vsaikrishna@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

Re: Fetching Oracle stored procedure data into QlikView FAILS

Wed, 08 Jun 2016 14:31:32 GMT

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

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

Re: Fetching Oracle stored procedure data into QlikView FAILS

krishnacbe — Wed, 08 Jun 2016 14:48:27 GMT

Thanks Laxman for sharing the details.