topic Error when I am trying to run stored procedure in Oracle - but sp did the job correctly in App Development

Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Tomino — Fri, 26 Jul 2019 12:55:50 GMT

Hi all,
I am using QlikSense Desktop and I spent a time by trying how to call stored procedure in Oracle from Qlik.
When I run this procedure from SQL developer with the same user and from the same computer it is working
OK. The procedure is without parameter and only wipe the table.
The final version is:
LIB CONNECT TO [Oracle_DB];
SQL {CALL sp_wipe table};
SQL{CALL sp_fill_data_1};
SQL{CALL sp_fill_data_2};
SQL{CALL sp_fill_data_3};
tab1:
Select fields..... from Oracle_DB.table;
tab2:
Load field1 as XXX, field2 as YYY resident tab1;

This code produce in Qlik error message:
The file has the wrong format
Detail: QVX_UNEXPECTED_END_OF_DATA: Unable to get column information for the fields that are used in the query:
Object reference not set to an instance of an object.
What is interesting is that the procedure did the job in Oracle successfully - wipe the table.
I have another 3 procedures and all of them did the job well but produce the same error.
If I run the whole script in debug mode I can run the whole script with these errors and I get all data in table.
Thanks for any help incl. how to not stop the script in case of error.

Thanks

Re: Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Anil_Babu_Samineni — Fri, 26 Jul 2019 13:10:03 GMT

How about the privileges? Do you/team have the read-only permission to SP?

Re: Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Tomino — Fri, 26 Jul 2019 13:25:20 GMT

Yes. The same user credential is working from another DB tool. In advance I get error message of Qlik and not Oracle - the sp wiped table or second sp insert data to the same table as I checked it by SQL Developer.

Re: Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Anil_Babu_Samineni — Fri, 26 Jul 2019 13:33:53 GMT

I don't know you have given typo error or missed this. We need to execute as

SQL EXEC {CALL sp_wipe table};
SQL EXEC {CALL sp_fill_data_1};
SQL EXEC {CALL sp_fill_data_2};
SQL EXEC {CALL sp_fill_data_3};

Re: Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Tomino — Fri, 26 Jul 2019 13:45:47 GMT

your syntax is not working, I tested before...
ORA-00900: invalid SQL statement
only without EXEC

Re: Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Tomino — Mon, 12 Aug 2019 07:30:45 GMT

Hello, any ideas ? We are running Oracle 11g (11.2).

Thanks

Re: Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Anil_Babu_Samineni — Tue, 13 Aug 2019 12:53:07 GMT

For me it is fetching from SP. Not sure, what you've tried?

Re: Error when I am trying to run stored procedure in Oracle - but sp did the job correctly

Tomino — Tue, 13 Aug 2019 14:54:12 GMT

I will do the summary and little simplification. I have one stored procedure in Oracle DB. This procedure is working without any issue (I run it from SQL developer tool). This procedure only delete all data from one table in Oracle DB. I wrote code in Qlik Sense Desktop:
LIB CONNECT TO [Oracle_DB];
SQL {CALL sp_wipe_table};

This code produce the following error:

Started loading data

Connected

{0}

The following error occurred:

QVX_UNEXPECTED_END_OF_DATA: Unable to get column information for the fields that are used in the query: Object reference not set to an instance of an object.

The error occurred here:

SQL {CALL spRrReportTable2017}

Data has not been loaded. Please correct the error and try loading again.

....But if I check the table in DB it is wiped