How to Use a Package Constant in SQL Select Statement

How to use a package constant in SQL SELECT statement?

You can't.

For a public package variable to be used in a SQL statement, you have to write a wrapper function to expose the value to the outside world:

SQL> create package my_constants_pkg
  2  as
  3    max_number constant number(2) := 42;
  4  end my_constants_pkg;
  5  /

Package created.

SQL> with t as
  2  ( select 10 x from dual union all
  3    select 50 from dual
  4  )
  5  select x
  6    from t
  7   where x < my_constants_pkg.max_number
  8  /
 where x < my_constants_pkg.max_number
           *
ERROR at line 7:
ORA-06553: PLS-221: 'MAX_NUMBER' is not a procedure or is undefined

Create a wrapper function:

SQL> create or replace package my_constants_pkg
  2  as
  3    function max_number return number;
  4  end my_constants_pkg;
  5  /

Package created.

SQL> create package body my_constants_pkg
  2  as
  3    cn_max_number constant number(2) := 42
  4    ;
  5    function max_number return number
  6    is
  7    begin
  8      return cn_max_number;
  9    end max_number
 10    ;
 11  end my_constants_pkg;
 12  /

Package body created.

And now it works:

SQL> with t as
  2  ( select 10 x from dual union all
  3    select 50 from dual
  4  )
  5  select x
  6    from t
  7   where x < my_constants_pkg.max_number()
  8  /

         X
----------
        10

1 row selected.

Oracle Packages and using a select statement in a public variable

We can include initialising code in a package by putting it at the end of the body. It takes the form of a BEGIN block which is terminated by the final END of the package body.

create or replace package BEACHCOMBER is
    v_Guest_Count pls_integer;
    function get_cnt return number;
end BEACHCOMBER;
/
create or replace package body BEACHCOMBER is
    function get_cnt return number
    is
    begin
        return v_Guest_Count;
    end get_cnt;

<< init_block >>
begin
    select count(*) 
    into v_Guest_Count
    from A2_GUESTS;
end BEACHCOMBER;
/

The code under the label << init_block >> is run the first time the package is invoked. This includes referencing the public variable. This code not run again in the session, unless the package is recompiled, which discards state.

Here is my test script. I have published this as a working demo on Oracle LiveSQL (because DBMS_OUTPUT) but you need a free Oracle account to run it. Check it out

Test set up

drop table A2_GUESTS
/

create table A2_GUESTS (id number);

insert into A2_GUESTS select level from dual connect by level <=23;

create or replace package BEACHCOMBER is
    v_Guest_Count pls_integer;
    function get_cnt return number;
end BEACHCOMBER;
/
create or replace package body BEACHCOMBER is
    function get_cnt return number
    is
    begin
        return v_Guest_Count;
    end get_cnt;
begin
    select count(*) 
    into v_Guest_Count
    from A2_GUESTS;
end BEACHCOMBER;
/

Here are the tests;

begin
    dbms_output.put_line('count = ' || BEACHCOMBER.v_Guest_Count);
end;
/

insert into A2_GUESTS values (42)
/

select BEACHCOMBER.get_cnt 
from dual
/

alter package BEACHCOMBER compile body
/

select BEACHCOMBER.get_cnt 
from dual
/

How to set a variable and use it in a select query in Oracle PL/SQL?

Use a cursor:

DECLARE
    p_name VARCHAR2(100);
    p_cur  SYS_REFCURSOR;
BEGIN
    p_name := 'Strawberry';
    OPEN p_cur FOR
    SELECT * FROM products where product_name=p_name;

    -- do something with the cursor.
END;
/

Or use a SQL/Plus-style bind variable declaration:

VARIABLE p_name VARCHAR2;

BEGIN
  :p_name := 'Strawberry';
END;
/

SELECT * FROM products where product_name=:p_name;

If only one row will ever be returned from your query (i.e. product_name is UNIQUE) then you can use SELECT ... INTO ...:

DECLARE
    p_name   VARCHAR2(100);
    p_value1 products.value1%TYPE;
    p_value2 products.value2%TYPE;
    p_value3 products.value3%TYPE;
BEGIN
    p_name := 'Strawberry';

    SELECT value1, value2, value3
    INTO   p_value1, p_value2, p_value3
    FROM   products
    WHERE  product_name=p_name;

    -- do something with the values.
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
    -- Handle the exception
  WHEN TOO_MANY_ROWS THEN
    NULL;
    -- Handle the exception
END;
/

Oracle CONSTANT from query in package

Nope. Constant is a constant, you can't modify its value.

For example:

SQL> declare
  2    l_dummy constant varchar2(1) := 'A';
  3  begin
  4    select dummy
  5      into l_dummy
  6      from dual;
  7  end;
  8  /
    into l_dummy
         *
ERROR at line 5:
ORA-06550: line 5, column 10:
PLS-00403: expression 'L_DUMMY' cannot be used as an INTO-target of a
SELECT/FETCH statement
ORA-06550: line 6, column 5:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

---

SQL> declare
  2    l_dummy constant varchar2(1) := 'A';
  3  begin
  4    l_dummy := 'B';
  5  end;
  6  /
  l_dummy := 'B';
  *
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00363: expression 'L_DUMMY' cannot be used as an assignment target
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

SQL>

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

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

How to use variables in a select statement in Oracle PL-SQL

PLSQL is different than SQL SERVER. It has its own syntax. See how you can do it as below:

DECLARE
   var    NUMBER := 1;
   var2   my_table%ROWTYPE;
BEGIN
   SELECT *
     INTO var2
     FROM my_table
    WHERE my_id = var;

  --To display result you need to add dbsm_output.put_line function.

  dbms_output.put_line(var2.<columnname>);

Exception
When others then
 Null;       
END;

Note: Assumption is that the query wil return a single row only.

---

Related Topics