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