Object does not exists incase of collections in plsql

Hi Team,

Below is the code I have written to get the department name for a given employeeid  , I have modified the code for the sake of asking question.
When I execute the code , Iam getting object does not exists or delete

Select * from table(get_empdata(1));

create or replace
FUNCTION get_empdata(p_empid varchar2)
RETURN  data_load.t_emplist PIPELINED IS
l_emplist data_load.t_emplist;
begin        
         
          Select a.emp_id, b.department_name
          bulk collect into data_load.l_emp_list
          from emp a join table(data_load.l_emp_dept)  b on (b.IS_EMP='Y' and b.dept_id=a.dept_id )
          where a.empid = p_empid;
         
                   
        for rec in l_emplist.first..l_emplist.last
        loop
                  pipe row (l_emplist(rec));        
        end loop;
end;


Create or replace package data_load
is
            type r_empdata is Record
            (
            empid number,
            deptname varchar2(40),
            is_emp char(1)
            );
            type t_emplist is table of r_empdata;
            l_emp_dept t_emplist:=t_emplist();
end;


Create or replace package body data_load
is
NULL;
begin
            l_emp_dept.extend;
            l_emp_dept(1).deptid=1;
            l_emp_dept(1).deptname='Finance';
            l_emp_dept(1).is_emp='Y';

            l_emp_dept.extend;
            l_emp_dept(2).deptid=2;
            l_emp_dept(2).deptname='Sales';
            l_emp_dept(2).is_emp='Y';

            l_emp_dept.extend;
            l_emp_dept(3).deptid=3;
            l_emp_dept(3).deptname='IT';
            l_emp_dept(3).is_emp='Y';

end;

Any help is really appreciated.
sam_2012Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
It doesn't work because you hard-coded the select inside the function and ignored the intent of pulling from the pre-populated type.

Here is a complete test case based on what you have defined.  I still say it likely isn't necessary and what you want but, here it is:
drop table myemp purge;
create table myemp(emp_id number, dept_id number);
insert into myemp values(1,1);
insert into myemp values(2,2);
insert into myemp values(3,3);
commit;


Create or replace package data_load
is
            type r_empdata is Record
            (
            dept_id number,
            deptname varchar2(40),
            is_emp char(1)
            );
            type t_emplist is table of r_empdata;
            l_emp_dept t_emplist:=t_emplist();
end;
/

show errors

Create or replace package body data_load
is
begin
            l_emp_dept.extend;
            l_emp_dept(1).dept_id :=1;
            l_emp_dept(1).deptname :='Finance';
            l_emp_dept(1).is_emp :='Y';

            l_emp_dept.extend;
            l_emp_dept(2).dept_id :=2;
            l_emp_dept(2).deptname :='Sales';
            l_emp_dept(2).is_emp :='Y';

            l_emp_dept.extend;
            l_emp_dept(3).dept_id :=3;
            l_emp_dept(3).deptname :='IT';
            l_emp_dept(3).is_emp :='Y';

end;
/

show errors

create or replace
FUNCTION get_empdata(p_empid varchar2) 
RETURN  data_load.t_emplist PIPELINED 
IS
	l_emplist data_load.t_emplist;
begin        
          
	Select a.emp_id, b.deptname, 'Z'
	bulk collect into l_emplist
	from myemp a join table(data_load.l_emp_dept)  b on (b.IS_EMP='Y' and b.dept_id=a.dept_id )
	where a.emp_id = p_empid;


                   
        for rec in l_emplist.first..l_emplist.last
        loop
                  pipe row (l_emplist(rec));        
        end loop; 
end;
/

show errors

Select * from table(get_empdata(1));
Select * from table(get_empdata(2));
Select * from table(get_empdata(3));

Open in new window


The output from the selects is below.

Yes, I know you don't need the 'Z' but you have three columns in your type, so I needed to select three columns i the output.
SQL> Select * from table(get_empdata(1));

   DEPT_ID DEPTNAME                                 I
---------- ---------------------------------------- -
         1 Finance                                  Z

SQL> Select * from table(get_empdata(2));

   DEPT_ID DEPTNAME                                 I
---------- ---------------------------------------- -
         2 Sales                                    Z

SQL> Select * from table(get_empdata(3));

   DEPT_ID DEPTNAME                                 I
---------- ---------------------------------------- -
         3 IT                                       Z

Open in new window

0
 
SujithData ArchitectCommented:
You have mixed up quite a few things here, especially the column names, variable names etc etc.

this is a workable form - change as it suits you

Create or replace package data_load
is
            type r_empdata is Record
            (
            deptid number,
            deptname varchar2(40),
            is_emp char(1)
            );
            type t_emplist is table of r_empdata;
            l_emp_dept t_emplist:=t_emplist();
end;


