topic running PL-SQL in QlikView

running PL-SQL

kaldubai — Wed, 25 Nov 2020 16:16:04 GMT

im trying to fetch one of the view from oracle erp

but in order to fetch the view i need to run a procedure to set the parameters otherwise it will be showing me 0 records

can some1 please advise

ODBC CONNECT TO [Qlik-oracle];

begin

SQL EXEC apps.mo_global.set_policy_context(p_access_mode => 'S', p_org_id => 397);

end;

Sale:

LOAD "SHR_ID",

SHR,

"CUST_NUM",

"CUST_NAME",

"CUSTOMER_PHONE",

"CTT_CLASS",

"TRX_DATE",

"TRX_NUMBER",

"SALESREP_NUMBER",

"SALESREP_NAME",

"SOR_NUMBER",

"BRAND_CODE",

"BRAND_NAME",

"ITEM_ID",

SEGMENT1,

DESCRIPTION,

QTYS,

QTYR,

"SOLD_VALUE",

"RET_VALUE",

"HDR_DISC",

"HDR_DISC_REASON",

"PL_DISC_AMT",

"LINE_DISC",

"LINE_DISC_PERC",

"LINE_CHANGE_REASON_CODE",

"LINE_DISC_REASON",

"LINE_DISC_APPROVER_NAME";

SQL SELECT *

FROM APPS."AMF_SALE_VIEW_WD";

Re: running PL-SQL

Peter_Cammaert — Mon, 27 Mar 2017 09:09:12 GMT

Please do not post the same question twice (even when in different forums). You will turn a single discussion in two threads and as a result split the attention. We can all see your original question in the general overview.

Original discussion: running PL-SQL

Re: running PL-SQL

maxgro — Sat, 28 Oct 2017 12:52:26 GMT

try (passing parameter by position)

odbc connect .....;

SQL

{CALL apps.mo_global.set_policy_context('S', 397) } ;

load .....;

sql select ....;

Re: running PL-SQL

kaldubai — Sun, 21 Jan 2018 07:03:43 GMT

@peter

thanks for notifying.

Re: running PL-SQL

kaldubai — Sun, 21 Jan 2018 08:57:43 GMT

the solution to this was as below:

sql call

//begin

apps.mo_global.set_policy_context(p_access_mode => 'S', p_org_id => 397);

I just had to comment out the begin key word and it worked just fine.

check the below thread for reference

Extracting views from Oracle