(3) Language, Identifiers, Declarations

Basic Aspects of Oracle PL/SQL

Character Sets

Lexical Units

Data Types

Declarations

References to identifiers

Scope and visibility of identifiers

Assigning Values to variables

Expressions

Error-reporting functions

SQL Functions in PL/SQL Expressions

Pragmas

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

Delimiters

Identifiers

Literals

Comments

Whitespace

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 BOOLEAN PLS_INTEGER BINARY_INTEGER REF CURSOR 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.

Collections	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.
Records	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...


Declarations

Variables

Constants

Initial Values

NOT NULL Constraint

%TYPE attribute

//declaring scalar Variable and Constant DECLARE   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 BEGIN   ... [EXCEPTION   ...] END;

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:

#!/bin/bash

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

sid=${ORACLE_SID}

sqlplus -s /nolog << EOF
connect ${login}/${password}@${sid}
... your sql code here...
quit
EOF

(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..

#!/bin/bash

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

... perform sqlplus statements

EOF