Forum Stats

  • 3,827,872 Users
  • 2,260,836 Discussions
  • 7,897,401 Comments

Discussions

I need help on lexical parameter in cursor

Asked to Learn
Asked to Learn Member Posts: 173
edited Nov 17, 2013 3:29PM in SQL & PL/SQL

Dear Experts,

Hope you are fine.

I'm using oracle DB 10.1.2 and forms 10.1.2.3. I want to write a cursor where  "few where condition" may exist or not exist.

Like bellow are the main cursor SQL.

SELECT TEMP_DATA
FROM TAB1
WHERE BDATE BETWEEN :B_DATE AND :E_DATE
AND DEPT_ID=:P1
AND DIVM_ID=:P2
ORDER BY BDATE;

Here i want, user may pass the value of  P1,P2 or any one or none. With more information. B_DATE and E_DATE must entered by user and

If user pass the both value of P1 and P2 then the above sql is ok for cursor. but if user don't pass the valu of P1 then cursor sql should like

SELECT TEMP_DATA
FROM TAB1
WHERE BDATE BETWEEN :B_DATE AND :E_DATE
AND DIVM_ID=:P2
ORDER BY BDATE;

How i do this in one cursor ?

Thanks for your time.

Ask2Learn

Tagged:

Best Answer

  • Sudhakar_B
    Sudhakar_B Member Posts: 501 Silver Badge
    Answer ✓

    Hi,

    You could write the SQL as below...

    SELECT TEMP_DATA  
    FROM TAB1  
    WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
    AND (DEPT_ID=:P1 OR :P1 is NULL)
    AND (DIVM_ID=:P2 OR :P2 is NULL)
    ORDER BY BDATE;  
    

    This way if P1 is not entered by user then it will be null (same for P2).

    Hope this helps.

    vr,

    Sudhakar

