Loading Oracle stored procedure from QlikView fails with Wrong number or type of arguments to procedure

[#1]

Hi Qlik Folks,

I'm novice to loading stored procedure data to QlikView. I tried out one many a times but it is failing each time with Wrong number or type of arguments to procedure name though it seems there is no error with the number of parameters.

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

Executing Oracle Stored Procedure from Qlikview... | Qlik Community

SQL Stored Procedure into QlikView

How to Call stored procedure in Qlikview

Re: Executing a stored procedure

My table is TEST_QV_TABLEand 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

IS

v_id INTEGER;

v_name VARCHAR2(10);

v_sales INTEGER;

v_flag VARCHAR2(1);

BEGIN

select ID,NAME,SALES,FLAG into v_id,v_name,v_sales,v_flag from TEST_QV_TABLE

where TEST_QV_TABLE.ID =v_id;

END;

4) Calling Stored procedure into QlikView

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

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

SQL CALL PROC_TEST_QV_TABLE(12,'Ravis',3000,'T');

5) Error after reloading:

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

Actually I want to insert the QlikView called record into table via stored procedure.

Could somebody please help me out.

Thanks in advance,

Regards,

VLC

[#2]

Hi All,

I found a partial solution for this problem.

I had updated the earlier stored procedure with this.

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 ;

Now, When I reload the QlikView script with below code, it goes well.

Tab:

Load *;

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

The issue is, it is not giving the the parameters in the above procedure call as fields for QlikView.

My intention is to use the parameter values as fields in QlikView and display in list boxes as explained in http://www.c-sharpcorner.com/UploadFile/35fef0/connection-of-stored-procedure-in-qlikview-applicatio...

Could some one please help me out with this.

Thanks in advance.

Regards,

VLC

[Clever_Anjos]

Did you tryied without ()?

SQL CALL PROC_TEST_QV_TABLE 12,'Ravis',3000,'T' ;

[#4]

Thank you Clever Anjos.

I had even tried with SQL CALL PROC_TEST_QV_TABLE 12,'Ravis',3000,'T' ;

But it throws below error:

I'm concerned whether I can retrieve stored procedure parameter values as field values so that I take in list boxes.

Could you please provide your inputs.

Thanks,

VLC

[#5 · Accepted solution]

Hi Folks,

I am facing one more issue in inserting records in Oracle database through stored procedure call.

The updated stored procedure for insertion is as 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;

I call stored procedure from QlikView as follows:

Load *;

SQL CALL PROC_TEST_QV_TABLE(10,'James',3500,'T');

I suppose a new record with above parameters is added to TEST_QV_TABLE but it is not happening though QlikView reloads the script without any errors or warnings.

Could somebody please help me here.

Thanks,

VLC