How to create a parameterized view in Oracle

Database views have been used for decades to help simplify both ad-hoc queries and reporting for developers, analysts, and end-users. But the problem with defining views is they tend to be either too specific (not easily reused) or not specific enough (too generic to be performant).

Imagine we need a view to help developers quickly find the details about orders waiting to ship, which have a total value of $100 or more. How would you create such a view?

Would you create a particular view that returns only the columns needed for this scenario with restrictive where clause predicates that limit the orders to only those that have not shipped and have a value greater than $100?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT i.order_id, i.product_id, i.price, i.description 
FROM   orders o, order_items i
WHERE  o.order_status > 6   -- orders waiting to ship
AND    o.order_total >= 100
AND    o.order_id = i.order_id;

Or do you create a more generic view that could be used for other reports and queries, even though it won’t be as performant as the dedicated view above?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT o.order_status, o.order_total,
       o.cust_id, o.order_date, o.rep,       
       o.order_mode, o.promotion_id, 
       i.* 
FROM   orders o, order_items i
WHERE  o.order_id = i.order_id;

Regardless of your approach, you will compromise either reusability or performance.

Starting in Oracle Database 19c (19.7), you no longer have to compromise, as Oracle introduced parameterized views or SQL Table Macros.

SQL table macros are expressions found in a FROM clause that acts as a polymorphic or parameterized view. Let’s look at how this can help us with the view we need to create to find the details about orders waiting to ship, which have a total value of $100 or more.

Creating a SQL Macro is similar to creating a PL/SQL function with an additional SQL_MACRO clause. The SQL MACRO clause can take an argument SCALAR (available from 21c onwards) or TABLE (19c onwards), but if you leave it blank, it defaults to a TABLE macro.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 ...
 RETURN stmt;
END orders_waiting_to_ship;
/

A SQL Macro always returns the view you define as text (VARCHAR, CHAR, or CLOB). The database will resolve that view definition and makes it part of the SQL statement that calls the SQL Macro. I’m capturing the view definition in a CLOB using the variable stmt above.

