I am trying to create a new table using a cursor and a select statements.
Declare
Cursor c1 is
select store_number from table1 where CLIENT_ID= 1 and ORG_ID = 1 ;
Type C1_TAB_TYPE is table of c1%ROWTYPE;
c1_list c1_TAB_TYPE;
Begin
For r1 in c1
Loop
select store_number
BULK COLLECT INTO c1_list from
(
SELECT store_number,
( (SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(a.geometry,b.geometry, 0.005), 0.005, 'unit=sq_mile') /SDO_GEOM.SDO_AREA((a.geometry), 0.005, 'unit=sq_mile'))*100) Total_area
FROM table2 a, table1 b
Where store_number != r1.store_number
AND sdo_relate(a.geometry, b.geometry, 'mask=anyinteract') = 'TRUE');
For i in 1..c1_list.count loop
insert into temp_Prop_area
(STORE_NUMBER,TOTAL_AREA)
values (r1.store_number,Total_Area);
End Loop;
End Loop;
End;
Here I am using a store number to pass into a cursor.Now once have a store number ,I check want to get the area of overlapping between polygon for each store and the small polygons from table 1 over which the stores overlay. That area is called as Total_area. I am trying to pass this store number and total area in to the temp_Prop_area table.
But I wont be able to do since in my cursor type I just have store_number . So what could I be doing to fix this.
Thank You.
Not sure I quite understand what you're doing, but the issue seems to be that your c1_list doesn't have a column to put the Total_Area? If so you can force that by selecting a dummy column in your cursor:
Declare
Cursor c1 is
select store_number, 0 as total_area
from table1 where CLIENT_ID= 1 and ORG_ID = 1 ;
Type C1_TAB_TYPE is table of c1%ROWTYPE;
c1_list c1_TAB_TYPE;
Then you can just select into that:
...
Loop
select store_number, total_area
BULK COLLECT INTO c1_list from
...
For i in 1..c1_list.count loop
insert into temp_Prop_area
(STORE_NUMBER,TOTAL_AREA)
values (c1_list(i).store_number,c1_list(i).Total_Area);
End Loop;
End Loop;
End;
But you aren't taking advantage of bulk inserts, which is a shame as you are already using bulk collect. If you define the collection based on your new table then you can use forall.
Declare
Cursor c1 is
select store_number
from table1 where CLIENT_ID= 1 and ORG_ID = 1 ;
Type C1_TAB_TYPE is table of temp_prop_area%ROWTYPE;
c1_list c1_TAB_TYPE;
...
Loop
select store_number, total_area
BULK COLLECT INTO c1_list from
...
Forall i in 1..c1_list.count
insert into temp_Prop_area
values c_list(i);
End Loop;
End;
This assumes your temp_prop_area only has those two columns, in that order; if not you'll need to adjust the select to pull dummy values for any other columns.
As per you suggestions here is the following block.
Declare
Cursor c1 is
select store_number ,0 as total_area
from TMCS.TMCS_All_Stores_TA where CLIENT_ID= 1 and ORG_ID = 1 ;
Type C1_TAB_TYPE is table of c1%ROWTYPE;
c1_list c1_TAB_TYPE;
Begin
For r1 in c1
Loop
select store_number,total_area
BULK COLLECT INTO c1_list from
(
SELECT store_number,
( (SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(a.geometry,b.geometry, 0.005), 0.005, 'unit=sq_mile') /SDO_GEOM.SDO_AREA((a.geometry), 0.005, 'unit=sq_mile'))*100) Total_area
FROM TMCS.TMCS_BG_STI_DATA a, TMCS.TMCS_All_Stores_TA b
Where store_number != r1.store_number
AND sdo_relate(a.geometry, b.geometry, 'mask=anyinteract') = 'TRUE');
For i in 1..c1_list.count loop
insert into TMCS.temp_Prop_area
values c1_list(i);
End Loop;
End Loop;
End;
Now in the last part I actually wanted them to split in two tables.One table will have all the values which have total_area as 100 and the other table will have the rest which are not 100. So could it be like or could it be more fine tuned
if total_area=100
then
For i in 1..c1_list.count loop
insert into TMCS.temp_Prop_area_100
values c1_list(i);
End Loop;
else if
For i in 1..c1_list.count loop
insert into TMCS.temp_Prop_area_block
values c1_list(i);
