Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have PL/SQL package call and it return one output. What is syntax to use PL/SQL package call in qlikview script?
Thanks!
Hi,
is this a function or procedure inside the package? Can you post how you would call it from sqlplus..
- Ralf
This is function inside the package.
It was called from PL/SQL as:
declare
y number;
begin
y:=xxx_custom.to_num('1');
end;
you could try:
SELECT xxx_custom.to_num('1') as y FROM dual;
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;
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
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
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?
Hi Tatiana,
a Procedure cannot work in the SELECT statement... It's not a QlikView issue. You need to use EXECUTE here.
- Ralf
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: