Oracle Stored Function Defining Cursor Based on Parameter

Question

I want a stored function like so:

create function myFunction (someSwitch boolean)
return ...
is
  cursor myCursor (params) is
    [BIG QUERY];
...

Where the BIG QUERY can have an extra WHERE clause if someSwitch is true.

Can this be done within the cursor definition and, if so, how? Otherwise, what would be my options: I'm thinking that I could either create two cursors (one with the extra clause, the other without) and then do the logic in the function body. I'm not very satisfied by this approach as the query is very big and I would want to avoid repeating code. Alternatively, I could create the cursor SQL dynamically; but this would incur a performance degradation, right?

Thanks :)

I thought so :( Never mind: It turns out that my cursor can't access out-of-schema objects, anyway, so it's dead in the water before it starts!... Thanks for your help :) — Xophmeister
– Xophmeister, Commented Jan 13, 2012 at 9:57

Florin Ghita · Accepted Answer · 2012-01-13 09:57:48Z

4

You can add the where in this way:

where (some_condition or the_other_conditions)

AFAIK oracle will do shortcut OR. That is: if some_condition is true then the_ohter_condition will not be evaluated(and, anyway, this is not important).

answered Jan 13, 2012 at 9:57

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Heller Over a year ago

PL/SQL will use the typical short-circuit evaluation, but in Oracle SQL "Left to right evaluation is not guaranteed for multiple conditions connected using OR" - docs.oracle.com/cd/E11882_01/server.112/e26088/…

Collectives™ on Stack Overflow

Oracle Stored Function Defining Cursor Based on Parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related