Link to home
Start Free TrialLog in
Avatar of BILL Carlisle
BILL CarlisleFlag for United States of America

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.descr) := j.status;
  end loop;

  ls_errmsg := 'If exists set actual';
  for i in (select descr, status
             from xxazz_cus.xxazz_load_statuses
            where proj_id = :P6_PROJ_ID) loop
    XXAZZ_NLI_PK.g_load_status_tab(i.descr) := i.status;
  end loop;
exception
  when others then
    raise_application_error(-20127, 'ERROR in Set Load Status Process - '||ls_errmsg||' - '||SQLERRM);
end;


SAVE Validation Condition:
PL/SQL Expression
      XXAZZ_NLI_PK.g_load_status_tab('CREATE_ORDER_HEADER') 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.descr) := 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_statuses --- if any exists, one of the descr is 'CREATE_ORDER_HEADER'
            where proj_id = 136) loop
    XXAZZ_NLI_PK.g_load_status_tab(i.descr) := i.status;  
htp.p(i.descr||' - '|| i.status);
  end loop;

htp.p('Result -- '||XXAZZ_NLI_PK.g_load_status_tab('CREATE_ORDER_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_tab('CREATE_ORDER_HEADER'),'P')='S') then
    return true;
 else
    return false;
 end if;
end;

Same error
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BILL Carlisle

ASKER

Thanks for the help! Yes, it works fine, just had the loading of the package table in the wrong place.
Moved it and all is fine!
Thanks again!
Bill