BILL Carlisle
asked on
PL/SQL table in a package accessed from outside package
Hi all,
Here is what I have
---Created a package spec with a declaration of PL/SQL table
create or replace PACKAGE XXAZZ_NLI_PK AS
type g_load_status_type is table of varchar2(1)
index by varchar2(255);
g_load_status_tab g_load_status_type;
--- then load in APEX process:
declare
ls_errmsg varchar2(100);
begin
ls_errmsg := 'Set default to "O" Open';
for j in (select PROCESSING_STEP descr, 'O' status
from XXAZZ_CUS.XXAZZ_PROCESSING _SEQ ) loop
XXAZZ_NLI_PK.g_load_status _tab(j.des cr) := j.status;
end loop;
ls_errmsg := 'If exists set actual';
for i in (select descr, status
from xxazz_cus.xxazz_load_statu ses
where proj_id = :P6_PROJ_ID) loop
XXAZZ_NLI_PK.g_load_status _tab(i.des cr) := i.status;
end loop;
exception
when others then
raise_application_error(-2 0127, 'ERROR in Set Load Status Process - '||ls_errmsg||' - '||SQLERRM);
end;
SAVE Validation Condition:
PL/SQL Expression
XXAZZ_NLI_PK.g_load_status _tab('CREA TE_ORDER_H EADER') IN ('O','E')
When I save I get this
Error processing condition.
ORA-01403: no data found
I tested the load and have values ..
begin
for j in (select PROCESSING_STEP descr, 'O' status
from XXAZZ_CUS.XXAZZ_PROCESSING _SEQ ) loop
XXAZZ_NLI_PK.g_load_status _tab(j.des cr) := j.status; --- one of the descr is 'CREATE_ORDER_HEADER'
htp.p(j.descr||' - '|| j.status);
end loop;
for i in (select descr, status
from xxazz_cus.xxazz_load_statu ses --- if any exists, one of the descr is 'CREATE_ORDER_HEADER'
where proj_id = 136) loop
XXAZZ_NLI_PK.g_load_status _tab(i.des cr) := i.status;
htp.p(i.descr||' - '|| i.status);
end loop;
htp.p('Result -- '||XXAZZ_NLI_PK.g_load_sta tus_tab('C REATE_ORDE R_HEADER') );
end;
1---CREATE_ORDER_HEADER - O
1---CREATE_ASSIGN_ITEMS - O
1---CREATE_PROJECT - O
1---CREATE_ORDER_LINES - O
1---CREATE_ITEM_ROUTINGS - O
1---CREATE_WIP_JOBS - O
1---CREATE_DRAFT_BUDGET - O
1---BASELINE_BUDGET - O
2---CREATE_ORDER_HEADER - S
2---CREATE_ASSIGN_ITEMS - S
2---CREATE_PROJECT - S
2---CREATE_ORDER_LINES - E
2---CREATE_ITEM_ROUTINGS - P
2---CREATE_WIP_JOBS - P
2---CREATE_DRAFT_BUDGET - P
2---BASELINE_BUDGET - P
Result -- S
Also tried
begin
if(nvl(XXAZZ_NLI_PK.g_load _status_ta b('CREATE_ ORDER_HEAD ER'),'P')= 'S') then
return true;
else
return false;
end if;
end;
Same error
Here is what I have
---Created a package spec with a declaration of PL/SQL table
create or replace PACKAGE XXAZZ_NLI_PK AS
type g_load_status_type is table of varchar2(1)
index by varchar2(255);
g_load_status_tab g_load_status_type;
--- then load in APEX process:
declare
ls_errmsg varchar2(100);
begin
ls_errmsg := 'Set default to "O" Open';
for j in (select PROCESSING_STEP descr, 'O' status
from XXAZZ_CUS.XXAZZ_PROCESSING
XXAZZ_NLI_PK.g_load_status
end loop;
ls_errmsg := 'If exists set actual';
for i in (select descr, status
from xxazz_cus.xxazz_load_statu
where proj_id = :P6_PROJ_ID) loop
XXAZZ_NLI_PK.g_load_status
end loop;
exception
when others then
raise_application_error(-2
end;
SAVE Validation Condition:
PL/SQL Expression
XXAZZ_NLI_PK.g_load_status
When I save I get this
Error processing condition.
ORA-01403: no data found
I tested the load and have values ..
begin
for j in (select PROCESSING_STEP descr, 'O' status
from XXAZZ_CUS.XXAZZ_PROCESSING
XXAZZ_NLI_PK.g_load_status
htp.p(j.descr||' - '|| j.status);
end loop;
for i in (select descr, status
from xxazz_cus.xxazz_load_statu
where proj_id = 136) loop
XXAZZ_NLI_PK.g_load_status
htp.p(i.descr||' - '|| i.status);
end loop;
htp.p('Result -- '||XXAZZ_NLI_PK.g_load_sta
end;
1---CREATE_ORDER_HEADER - O
1---CREATE_ASSIGN_ITEMS - O
1---CREATE_PROJECT - O
1---CREATE_ORDER_LINES - O
1---CREATE_ITEM_ROUTINGS - O
1---CREATE_WIP_JOBS - O
1---CREATE_DRAFT_BUDGET - O
1---BASELINE_BUDGET - O
2---CREATE_ORDER_HEADER - S
2---CREATE_ASSIGN_ITEMS - S
2---CREATE_PROJECT - S
2---CREATE_ORDER_LINES - E
2---CREATE_ITEM_ROUTINGS - P
2---CREATE_WIP_JOBS - P
2---CREATE_DRAFT_BUDGET - P
2---BASELINE_BUDGET - P
Result -- S
Also tried
begin
if(nvl(XXAZZ_NLI_PK.g_load
return true;
else
return false;
end if;
end;
Same error
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Moved it and all is fine!
Thanks again!
Bill