Hi,
SF at oracle.com/technetwork/issue-archive/o53plsql-083350.html states that you can compare two database tables (of the same structure) by defining a nested table type (using %ROWTYPE) and two NT variables of that type, and loading the contents of each table into its respective NT variable, before comparing them using the = operator.
Having read the Oracle documentation which states that you can only compare NTs for equality if they don't contain record types, I was surprised to read this, but figured I would try it because I must be misunderstanding SF, but it didn't work.
SCOTT@ORCL> create table empcopy3 as select * from emp;
Table created.
declare
type emp_ntt is table of emp%rowtype;
emp_nt1 emp_ntt;
emp_nt2 emp_ntt;
begin
select * bulk collect into emp_nt1 from emp;
select * bulk collect into emp_nt2 from empcopy3;
if(emp_nt1=emp_nt2) then dbms_output.put_line('tables are eq');
else dbms_output.put_line('tables are not eq');
end if;
end;
/
SCOTT@ORCL> @C:\Users\J\Documents\SQL\test23.sql
if(emp_nt1=emp_nt2) then dbms_output.put_line('tables are eq');
*
ERROR at line 8:
ORA-06550: line 8, column 15:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored
If I instead do this, as expected, it works:
declare
type emp_ntt is table of emp.empno%type;
emp_nt1 emp_ntt;
emp_nt2 emp_ntt;
begin
select empno bulk collect into emp_nt1 from emp;
select empno bulk collect into emp_nt2 from empcopy3;
if emp_nt1 = emp_nt2 then dbms_output.put_line('tables are eq');
else dbms_output.put_line('tables are not eq');
end if;
end;
/
SCOTT@ORCL> @C:\Users\J\Documents\SQL\test23.sql
tables are eq
PL/SQL procedure successfully completed.
But SF goes on to say he timed the execution of his NT equality method, comparing it with a SQL-only equivalent, and so I must be missing something. My understanding is that using %ROWTYPE declares a record type.
Any pointers would be appreciated, thanks,
Jason