(3) Language, Identifiers, Declarations

Character Sets
Lexical Units
Data Types
References to identifiers
Scope and visibility of identifiers
Assigning Values to variables
Error-reporting functions
SQL Functions in PL/SQL Expressions
Conditional Compilation

Character Sets
Single-byte vs Multi-byte representation

(a) Database CS
  • Stored source text of PL/SQL units
  • Character values of data types CHAR, VARCHAR2, CLOB, and LONG
(a) National CS
  • Character values of data types NCHAR, NVARCHAR2 and NCLOB

Lexical Units

Identifiers name PL/SQL Elements. Reserved words and Keywords should not be used as identifiers.
  • Predefined identifiers are declared in the predefined package STANDARD.
  • User-defined (ordinary or Quoted)
To list all predefined identifiers:
select type_name from all_types where predefined='YES';

Data Types

(a) Scalar data types
Can have subtypes. A data type and its subtypes comprise a data type family.

PL/SQL scalar data types are:
  • The SQL data types
  • User-defined subtypes

(b) Composite data types
  • These are structured data types. They have internal components (scalar or composite) that can be individually accessed. Composite variables can be passed as parameter.
  • Two types: Collections and Records.

  • Internal components (elements) are always of the same data type.
  • To create a collection variable: either (a) define a collection type and then create a variable of that type OR use %TYPE attribute.
  • Lists and arrays are classic examples of collections.
  • Here the internal components (fields) can be of different data types.
  • You access each field by its name.
  • A record variable can hold a table row, or some columns from a table row.
  • To create a record variable: either (a) define a RECORD type and then create a variable of that type, OR use %ROWTYPE or %TYPE.
  • Struct (in C); RECORD (in Pascal)

Click here for more information on Oracle and SQL Data types...

Initial Values
NOT NULL Constraint
%TYPE attribute

//declaring scalar Variable and Constant
  part_number NUMBER(6); -- SQL data type
  part_name VARCHAR2(20); -- SQL data type
  in_stock BOOLEAN := FALSE; -- PL/SQL-only data type

  max_days CONSTANT INTEGER := 366; -- SQL data type
  ispaid CONSTANT BOOLEAN := FALSE; -- PL/SQL-only

If the declaration is in a block or subprogram, the initial value is assigned to the variable or constant every time control passes to the block or subprogram.
If the declaration is in a package specification, the initial value is assigned to the variable or constant for each session (whether the variable or constant is public or private).

Protecting passwords in Sql*Plus

When connecting to SQL*Plus, username and password may be displayed in the list of active processes:

For example, if you connect:
$ sqlplus user/password@sid

then a list of processes will show username and password

$ ps -ef | grep sql 
jdoe    5652  4963  0 10:11 pts/0    00:00:00 sqlplus user/password@sid      
jdoe    5667  5632  0 10:13 pts/1    00:00:00 grep sql
How to protect user credentials when connecting to the database interactively or through a shell script?
(a) First, do not specify the password from the command line. Instead use:
$ sqlplus /nolog @connect.sql     -- and store user credentials in the connect.sql file

(b) If you need the user to enter his or hers credentials, you can use:

echo "Enter username : "
read login
echo "Enter password: "
read -s password


sqlplus -s /nolog << EOF
connect ${login}/${password}@${sid}
... your sql code here...
(c) alternatively, you can read the password from a protected file:
-- Create password file and change file permissions so that no one besides the owner has access
$ cat passowd > mypwdfile
$ chmod 700 mypwdfile

-- Create the script..


user= user_name
pass=`cat mypwdfile`
sqlplus -s ${user}/${pass} << EOF

... perform sqlplus statements