PLSQL - Parameter cursor with different return

Question

I know the title is saying nothing... but the argument is a little "complex" to explain in a single row.

All the code i'm writing is just an example, my current code is from other table etc, but the behaviour is the same.

i have defined a cursor like this:

CURSOR emp_cur (l_type)
IS
    with emp_general AS (select *
                           from emp
                          where type = l_type),
         emp_active AS (select *
                          from emp_geral
                         where status = ACTIVE_STATUS),
        emp_inactive AS (select *
                           from emp_general
                          where status = INACTIVE_STATUS)
    select distinct name, department
      from emp_active
     minus
    select distinct name, department
      from emp_inactive;

This cursor take a parameter for filter emp type and make a minus to fetch ACTIVE - INACTIVE emp.

This cursor return name and department.

Now have to declare different cursor with different "select" statemant, for example:

select location
  from emp_active
select location
  from emp_active

I would like to dont duplicate my cursor just to change the select. There is a way to do this and avoid code duplication (withuout using DynamicSQL - Difficult to debug in production enviroment)?

I could think of using GTT.(Global temporary table). See my answer. — Lalit Kumar B
– Lalit Kumar B, Commented Mar 16, 2015 at 12:20
Are you saying that you'd like to use the same cursor to query employees either by type or by location ? — user272735
– user272735, Commented Mar 16, 2015 at 17:06
Are you also aware that your SQL is unnecessary complex can be greatly simplified ? I'm not sure if that applies to your real query though. — user272735
– user272735, Commented Mar 16, 2015 at 17:10

Lalit Kumar B · Accepted Answer · 2015-03-17 04:57:42Z

1

You could create two Global temporary tables explicitly once(not on the fly):

emp_active_gtt
emp_inactive_gtt

Such that, each temp table will have the entire result set of active and inactive records respectively.

For example, in the code you would do:

insert into emp_active_gtt 
select *
from ....
where status ='ACTIVE'

Similarly, for inactive records:

insert into emp_inactive_gtt 
select *
from ....
where status ='INACTIVE'

You could now use the two tables in the scope of the session anywhere to get the required rows.

Read more about GTT in the documentation here https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2153132

edited Mar 17, 2015 at 4:57

answered Mar 16, 2015 at 12:20

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

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

2 Comments

Mistre83 Over a year ago

This could be a solution (i have never used GTT). If one day i have to alter the emp table, i need to drop the GGT associated and next re-create them ?. I have to create the two GTT i made the insert on them and next (with two function) i do different select for the behaviour i want. I have well understand ?.ps: the default of GGT is that when the transaction is finished (commit) the data on it will be deleted right? Two different transaction have the GGT empty.

Lalit Kumar B Over a year ago

Depending on your requirement, you could create the GTT as on commit preserve rows or delete rows. And, you don't have to drop the GTT, it is nothing like associated to any table. If you are specifically using the GTT for a particular process, for example in your case EMP table manipulation, all you need to do is, whenever you later you EMP table, you just need to alter the GTT as well. It would work as any other table.

Collectives™ on Stack Overflow

PLSQL - Parameter cursor with different return

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related