Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, Subramanian.

Asked: February 26, 2018 - 9:40 pm UTC

Last updated: February 28, 2018 - 2:03 am UTC

Version: 12cR1

Viewed 1000+ times

You Asked

Hi Tom,
I have defined a cursor using two views in its query like below.

Cursor pmt_cur(CV_txn_id in number) is SELECT sum(Amount),tgt_group_cd from payments_view,transactions_view where payments.tcd_id=pmt_tgt_id and txn_id=CV_txn_Id;

I have parameter set at the system based on which the selection from the view has to be changed.Assume,if the parameter is set to Y,the above query should be used and if its set to N,lnstead of using payments_view and transactions_view,the cursor should use pmt_vw and txns_vw which has the columns mentioned in the original cursor.

How can I achieve this and would be greatful if you can provide an example of similar scenario with multiple dynamic views involving Cursor parameter.

and Connor said...

Reff cursors can do this. Here's an example using SQL Plus as the calling environment, but it can be done totally inside PLSQL as well

SQL> create or replace
  2  function dyn_cur(p_type int) return sys_refcursor is
  3    rc sys_Refcursor;
  4  begin
  5    if p_type = 1 then
  6      open rc for select * from scott.emp;
  7    else
  8      open rc for select * from scott.dept;
  9    end if;
 10    return rc;
 11  end;
 12  /

Function created.

SQL>
SQL> variable r refcursor
SQL> exec :r := dyn_cur(1);

PL/SQL procedure successfully completed.

SQL> print r

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> exec :r := dyn_cur(2);

PL/SQL procedure successfully completed.

SQL> print r

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SQL>
SQL>
SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

parameterized views

Racer I., February 27, 2018 - 7:21 am UTC

Hi,

Another long standing wish for Oracle to introduce parameterized views. Search for that to find the various ways for :

I have parameter set at the system...

One option (replace GetMode with your technique) :

SELECT sum(Amount), tgt_group_cd
from (
  SELECT Amount, tgt_group_cd, tv.txn_id
  from payments_view pv,transactions_view tv
  where pv.tcd_id=tv.pmt_tgt_id
  and GetMode = 1
  union all
  SELECT Amount,tgt_group_cd, tv.txn_id
  from pmt_vw pv, txns_vw tv
  where pv.tcd_id=tv.pmt_tgt_id
  and GetMode = 0
  )
where txn_id=CV_txn_Id  
group by tgt_group_cd

may require some tuning effort.

regards,

February 28, 2018 - 2:03 am UTC

(For the OP)

Search this site for sys_context for examples of how you achieve this.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

parameterized views

More to Explore

PL/SQL demos

PL/SQL docs

Dynamic Views in cursor

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

parameterized views

More to Explore

PL/SQL demos

PL/SQL docs