Create or replace package body data_load
is
begin
            l_emp_dept.extend;
            l_emp_dept(1).deptid :=1;
            l_emp_dept(1).deptname :='Finance';
            l_emp_dept(1).is_emp :='Y';

            l_emp_dept.extend;
            l_emp_dept(2).deptid :=2;
            l_emp_dept(2).deptname :='Sales';
            l_emp_dept(2).is_emp :='Y';

            l_emp_dept.extend;
            l_emp_dept(3).deptid :=3;
            l_emp_dept(3).deptname :='IT';
            l_emp_dept(3).is_emp :='Y';

end;



create or replace
FUNCTION get_empdata(p_empid varchar2) 
RETURN  data_load.t_emplist PIPELINED 
IS
l_emplist data_load.t_emplist;
begin        
          
          Select 10 deptid, 'hello' deptname, 'Y'  is_emp
          bulk collect into l_emplist
          from dual;
          
                   
        for rec in l_emplist.first..l_emplist.last
        loop
                  pipe row (l_emplist(rec));        
        end loop; 
end;

Select * from table(get_empdata(1));

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Is there a reason you are using collections and a pipelined function for this?  There are likely easier ways to get the results you want.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
sam_2012Author Commented:
Actually , I need to fetch the data based on 3 different conditions , Initally I used set operator union all to get the data based on the 3 different conditions as a single query .
But the problem was if the first query  returned 0 rows , even though the second query fetches data it is ignored.
Hence Iam using collections to fetch data into it and then use multiset to combine them into a single result and return back the data as row to anoher function where iam using this data to join another table . Hence Iam using pipeline function
0
 
sam_2012Author Commented:
My requirement is I need to convert the data stored in a collection as table  and the use it  join another table .
0
 
sam_2012Author Commented:
Sujith the solution provided does not work for me
0
 
slightwv (䄆 Netminder) Commented:
>>all to get the data based on the 3 different conditions as a single query
The code stub you posted still selects it one employee at a time so even if it ran, it likely won't get you what you want.

>>I need to fetch the data based on 3 different conditions

That sounds like a single select with a where clause.  I'm not seeing the need for packages, types and collections.

>>and the use it  join another table .

If you can make it a single query, you can use it in a join.
0
 
sam_2012Author Commented:
I cannot use a join to fetch data from the same table for different conditions , I need to go for an set operator
0
 
sam_2012Author Commented:
The fact is I need to stored some data mapping dynamically in an collection and then use it to join another table . I cannot create a table that is the requirement .
0
 
SujithData ArchitectCommented:
Why didn't the code work for you? It's a workable form of the code you posted.
0
 
sam_2012Author Commented:
Hi slightwv,

Iam still getting the ORA-21700: object does not exist or is marked for delete , when i run the code u have shared. Any help is really appreciated. I compiled the code as you have mentioned .

Select * from table(get_empdata(1));
0
 
SujithConnect With a Mentor Data ArchitectCommented:
@sam_2012, what version of oracle are you on. I believe the usage of pl/sql collections in SQL context is not possible prior to 12c.

A workable solution for the pre versions is given below, instead of types declared in package, you will have to declare objects as in the example here.

set serveroutput on 

drop table emp;

create table emp(
empid number,
deptid number
);

insert into emp values(1, 1);
insert into emp values(2, 2);

drop type t_emplist;

drop type r_empdata;

create or replace            type r_empdata is object
            (
            deptid number,
            deptname varchar2(40),
            is_emp char(1)
            );
/
            
create or replace            type t_emplist is table of r_empdata;
/

drop package data_Load
/
Create or replace package data_load
is

            l_emp_dept t_emplist := t_emplist() ;
end;
/

Create or replace package body data_load
is
begin
            l_emp_dept.extend;
            l_emp_dept(1) := r_empdata(1,  'Finance', 'Y');

            l_emp_dept.extend;
            l_emp_dept(2):= r_empdata(2,  'Sales', 'Y');

            l_emp_dept.extend;
            l_emp_dept(3):= r_empdata(3,  'IT', 'Y');

end;
/

create or replace
FUNCTION get_empdata(p_empid varchar2) 
RETURN  t_emplist PIPELINED 
IS
l_emplist t_emplist;
begin        
          
          Select r_empdata( a.empid, b.deptname, 'Y' )
          bulk collect into l_emplist
          from emp a join table(data_load.l_emp_dept)  b on (b.IS_EMP='Y' and b.deptid=a.deptid ) 
          where a.empid = p_empid;
          
                   
        for rec in l_emplist.first..l_emplist.last
        loop
                  pipe row (l_emplist(rec));        
        end loop; 
end;
/

Select * from table(get_empdata(1));

Select * from table(get_empdata(2));

Open in new window


This is the results returned
SQL> Select * from table(get_empdata(1));

    DEPTID DEPTNAME                                 I
---------- ---------------------------------------- -
         1 Finance                                  Y

SQL>
SQL> Select * from table(get_empdata(2));

    DEPTID DEPTNAME                                 I
---------- ---------------------------------------- -
         2 Sales                                    Y

Open in new window

0
 
sam_2012Author Commented:
mY version of DB is oracle 12c
0
 
sam_2012Author Commented:
awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.