0

I have the below code with 2 cursors. Both are time consuming ones even after required tuning. So, my idea is to increase performace of the function by invoking the cursor SQL only on need basis based on some known condition.

Existing code :

Function MyFunction(Param1 IN DATE) RETURN BOOLEAN

CURSOR C1 IS <MyTimeConsumingQuery1>;

CURSOR C2 IS <MyTimeConsumingQuery2>;

IF NVL(KnownCondition,'Y') = 'N' THEN
  OPEN C1;

ELSE
 OPEN C2;

END IF; 

...
END MyFunction

Below is what I am trying to achieve:

Function MyFunction(Param1 IN DATE) RETURN BOOLEAN

IF NVL(KnownCondition,'Y') = 'N' THEN

CURSOR C1 IS <MyTimeConsumingQuery1>;

  OPEN C1;

ELSE

CURSOR C2 IS <MyTimeConsumingQuery2>;

 OPEN C2;

END IF; 

...
END MyFunction

When I try to compile the code, I am getting PLS-00103: Encountered the symbol error.

How can I selectively invoke the cursor query to increase the performace?

user2488578
  • 896
  • 4
  • 21
  • 40
  • 1
    How is the compile error related to your question "How to increase the performance?" – Wernfried Domscheit Jan 16 '18 at 14:15
  • I'm not sure I understand the question at all... surely the execution time of the cursor query is irrelevant until the cursor is opened anyway? It's also hard to tell what you're really doing as you've left the declare/begin/end out of both versions - so can't tell if you're using a sub-block for the cursor declaration in the second one? Also, is `KnownCondition` something fixed at compile time, or something determined within the function? – Alex Poole Jan 16 '18 at 14:28
  • Increasing the performance is what I am trying to achieve. Getting compilation error is the consequence of what I am trying to achieve. My focus is on increasing the performance. – user2488578 Jan 16 '18 at 14:31
  • @AlexPoole "surely the execution time of the cursor query is irrelevant until the cursor is opened anyway?" - You mean the cursor query will not be "Actually" executed until it is opened? Please throw some light on this as I am new to PL SQL. – user2488578 Jan 16 '18 at 14:33
  • [From the docs](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/OPEN-statement.html): "The OPEN statement opens an explicit cursor, allocates database resources to process the associated query, identifies the result set, and positions the cursor before the first row of the result set." The cursor declaration is parsed at compile time, but isn't executed at runtime until you open it. The query may have parameters, or rely on variables set elsewhere in the function that aren't known until it's opened, for a start. – Alex Poole Jan 16 '18 at 14:36
  • 2
    please investigate first where the performance problem is caused: declaring the cursor, opening it or fetching from it. Then approach only the part that is relevant. – Marmite Bomber Jan 16 '18 at 14:48
  • Defining a static cursor doesn't open it, just as defining a procedure doesn't execute it. Maybe you should revisit the profiler report that told you defining cursors took time. – William Robertson Jan 16 '18 at 16:24

1 Answers1

0

There are many ways to do this. Just for example

Function MyFunction(Param1 IN DATE) RETURN BOOLEAN is
begin
  IF NVL(KnownCondition,'Y') = 'N' THEN
    declare  
      CURSOR C1 IS <MyTimeConsumingQuery1>;
    begin
      OPEN C1;
      ...
    end;
ELSE
    declare  
      CURSOR C2 IS <MyTimeConsumingQuery2>;
    begin
      OPEN C2;
      ...
    end;
END IF; 

...
END MyFunction
Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28