Re: SELECT from a Nested Table

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 19 Feb 2003 21:40:51 GMT
Message-ID: <b30tl2$1h3tiv$1@ID-82536.news.dfncis.de>

Hello

Your collection type must not be local to a package, it must be global:

create type vc_t as table of varchar2(20); /

Then, your package becomes:

create or replace package ptg_admin as

type dept_list is table of varchar2(20);

function makedeptlist(deptdelimlist in varchar2, deliminator in varchar2)

return vc_t;

procedure budgetsnotindeptlist(deptdelimlist in varchar2, deliminator in varchar2);

end;
/

CREATE OR REPLACE PACKAGE BODY PTG_ADMIN as

FUNCTION makeDeptList
(
deptDelimList in varchar2,
deliminator in varchar2
) return vc_t
IS

  i integer;
  j integer;
  k integer;

deptList vc_t := vc_t();

BEGIN

  i := 1;
  j := 1;
  k := 1;

  while k <= length(deptDelimList)
  loop
    j := instr(deptDelimList,deliminator,k,1);

if j = 0 then
begin

      deptList.extend;
      deptList(deptList.count) := substr(deptDelimList,k, length(deptDelimList));
      k := length(deptDelimList) + 1;

    end;
    else
    begin

      deptList.extend;
      deptList(deptList.count) := substr(deptDelimList,k, (j- k));
      k := j + 1;

end;
end if;

i := i + 1;

  end loop;
  RETURN deptList;
END MAKEDEPTLIST; procedure BudgetsNotInDeptList
(
  deptDelimList in varchar2,
  deliminator in varchar2
)
IS
  deptList vc_t;
begin
  deptList := makeDeptList(deptDelimList,deliminator);

for r in (select * from table(CAST(deptList AS vc_t))) loop dbms_output.put_line(r.column_value); end loop;

end BudgetsNotInDeptList;
end;
/

You also didn't inialize your return variable and extend it when needed.

Hth

Rene Nyffenegger

> Hello, everyone. I have a question about SELECTing data from a nested
> table. I am running Oracle 9i. I am using a package that has one
> function which returns a variable of type TABLE, and one procedure
> which returns the rows from that table. My source code is below. My
> main problem is on this:
>
> SELECT * FROM THE(SELECT CAST(deptList AS sa.PTG_ADMIN.DEPT_LIST) FROM
> DUAL);
>
> My function works fine, because I can put DBMS_OUTPUT statements in it
> and it will print out each row of the table variable. But I can't get
> the SELECT in my procedure to work correctly. Any help is much
> appreciated. Thanks!
>
> --Mike Loll
>
> p.s. Oh, you will notice the "SA" schema name ... this DB was migrated
> from SQL Server and it created the scheme name as "SA". Interesting,
> I guess. I plan on moving everything to a new schema name soon.
>
> p.s.s. I am a complete Oracle newbie. :)
>
> ---
> CREATE OR REPLACE PACKAGE "SA"."PTG_ADMIN" as
>
> TYPE DEPT_LIST IS TABLE OF VarChar2(20);
>
> function makeDeptList(deptDelimList in varchar2, deliminator in
> varchar2)
> return sa.PTG_admin.DEPT_LIST;
>
> procedure BudgetsNotInDeptList(deptDelimList in varchar2,
> deliminator in varchar2);
>
> end;
>
> CREATE OR REPLACE PACKAGE BODY "SA"."PTG_ADMIN" as
>
> FUNCTION makeDeptList
> (
> deptDelimList in varchar2,
> deliminator in varchar2
> ) return SA.PTG_ADMIN.DEPT_LIST
> IS
> i integer;
> j integer;
> k integer;
> deptList DEPT_LIST;
>
> BEGIN
>
> i := 1;
> j := 1;
> k := 1;
>
> while k <= length(deptDelimList)
> loop
> j := instr(deptDelimList,deliminator,k,1);
>
> if j = 0 then
> begin
> deptList(i) := substr(deptDelimList,k, length(deptDelimList));
> k := length(deptDelimList) + 1;
> end;
> else
> begin
> deptList(i) := substr(deptDelimList,k, (j- k));
> k := j + 1;
> end;
> end if;
>
> i := i + 1;
>
> end loop;
> RETURN deptList;
> END MAKEDEPTLIST;
>
> procedure BudgetsNotInDeptList
> (
> deptDelimList in varchar2,
> deliminator in varchar2
> )
> IS
> deptList DEPT_LIST;
> begin
> deptList := makeDeptList('0,1,2,3,4,5,6,7,8,9,10', ',');
>
> SELECT * FROM THE(SELECT CAST(deptList AS sa.PTG_ADMIN.DEPT_LIST)
> FROM DUAL );
>
> end BudgetsNotInDeptList;
> end;

-- 
  no sig today

Received on Wed Feb 19 2003 - 15:40:51 CST

Re: SELECT from a Nested Table - Need our help.