Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help needed in Collections

758635Apr 1 2010 — edited Apr 9 2010
Hi Experts,
I need your help in solving this .
I am getting compilation errors as:
Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY TABLE_EXAMPLE:

LINE/COL ERROR
-------- -------------------------------------------------------------
18/1     PL/SQL: Statement ignored
18/20    PLS-00306: wrong number or types of arguments in call to '='
I will try to explain this as simple as possible .

The data in emp is like this:
     EMPNO ENAME      JOB              SAL
------- ---------- --------- ----------
   7782 CLARK      MANAGER         2450
   7839 KING       PRESIDENT       5000
   7934 MILLER     CLERK           1300
I need to pass parameters and values as table type as follows:
declare
       v_tab TABLE_EXAMPLE.tablet:=TABLE_EXAMPLE.tablet();
    begin
      v_tab.extend;
     v_tab(1).parameter:='ENAME';
     v_tab(1).value:= 'PETER';
     v_tab(1).parameter:='JOB';
     v_tab(1).value:= 'CEO';
     
      TABLE_EXAMPLE.P1(v_tab);
   end;
   
After the package is run the data should look like this :
 EMPNO ENAME      JOB              SAL
------ ---------- --------- ----------
  7782 PETER      CEO             2450
  7839 PETER      CEO             5000
  7934 PETER      CEO             1300
I have tried hard to create the code but i will highlight where i am stuck :
create or replace package table_example as
type tname is record(parameter emp%rowtype,value emp%rowtype);
type tablet is table of tname;
type emp_table is table of emp%rowtype;
procedure p1(arg in table_example.tablet);
end;
/

Package created
create or replace package body table_example as
procedure p1(arg in table_example.tablet)as
l_emp_tbl emp_table:=emp_table() ;
cursor c is select * from emp;
k number;
m number;
begin

k:=1;
for i in c loop
l_emp_tbl(k).ename:=i.ename;
l_emp_tbl(k).empno:=i.empno;
l_emp_tbl(k).deptno:=i.deptno;
l_emp_tbl(k).sal:=i.sal;

--------------------i am stuck here : how to assign the values?---------------------------
m:=1;
for  j in 1..arg.last loop
if arg(m).parameter= l_emp_tbl(j) then
l_emp_tbl(j):=arg(m).value ;
end if;
end loop;
____________stuck here ___________________

end loop;
exception
when others then 
dbms_output.put_line('other errors');
end;

end  table_example;


Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY TABLE_EXAMPLE:

LINE/COL ERROR
-------- --------------------------------------------------------------
18/1     PL/SQL: Statement ignored
18/20    PLS-00306: wrong number or types of arguments in call to '='
Please help.Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 7 2010
Added on Apr 1 2010
26 comments
2,169 views