Home » SQL & PL/SQL » SQL & PL/SQL » duplicates in pl/sql table
duplicates in pl/sql table [message #36617] Thu, 06 December 2001 05:36 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: Procedure and functions
Next Topic: adding table to memory?
Goto Forum:
  


Current Time: Fri Aug 14 15:43:22 CDT 2020