| duplicates in pl/sql table [message #36617] |
Thu, 06 December 2001 05:36  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
I have a pl/sql table that I load from REF cursor.
I use this cursor several times FOR different very complicated SELECTs and when my pl/sql table is loaded (and it has about 5 million records) I need to pass it to other procedures as a parameter.
My problem is that after all these loads the table gets duplicates.
I am trying to find the best way of how to get rid of them (I cannot compare cursor variable against anything,i.e. I cannot state anything like
IF cursor_variable.column_name<>pl_sql_table.column_name
THEN... load cursor_variable INTO pl_sql_table)
and if anybody has an idea of how to do it elegantly and share it with me I will appreciate it greatly.
Thank you
----------------------------------------------------------------------
|
|
|
|
| Re: duplicates in pl/sql table [message #36619 is a reply to message #36617] |
Thu, 06 December 2001 06:33  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
you can use pl/sql block below to remove duplicate values from pl/sql table.
declare
type tab1 is table of varchar2(10) index by binary_integer;
t1 tab1;
t2 tab1;
l_count number;
n number(20):=1;
begin
t1(1) := 'S';
t1(2) := 'U';
t1(3):= 'R';
t1(4):= 'E';t1(5):= 'H';t1(6):= 'S';t1(7):= 'U';
t1(8):= 'D';
t1(9):= 'B';
t1(10):= 'V';
t1(11):= 'S';
t1(12):= 'R';
FOR i IN 1..t1.last LOOP
IF t1.exists(i) THEN
for j in i+1..t1.last loop
IF t1.exists(j) THEN
IF t1(i)=t1(j) THEN
t1.delete(j);
END IF;
ELSE
null;
END IF;
end loop;
t2(n) := t1(i);
n := n+1;
END IF;
END LOOP;
for k in 1..t2.last loop
dbms_output.put_line(t2(k));
end loop;
end;
----------------------------------------------------------------------
|
|
|
|
| Re: duplicates in pl/sql table [message #36623 is a reply to message #36619] |
Thu, 06 December 2001 07:48 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Thank you very much, Suresh,
but perhaps I still cannot figure something out because after I applied your suggestion to my code, The line
IF tblmtc(n)=tblmtc(j)
gives me this error message:
ERROR at line 1:
ORA-06550: line 128, column 21:
PLS-00306: wrong number or types of arguments in call to '='
How can it be if I equate the same pl/sql table?
----------------------------------------------------------------------
|
|
|
|
| Re: duplicates in pl/sql table [message #36625 is a reply to message #36623] |
Thu, 06 December 2001 08:02 |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
i cant figure it out where you are getting that error. use same syntax as in my program. so that i can easily figure it out. in my program there is no "IF tblmtc(n)=tblmtc(j)" statement.(use your pl/sql table names but use index variables like i,j , the way i used in my program )
or send me your actual code to my email address
you can compare or equate 2 elements in same pl/sql tables.(or collection)
ex:
if t1(1)=t1(2) -- correct
i am suspecting , you are not using for loop index variables properly.
SURESH
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
