oracle functions in qlikview

[#1]

Hello,

Is there a way to use a oracle function with in and out parameters?

Thank you,

Silviu.

[fernando_tonial]

It is possible.

See my function.

create or replace function f_md5 (cValor varchar) return varchar2 is
cInput varchar2(2000) := cValor;
cHexkey varchar2(32) := null;
begin
cHexkey := rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(cInput)));
return nvl(cHexkey,'');
end;
/

Using the function.

select f_md5('senha') as MD5 from dual;

Best Regards.

Tonial.

[#3]

thanx.

What I wold like to do is to execute an Oracle function (like the one that you mention) in edit module script of Qlikview.

Best regards.

Silviu.

[CELAMBARASAN]

Hi,

     You can use oracle functions but it possible only in the load script i.e, loading data from the database.

Celambarasan

[jagan]

Hi,

Oracle functions can be used in the qlikview in the Load script by using the Select queries.  Can you tell what function you need to use it in Edit Module. 

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     You cann't use the oracle functions as macro.

Celambarasan

[#7]

Hello.

My function returns if a policy is changed or not.

What i would like to do is to load the POLICY table, to apply the function into the load script, and to add one field at the end of the table that will contain Y/N(changed/not changed) for each row(each policy ID).

Oracle function:

FUNCTION IS_ANNEX_CHANGED (p_policy_id number) return varchar is

cursor c1 is select count(*)

from

(

select cfg.fract_type

from gen_risk_covered grc, cfg_gl_gen_fractions cfg

where grc.cover_type=cfg.cover_type

and grc.insr_type=cfg.insr_type

and risk_state between 0 and 12

and grc.annex_id<>0

and policy_id=p_policy_id

minus

select cfg.fract_type

from gen_risk_covered grc, cfg_gl_gen_fractions cfg

where grc.cover_type=cfg.cover_type

and grc.insr_type=cfg.insr_type

--and risk_state between 0 and 12

and grc.annex_id=0

and policy_id=p_policy_id

);

nr number;

begin

open c1;

fetch c1 into nr;

close c1;

if nvl(nr,0)>0 then

return 'Y';

else return 'N';

end if;

end;

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

policy:

LOAD POLICY_ID,

     INSR_TYPE,

     OFFICE_ID,

     CLIENT_ID,

     AGENT_ID,

     INSR_BEGIN,

     INSR_END

    ...................

FROM

[...\policy.qvd]

(qvd);

[rajeshvaswani77]

There are multiple ways you could do this in.

1) Oracle Functions or any normal oracle database queries can be called in the Qlikview level by preceding with SQL.

2)Convert this logic in to Set analysis and no need to use the oracle function then.

3)Have this value readily stored in the QVD where you would use the part of the function logic statements and load them in to policy.qvd

Thanks,

Rajesh Vaswani

[rajeshvaswani77]

There are multiple ways you could do this in.

1) Oracle Functions or any normal oracle database queries can be called in the Qlikview level by preceding with SQL.

2)Convert this logic in to Set analysis and no need to use the oracle function then.

3)Have this value readily stored in the QVD where you would use the part of the function logic statements and load them in to policy.qvd

Thanks,

Rajesh Vaswani

[#10]

Thank you!

The best way for me is a method to call this function into the qlikview load script, but I don't know how.

Can you please detaliate the 1'st point that you mention earlier?

Best regards,

Silviu.