Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PL/SQL Fetching more than one row

 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a stored procedure written in PL/SQL.

Here is a portion of it:

This part is returning more than one row:
OPEN p_cur_out FOR
SELECT sale_id
FROM car_sales
WHERE region = 100;

However, this part below is only returning one row. Could you explain why?

LOOP
FETCH p_cur_out INTO lv_sale_id
OPEN p_cur_out FOR
SELECT sale_id

FROM sales
WHERE region = 100
AND mysales = lv_sale_id;

EXIT WHEN p_cur_out%ROWCOUNT = 0 OR p_cur_out%NOTFOUND;
END LOOP;
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
UseCodeTags so that it is more readable.
Have you executed these 2 queries individually? What is the result of these queries?
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tapas Chand wrote:UseCodeTags so that it is more readable.
Have you executed these 2 queries individually? What is the result of these queries?


The following query returns a 3 and a 14.
SELECT sale_id FROM car_sales WHERE region = 100;

The following query returns a 3:
SELECT sale_id FROM sales WHERE region = 100 AND mysales = 3;

The following query returns a 14:
SELECT sale_id FROM sales WHERE region = 100 AND mysales = 14;

The problem is that the stored procedure only goes through the loop once. It should go through the loop twice.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When your second query returns only 1 row, why are you expecting the block in loop to execute twice?
Am I missing something?
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tapas Chand wrote:When your second query returns only 1 row, why are you expecting the block in loop to execute twice?
Am I missing something?


The first query returns two rows so the block in the loop should be executed twice. For every sale_id, there should be a corresponding lv_sale_id.
 
Dave Tolls
Ranch Hand
Posts: 2103
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


But that OPEN there is replacing your earlier OPEN for the cursor.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Dave says, you are replacing your first query with the second query in the cursor when you re-use the p_cur_out variable, so your query results are going to be messed up. Also, it's hard to be sure from these snippets, but it looks like you are doing nested cursor queries here. This is usually a code-smell, and in this case it looks like you could probably fetch the required data with a single query joining the "sales" and "car_sales" tables.

Finally, it looks like you're having trouble with the explicit loop syntax here. You might want to use a cursor FOR loop instead.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would like to thank everyone for their help. I rewrote my code so that it would only have one cursor.

Moreover, I was able to combine the two select statements like this:


It is working as expected now.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic