Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443372] |
Mon, 15 February 2010 06:16 |
sitesh.mukherjee
Messages: 5 Registered: December 2009 Location: India
|
Junior Member |
|
|
Guys,
I've a table like below data.
Table_1:
Name Object_Name
----------------------
1001 CUST_DATA
1001 ITEM_DATA
1001 SALES_DATA
1002 CUST_DATA
1002 ITEM_DATA
1002 RATE_DATA
1002 GROUP_DATA
Now from a procedure using Varray, I'm populating the "Object_Name" from the Table_1 compare with "Name".
While I'm creating Varray through pl/sql block for Object_name's, I'm getting the data from the Varray like below.
CUST_DATA
ITEM_DATA
SALES_DATA
CUST_DATA
ITEM_DATA
RATE_DATA
GROUP_DATA
Now my actual requirement is to eliminate the duplicate names from Varray and generate the Varray list with distinct object names , like below.
CUST_DATA
ITEM_DATA
SALES_DATA
RATE_DATA
GROUP_DATA
Can anybody help me on this?
Thanks is advance.....
|
|
|
Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443373 is a reply to message #443372] |
Mon, 15 February 2010 06:17 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Populate using DISTINCT.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Regards
Michel
[Updated on: Mon, 15 February 2010 06:18] Report message to a moderator
|
|
|
Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443377 is a reply to message #443372] |
Mon, 15 February 2010 06:32 |
sitesh.mukherjee
Messages: 5 Registered: December 2009 Location: India
|
Junior Member |
|
|
Michael,
If I use the DISTINCT clause from query comparing Name & Object_Name, then also I'll get duplicate values for Object Names. So, distinct clause can't be used.
My requirement is to create a Single dimention VARRAY for Object names, where only distinct object_names should be considered. And based on the result I need to write other business logic as per requirement.
My present code is like below:
-------------------------------
CREATE OR REPLACE procedure varray_test1
is
type app_desc is varray(50) of Table_1.Object_Name%type;
a_desc app_desc := app_desc();
indx number := 1;
begin
for r1 in
(select object_name from Table_1
where name in('1001','1002')) loop
if indx < a_desc.limit then
a_desc.extend;
a_desc(a_desc.last) := r1.referenced_name;
dbms_output.put_line(a_desc(indx));
indx := indx +1;
end if;
end loop;
end;
I want to eliminate the duplicate names befor useing EXTEND.
If somebody gives me the solution using Nested Table, that is also be ok with me.
Thanks in advance...
|
|
|
|
Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443389 is a reply to message #443377] |
Mon, 15 February 2010 08:04 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Add a DISTNCT:CREATE OR REPLACE procedure varray_test1
is
type app_desc is varray(50) of Table_1.Object_Name%type;
a_desc app_desc := app_desc();
indx number := 1;
begin
for r1 in
(select distinct object_name from Table_1
where name in('1001','1002')) loop
if indx < a_desc.limit then
a_desc.extend;
a_desc(a_desc.last) := r1.referenced_name;
dbms_output.put_line(a_desc(indx));
indx := indx +1;
end if;
end loop;
end;
[too slow!!!!!]
[Updated on: Mon, 15 February 2010 08:04] Report message to a moderator
|
|
|
Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443505 is a reply to message #443389] |
Tue, 16 February 2010 01:47 |
sitesh.mukherjee
Messages: 5 Registered: December 2009 Location: India
|
Junior Member |
|
|
Guys,
I've given the above query as an example for better understanding.
But, the actual query contain some analytical functions also (like COUNT(*) over partition, ROW_NUMBER over partition etc.) to get the count and sequence.So I can't use the DISTINCT clause here.
I need to eliminate the duplicate Object_Names from the VARRAY result, which is populating through cursor.
Actual scenario data like below:
--------------------------------
Name Object_Name CNT SEQ
1001 CUST_DATA 3 1
1001 ITEM_DATA 3 2
1001 SALES_DATA 3 3
1002 CUST_DATA 4 1
1002 ITEM_DATA 4 2
1002 RATE_DATA 4 3
1002 GROUP_DATA 4 4
Present VARRAY data like below:
-------------------------------
CUST_DATA
ITEM_DATA
SALES_DATA
CUST_DATA
ITEM_DATA
RATE_DATA
GROUP_DATA
I want the data like below into the VARRAY:
-------------------------------------------
CUST_DATA
ITEM_DATA
SALES_DATA
RATE_DATA
GROUP_DATA
Can anybody help this? Solution from nested table option also will be ok with me.
Thanks in advance...
|
|
|
|