Forum Stats

  • 3,727,639 Users
  • 2,245,430 Discussions
  • 7,852,905 Comments

Discussions

How to use AS OF TIMESTAMP Clause Parameter as Variable instead of TIMESTAMP Literal?

User_M80CR
User_M80CR Member Posts: 28 Green Ribbon

Hello Support,

In the attached PL/SQL File [ZIP format] I have created the AS OF TIMESTAMP Clause Demo using Parameter instead of TIMESTAMP Literal from the following demo provided by Jonathan Gennick at the following web-site

But I get the following error during PL/SQL compile process as shown below:

Can you please let me know how to provide parameter instead of TIMESTAMP Literal within AS OF TIMESTAMP Clause?


Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,263 Black Diamond
    edited February 22 Accepted Answer

    Congratulations. You just found a bug in PL/SQL + ANSI join syntax. I narrowed it down. ANSI join syntax:

    DECLARE
      Snapshot_Instant_Test TIMESTAMP;
    -- ***********************************************************************
    -- ***********************************************************************
     PROCEDURE Test_Get_Results_Snapshot( Time_Instant_Param IN TIMESTAMP, Time_Test_Param IN TIMESTAMP )
    IS
    BEGIN
     FOR loop_aa IN
     (
      SELECT tr.patient_id, tr.test_time, tr.test_result,
         MAX(tr.test_time) OVER
          (PARTITION BY tr.patient_id) AS test_max_time
      FROM
       test_results AS OF TIMESTAMP Time_Instant_Param tr
    INNER JOIN
       test_names AS OF TIMESTAMP TIMESTAMP '2021-02-22 15:39:10' tn
       ON tr.test_code = tn.test_code
     )
     LOOP
      dbms_output.put_line(loop_aa.patient_id);
     END LOOP loop_aa;
    END Test_Get_Results_Snapshot;
    -- ***********************************************************************
    -- Anonymous PL/SQL Block
    -- ***********************************************************************
    BEGIN
     Snapshot_Instant_Test := TO_TIMESTAMP('2021-02-22 13:39:10', 'YYYY-MM-DD HH24:MI:SS');
     Test_Get_Results_Snapshot(Time_Instant_Param => Snapshot_Instant_Test, Time_Test_Param => TIMESTAMP '2008-02-03 08:00:00');
    END;
    /
      SELECT tr.patient_id, tr.test_time, tr.test_result,
      *
    ERROR at line 10:
    ORA-06550: line 10, column 3:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 10, column 3:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 20, column 24:
    PLS-00364: loop index variable 'LOOP_AA' use is invalid
    ORA-06550: line 20, column 3:
    PL/SQL: Statement ignored
    
    SQL>
    
    

    Oracle native join syntax:

    DECLARE
      Snapshot_Instant_Test TIMESTAMP;
    -- ***********************************************************************
    -- ***********************************************************************
     PROCEDURE Test_Get_Results_Snapshot( Time_Instant_Param IN TIMESTAMP, Time_Test_Param IN TIMESTAMP )
    IS
    BEGIN
     FOR loop_aa IN
     (
      SELECT tr.patient_id, tr.test_time, tr.test_result,
         MAX(tr.test_time) OVER
          (PARTITION BY tr.patient_id) AS test_max_time
      FROM
       test_results AS OF TIMESTAMP Time_Instant_Param tr,
       test_names AS OF TIMESTAMP TIMESTAMP '2021-02-22 15:39:10' tn
       WHERE tr.test_code = tn.test_code
     )
     LOOP
      dbms_output.put_line(loop_aa.patient_id);
     END LOOP loop_aa;
    END Test_Get_Results_Snapshot;
    -- ***********************************************************************
    -- Anonymous PL/SQL Block
    -- ***********************************************************************
    BEGIN
     Snapshot_Instant_Test := TO_TIMESTAMP('2021-02-22 15:39:10', 'YYYY-MM-DD HH24:MI:SS');
     Test_Get_Results_Snapshot(Time_Instant_Param => Snapshot_Instant_Test, Time_Test_Param => TIMESTAMP '2008-02-03 08:00:00');
    END;
    /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    SY.

    User_M80CR
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,578 Gold Crown
    Accepted Answer

    There's a fairly simple workaround to the problem if you want to stick with "ANSI" syntax, just embed the table names in an inline view with the "as of" clause in the inline view: https://jonathanlewis.wordpress.com/2020/01/27/ansi-flashback/


    Surprisingly this is a bug that's been around for at least 7 years and still hasn't been fixed. I can't find a bug number for it, though.


    Regards

    Jonathan Lewis

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,296 Red Diamond

    Hi, @User_M80CR

    In the attached PL/SQL File ...

    Post your code right in this site. Not everyone who wants to help you can or will open attachments.

  • User_M80CR
    User_M80CR Member Posts: 28 Green Ribbon

    Hello Support,

    Posted the code as shown below [Time_Instant_Param is the parameter I want to use] :

    set serveroutput on size 30000;

    SET DEFINE OFF

    DECLARE

      Snapshot_Instant_Test TIMESTAMP;

    -- ***********************************************************************

    -- ***********************************************************************

     PROCEDURE Test_Get_Results_Snapshot( Time_Instant_Param IN TIMESTAMP, Time_Test_Param IN TIMESTAMP )

    IS

    BEGIN  

     FOR loop_aa IN

     (

    SELECT archive.patient_id, archive.test_time, archive.test_name, archive.test_result

    FROM (

      SELECT tr.patient_id, tr.test_time, tn.test_name, tr.test_result,

         MAX(tr.test_time) OVER 

          (PARTITION BY tr.patient_id) AS test_max_time

      FROM 

       (test_results AS OF TIMESTAMP (Time_Instant_Param)) tr  -- TO_TIMESTAMP('2021-02-19 13:36:10', 'YYYY-MM-DD HH24:MI:SS' )

       INNER JOIN 

       test_names AS OF TIMESTAMP (TIMESTAMP '2021-02-19 13:36:10') tn

       ON tr.test_code = tn.test_code

      WHERE tr.test_time <= Time_Test_Param

      ) archive

    WHERE archive.patient_id = 101

     AND archive.test_time = archive.test_max_time

     )

     LOOP

      dbms_output.put_line(loop_aa.patient_id);

     END LOOP loop_aa;

    END Test_Get_Results_Snapshot;

    -- ***********************************************************************

    -- Anonymous PL/SQL Block

    -- ***********************************************************************

    BEGIN

     Snapshot_Instant_Test := TO_TIMESTAMP('2021-02-19 13:36:10', 'YYYY-MM-DD HH24:MI:SS');  

     Test_Get_Results_Snapshot(Time_Instant_Param => Snapshot_Instant_Test, Time_Test_Param => TIMESTAMP '2008-02-03 08:00:00');

    END;

    /

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,263 Black Diamond
    edited February 22

    I bet table TEST_RESULTS has column Time_Instant_Param. In SQL ststement column name takes precedence over variable name. Develop a habit of prefixing PL/SQL variables/parameters. E.g. P_PARAMETER_NAME, V_VARIABLE_NAME.

    SY.

  • User_M80CR
    User_M80CR Member Posts: 28 Green Ribbon

    Hello Solomon,

    Following are the DDL statements for the respective TABLE :

    -- *******************************

    CREATE TABLE test_names (

    test_code VARCHAR2(4 CHAR),

    test_name VARCHAR2(30 CHAR),

    CONSTRAINT test_name_pk PRIMARY KEY (test_code),

    CONSTRAINT test_code_non_null_check CHECK(test_code IS NOT NULL), -- Overkill as it is Primary Key?

    CONSTRAINT test_name_non_null_check CHECK(test_name IS NOT NULL),

    CONSTRAINT test_names_trim_TEST_CODE check (

       regexp_like (TEST_CODE, '^[^[:space:]](.*[^[:space:]])?$')),

    CONSTRAINT test_names_trim_TEST_NAME check (

       regexp_like (TEST_NAME, '^[^[:space:]](.*[^[:space:]])?$'))

    ) TABLESPACE MSR_DATA;

    -- *******************************

    CREATE TABLE test_results (

    patient_id NUMBER,

    test_time TIMESTAMP,

    test_code VARCHAR2(4 CHAR),

    test_result VARCHAR2(10 CHAR),

    CONSTRAINT test_results_pk PRIMARY KEY (patient_id, test_time, test_code),

    CONSTRAINT test_code_fk FOREIGN KEY (test_code) REFERENCES test_names (test_code),

    CONSTRAINT test_results_trim_TEST_RESULT check (

       regexp_like (TEST_RESULT, '^[^[:space:]](.*[^[:space:]])?$'))

    ) TABLESPACE MSR_DATA;

    -- *******************************

    I don't see any name collision here as you mentioned.

    Best Regards

  • mathguy
    mathguy Member Posts: 9,700 Gold Crown

    Hello Support?

    Were you trying to reach the Oracle support team? Wrong site!

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,263 Black Diamond
    edited February 22 Accepted Answer

    Congratulations. You just found a bug in PL/SQL + ANSI join syntax. I narrowed it down. ANSI join syntax:

    DECLARE
      Snapshot_Instant_Test TIMESTAMP;
    -- ***********************************************************************
    -- ***********************************************************************
     PROCEDURE Test_Get_Results_Snapshot( Time_Instant_Param IN TIMESTAMP, Time_Test_Param IN TIMESTAMP )
    IS
    BEGIN
     FOR loop_aa IN
     (
      SELECT tr.patient_id, tr.test_time, tr.test_result,
         MAX(tr.test_time) OVER
          (PARTITION BY tr.patient_id) AS test_max_time
      FROM
       test_results AS OF TIMESTAMP Time_Instant_Param tr
    INNER JOIN
       test_names AS OF TIMESTAMP TIMESTAMP '2021-02-22 15:39:10' tn
       ON tr.test_code = tn.test_code
     )
     LOOP
      dbms_output.put_line(loop_aa.patient_id);
     END LOOP loop_aa;
    END Test_Get_Results_Snapshot;
    -- ***********************************************************************
    -- Anonymous PL/SQL Block
    -- ***********************************************************************
    BEGIN
     Snapshot_Instant_Test := TO_TIMESTAMP('2021-02-22 13:39:10', 'YYYY-MM-DD HH24:MI:SS');
     Test_Get_Results_Snapshot(Time_Instant_Param => Snapshot_Instant_Test, Time_Test_Param => TIMESTAMP '2008-02-03 08:00:00');
    END;
    /
      SELECT tr.patient_id, tr.test_time, tr.test_result,
      *
    ERROR at line 10:
    ORA-06550: line 10, column 3:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 10, column 3:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 20, column 24:
    PLS-00364: loop index variable 'LOOP_AA' use is invalid
    ORA-06550: line 20, column 3:
    PL/SQL: Statement ignored
    
    SQL>
    
    

    Oracle native join syntax:

    DECLARE
      Snapshot_Instant_Test TIMESTAMP;
    -- ***********************************************************************
    -- ***********************************************************************
     PROCEDURE Test_Get_Results_Snapshot( Time_Instant_Param IN TIMESTAMP, Time_Test_Param IN TIMESTAMP )
    IS
    BEGIN
     FOR loop_aa IN
     (
      SELECT tr.patient_id, tr.test_time, tr.test_result,
         MAX(tr.test_time) OVER
          (PARTITION BY tr.patient_id) AS test_max_time
      FROM
       test_results AS OF TIMESTAMP Time_Instant_Param tr,
       test_names AS OF TIMESTAMP TIMESTAMP '2021-02-22 15:39:10' tn
       WHERE tr.test_code = tn.test_code
     )
     LOOP
      dbms_output.put_line(loop_aa.patient_id);
     END LOOP loop_aa;
    END Test_Get_Results_Snapshot;
    -- ***********************************************************************
    -- Anonymous PL/SQL Block
    -- ***********************************************************************
    BEGIN
     Snapshot_Instant_Test := TO_TIMESTAMP('2021-02-22 15:39:10', 'YYYY-MM-DD HH24:MI:SS');
     Test_Get_Results_Snapshot(Time_Instant_Param => Snapshot_Instant_Test, Time_Test_Param => TIMESTAMP '2008-02-03 08:00:00');
    END;
    /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    SY.

    User_M80CR
  • User_M80CR
    User_M80CR Member Posts: 28 Green Ribbon

    Hello Solomon,

    Thank you for the help.

    I know to debug this up to the root, you removed the WHERE clause.

    Can you please modify my original query with the Oracle Native Join Syntax post it here after testing it runs successfully in your server?

    That will help me a lot as two successive WHERE clause is not compiling in PL/SQL.

    Your help is appreciated.

    Best Regards

  • User_M80CR
    User_M80CR Member Posts: 28 Green Ribbon

    Hello Solomon,

    I used "AND" instead of "WHERE" for the original SQL statement within your non-ANSI Native JOIN syntax.

    It works as expected.

    Thank you for your debug analysis effort.

    Best Regards

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,578 Gold Crown
    Accepted Answer

    There's a fairly simple workaround to the problem if you want to stick with "ANSI" syntax, just embed the table names in an inline view with the "as of" clause in the inline view: https://jonathanlewis.wordpress.com/2020/01/27/ansi-flashback/


    Surprisingly this is a bug that's been around for at least 7 years and still hasn't been fixed. I can't find a bug number for it, though.


    Regards

    Jonathan Lewis

  • Daniel Hurmuz-Oracle
    Daniel Hurmuz-Oracle Member Posts: 2 Employee

    Hi all,

    I've opened the below new BUG:

    BUG 32568211 - ORA-00984 WHEN USING PL/SQL WITH ANSI JOIN SYNTAX

    Seems similar to BUG 10373334 - ORA-984 ON JOIN .. AS OF SCN

    Regards,

    Daniel

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,578 Gold Crown

    @Daniel Hurmuz-Oracle

    Thanks for the follow-up.


    Unfortunately neither of those bugs seems to be visible when I search MOS.


    Regards

    Jonathan Lewis

Sign In or Register to comment.