topic Re: How to use PL/SQL package call in qlikivew script in QlikView

How to use PL/SQL package call in qlikivew script

Wed, 09 Jan 2013 13:57:58 GMT

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

Thanks!

Re: How to use PL/SQL package call in qlikivew script

rbecher — Wed, 09 Jan 2013 14:02:44 GMT

Hi,

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

- Ralf

Re: How to use PL/SQL package call in qlikivew script

Wed, 09 Jan 2013 14:58:51 GMT

This is function inside the package.

It was called from PL/SQL as:

declare

y number;

begin

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

end;

Re: How to use PL/SQL package call in qlikivew script

rbecher — Wed, 09 Jan 2013 15:19:59 GMT

you could try:

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

Re: How to use PL/SQL package call in qlikivew script

Thu, 10 Jan 2013 15:58:59 GMT

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;

Re: How to use PL/SQL package call in qlikivew script

beristardj — Fri, 11 Jan 2013 16:57:47 GMT

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

Re: How to use PL/SQL package call in qlikivew script

rbecher — Fri, 11 Jan 2013 17:57:11 GMT

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

Re: How to use PL/SQL package call in qlikivew script

Thu, 08 Aug 2013 13:22:46 GMT

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?

Re: How to use PL/SQL package call in qlikivew script

rbecher — Thu, 08 Aug 2013 13:27:32 GMT

Hi Tatiana,

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

- Ralf

Re: How to use PL/SQL package call in qlikivew script

Thu, 08 Aug 2013 13:40:28 GMT

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:

Re: How to use PL/SQL package call in qlikivew script

rbecher — Thu, 08 Aug 2013 13:44:13 GMT

How about this:

SQL EXECUTE export_pkg.set_from_date(to_date('$(vMaxDate)','dd.mm.yyyy'));

Re: How to use PL/SQL package call in qlikivew script

Thu, 08 Aug 2013 14:46:47 GMT

Thank you Ralf, but still not works

QV don't like this part "export_pkg.set_from_date", it even has not reached the passed parameters yet

Re: How to use PL/SQL package call in qlikivew script

Fri, 09 Aug 2013 08:46:31 GMT

it's works with CALL instead EXEC/EXECUTE

thanks

http://qlikviewmaven.blogspot.ru/2012/12/execute-oracle-procedure-from-loadscript.html

// Call Oracle data summarization procedure
sql call corp_report.update_rpt_data('$(var_COUNTRY)');