Note: the following text is excerpted with permission from Oracle PL/SQL Programming, 6th Edition, published by O’Reilly Media. It’s just one page of 1500 with lots of information about the PL/SQL language. So maybe you should get yourself a copy of this most recent edition (covers through Oracle Database 12c). Link: https://www.oreilly.com/library/view/oracle-plsql-programming/9781449324445/
The Root Package called STANDARD
A true object-oriented language like Java has a root class (in Java it is called Object, not surprisingly), from which all other classes are derived. PL/SQL is, officially, an object-relational language, but at its core it is a relational, procedural programming language and it has at its core a “root” package named STANDARD.
The packages you build are not derived from STANDARD, but almost every program you write will depend on and use this package. It is, in fact, one of the two default packages of PL/SQL, the other being DBMS_STANDARD.
The beginnings of PL/SQL
To best understand the role that these packages play in your programming environment, it is worth traveling back in time to the late 1980s, before the days of Oracle7 and SQL*Forms 3, before Oracle PL/SQL even existed. Oracle had discovered that while SQL was a wonderful language, it couldn’t do everything. Their customers found themselves writing C programs that executed the SQL statements, but those C programs had to be modified to run on each different operating system.
Oracle decided that it would create a programming language that could execute SQL statements natively and be portable across all operating systems on which the Oracle database was installed. The company also decided that rather than come up with a brand-new language on their own, they would evaluate existing languages and see if any of them could serve as the model for what became PL/SQL.
In the end, Oracle chose Ada as that model. Ada was originally designed for use by the U.S. Department of Defense, and was named after Ada Lovelace, an early and widely respected software programming pioneer. Packages are a construct adopted from Ada. In the Ada language, you can specify a “default package” in any given program unit. When a package is the default, you do not have to qualify references to elements in the package with the package_name dot syntax as in my_package.call_procedure.
When Oracle designed PL/SQL, they kept the idea of a default package, but changed the way it is applied. We (users of PL/SQL) are not allowed to specify a default package in a program unit. Instead, there are just two default packages in PL/SQL, STANDARD and DBMS_STANDARD. They are defaults for the entire language, not for any specific program unit.
You can (and almost always will) reference elements in either of these packages without using the package name as a dot-qualified prefix. Let’s now explore how Oracle uses the STANDARD package (and to a lesser extent, DBMS_STANDARD) to, as stated in the Oracle PL/SQL User Guide, “define the PL/SQL environment.”
See the Feuertips Episode on the STANDARD package
How Oracle uses the STANDARD package
STANDARD declares a set of types, exceptions, and subprograms that are automatically available to all PL/SQL programs and would be considered (mistakenly) by many PL/SQL developers to be “reserved words” in the language. When compiling your code, Oracle must resolve all unqualified identifiers; it first checks to see if an element with that name is declared in the current scope. If not, it then checks to see if there an element with that name defined in STANDARD or DBMS_STANDARD. If a match is found for all identifiers in your program, the code can be compiled (assuming there are no syntax errors).
To understand the role of STANDARD, consider the following, very strange-looking block of PL/SQL code. What do you think will happen when I execute this block?
1 declare
2 subtype date is number;
3 varchar2 date := 11111;
4 to_char pls_integer;
5 no_data_found exception;
6 begin
7 select 1
8 into to_char
9 from sys.dual
10 where 1 = 2;
11 exception
12 when no_data_found then
13 dbms_output.put_line('Trapped!');
14 end;
Most PL/SQL developers will say either “This block won’t even compile,” or “it will display the word ‘Trapped!’ since 1 is never equal to 2.”
In fact, the block will compile, but when you run it, you will see an unhandled NO_DATA_FOUND exception:
ORA-01403: no data found ORA-06512: at line 7
Now isn’t that odd? NO_DATA_FOUND is the only exception I am actually handling, so how can it escape unhandled? Ah, but the question is: which NO_DATA_FOUND am I handling? You see, in this block, I have declared my own exception named NO_DATA_FOUND. This name is not a reserved word in the PL/SQL language (in contrast, BEGIN is a reserved word. You cannot name a variable “BEGIN”). Instead, it is an exception that is defined in the specification of the STANDARD package, as follows:
NO_DATA_FOUND exception; PRAGMA EXCEPTION_INIT(NO_DATA_FOUND, 100);
Since I have a locally-declared exception with the name NO_DATA_FOUND, any unqualified reference to this identifier in my block will be resolved as my exception and not STANDARD’s exception.
If, on the other hand, line 12 in my exception section looked like this:
WHEN STANDARD.NO_DATA_FOUND
then the exception would be handled and the word “Trapped!” displayed.
In addition to the oddness of NO_DATA_FOUND, these lines also appear to be rather strange and deserve some explanation:
Line 2: subtype date is number;
Define a new type of data named “DATE”, which is actually of type NUMBER.
Line 3: varchar2 date := 11111;
Declare a variable named “VARCHAR2” of type “DATA” and assign it a value of 11111.
Line 4: to_char pls_integer;
Declare a variable named “TO_CHAR” of type PLS_INTEGER.
I can “repurpose” these names of “built-in” elements of the PL/SQL language, because they are all defined in the STANDARD package. These names are not reserved by PL/SQL; they are simply and conveniently referenceable without their package name.
The STANDARD package contains the definitions of the supported datatypes in PL/SQL, the predefined exceptions, and the built-in functions, such as TO_CHAR, SYSDATE, and USER. The DBMS_STANDARD package contains transaction-related elements, such as COMMIT, ROLLBACK, and the trigger event functions INSERTING, DELETING, and UPDATING.
Conclusion
Here are a few things to note about STANDARD:
- You should never change the contents of this package. If you do, I suggest that you not contact Oracle Support and ask for help. You have likely just violated your maintenance agreement! Your DBA could give you read-only authority on the RDBMS/Admin directory so that you can examine this package, along with any of the supplied packages, like DBMS_OUTPUT (check out dbmsotpt.sql) and DBMS_UTILITY (check out dbmsutil.sql).
- Oracle even lets you read the package body of STANDARD; most package bodies, such as for DBMS_SQL, are wrapped or pseudo-encrypted. STANDARD is not. Look and you will see, for instance, that the USER function always executes a SELECT from SYS.dual, while SYSDATE will only execute a query if a C program to retrieve the system timestamp fails.
- Just because you see a statement in STANDARD doesn’t mean you can write that same code in your own PL/SQL blocks. You cannot, for example, declare a subtype with a range of values, as is done for BINARY_INTEGER.
- Just because you see something defined in STANDARD doesn’t mean you can use it in PL/SQL. For example, the DECODE function is declared in STANDARD, but it can be called only from within a SQL statement.
How STANDARD Package Defines the PL/SQL Environment (Oracle doc)
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-packages.html#GUID-6471BE85-7F01-4218-BC6B-C945C588FEFF
