How can I declare constant in PL SQL without writing procedure in "begin"?-CodePudding

I would like to do something like that:

DECLARE MY_DATE CONSTANT DATE := DATE'2022-07-01';
BEGIN null; end;
SELECT MY_DATE FROM dual

DECLARE MY_DATE CONSTANT DATE := DATE'2022-07-01';
BEGIN null; end;

SELECT CASE WHEN sysdate > MY_DATE THEN 1 ELSE 0 END FROM dual

But I am getting error. How can I fix it?

CodePudding user response：

If you're on a recent version you can declare a local function in a CTE:

WITH
  FUNCTION MY_DATE RETURN DATE IS
  BEGIN
    RETURN DATE '2022-07-01';
  END;
SELECT MY_DATE FROM dual

Or you can use a normal CTE:

WITH cte (MY_DATE) AS (
  SELECT DATE '2022-07-01' FROM DUAL
)
SELECT MY_DATE FROM cte

db<>fiddle

CodePudding user response：

NEW: From the comment I see you're declaring a constant within a package and your aim is to use it in an SQL statement outside of package.

You need a function to return that constant.

NOTE: I wouldn't recommend to store constants in a package and use it in SQL. I'd prefer to store them in a special table because of overhead you'll get when adding or removing constants. And performance issues are to be expected as well because of context switching while using PLSQL constants in SQL statements.

However, answering your question, here's the example for a package having multiple constants:

CREATE OR REPLACE PACKAGE MYCONSTANTS 
IS 
  MY_DATE CONSTANT DATE := DATE'2022-07-01'; 
  MY_CHAR CONSTANT VARCHAR2(10) := 'ABCD';

  function get_my_date return date;
  function get_my_char return varchar2;
END;
/

create or replace PACKAGE body MYCONSTANTS IS 
  function get_my_date return date
  is
  begin
    return my_date;
  end;
  
  function get_my_char return varchar2
  is
  begin
    return MY_CHAR;
  end;
END;    
/

And the you can say:

select MYCONSTANTS.get_my_date from dual;
select MYCONSTANTS.get_my_char from dual;

OLD: As far as I can see you don't need PL/SQL but just SQL.

The PLSQL is what you would have between "begin" and "end". But in your example, you have just null there.

So, all you need is "define"

def my_date = date '2022-07-01';

begin
  null;
end;
/ -- you need this to start execution of an pl/sql block

select &my_date from dual; -- be aware you need to use an "&" before var name