PL SQL cursor is throwing error...any idea?

[Vinod Vijay]

I have a quesiton for you all. I have taken a small chunk of code from already existing working PL SQL procedure which is suppose to work but it is not. I have no idea why cursor is throwing error.
Any idea???

Following below is the code and attached screen shot for your reference:-

BEGIN
	
	   DBMS_OUTPUT.PUT_LINE('PL SQL SUCCESSFULLY STARTED');
	
	   CURSOR Cur_Lines IS SELECT * FROM spt_quote_line
         WHERE quote_number = '263655' AND group_header = 'N' FOR UPDATE;
	
	   
	   FOR lr_lines IN Cur_Lines
         LOOP
	
	        SELECT * INTO lr_sid_gbl FROM spt_bucket_global
              WHERE bucket_id = lr_lines.bucket_id;
	
        	  BEGIN
	                SELECT * INTO lr_coverage FROM spt_coverage
                      WHERE coverage_code = lr_lines.coverage_code   AND
                         industry_code = lr_sid_gbl.industry_code AND
                         country_code = 'CA'             AND
                         warranty_upgrade = lr_lines.warranty_upgrade    AND
                         active_flag <> 'N';
	               
				    DBMS_OUTPUT.PUT_LINE('DATA FOUND');					
                   
			     EXCEPTION WHEN NO_DATA_FOUND THEN
	            
		                DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
		
	        END;
	     END LOOP;
		DBMS_OUTPUT.PUT_LINE('PL SQL SUCCESSFULLY ENDED');
 END;
	

[Martin Vajsar]

You cannot declare variables in the code in PL/SQL; move the cursor declaration immediately before the BEGIN line.

Moreover, you're doing a join in the code. It is very ineffective. Do all the joins directly in the SQL, it will perform better.

[Vinod Vijay]

Martin Vajsar wrote:You cannot declare variables in the code in PL/SQL; move the cursor declaration immediately before the BEGIN line.

Moreover, you're doing a join in the code. It is very ineffective. Do all the joins directly in the SQL, it will perform better.

Thanks my PL SQL block started to work. But could please explain me what do you mean by "Do all the joins directly in the SQL"
I guess, I used joining in SQL only. Is there anything else to do for good performance? Please advice

[Jussi Taimiaho]

You are making two consecutive loops for searching data from 3 tables, which can all be joined with an inner join -query.

...
FROM spt_quote_line, spt_bucket_global, spt_coverage
WHERE spt_quote_line.bucket_id = spt_bucket_global.bucket_id
AND spt_coverage.coverage_code = spt_bucket_global.coverage_code

and so forth