How to use PL/SQL package call in qlikivew script

[#1]

I have PL/SQL package call and it return one output. What is syntax to use PL/SQL package call in qlikview script?

Thanks!

[rbecher]

Hi,

is this a function or procedure inside the package? Can you post how you would call it from sqlplus..

- Ralf

[#3]

This is function inside the package.

It was called from PL/SQL as:

declare

y number;

begin

y:=xxx_custom.to_num('1');

end;

[rbecher]

you could try:

SELECT xxx_custom.to_num('1') as y FROM dual;

[#5]

It doesn't work. I tried to call it in pl/sql and it is good. But in qlikview script, it complained not have valid column if I used:  SELECT xxx_custom.to_num('1') as y FROM dual;

[beristardj]

Its may be one theme from oracle.

In this case my recomendation is use Store Procedure, but you need grants for store any objects. and grants for execute this procedure.

on the oracle only run

in the QV script yo can try

SQL

execute schema.store_procedure([vars]) ;

But, be carefull, the nature of qlikview is not invasive.

"One great power required one great Responsability" -Ben Parker-

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

Esto puede ser mas un tema de Oracle

En este caso mi recomendacion es uso de Store Procedure, pero necesitas privilegios de Store any Objects, y privilegios para ejecutar este Procedimiento

Asi oracle solo ejecuta

en el script de qlikview intenta

SQL

execute schema.store_procedure([vars]) ;

Pero, ten cuidado, la naturaleza de qlikview es no ser invasivo

Saludos desde Mexico

[rbecher]

I tried it with a basic example and it works in QlikView:

-- Create Oracle Package with a Function:

CREATE OR REPLACE PACKAGE test_pkg AS

   FUNCTION get_sum (a NUMBER, b NUMBER)

      RETURN NUMBER;

END test_pkg;

/

CREATE OR REPLACE PACKAGE BODY test_pkg AS

FUNCTION get_sum

   (a in NUMBER, b in NUMBER)

   RETURN NUMBER IS

   BEGIN  

      RETURN a + b;

   END;

END test_pkg;

/

// Select in QlikView:

SELECT test_pkg.get_sum(7,9) as y FROM dual;

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

So, if this is not working for you then the issue could be related to:

- QlikView version

- ODBC/OLEDB driver or version

- Missing privileges on Package for the connected User

- Ralf

[#8]

Hello

Here is Function inside package, it's works But with the procedure inside the package it's not works

Do you have any ideas how to fix it?

[rbecher]

Hi Tatiana,

a Procedure cannot work in the SELECT statement... It's not a QlikView issue. You need to use EXECUTE here.

- Ralf

[#10]

EXEC not works too...

I can exec procedure, but it's a BIG problem with exec procedure in package

I try to call  oracle procedure set_from_date(p_from_date timestamp)

sp:

SQL EXEC export_pkg.set_from_date '$(vMaxDate)';

and the second day I receive errors only: