Translate code from SQL SERVER using temporary table to Oracle PLSQL using Cursor

Question

How could I transform a temporary table to make an update in SQL server to an equivalent code in PLSQL with a cursor?

ALTER proc [dbo].[sp_lista] (@pcFI datetime, @pcFF datetime, @vSuma tinyint) as
    begin tran

    Declare @crLista Table (
        NEnt int,
        NEje int,
        NExp char(17),
        NAcu char(17),
        NSen char(17)
     )

     if @vSuma = 0
         begin
             insert into @crLista 
             select tablax.num_entrada, tablax.ejercicio,                                               tablax.num_expediente, tablax.num_acuerdo, tablax.num_sentencia,    tablax.descripcion
             from   tablax inner join tablaz on
                    tablaz.num_expediente = tablax.num_expediente
             where  tablax.est_suma = 0 and (EXISTS
                    ( SELECT 1
                      FROM   tablay
                      WHERE  tablay.cve_usuario = SESSION_USER
                      and    tablay.cve_region = tablaz.cve_region
                      and    tablay.cve_sala = tablaz.cve_sala
                      and    tablay.cve_mag = tablaz.cve_mag
                      and    tablay.cve_srio = tablaz.cve_srio) )

         update tablax 
                set notificado = 1,
                fec_notificado = getdate()
         from   @crLista MLista
         where  MLista.NEnt = tablax.num_entrada and
                MLista.NEje = tablax.ejercicio and tablax.est_suma = @vSuma

In PL/SQL I have something like the following:

create or replace PROCEDURE sp_lista(
    v_pcFI IN DATE,
    v_pcFF   IN DATE,
    v_vSuma  IN NUMBER
)
AS
    vcMLista SYS_REFCURSOR;
BEGIN
    IF v_vSuma = 0 THEN
        BEGIN
            open vcMLista for
                SELECT tablax.num_entrada ,
                       tablax.ejercicio ,
                       tablax.num_expediente ,
                       tablax.num_acuerdo ,
                       tablax.num_sentencia
                FROM   tablax
                       JOIN tablaz
                       ON   tablaz.num_expediente = tablax.num_expediente
                WHERE  tablax.est_suma = 0
                AND    ( EXISTS
                         ( SELECT 1
                           FROM   tablay
                           WHERE  tablay.cve_usuario  = v_vcUsr_actual
                           AND    tablay.cve_region   =  tablaz.cve_region
                           AND    tablay.cve_sala     = tablaz.cve_sala
                           AND    tablay.cve_mag      = tablaz.cve_mag
                           AND    tablay.cve_srio     = tablaz.cve_srio
                         ) )
                FOR UPDATE;

Subsequently in SQL SERVER a temporary table is used and the following is done:

update tablax 
       set notificado = 1,
       fec_notificado = getdate()
from   @crLista MLista
where  MLista.NEnt = tablax.num_entrada and
       MLista.NEje = tablax.ejercicio and tablax.est_suma = @vSuma

update tablay set fmod =getdate()
where  ndoc in 
       ( select distinct NExp from @crLista )

How can I do the PLSQL equivalent with a cursor? I've tried the following:

OPEN crLista;
FETCH crLista INTO reg;

WHILE crLista%FOUND LOOP
    UPDATE actm_lista SET  
           notificado = 1,
           FEC_NOTIFICADO = sysdate
    WHERE  CURRENT OF crLista;

    FETCH crLista INTO reg;
END LOOP;

OPEN crLista;
FETCH crLista INTO reg;

WHILE crLista%FOUND LOOP  
    UPDATE tablax
    SET    fmod = SYSDATE
    WHERE  ne IN
           ( SELECT DISTINCT reg.ne FROM crLista );

    FETCH crLista INTO reg;
END LOOP;    

UPDATE tablay
SET    fmod = SYSDATE
WHERE  ndoc IN
       ( SELECT DISTINCT ndoc FROM crLista );

But when trying to do the "from crLista" I get the error "the table or view does not exist".

First, you don't have a temporary table in SQL Server. You have a variable declared as a table. And then, why do you need to do the same but with a CURSOR? Whats the objective of this? Can't you just use a temporary table in Oracle? — asemprini87, Feb 23 '17 at 19:36

score 0 · Accepted Answer · edited May 23 '17 at 12:00

You can't query a cursor as if it was a table. They are completely different things. All you can do with a cursor is open, fetch, and close.

Since you need to use the results in two places, it might be worth creating a global temporary table. You'll have to define this separately, as the definition is permanent like a normal table - it's just the contents that are private to your session.

You might also look at bulk-fetching the results into a collection and using two forall statements, as in this question:

bulk collect using "for update"

(In that example the only issue was around adding a for update clause, which you can ignore.)

Translate code from SQL SERVER using temporary table to Oracle PLSQL using Cursor

1 Answers1