Re: Schema variables - new implementation for Postgres 15

6. Oracle

Oracle PL/SQL allows the use of package variables. PL/SQL is +/- ADA language - and package variables are "global" variables. They are not directly visible from SQL, but Oracle allows reduced syntax for functions without arguments, so you need to write a wrapper

CREATE OR REPLACE PACKAGE my_package
AS
    FUNCTION get_a RETURN NUMBER;
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package
AS
    a  NUMBER(20);

    FUNCTION get_a
    RETURN NUMBER
    IS
    BEGIN
      RETURN a;
    END get_a;
END my_package;

SELECT my_package.get_a FROM DUAL;

Inside SQL the higher priority has SQL, inside non SQL commands like CALL or some PL/SQL command, the higher priority has packages.