Oracle CONSTANT from query in package-CodePudding

This is my CONSTANT in package.

const_percent CONSTANT NUMBER DEFAULT 0.3;

Is it possible set CONSTANT from query and is it good solution?

eg.

const_percent   =  Select parameter_value from tbl_parameters where name = 'const_percent'

CodePudding user response：

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>

CodePudding user response：

Yes. It is possible to set a variable from a query. The syntax is -

DECLARE
       const_percent NUMBER;
BEGIN
     SELECT parameter_value
       INTO const_percent
       FROM tbl_parameters
      WHERE name = 'const_percent';

    DBMS_OUTPUT.PUT_LINE(const_percent)
END;