I was asked by a colleague a couple of weeks ago if it was possible to create ENUM's in PL/SQL like its possible to create in languages such as C. The actual example the person emailed me is too business/market specific for his company and I don't want to repeat it here as I don't want to give away who he works for without his permission so I will use a simpler examples.
Because I have some knowledge of PL/SQL my thoughts turned to TYPEs as it should be possible to create a TYPE that is in effect a constrained TYPE or an enumerated type. A simple example would be the BOOLEAN type in PL/SQL that is limited to values of TRUE and FALSE:
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=TRUE;
5 pv_var:=FALSE;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
|
That works as designed but what if we tried to assign a different value?
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=7;
5 end;
6 /
pv_var:=7;
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:='NOT';
5 end;
6 /
pv_var:='NOT';
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
SQL>
|
That doesn't work as the values are constrained - great. What is interesting is that we are always allowed to set the variable to NULL so in essence the TWO value ENUM has a three value set of possible values, TRUE, FALSE and NULL:
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=NULL;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
|
The BOOLEAN data value is defined in the standard.sql (stdspec.sql and stdbody.sql) files as:
type BOOLEAN is (FALSE, TRUE);
|
Now that looks exactly like whats needed for my colleague. Indeed the ADA language that PL/SQL is based on supports
enumerations in the same format as the BOOLEAN type is supported in PL/SQL in the STANDARD PACKAGE. Can we then use this same syntax in PL/SQL for instance:
SQL> declare
2 type colors is (RED, GREEN, BLUE, YELLOW);
3 begin
4 null;
5 end;
6 /
type colors is (RED, GREEN, BLUE, YELLOW);
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00505: User Defined Types may only be defined as PLSQL Tables or Records
ORA-06550: line 2, column 1:
PL/SQL: Item ignored
SQL>
|
The above simple test shows that its not possible for us to create ENUMs in PL/SQL like we can do in ADA. i.e. we can only create TYPEs that are tables or records. This is annoying but also an illustration of undocumented Oracle as Oracle themselves use the TYPE syntax to create an enumeration type in the STANDARD package but dont allow us to do the same. Clearly this syntax does compile if the code is within the standard package but not anywhere else so it is possible (but clearly not recommended or advised as it would affect support/warranty) to add new enumerations to the standard package. Oracle must have a check in the compiler that forces error PLS-00505 if the TYPE is an enumerator and not in the STANDARD package. I am also logged in AS SYSDBA above so its not the user that allows this but the location (STANDARD PACKAGE).
Why do Oracle use syntax available to them only in the STANDARD package and not available to us? - well, my educated guess would be that they have only implemented this syntax in a very narrow way, i.e. to fulfill a particular case and not much more. They must have made sure it compiles the BOOLEAN correctly but not tested or implemented much else hence we cannot use it. This is also a reason not to simply add your own enums to the standard package as they may compile but the results are likely to be undefined in some cases where the testing didn't iron out the rules properly.
It is quite interesting that Oracle constrain (or create an enumeration) a TYPE using the TYPE syntax as ADA does when Oracle also supports the SUBTYPE syntax to provide constrained and un-constrained types. An unconstrained type is really just an ALIAS for another type. See the STANDARD package for some examples. A constrained type is a type that limits the numeric values (which are allowed in user PL/SQL space; what is not allowed it seems is the connection between ENUM identifiers which in the C language would be numercially associated and indeed C implements the enum values as integers) and numeric constrains. This leads me to the idea that an ENUM can be emulated via CONSTANTS and SUBTYPES and this idea is what I have passed on to my colleague:
SQL> declare
2 RED constant number(1):=1;
3 GREEN constant number(1):=2;
4 BLUE constant number(1):=3;
5 YELLOW constant number(1):=4;
6 --
7 VIOLET constant number(1):=7;
8 --
9 subtype colors is binary_integer range 1..4;
10 --
11 pv_var colors;
12 begin
13 pv_var:=YELLOW;
14 end;
SQL> /
PL/SQL procedure successfully completed.
SQL> 13
13* pv_var:=YELLOW;
SQL> c/YELLOW/VIOLET/
13* pv_var:=VIOLET;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13
SQL>
|
As you can see we create a set of CONSTANT values that represent the values of the ENUM and then create a SUBTYPE that constrains the values of any variable of this SUBTYPE to these values.
This is not a perfect solution as we cannot do TYPE BOOLEAN IS (TRUE,FALSE) as Oracle does in the standard package or indeed TYPE COLORS IS (RED,GREEN,BLUE,YELLOW) as we can in ADA but its close; there are also issues around using this across multiple peices of code as ideally we would write this once which would inevitably cause us to need to do PACKAGE.CONSTANT. What is missing is the tie between the words (enums) and the type but what we have here is almost what C does, except C does the mapping between say YELLOW and 4 in the bacground for us and allows us to write YELLOW without seperately defining it. To illustrate the use of this idea to my colleague I created a simple example program that allows the use of the "enum" as a parameter, return type of a function etc, basically in a similar context as I would use an enum in my C programs.
SQL> declare
2 RED constant number(1):=1;
3 GREEN constant number(1):=2;
4 BLUE constant number(1):=3;
5 YELLOW constant number(1):=4;
6 --
7 VIOLET constant number(1):=7;
8 --
9 subtype colors is binary_integer range 1..4;
10 --
11 pv_var colors;
12 --
13 function test_a (pv_var1 in colors) return colors
14 is
15 begin
16 if(pv_var1 = YELLOW) then
17 return(BLUE);
18 else
19 return(RED);
20 end if;
21 end;
22 --
23 begin
24 pv_var:=test_a(YELLOW);
25 if (pv_var=YELLOW) then
26 dbms_output.put_line('YELLOW');
27 elsif(pv_var=RED) then
28 dbms_output.put_line('RED');
29 elsif(pv_var=BLUE) then
30 dbms_output.put_line('BLUE');
31 elsif(pv_var=GREEN) then
32 dbms_output.put_line('GREEN');
33 else
34 dbms_output.put_line('UN-KNOWN');
35 end if;
36 end;
37 /
BLUE
PL/SQL procedure successfully completed.
SQL>
|
OK, enough of ENUM's in PL/SQL for now. I like to look into the internals of Oracle and in particular PL/SQL and in the standard package there are lots of other gems such as the definition of VARCHAR2 which follows the similar syntax used in ADA to define a new type. In ADA we can do:
TYPE BOOL IS NEW BOOLEAN;
mybool: BOOL
|
Which is similar syntax to PL/SQL but again we only find this syntax used in the standard package. There are 10 occurances of this syntax in the shipped SQL code of the 11g database all of which use new occurances of CHAR_BASE or DATE_BASE. If we try and use the same syntax in user space code as follows we get:
SQL> declare
2 type mychar is new char_base;
3 begin
4 null;
5 end;
SQL> /
type mychar is new char_base;
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00504: type CHAR_BASE may not be used outside of package STANDARD
ORA-06550: line 2, column 1:
PL/SQL: Item ignored
SQL>
|
Interesting, these are undocumented language features of PL/SQL that come as standard from ADA but we are not allowed to use them ourselves in our PL/SQL code. It is interesting as I said to delve into the code shipped by Oracle to see what they use and do that we are not allowed to, because in some cases we may actually extend our knowledge and maybe we will find a feature that does work and is useful in some cases. For me it's definetely about the learning more about how Oracle works. Have fun!
PFCLScan
PFCLObfuscate
PFCLCode
PFCLForensics
PFCLReselling
PFCLATK
PFCLCookie
PFCLTraining
PFCLServices
PFCLConsulting
PFCLCustom
PFCLCloud
PFCLUserRights
PFCLSTK
PFCLSFTK
PFCLSEO