Let’s add the text for the view and the parameters we want to pass.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship(order_value INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
  SELECT i.*
  FROM   orders o, order_items i
  WHERE  o.order_status > 6
  AND    o.order_total >= order_value
  AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_waiting_to_ship;
/
 
FUNCTION ORDERS_WAITING_TO_SHIP compiled

In the code example above, I’m using the more selectivity view definition, but instead of specifying the order_total to be $100 or higher, I’m going to parameterize the value of the order. That way, we can reuse this SQL Table Macro regardless of what order_value is needed. You will notice I have also added a parameter to the function called order_value, which the user will pass in when they call the function.

You will also notice that my function ORDERS_WAITING_TO_SHIP compiled without errors. This doesn’t mean my view definition or syntax is correct. You will only see an error at runtime. Remember, a SQL TABLE Macro produces your view definition as text that the optimizer will insert into the SQL statement that calls it.

It’s straightforward to call a SQL Table Macro. The Table Macro goes in the FROM clause of the SQL statement. In the example below, I’m calling our orders_waiting_to_Ship SQL Macro and passing it the value 100.

 SELECT *
FROM orders_waiting_to_ship(100) 
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION    
___________ _____________ ________ ______________ 
      10110            10       23 lipstick       
      10110             7       17 Lip gloss      
      10110             8       30 Mascarra
      10110             1       35 Foundation
      20209             9       15 Blusher        
      20209            10       23 lipstick  
      20209             1       35 Foundation
      20209             2       32 Coverup
      30307             7       17 Lip gloss      
      30307             8       30 Mascarra       
      30307             1       35 Foundation       
      30307             2       32 Coverup

We could take it a step further and generalize our SQL Table Macro a little more by parameterizing the order_status and the order_value; that way, our developers can use it to check the status of shipped orders and orders waiting to ship.

 CREATE OR REPLACE FUNCTION orders_chk(order_value INTEGER, stat INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
 SELECT i.*
 FROM   orders o, order_items i
 WHERE  o.order_status = orders_chk.stat
 AND    o.order_total >= orders_chk.order_value
 AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_chk;
/
 
FUNCTION ORDERS_CHK compiled
 
SELECT *
FROM orders_chk(10,6)
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION     ......
___________ _____________ ________ ______________ __________ 
      10110            10       23 lipstick        ......
      10110             7       17 Lip gloss       ......
      10110             8       30 Mascarra        ......
      10110             1       35 Foundation      ......
      10111             9       15 Blusher         ......         
      10112             1       35 Foundation      ......
      10113             1       17 Lip gloss       ......

There are several restrictions on SQL TABLE Macros you should be aware of:

    • When used directly in a SQL statement, the SQL_MACRO annotation is disallowed with RESULT_CACHE, PARALLEL_ENABLE, and PIPELINE.
    • SQL statements with WITH clauses are not supported in SQL macros.
    • A SQL macro always runs with invoker rights.
    • SQL macros in views are always executed with the privileges of the view owner.
    • SQL macros can’t be used in virtual column expression, function-based indexes, editioning views, or materialized views.
    • SQL macros can’t be used in type methods.

6 thoughts on “How to create a parameterized view in Oracle”

  1. I am noticing some odd behavior when SQL macros are placed in views. Here is script showing what I am talking about. I am using 19C version (19.19.0.0.0 and 19.20.0.1.0, that later is autonomous DB).

    — Step 1: Create the package to replicate the error.
    CREATE OR REPLACE PACKAGE sql_macro_view_ora_600_pkg
    AUTHID DEFINER
    AS
    — In general calling this procedure does nothing.
    — However, if the package is invalid, calling it will attempt to validate it.
    — And if this is the first time thje package is called in the session, calling it will
    — initialize package declarations and excecute the initializastion section.
    PROCEDURE do_nothing;

    — This function is the most basic example of a table SQL macro
    FUNCTION sql_macro_view_base
    RETURN VARCHAR2
    SQL_MACRO
    ;
    END sql_macro_view_ora_600_pkg;
    /
    — Step 2: Create the package body to replicate the error.
    CREATE OR REPLACE PACKAGE BODY sql_macro_view_ora_600_pkg
    AS
    — In general calling this procedure does nothing.
    — However, if the package is invalid, calling it will attempt to validate it.
    — And if this is the first time thje package is called in the session, calling it will
    — initialize package declarations and excecute the initializastion section.
    PROCEDURE do_nothing
    AS
    BEGIN
    NULL;
    END do_nothing;
    — This function is the most basic example of a table SQL macro
    FUNCTION sql_macro_view_base
    RETURN VARCHAR2
    SQL_MACRO
    AS
    BEGIN
    RETURN ‘SELECT * FROM dual’;
    END sql_macro_view_base;
    END sql_macro_view_ora_600_pkg;
    /

    — Step 3: Execute the packaged procedure to ensure it works
    EXEC sql_macro_view_ora_600_pkg.do_nothing;

    — Step 4: Execute the packaged SQL Macro function to ensure it works.
    SELECT * FROM sql_macro_view_ora_600_pkg.sql_macro_view_base();

    — Step 5: Create a view on the package SQL Macro function to replicate the error
    CREATE OR REPLACE VIEW sql_macro_view
    AS
    SELECT * FROM sql_macro_view_ora_600_pkg.sql_macro_view_base();

    — Step 6: Test the view to ensure it can be selected from without error.
    SELECT * FROM sql_macro_view;

    — Step 7: Recompile the package body
    ALTER PACKAGE sql_macro_view_ora_600_pkg compile body;

    — Step 8: Re-execute the packaged procedure to ensure it still works
    BEGIN sql_macro_view_ora_600_pkg.do_nothing; END;
    /
    — Step 9: Execute the packaged SQL Macro function to ensure it works.
    SELECT * FROM sql_macro_view_ora_600_pkg.sql_macro_view_base();

    — Step 10: Checking the status of the view reveals it is INVALID.
    — This is not surprising given that the function it was dependent upon was recompiled.
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;

    — Step 11: Test the invalid view to see if it automaticaly revalidates and executes as it did prevously.
    — It does not. The execution of the view based upon the SQL macro results in an ORA-00600
    — ORA-00600: internal error code, arguments: [kqlchg-parent-not-pinned], [SQL_MACRO_VIEW], [SQL_MACRO_VIEW_ORA_600_PKG], [], [], [], [], [], [], [], [], []
    — 00600. 00000 – “internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]”
    SELECT * From sql_macro_view;

    — Step 12. Checking the status of the view reveals it is still INVALID..
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;

    — Step 13. Run these two blocks, the first executes a procedure that does nothing from the same package that contains the
    — SQL Macro that the view is based on. The second executes the failing select statement dynamically.
    — The second block again fails with the ORA-0600.
    BEGIN
    sql_macro_view_ora_600_pkg.do_nothing;
    END;
    /
    DECLARE
    l_dummy CHAR(1);
    BEGIN
    EXECUTE IMMEDIATE ‘SELECT * FROM SQL_MACRO_VIEW’ INTO l_dummy;
    END;
    /

    — Step 14. Checking the status of the view reveals it is still invalid
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;

    — Step 15. Combine the two blocks from step 13 into one block but also add a static version of the
    — second statement which is the orignal statement that failed but contained in a PL/SQL block.
    — This block also fails with the ORA-0600.
    DECLARE
    l_dummy CHAR(1);
    BEGIN
    sql_macro_view_ora_600_pkg.do_nothing;
    EXECUTE IMMEDIATE ‘SELECT * FROM SQL_MACRO_VIEW’ INTO l_dummy;
    SELECT * INTO l_dummy FROM SQL_MACRO_view;
    END;
    /
    — Step 16. Checking the status of the view reveals it is still invalid
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;

    — Step 17. Now if one takes the same block from step 15 and removes the second statement, it still raises the ORA-0600.
    DECLARE
    l_dummy CHAR(1);
    BEGIN
    sql_macro_view_ora_600_pkg.do_nothing;
    SELECT * INTO l_dummy FROM SQL_MACRO_view;
    END;
    /

    —Step 18. And now the status of the view is still invalid
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;

    — Step 19. But now if one takes the same block from step 15 and removes the third statement, it executes without error.
    DECLARE
    l_dummy CHAR(1);
    BEGIN
    sql_macro_view_ora_600_pkg.do_nothing;
    EXECUTE IMMEDIATE ‘SELECT * FROM SQL_MACRO_VIEW’ INTO l_dummy;
    END;
    /

    —Step 20. And now the status of the view is valid
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;

    — Step 21 And the orginal failing statement (step 11) now executes without error
    SELECT * FROM sql_macro_view;

    — Step 22. Recompile the package to invalidate the view.
    ALTER PACKAGE sql_macro_view_ora_600_pkg compile body;
    —Step 23. And now the status of the view is invalid
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;
    — Step 24. Show that querying the view once again results in the same ORA-00600
    SELECT * FROM sql_macro_view;
    — Step 25. Validate the view by recompiling it. Unfortuately not all account that can query the view would have this privilege
    ALTER VIEW sql_macro_view compile;
    —Step 26. And now the status of the view is valid
    SELECT STATUS FROM user_objects where object_name = ‘SQL_MACRO_VIEW’;
    — Step 27. Show that querying the view once again executes without raising an ORA-00600.
    SELECT * FROM sql_macro_view;

    /*
    Summary Questions:
    1. Why does a view invalidated by compiling the package containing the SQL Macro it is dependent upon not automatically validate when it is queried?
    (whether or not it is queried statically outside a PLSQL block or inside a PLSQL block or dynamically within a PL/SQL block)
    2. Why does a view invalidated by compiling the package containing the SQL Macro it is dependent upon, raise an ORA-00600 when it is queried?
    (whether or not it is queried statically outside a PLSQL block or inside a PLSQL block or dynamicall ywithin a PL/SQL block)
    (see steps 11, 17, 13)
    3. However, why does querying the view dynamically immediately after executing any routine in the same package as the SQL macro in the same block
    validate the view and allow the dynamic query to complete without raising the error?
    (see step 19)
    4. Given querying the view dynamically immediately after executing any routine in the same package as the SQL macro in the same block both validates the view
    and does not raise the error, why does replacing the dynamic querying of the view with a static query of the view NOT have the same result?
    (see step 17)
    5. Given querying the view dynamically immediately after executing any routine in the same package as the SQL macro in the same block both validates the view
    and does not raise the error, why does adding the static querying of the view immediately after the dynamic query of the view prevent the view from validating and result in the raise of the ORA-00600?
    (see step 15)
    */

  2. You need to add WITH to the list of restrictions.

    The first case works as expected., returning ‘123’.

    create or replace FUNCTION TEST (pi_TEST IN INTEGER) RETURN VARCHAR2 SQL_MACRO AS
    BEGIN
    RETURN ‘SELECT pi_TEST
    FROM DUAL’;
    END TEST;
    /
    SELECT *
    FROM TEST(123)
    /

    create or replace FUNCTION TEST2 (pi_TEST IN INTEGER) RETURN VARCHAR2 SQL_MACRO AS
    BEGIN
    RETURN ‘WITH a AS (SELECT pi_TEST
    FROM DUAL)
    SELECT *
    FROM DUAL’;
    END TEST2;
    /
    SELECT *
    FROM TEST2(123)
    /

    The second returns:
    “ORA-00904: “PI_TEST”: invalid identifier
    00904. 00000 – “%s: invalid identifier”
    *Cause:
    *Action:
    Error at Line: 20 Column: 27″.

    1. Thanks for the heads-up on this Martin. You are correct, statements with a WITH clause are not in SQL macros.

      SQL> create or replace FUNCTION TEST2 (pi_TEST IN INTEGER) RETURN VARCHAR2
      2 SQL_MACRO AS
      3 BEGIN
      4 RETURN ‘(WITH a AS (SELECT pi_TEST FROM DUAL) SELECT * FROM a)’;
      5 END TEST2;
      6* /

      Function TEST2 compiled

      SQL> select * from test2(123);

      Error at Command Line : 1 Column : 1
      Error report –
      SQL Error: ORA-64626: invalid SQL text returned from SQL macro:
      ORA-32034: unsupported use of WITH clause

Leave a Reply

Your email address will not be published. Required fields are marked *