Oracle PL/SQL Quick Help : Developers

A PL/SQL Block consists of three sections:
1.The Declaration section (optional).
2.The Execution section (mandatory).
3.The Exception (or Error) Handling section (optional).
Sample below :
     Variable declaration
     Program Execution
Exception handling
PL/SQL Variables
The General Syntax to declare a variable is:
variable_name datatype [NOT NULL := value ];
variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT value is also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.
Example :
DECLARE Empcode number(4);
EmpName varchar2(10) NOT NULL := “Abhi”;

assign values to variables
       variable_name:= value;
       SELECT column_nameINTO variable_name FROM table_name [WHERE condition];

Scope of Variables

       Local variables – These are declared in a inner block and cannot be referenced by outside Blocks.
       Global variables – These are declared in a outer block and can be referenced by its itself and by its inner blocks.
       Example :
 var_num number;  // Global variables
var_num := 100;
var_sqr number;  // Local variables
var_sqr := var_num * var_num2

PL/SQL Constants

The General Syntax to declare a constant is: constant_name CONSTANT datatype := VALUE;

Example :
DECLARE empCode CONSTANT number (3) := 10;

Conditional Statements in PL/SQL
IF condition 1 THEN  
statement 1;  
statement 2;
ELSIF condtion2 THEN  
statement 3;
statement 4;
Parameters in Procedure and Functions
1)      IN type parameter: These types of parameters are used to send values to stored procedures.
CREATE [OR REPLACE] PROCEDURE procedure_name ( param_name1 IN datatype, param_name12 IN datatype … )
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)

Types of Exception

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

Named System Exceptions
When you open a cursor that is already open.
When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.
When a SELECT…INTO clause does not return any row from a table.
When you SELECT or fetch more than one row into a record or variable.
When you attempt to divide a number by zero.
Example :
Execution section
dbms_output.put_line (‘No record found.’);
User-defined Exceptions
userDefinedExp EXCEPTION;  
RAISE userDefinedExp;     
EXCEPTION    WHEN userDefinedExp THEN         
raise_application_error(-2100, ‘The User Defined Exception.’);
       RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
       RAISE_APPLICATION_ERROR (error_number, error_message);
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
Unnamed System Exceptions
rec_exception EXCEPTION;   
EXCEPTION_INIT (rec_exception, -2296);
WHEN rec_exception   THEN
Dbms_output.put_line(‘Unnamed System Exceptions.’);

What are few Oracle DDL statements or commands ?

Below are few  DDL commands to create, alter, drop, and rename tables.


  • Create a table.
  • Create a table based on another table by using a subquery.


  • Modify table structures.
  • Change column widths, change column data types, and add columns.


  • Remove rows and a table structure.
  • Once executed, this statement cannot be rolled back.


  • Rename a table, view, sequence, or synonym.


  • Remove all rows from a table and release the storage space used by the table.


  •  Add a comment to a table or a column.
  • Query the data dictionary to view the comment.

Oracle 9i Tutorial : UPDATE Statement Syntax

UPDATE Statement Syntax

Update Statement is used to modify existing rows in table.

Below is Update State Syntax :

UPDATE  table
SET  column = value [, column = value, …]
[WHERE   condition];

In the syntax:

  table  is the name of the table
  column  is the name of the column in the table to populate
  value  is the corresponding value or subquery for the column
  condition  identifies the rows to be updated and is composed of column names   expressions, constants, subqueries, and comparison operators

Updating Rows in a Table

Specific row or rows are modified if you specify the WHERE clause.
   •All rows in the table are modified if you omit the WHERE clause.

Updating Rows Based on Another Table

You can use subqueries in UPDATE statements to update rows in a table. The example on the slide updates the COPY_EMP table based on the values from the EMPLOYEES table. It changes the department number of all employees with employee 200’s job ID to employee 100’s current department number.

Oracle 9i Tutorial : Adding Comments to Table

Adding a Comment to a Table

You can add a comment of up to 2,000 bytes about a column, table, view, or snapshot by using the COMMENT statement. The comment is stored in the data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:
    COMMENT ON TABLE table | COLUMN table.column
      IS text‘;
In the syntax:
  table  is the name of the table
column  is the name of the column in a table
text  is the text of the comment
You can drop a comment from the database by setting it to empty string ():
    COMMENT ON TABLE  employees IS ‘;

Oracle 9i Tutorial : ALTER TABLE Statement

 ALTER TABLE Statement

         After you create a table, you may need to change the table structure because: you omitted column, your column definition needs to be changed, or you need to remove columns. You can do this by using the ALTER TABLE statement.
Use the ALTER TABLE statement to:
Add a new column
Alter table employee add(manager number(4));
Modify an existing column
Alter table employee modify(empname varchar2(10));
Define a default value for the new column
Alter table employee modify(hiredate date default sysdate);
Drop a column
                     – Alter table employee drop column manager;

Oracle 9i Tutorial : Creating a Table by Using a Subquery Syntax

Creating a Table  by Using a Subquery Syntax

Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
Match the number of specified columns to the number of subquery columns.

Define columns with column names and default values.

Creating a Table from Rows in Another Table
A second method for creating a table is to apply the AS subquery clause, which both creates the table and inserts rows returned from the subquery.
In the syntax:
  table  is the name of the table
  column  is the name of the column, default value, and integrity constraint
  subquery  is the SELECT statement that defines the set of rows to be inserted into   the new table
The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.
The column definition can contain only the column name and default value.
If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the column names in the subquery.
The integrity rules are not passed onto the new table, only the column data type definitions.
Example :