# 100CodeExamples - Constants in PL/SQL and SQL

• Script Name 100CodeExamples - Constants in PL/SQL and SQL
• Visibility Unlisted - anyone with the share link can access
• Description How to use constants in PL/SQL and bring them to SQL
• Area PL/SQL General
• Contributor Pesse
• Created Tuesday March 05, 2019
• Statement 1
``````create table characters_of_force (
name varchar2(128) not null primary key,
alignment varchar2(10) not null,
constraint characters_of_force_chk
check (alignment in ('dark','light','neutral'))
)``````
Table created.
• Statement 2
``````insert into characters_of_force
1 row(s) inserted.
• Statement 3
``````insert into characters_of_force
values ('Luke Skywalker', 'light')``````
1 row(s) inserted.
• Statement 4
``````insert into characters_of_force
values ('Aurra Sing', 'neutral')``````
1 row(s) inserted.
• Statement 5
``select * from characters_of_force``
NAMEALIGNMENT
Luke Skywalkerlight
Aurra Singneutral

3 rows selected.
• Statement 6
``````create or replace package alignment_types as
/* the constant keyword prevents the package variable
from being overridden at a later time */
dark constant varchar2(10) := 'dark';
light constant varchar2(10) := 'light';
neutral constant varchar2(10) := 'neutral';
end; ``````
Package created.
• Statement 7
``````begin
dbms_output.put_line(
'Anakin was first aligned to '
|| alignment_types.light
|| ' but turned to '
|| alignment_types.dark
|| ' later');
end; ``````
Statement processed.
Anakin was first aligned to light but turned to dark later
• Statement 8
``select alignment_types.light from dual``
ORA-06553: PLS-221: 'LIGHT' is not a procedure or is undefined
• Statement 9
``````create or replace package alignment_types as
/* No public constants anymore,
but functions without parameters */
function light return varchar2;
function dark return varchar2;
function neutral return varchar2;
end; ``````
Package created.
• Statement 10
``````create or replace package body alignment_types as
/* Here we have our constants again,
hiding them from public access */
g_dark constant varchar2(10) := 'dark';
g_light constant varchar2(10) := 'light';
g_neutral constant varchar2(10) := 'neutral';

/* The functions are our only publicly available item */
function light return varchar2
as
begin
return g_light;
end;

function dark return varchar2
as
begin
return g_dark;
end;

function neutral return varchar2
as
begin
return g_neutral;
end;
end; ``````
Package Body created.
• Statement 11
``select alignment_types.light from dual``
LIGHT
light
• Statement 12
``````alter table characters_of_force
drop constraint characters_of_force_chk``````
Table altered.
• Statement 13
``````alter table characters_of_force
check ( alignment in
(alignment_types.light,
alignment_types.dark,
alignment_types.neutral)
)``````
ORA-00904: "ALIGNMENT_TYPES"."NEUTRAL": invalid identifier
• Statement 14
``````create or replace package alignment_types as
function light return varchar2;
function dark return varchar2;
function neutral return varchar2;
/* We need a deterministic check-function */
function valid_type(i_value in varchar2)
return int deterministic;
end; ``````
Package created.
• Statement 15
``````create or replace package body alignment_types as
g_dark constant varchar2(10) := 'dark';
g_light constant varchar2(10) := 'light';
g_neutral constant varchar2(10) := 'neutral';

function light return varchar2
as
begin
return g_light;
end;

function dark return varchar2
as
begin
return g_dark;
end;

function neutral return varchar2
as
begin
return g_neutral;
end;

/* This function can just check against our constants */
function valid_type(i_value in varchar2)
return int deterministic
as
begin
if ( i_value in (light, dark, neutral)) then
return 1;
else
return 0;
end if;
end;
end; ``````
Package Body created.
• Statement 16
``````alter table characters_of_force
add (alignment_check number generated always as (
alignment_types.valid_type(alignment)
))``````
Table altered.
• Statement 17
``````alter table characters_of_force
``````insert into characters_of_force ( name, alignment )