Forum Stats

  • 3,826,778 Users
  • 2,260,707 Discussions
  • 7,897,072 Comments

Discussions

Parameterized Views

1235

Comments

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    Maybe a helpful intermediate step would be to enable the usage of (parameterized) PL/SQL cursors as SQL query_table_expressions ?

    create or replace package pkg is

    cursor c1 (...

    end;

    select ... from pkg.c1(...)

    Mmm, that doesn't work dude.

    CREATE OR REPLACE PACKAGE MARTIN_TEST AS

      CURSOR C1 (p_Object_Name VARCHAR2) IS

        SELECT *

        FROM   USER_OBJECTS

        WHERE  OBJECT_NAME = (p_Object_Name);

    END MARTIN_TEST;

    /

    SELECT *

    FROM   MARTIN_TEST.C1('DUAL')

    /

    ORA-00933: SQL command not properly ended

    00933. 00000 -  "SQL command not properly ended"

    *Cause:   

    *Action:

    Error at Line: 11 Column: 22

    You have to OPEN the cursor like this:

    DECLARE

      C1_REC USER_OBJECTS%ROWTYPE;

      --

    BEGIN

      OPEN  MARTIN_TEST.C1('DUAL');

      FETCH MARTIN_TEST.C1 INTO C1_REC;

      --

      <Do Processing Here>

      --

      CLOSE MARTIN_TEST.C1;

    END;

    /

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 63 Bronze Badge
    edited Jun 25, 2019 8:56AM

    Mmm, that doesn't work dude.

    CREATE OR REPLACE PACKAGE MARTIN_TEST AS

      CURSOR C1 (p_Object_Name VARCHAR2) IS

        SELECT *

        FROM   USER_OBJECTS

        WHERE  OBJECT_NAME = (p_Object_Name);

    END MARTIN_TEST;

    /

    SELECT *

    FROM   MARTIN_TEST.C1('DUAL')

    /

    ORA-00933: SQL command not properly ended

    00933. 00000 -  "SQL command not properly ended"

    *Cause:   

    *Action:

    Error at Line: 11 Column: 22

    You have to OPEN the cursor like this:

    DECLARE

      C1_REC USER_OBJECTS%ROWTYPE;

      --

    BEGIN

      OPEN  MARTIN_TEST.C1('DUAL');

      FETCH MARTIN_TEST.C1 INTO C1_REC;

      --

      <Do Processing Here>

      --

      CLOSE MARTIN_TEST.C1;

    END;

    /

    Yeah, was meant as a supplemental idea, not a yet available alternative, thanks for clarification.

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    Maybe a helpful intermediate step would be to enable the usage of (parameterized) PL/SQL cursors as SQL query_table_expressions ?

    create or replace package pkg is

    cursor c1 (...

    end;

    select ... from pkg.c1(...)

    Rainer Stenzel wrote:Maybe a helpful intermediate step would be to enable the usage of (parameterized) PL/SQL cursors as SQL query_table_expressions ?create or replace package pkg iscursor c1 (...end;select ... from pkg.c1(...)

    Most likely, you would need to wrap the parameterized PL/SQL cursor with TABLE().

    eg

    select * from table( pkg.c1( var1,var2) )

    With that, anything that provides a collection  would follow the same "wrap with TABLE()" syntax

    • Pipelined table function
    • SQL Collections
    • PL/SQL Collections
    • PL/SQL Cursors (the proposed alternative idea)

    MK

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    SELECT *

    FROM   TABLE(MARTIN_TEST.C1('DUAL'))

    /

    ORA-06553: PLS-707: unsupported construct or internal error [2603]

    06553. 00000 -  "PLS-%s: %s"

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    SELECT *

    FROM   TABLE(MARTIN_TEST.C1('DUAL'))

    /

    ORA-06553: PLS-707: unsupported construct or internal error [2603]

    06553. 00000 -  "PLS-%s: %s"

    FatMartinR wrote:SELECT *FROM TABLE(MARTIN_TEST.C1('DUAL'))/ORA-06553: PLS-707: unsupported construct or internal error [2603]06553. 00000 - "PLS-%s: %s"

    Maybe this tread would be better off in the   forum so that we can discuss the usefulness of such a feature/syntax.

    Threads in that forum don't have to worry about the syntax working since these are   about potential new features and ORA-6553 is the expected result if ran on any current version of Oracle.

    MK

    ApexBine
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    W00T!!!

    20c may have Macros! (safe harbor statement)

    You can create a Parameterized View with SQL Macros!!

    Video by Chris Saxon

    https://www.youtube.com/watch?v=1UDZulHYZ24&t=1340s

    Chris Saxon-Oracle
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    W00T!!!

    20c may have Macros! (safe harbor statement)

    You can create a Parameterized View with SQL Macros!!

    Video by Chris Saxon

    https://www.youtube.com/watch?v=1UDZulHYZ24&t=1340s

    Yes SQL Macros definitly solve some of the problems that we want to solve with parametrized views.

    Certainly it will work for the example that @ApexBine showed in the original idea.

    However SQL macros seem to come with several restrictions. So there are not that easy to use.

    For example if we use a pipelined table function to create a select without the parameter if the parameter is null and otherwise with the parameter binded, then this might not work with SQL macros. The pipelined table function would be a possible way to increase the performance for selects that have several such parameters:

    ...  col1 = nvl(:param1, col1) and col2=nvl(:param2, col2)and col3=nvl(:param3, col3)

    as far as I understood SQL macros will not help with such a case (which is quite different from original example).

    There was an interesting Ask Tom Office hour in October 2019 about this also from @Chris Saxon-Oracle.

    Unfortunatly I don't see a link anymore where the chat window from that session can be seen.

    It links to the same video that you already posted.

  • Chris Saxon-Oracle
    Chris Saxon-Oracle Member Posts: 21 Employee

    Yes SQL Macros definitly solve some of the problems that we want to solve with parametrized views.

    Certainly it will work for the example that @ApexBine showed in the original idea.

    However SQL macros seem to come with several restrictions. So there are not that easy to use.

    For example if we use a pipelined table function to create a select without the parameter if the parameter is null and otherwise with the parameter binded, then this might not work with SQL macros. The pipelined table function would be a possible way to increase the performance for selects that have several such parameters:

    ...  col1 = nvl(:param1, col1) and col2=nvl(:param2, col2)and col3=nvl(:param3, col3)

    as far as I understood SQL macros will not help with such a case (which is quite different from original example).

    There was an interesting Ask Tom Office hour in October 2019 about this also from @Chris Saxon-Oracle.

    Unfortunatly I don't see a link anymore where the chat window from that session can be seen.

    It links to the same video that you already posted.

    I believe you can use SQL macros for that scenario Sven - though perhaps I've not understood what you're trying to do!

    Unfortunatly I don't see a link anymore where the chat window from that session can be seen.

    Could you clarify what you're looking for here? I can probably dig it out

    berx
  • JPHether
    JPHether Member Posts: 1

    Hey all,

    what version of Oracle was parameterized views introduced?  I'm trying it on 11gR2 but no go.