Home » SQL & PL/SQL » SQL & PL/SQL » [merged] better way of doing this / not going inside FOR loop
[merged] better way of doing this / not going inside FOR loop [message #359299] Fri, 14 November 2008 10:56 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi Gurus,
I have a simple PL/SQL code as below.It is not inserting into t_localities table.Will you pls have a look why the code inside the loop is not executed.
I want to insert the values inti t_localities table based on the values selected by the cursor c1
set serveroutput on size 500000
set verify off
set lines 200

declare

cursor c1 is
select  distinct b.locality_id locid, 
	l.name locname , 
	b.county_id county, 
	l1.locality_id sec
	from t_buildings b, 
	t_urban_areas ua, 
	t_localities l, 
	t_localities l1, 
	t_post_towns p
	where b.urban_area_id=ua.urban_area_id
	and b.urban_area_id > 27
	and b.locality_id =l.locality_id
	and l.secondary_locality_id is null
	and b.thorfare_id > 0
	and p.name <> l.name
	and ua.name=p.name
	and p.name=l1.name
	and l1.locality_type_id=59
	and b.county_id=l.county_id
	and l.county_id=p.county_id
	and b.county_id=l1.county_id
	and l.locality_type_id<>59
	and b.invalid ='N'
	and b.route_id=5620;

		 	
begin
dbms_output.put_line('out');

for c1_rec in c1 loop
 dbms_output.put_line('in');
 dbms_output.put_line('sec'||c1_rec.sec);
	
	insert into t_localities
	(locality_id, locality_type_id, county_id, name, changed_user, changed_date, create_date, secondary_locality_id)
	VALUES(id_seq.nextval,58,c1_rec.county, c1_rec.locname,user,sysdate,sysdate,c1_rec.sec);
  end loop;
commit;
dbms_output.put_line('out again');
end;
/


When I run,it is not going inside the for loop..just curious why?
SQL> @test8
out
out again

PL/SQL procedure successfully completed.

Re: PL/SQL code not going inside FOR loop!!? [message #359300 is a reply to message #359299] Fri, 14 November 2008 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just (or the like):
insert into t_localities
(locality_id, locality_type_id, county_id, name, changed_user, changed_date, create_date, secondary_locality_id)
select  id_seq.nextval, 58, county, locname, user, sysdate, sysdate, sec
	from t_buildings b, 
	t_urban_areas ua, 
	t_localities l, 
	t_localities l1, 
	t_post_towns p
	where b.urban_area_id=ua.urban_area_id
	and b.urban_area_id > 27
	and b.locality_id =l.locality_id
	and l.secondary_locality_id is null
	and b.thorfare_id > 0
	and p.name <> l.name
	and ua.name=p.name
	and p.name=l1.name
	and l1.locality_type_id=59
	and b.county_id=l.county_id
	and l.county_id=p.county_id
	and b.county_id=l1.county_id
	and l.locality_type_id<>59
	and b.invalid ='N'
	and b.route_id=5620;

And maybe your select returns nothing.

Regards
Michel

[Updated on: Fri, 14 November 2008 11:03]

Report message to a moderator

Re: PL/SQL code not going inside FOR loop!!? [message #359539 is a reply to message #359300] Mon, 17 November 2008 02:59 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
hi michel thanks for the reply..
it worked now in pl/sql too..
you are right, if the select statement has 0 rows, it shouldn't go into the loop, right?I didnt think about that possibility..
(but,the select statement actually returned 1 row in this example and i was confused..I was trying in a different user where the select returned 0 rows and thats why it did not display the output messgage!)Now it works fine.

[Updated on: Mon, 17 November 2008 03:37]

Report message to a moderator

Re: PL/SQL code not going inside FOR loop!!? [message #359542 is a reply to message #359539] Mon, 17 November 2008 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, you don't need a loop a single insert makes the trick.
And I tend to trust Oracle, if it does not come into the loop then your select returns nothing.

Regards
Michel
Re: PL/SQL code not going inside FOR loop!!? [message #359572 is a reply to message #359542] Mon, 17 November 2008 06:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you are looking in hte wrong placve for the record,
or your code is erroring, but you;ve got something like a 'WHEN OTHERS THEN NULL;' that hides the error.

But Oracle is right, as a default position.
Is there a better way of doing this ...?? [message #360038 is a reply to message #359299] Wed, 19 November 2008 05:38 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi gurus, thanks for all the support from this forumn..its great!
I have a pl/sql program as shown below.
Its hanging when I am running it..I am interested to know what better way this could be implemented..
Basically in this, cursor C1 select the candidates for insert into a table.
Cursor c3 selects a combination of 3 columns.
If the same combination of rows in c3 is there in c1,I dont want to insert rows in t_localities, but instead just display error message..Please help.

code:
set serveroutput on size 1000000
spool c:\mywork\loc.txt
select count(*) from t_localities;

declare

CURSOR C1 IS 
SELECT distinct b.locality_id locid, 
	l.name locname , 
	b.county_id county, 
	l1.locality_id sec
	from t_buildings b, 
	t_urban_areas ua, 
	t_localities l, 
	t_localities l1, 
	t_post_towns p
	where b.urban_area_id=ua.urban_area_id
	and b.urban_area_id > 27
	and b.locality_id =l.locality_id
	and l.secondary_locality_id is null
	and b.thorfare_id > 0
	and p.name <> l.name
	and ua.name=p.name
	and p.name=l1.name
	and l1.locality_type_id=59
	and b.county_id=l.county_id
	and l.county_id=p.county_id
	and b.county_id=l1.county_id
	and l.locality_type_id<>59
	and b.invalid ='N';

CURSOR c3 is
select l.name locname2,
       b.county_id county2,
       l.secondary_locality_id sec2 from t_localities l,
       t_buildings b, 
       t_urban_areas ua where
       b.urban_area_id=ua.urban_area_id
	and b.locality_id =l.locality_id
        and b.urban_area_id > 27
	and b.thorfare_id > 0
        and b.county_id=l.county_id
        and l.secondary_locality_id is not null
	and b.invalid ='N';


BEGIN

for c2 in c1 loop
for c4 in c3 loop
if (c2.locname=c4.locname2 and c2.county=c4.county2 and c2.sec=c4.sec2) then
dbms_output.put_line('This combination of'||c2.locname||','||c2.county||','||c2.sec||'already exists');
else
	INSERT INTO T_LOCALITIES(locality_id, locality_type_id, county_id, name, changed_user, changed_date, create_date, secondary_locality_id) 
        VALUES (id_seq.nextval,58,c2.COUNTY,c2.LOCNAME,USER,SYSDATE,SYSDATE,c2.SEC);

end if;
end loop;
end loop;

exception
WHEN dup_val_on_index 
THEN 
dbms_output.put_line(sqlcode||sqlerrm);
end;
/

select count(*) from t_localities;
Re: Is there a better way of doing this ...?? [message #360041 is a reply to message #360038] Wed, 19 November 2008 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you really need the dbms_output?
If not, this becomes:


INSERT INTO t_localities (<columns>)
SELECT <statement that gets the data you need to insert>;

Re: Is there a better way of doing this ...?? [message #360049 is a reply to message #360038] Wed, 19 November 2008 06:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Better way is to do in straight SQL if Possible

Or

Incorporating the If condition in the Second cursor (if you do not really need dbms_output)

Smile
Rajuvan.
Re: Is there a better way of doing this ...?? [message #360050 is a reply to message #360041] Wed, 19 November 2008 06:11 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
yes i need the dbms_output to display which combinations did not work and display it in a txt file
Also 1 select will not do, I tried in several ways without a cursor, but not successful
Is there a better programming method?
Re: Is there a better way of doing this ...?? [message #360076 is a reply to message #360050] Wed, 19 November 2008 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
insert into t_localities
select......

FOR rec IN <select statement to get combos that don't work> LOOP

  dbms_output.put_line('This combination of'||rec.locname||','||rec.county||','||rec.sec||'already exists');

END LOOP;

Re: Is there a better way of doing this ...?? [message #360118 is a reply to message #360038] Wed, 19 November 2008 12:10 Go to previous message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear I think you can do it by following code.

declare

CURSOR c3 is
select l.name locname2, b.county_id county2,
l.secondary_locality_id sec2 from t_localities l,
t_buildings b,
t_urban_areas ua where
b.urban_area_id=ua.urban_area_id
and b.locality_id =l.locality_id
and b.urban_area_id > 27
and b.thorfare_id > 0
and b.county_id=l.county_id
and l.secondary_locality_id is not null
and b.invalid ='N';
Chk Number:=0;

BEGIN

for c4 in c3 loop

Select Count(*) Into Chk
from t_buildings b,
t_urban_areas ua,
t_localities l,
t_localities l1,
t_post_towns p
where b.urban_area_id=ua.urban_area_id
and b.urban_area_id > 27
and b.locality_id =l.locality_id
and l.secondary_locality_id is null
and b.thorfare_id > 0
and p.name <> l.name
and ua.name=p.name
and p.name=l1.name
and l1.locality_type_id=59
and b.county_id=l.county_id
and l.county_id=p.county_id
and b.county_id=l1.county_id
and l.locality_type_id<>59
and b.invalid ='N'
and i.name=c4.locname2
and b.county_id=c4.county2
and l1.locality_id =c4.sec2;

If Chk>0 Then
dbms_output.put_line('This combination of'||c2.locname||','||c2.county||','||c2.sec||'already exists');
Else
INSERT INTO T_LOCALITIES(locality_id, locality_type_id, county_id, name, changed_user, changed_date, create_date, secondary_locality_id)
VALUES (id_seq.nextval,58,c2.COUNTY,c2.LOCNAME,USER,SYSDATE,SYSDATE,c2.SEC);
End If;

end loop;

exception
WHEN dup_val_on_index
THEN
dbms_output.put_line(sqlcode||sqlerrm);
end;
/

Muhammad Asif Malik

[Updated on: Wed, 19 November 2008 12:16]

Report message to a moderator

Previous Topic: SQL Query verification
Next Topic: Using Decode in Where Clause
Goto Forum:
  


Current Time: Sun Dec 11 06:03:57 CST 2016

Total time taken to generate the page: 0.11235 seconds