CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
pmreis
pmreis

Reputation: 100

I can use a timestamp variable on query of cursor in a store procedure?

I need a timestamp variable that whill change dinamicaly, but when i try pass this timestamp variable to statement , this doesn't work:

without a timestamp variable ,it works:

CREATE OR REPLACE
PROCEDURE "PR_TEST"( sl_cursor OUT SYS_REFCURSOR)  
IS
    stm VARCHAR2(3000);
    var_ativo number:= 1 ;
BEGIN
    stm := 'SELECT
               *
        FROM
            SIMET.TB_1 SC
            JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
        WHERE
             SC.IS_ACTIVE = ' || var_ativo ;            
     OPEN sl_cursor FOR  stm ;
END;

but with the timestamp var , it doesnt work:

CREATE OR REPLACE
PROCEDURE "PR_TEST"( sl_cursor OUT SYS_REFCURSOR)  
IS
    stm VARCHAR2(3000);
    var_ativo number:= 1 ;
    var_ts timestamp := SYSTIMESTAMP
BEGIN
    stm := 'SELECT
               *
        FROM
            SIMET.TB_1 SC
            JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
        WHERE
             SC.IS_ACTIVE = ' || var_ativo 
         AND 
             sc.timestamp = ' || var_ts

     OPEN sl_cursor FOR  stm ;
END;

Someone knows how i could do it?

Upvotes: 0

Views: 1331

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

The immediate problem is that you aren't concatenating your string properly; you need to have the AND as part of the literal too. And you're missing a semicolon.

stm := 'SELECT
           *
    FROM
        SIMET.TB_1 SC
        JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
    WHERE
         SC.IS_ACTIVE = ' || var_ativo || '
     AND 
         sc.timestamp = ' || var_ts;

But now you are implicitly converting your v_ts variable to a string. As a string it would need to be enclosed in escaped single quotes:

         sc.timestamp = ''' || var_ts || '''';

But that involves more implicit conversion to compare with the column values so really you'd want to explicitly convert it both ways. Which, as you might imagine, is a bit of a red-flag that you're doing something wrong.

If you are using dynamic SQL then you should use bind variables:

stm := 'SELECT
           *
    FROM
        SIMET.TB_1 SC
        JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
    WHERE
         SC.IS_ACTIVE = :ativo
     AND 
         sc.timestamp = :ts';

 OPEN sl_cursor FOR stm USING var_ativo, var_ts;

But from what you've shown you should not be using dynamic SQL here at all:

 OPEN sl_cursor FOR
    SELECT
           *
    FROM
        SIMET.TB_1 SC
        JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
    WHERE
         SC.IS_ACTIVE = var_ativo 
     AND 
         sc.timestamp = var_ts;

To quote Tom Kyte: "You use dynamic sql only when there is quite simply NO WAY to do it statically."

Your premise that splitting whatever you're doing into smaller queries and updates is probably incorrect, and you're likely to be making it slower overall; but that's a separate issue and something that would need a lot more information to address.

Upvotes: 1

Related Questions