topic Re: package in App Development

package

Tool_Tip — Mon, 09 Sep 2024 08:42:00 GMT

Dear all,

We have oracle package named as pack1.prod wherein we always pass one parameter as ID.

So, we have created our fact table in qliksense which has same ID and we need to pass this ID to this oracle package to get its respective values.

pack1.prod has column like ID, profit, margin so for each ID we have respective calculation on profit and margin now we want to display this profit and margin column to our fact table.

Fact table had ID column as well as some other dimensions tables.

So lets say we have 2000 unique ID into fact for which we need to get respective profit and margin by calling oracle package.

Kindly let us know how can we get it

Re: package

Kushal_Chawda — Mon, 09 Sep 2024 09:03:43 GMT

@Tool_Tip Create a data connection from Qlik sense to connect to oracle. You need to connect to oracle database with a user who has permissions to execute package from Qlik sense. If that setup is done, you can call oracle package from load script.

You can generate a loop in Qlik sense to call individual ID in package parameter

Data:
LOAD ID
FROM Fact;

For i=1 to FieldValueCount('ID')

let vFieldValueID = FieldValue('ID',$(i));

package_id:
SQL call pack1.prod ('$(vFieldValueID)');

drop table Data;

Re: package

Kushal_Chawda — Mon, 09 Sep 2024 09:08:56 GMT

@Tool_Tip If you can pass the multiple comma separated values in parameter, you can do below

Data:
LOAD concat(chr(39) & ID & chr(39),',') as IDs
FROM Fact;

let vIDs= Peek('IDs',0,'Data');

package_id:
SQL call pack1.prod ($(vIDs));

drop table Data;

Re: package

Tool_Tip — Mon, 09 Sep 2024 10:06:32 GMT

Hi Kushal,

Getting below error:

Re: package

Kushal_Chawda — Mon, 09 Sep 2024 10:10:26 GMT

@Tool_Tip try with

SQL exec pack1.prod ()

Re: package

Tool_Tip — Mon, 09 Sep 2024 10:22:06 GMT

Re: package

Kushal_Chawda — Mon, 09 Sep 2024 10:26:12 GMT

@Tool_Tip can you share which script you are using?

Re: package

Tool_Tip — Mon, 09 Sep 2024 10:28:17 GMT

Re: package

Tool_Tip — Mon, 09 Sep 2024 10:45:06 GMT

Hi Kaushal,

I think it seems permission issue. The user running this package from qlik needs permission from database site.

Re: package

Kushal_Chawda — Mon, 09 Sep 2024 11:02:30 GMT

@Tool_Tip It seems permission issue only as I mentioned earlier that connection user should have permissions to execute package

Re: package

Tool_Tip — Thu, 12 Sep 2024 05:05:27 GMT

I tried to run the query as below :

select * from table (pack1.prod ('1464757576'));

it is working fine so its does mean permission issue.