Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cursor not returning same rows as equivalent query 1

Status
Not open for further replies.
goaway1234

goaway1234

Programmer
Jun 2, 2004
78
US
consider the following:

Code: 
for l_child in (select *
                from dropdown
               where (p_category='Action' and 
                      category='Reason' and 
                      type=l_orig_contents)
                  or (p_category='Action' and 
                      category='Comment' and 
                      type2=l_orig_contents)
                  or (p_category='Reason' and 
                      category='Comment' and 
                      type=l_orig_contents))
loop
    for l_child_orgs in (select dd.id
          from dropdown dd,
               dropdown_org org,
               security_t
         where org.show_Orgs=dd.contents
           and dd.category='Orgs'
           and org.dropdown_id=l_child.id
           and security_t.grpname='Orgs'
           and security_t.userid=Nvl(p_security_uid, 0)
           and security_t.showname=dd.contents)
    loop
        ...

seems like pretty standard stuff, right? Except that the cursor in the inner loop is not returning any rows. However, when I execute the same query by substituting values for l_child.id and p_security_uid, i get numerous results. I am absolutely certain of what the values in these two variables are, and so I'm not using different values executing the query by itself. Can anyone help?
 
Sort by date Sort by votes
NW,

If I were in your position, I would troubleshoot this by first adding in a display of the values for "l_child.id" and "p_security_uid":
Code: 
set serveroutput on format wrap
exec dbms_output.enable(1000000)
declare
...loop
    [b]dbms_output.put_line('l_child_orgs = ['||l_child_orgs||
        ']. p_security_uid = ['||p_security_uid||']');[/b]
    for l_child_orgs in (select...
I know that you are "absolutely certain of what the values in these two variables are", but the above will confirm what is happening chronologically. Plus, with the confirmation, you can again manually seed the values in your non-PL/SQL query to confirm results.

Please post your findings here.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Yes, this is how i'm sure of what those values are.

I've tried opening the cursor in a number of ways, and it always fails to return any rows. Below is the current code that I'm using to open the cursor in the inner loop:

Code: 
open new_child_orgs(l_child.id);
dbms_output.put_line('child id ' || l_child.id || ', ' || p_security_uid || ' yields ' || new_child_orgs%rowcount || ' new associations.');

it outputs

Code: 
child id 227, 1 yields 0 new associations.
child id 224, 1 yields 0 new associations.
child id 226, 1 yields 0 new associations.
child id 225, 1 yields 0 new associations.
child id 217, 1 yields 0 new associations.
child id 220, 1 yields 0 new associations.
child id 223, 1 yields 0 new associations.

And like i said, substituting the numbers for the variables and executing the sql returns at least three rows for every id listed. Thanks for the prompt advice!
 
Upvote 0 Downvote
Well, then the other possible explanation for the (mis-)behaviour is that you are inadvertently accessing different tables. Let me know what happens when you explicitly qualify all the tables with their owner name: "...<owner>.dropdown dd...".

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
unfortunately i get the same results when using the schema prefix.
 
Upvote 0 Downvote
NWDurcholz said:
i get the same results when using the schema prefix.
Click to expand...
And you used the prefix on both the SQL-only and the embedded-in-PL/SQL solution?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Sem,

It's always nice to have your wisdom settle gently upon an obscure technical problem, causing the obscurity to disappear like an early-morning fog lifting from a dewy meadow. I just wish there were not a 9-hour time difference between us so that I wouldn't have to wait so long for your contributions on threads. [smile]

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
yes, well thank you for all the lovely banter, but the rowcount statement was just something i threw in there for demonstration. I didn't consider the 15 lines in between opening the cursor and the put_line relevant. The cursor doesn't return any rows even when I check the rowcount after fetching; The fact that nothing in the inner loop executed told me that before i checked the rowcount. I am _absolutely_ sure of what tables I am accessing, what the parameter values are, how many rows should be returned, and of how many are being returned. These were the first things I checked.

anyway, i found another way to skin this cat. what i was after is concatenating all of the values in one column into a comma separated list, and other developers here helped me to write a function that will do that in parallel with the query processing.
 
Upvote 0 Downvote
Actually you may also consider inline cursor - relatively new Oracle feature.
Code: 
SELECT l_child.*,
       CURSOR (SELECT dd.id
                 FROM dropdown dd,
                      dropdown_org org,
                      security_t
                WHERE org.show_Orgs = dd.contents
                  AND dd.category = 'Orgs'
                  AND org.dropdown_id = l_child.id
                  AND security_t.grpname = 'Orgs'
                  AND security_t.userid =
                      Nvl(p_security_uid, 0)
                  AND security_t.showname = dd.contents)
  FROM dropdown l_child
 WHERE (p_category = 'Action' AND category = 'Reason' AND
       TYPE = l_orig_contents)
    OR (p_category = 'Action' AND category = 'Comment' AND
       type2 = l_orig_contents)
    OR (p_category = 'Reason' AND category = 'Comment' AND
       TYPE = l_orig_contents)

More possible reasons:
1. p_security_uid exists in one of tables thus is treated as table column and not a parameter.
2. one or more of tables are in fact views or has security policy established.


Regards, Dima
 
Upvote 0 Downvote
So, NW, since your buds have helped you create the function, do you have any other questions (that hopefully we can be more helpful with than your last question)?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Dave, now you see that all your compliments were in vain :)

I wouldn't like to give up, as suspect the correct answer is quite close but is too simple to be found at once.

Regards, Dima
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheLazyPig
  • Question
Button not aligned after click
Replies
0
Views
191
TheLazyPig
TheLazyPig
George L
  • Locked
  • Question
web app not saving &quot;all&quot; records suddenly
Replies
3
Views
800
carp
carp
Rasheed Shaik
  • Locked
  • Question
\Orant\DBS\FMCUS.MSG not found
Replies
0
Views
1K
Rasheed Shaik
Rasheed Shaik
TheLazyPig
  • Locked
  • Question
Separate 1 row value to multiple rows
Replies
4
Views
785
carp
carp
LarrySteele
  • Locked
  • Question
Trying to Transpose Columns to Rows w/ Field Names 1
Replies
9
Views
595
carp
carp

Part and Inventory Search

Sponsor

Back
Top