Answers

  • Sudhakar_B
    Sudhakar_B Member Posts: 501 Silver Badge
    Answer ✓

    Hi,

    You could write the SQL as below...

    SELECT TEMP_DATA  
    FROM TAB1  
    WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
    AND (DEPT_ID=:P1 OR :P1 is NULL)
    AND (DIVM_ID=:P2 OR :P2 is NULL)
    ORDER BY BDATE;  
    

    This way if P1 is not entered by user then it will be null (same for P2).

    Hope this helps.

    vr,

    Sudhakar

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Nov 17, 2013 11:32AM

    Sometimes supervisors don't want to see or among predicates

    SELECT TEMP_DATA 

      FROM TAB1 

    WHERE BDATE BETWEEN :B_DATE AND :E_DATE 

       AND DEPT_ID = NVL(:P1,DEPT_ID)

       AND DIVM_ID = NVL(:P2,DIVM_ID)

    ORDER BY BDATE


    Regards

    Etbin

  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy
    edited Nov 17, 2013 9:48AM
    Etbin wrote:
    
    Sometimes supervisors don't want to see or among predicates
    
    SELECT TEMP_DATA  
      FROM TAB1  
     WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
       AND DEPT_ID = NVL(:P1,DEPT_ID) 
       AND DIVM_ID = NVL(:P2,DIVM_ID)
     ORDER BY BDATE
    
    Regards Etbi

    HI Etbin

    In my own practice I found that

     :P1 is null or column = :P1
    faster  than
     column = nvl(:P1,column)
    
    

    i think  it is faster because short circuit is working ... But I would like to note that it is only my opinion and not documented

    ----

    Ramin Hashimzade

  • Sudhakar_B wrote:
    
    Hi,
    You could write the SQL as below...
    
    
    1. SELECT TEMP_DATA   
    2. FROM TAB1   
    3. WHERE BDATE BETWEEN :B_DATE AND :E_DATE   
    4. AND (DEPT_ID=:P1 OR :P1 is NULL) 
    5. AND (DIVM_ID=:P2 OR :P2 is NULL) 
    6. ORDER BY BDATE;   
    SELECT TEMP_DATA  
    FROM TAB1  
    WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
    AND (DEPT_ID=:P1 OR :P1 is NULL)
    AND (DIVM_ID=:P2 OR :P2 is NULL)
    ORDER BY BDATE;  
    This way if P1 is not entered by user then it will be null (same for P2).
    
    Hope this helps.
    vr,
    Sudhakar
    

    Thanks Sudhakar. It's work like a charm.

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown

    I'm making no claims. It just occurred to me that from time to time I have to deal with stereotypes like this (and the OP might too)

    Regards

    Etbin

  • Sudhakar_B
    Sudhakar_B Member Posts: 501 Silver Badge

    Thanks Etbin, Ramin,

    We are all victims of profiling (I mean performance related) from time to time. One thing I have come to firmly believe, over years is that, Oracle Optimizer for the most part is way more intelligent than me ;-)

    Got my 12c test DB few days back and tried this scenario out.

    In my very limited testing it seems column = nvl(:P1,column) performs better than :P1 is null or column = :P1 (From explain plan and consistent gets perspective)

    Moral of the story is as far as performance in Oracle is concerned, for your specific case test it out o find what works better!!!

    Also,

    With column = nvl(:P1,column) construct Oracle 12c builds and access path using TABLE ACCESS BY INDEX ROWID BATCHED operation. I guess this new in 12c. Need to read more about this.

    SQL> select * from v$version;
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    
    SQL>
    SQL> set autotrace traceonly statistics explain;
    SQL> set timing on;
    SQL> set line 200;
    SQL>
    SQL> var e1 number;
    SQL> -- E1 is null
    SQL> select * from employees
      2  where employee_id = :e1 OR :e1 is null;
    
    
    107 rows selected.
    
    
    Elapsed: 00:00:00.00
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1445457117
    
    
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |     6 |   414 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       1 - filter(:E1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:E1))
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             15  consistent gets
              0  physical reads
              0  redo size
          10121  bytes sent via SQL*Net to client
            621  bytes received via SQL*Net from client
              9  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            107  rows processed
    
    
    SQL>
    SQL> select * from employees
      2  where employee_id  = nvl( :e1 ,employee_id);
    
    
    107 rows selected.
    
    
    Elapsed: 00:00:00.00
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 929977954
    
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |               |   108 |  7452 |     0   (0)| 00:00:01 |
    |   1 |  CONCATENATION                        |               |       |       |            |          |
    |*  2 |   FILTER                              |               |       |       |            |          |
    |   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   107 |  7383 |     0   (0)| 00:00:01 |
    |*  4 |     INDEX FULL SCAN                   | EMP_EMP_ID_PK |   107 |       |     0   (0)| 00:00:01 |
    |*  5 |   FILTER                              |               |       |       |            |          |
    |   6 |    TABLE ACCESS BY INDEX ROWID        | EMPLOYEES     |     1 |    69 |     0   (0)| 00:00:01 |
    |*  7 |     INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       2 - filter(:E1 IS NULL)
       4 - filter("EMPLOYEE_ID" IS NOT NULL)
       5 - filter(:E1 IS NOT NULL)
       7 - access("EMPLOYEE_ID"=:E1)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             19  consistent gets
              0  physical reads
              0  redo size
          10121  bytes sent via SQL*Net to client
            621  bytes received via SQL*Net from client
              9  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            107  rows processed
    
    
    SQL>
    SQL> -- E1 is 10 KNOWN to be not found;
    SQL> begin :e1 := 10; end;
      2  /
    
    
    PL/SQL procedure successfully completed.
    
    
    Elapsed: 00:00:00.00
    SQL>
    SQL> select * from employees
      2  where employee_id = :e1 OR :e1 is null;
    
    
    no rows selected
    
    
    Elapsed: 00:00:00.00
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1445457117
    
    
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |     6 |   414 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       1 - filter(:E1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:E1))
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              7  consistent gets
              0  physical reads
              0  redo size
           1061  bytes sent via SQL*Net to client
            533  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              0  rows processed
    
    
    SQL>
    SQL> select * from employees
      2  where employee_id  = nvl( :e1 ,employee_id);
    
    
    no rows selected
    
    
    Elapsed: 00:00:00.00
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 929977954
    
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |               |   108 |  7452 |     0   (0)| 00:00:01 |
    |   1 |  CONCATENATION                        |               |       |       |            |          |
    |*  2 |   FILTER                              |               |       |       |            |          |
    |   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   107 |  7383 |     0   (0)| 00:00:01 |
    |*  4 |     INDEX FULL SCAN                   | EMP_EMP_ID_PK |   107 |       |     0   (0)| 00:00:01 |
    |*  5 |   FILTER                              |               |       |       |            |          |
    |   6 |    TABLE ACCESS BY INDEX ROWID        | EMPLOYEES     |     1 |    69 |     0   (0)| 00:00:01 |
    |*  7 |     INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       2 - filter(:E1 IS NULL)
       4 - filter("EMPLOYEE_ID" IS NOT NULL)
       5 - filter(:E1 IS NOT NULL)
       7 - access("EMPLOYEE_ID"=:E1)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              1  consistent gets
              0  physical reads
              0  redo size
           1061  bytes sent via SQL*Net to client
            533  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              0  rows processed
    
    
    SQL>
    SQL> -- E1 is 107 KNOWN to be found;
    SQL> begin :e1 := 107; end;
      2  /
    
    
    PL/SQL procedure successfully completed.
    
    
    Elapsed: 00:00:00.00
    SQL>
    SQL> select * from employees
      2  where employee_id = :e1 OR :e1 is null;
    
    
    Elapsed: 00:00:00.00
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1445457117
    
    
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |     6 |   414 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       1 - filter(:E1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:E1))
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              8  consistent gets
              0  physical reads
              0  redo size
           1323  bytes sent via SQL*Net to client
            544  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    
    SQL>
    SQL> select * from employees
      2  where employee_id  = nvl( :e1 ,employee_id);
    
    
    Elapsed: 00:00:00.00
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 929977954
    
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |               |   108 |  7452 |     0   (0)| 00:00:01 |
    |   1 |  CONCATENATION                        |               |       |       |            |          |
    |*  2 |   FILTER                              |               |       |       |            |          |
    |   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   107 |  7383 |     0   (0)| 00:00:01 |
    |*  4 |     INDEX FULL SCAN                   | EMP_EMP_ID_PK |   107 |       |     0   (0)| 00:00:01 |
    |*  5 |   FILTER                              |               |       |       |            |          |
    |   6 |    TABLE ACCESS BY INDEX ROWID        | EMPLOYEES     |     1 |    69 |     0   (0)| 00:00:01 |
    |*  7 |     INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       2 - filter(:E1 IS NULL)
       4 - filter("EMPLOYEE_ID" IS NOT NULL)
       5 - filter(:E1 IS NOT NULL)
       7 - access("EMPLOYEE_ID"=:E1)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              2  consistent gets
              0  physical reads
              0  redo size
           1323  bytes sent via SQL*Net to client
            544  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    
    SQL>
    

    vr,

    Sudhakar

This discussion has been closed.