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
LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Can you construct a test case I can replicate?


This worked for me...

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;
END;
/

create table xxazz_processing_seq(processing_step varchar2(255));

create table xxazz_load_statuses(proj_id number, descr varchar2(255),status varchar2(1));

insert into xxazz_processing_seq values ('CREATE_ORDER_HEADER');
insert into xxazz_processing_seq values ('CREATE_ASSIGN_ITEMS');
insert into xxazz_processing_seq values ('CREATE_PROJECT');
insert into xxazz_processing_seq values ('CREATE_ORDER_LINES');
insert into xxazz_processing_seq values ('CREATE_ITEM_ROUTINGS');
insert into xxazz_processing_seq values ('CREATE_WIP_JOBS');
insert into xxazz_processing_seq values ('CREATE_DRAFT_BUDGET');
insert into xxazz_processing_seq values ('BASELINE_BUDGET');


insert into xxazz_load_statuses values (136,'CREATE_ORDER_HEADER','S');
insert into xxazz_load_statuses values (136,'CREATE_ASSIGN_ITEMS','S');
insert into xxazz_load_statuses values (136,'CREATE_PROJECT','S');
insert into xxazz_load_statuses values (136,'CREATE_ORDER_LINES','E');
insert into xxazz_load_statuses values (136,'CREATE_ITEM_ROUTINGS','P');
insert into xxazz_load_statuses values (136,'CREATE_WIP_JOBS','P');
insert into xxazz_load_statuses values (136,'CREATE_DRAFT_BUDGET','P');
insert into xxazz_load_statuses values (136,'BASELINE_BUDGET','P');
commit;

BEGIN
    FOR j IN (SELECT processing_step descr, 'O' status
                FROM xxazz_processing_seq)
    LOOP
        xxazz_nli_pk.g_load_status_tab(j.descr) := j.status; --- one of the descr is 'CREATE_ORDER_HEADER'
        DBMS_OUTPUT.put_line(j.descr || ' - ' || j.status);
    END LOOP;

    FOR i IN (SELECT descr, status
                FROM 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;
        DBMS_OUTPUT.put_line(i.descr || ' - ' || i.status);
    END LOOP;

    DBMS_OUTPUT.put_line('Result -- ' || xxazz_nli_pk.g_load_status_tab('CREATE_ORDER_HEADER'));
END;
/

Open in new window

flow01Commented:
And the loading of the internal table and trying to access the data in it did occur in the same database session ?  Each database session using the package has it own 'occurrrence' of data.

To check if there are any rows in the package internal table

DECLARE
  v_desc varchar2(80);
BEGIN
 -- nr of rows in the array
 DBMS_OUTPUT.PUT_LINE('#:' ||XXAZZ_NLI_PK.g_load_status_tab.COUNT);
-- show table
 v_desc := XXAZZ_NLI_PK.g_load_status_tab.first;
 WHILE v_desc IS NOT NULL LOOP
   DBMS_OUTPUT.PUT_LINE(v_desc || ':'  ||XXAZZ_NLI_PK.g_load_status_tab(v_desc);
   v_desc := XXAZZ_NLI_PK.g_load_status_tab.NEXT(v_desc);
END LOOP;
DBMS_OUTPUT.PUT_LINE('end of table');
END;

END;

BEGIN
  IF XXAZZ_NLI_PK.g_load_status_tab.EXISTS('CREATE_ORDER_HEADER') THEN
      DBMS_OUTPUT.PUT_LINE('row available');
  ELSE
      DBMS_OUTPUT.PUT_LINE('row not available');
  END IF;
END;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BILL CarlisleAPEX DeveloperAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.