Oracle Applications: PL SQL Interview FAQs

Question: How can I create a table from another table without copying any values from the old table?

Answer: To do this, the basic syntax is:

CREATE TABLE new_table
AS (SELECT * FROM old_table WHERE 1=2);

For example:

CREATE TABLE suppliers
AS (SELECT * FROM companies WHERE 1=2);

This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.

SQL: ALTER TABLE Statement

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
RENAME TO vendors;

This will rename the suppliers table to vendors.

Adding column(s) to a table

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD column_name column-definition;

For example:

ALTER TABLE supplier
ADD supplier_name varchar2(50);

This will add a column called supplier_name to the supplier table.

Syntax #2

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD (	column_1	column-definition,
	column_2	column-definition,
	...
	column_n	column_definition );

For example:

ALTER TABLE supplier
ADD (	supplier_name	varchar2(50),
	city	varchar2(45) );

This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table

Syntax #1

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY column_name column_type;

For example:

ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (	column_1	column_type,
	column_2	column_type,
	...
	column_n	column_type );

For example:

ALTER TABLE supplier
MODIFY (	supplier_name	varchar2(100)	not null,
	city	varchar2(75)		);

This will modify both the supplier_name and city columns.

Drop column(s) in a table

Syntax #1

To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
DROP COLUMN column_name;

For example:

ALTER TABLE supplier
DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1

Starting in Oracle 9i Release 2, you can now rename a column.

To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!

Practice Exercise #1:

Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments
(	department_id	number(10)	not null,
	department_name	varchar2(50)	not null,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

The following ALTER TABLE statement would rename the departments table to depts:

ALTER TABLE departments
RENAME TO depts;

Practice Exercise #2:

Based on the employees table below, add a column called salary that is a number(6) datatype.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following ALTER TABLE statement would add a salary column to the employees table:

ALTER TABLE employees
ADD salary number(6);

Practice Exercise #3:

Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.

CREATE TABLE customers
(	customer_id	number(10)	not null,
	customer_name	varchar2(50)	not null,
	address	varchar2(50),
	city	varchar2(50),
	state	varchar2(25),
	zip_code	varchar2(10),
	CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

The following ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

ALTER TABLE customers
ADD (	contact_name	varchar2(50),
	last_contacted	date );

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):

ALTER TABLE employees
MODIFY employee_name varchar2(75);

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

CREATE TABLE customers
(	customer_id	number(10)	not null,
	customer_name	varchar2(50),
	address	varchar2(50),
	city	varchar2(50),
	state	varchar2(25),
	zip_code	varchar2(10),
	CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

The following ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

ALTER TABLE customers
MODIFY (	customer_name	varchar2(50) not null,
	state	varchar2(2) );

Practice Exercise #6:

Based on the employees table below, drop the salary column.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	salary	number(6),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following ALTER TABLE statement would drop the salary column from the employees table:

ALTER TABLE employees
DROP COLUMN salary;

Practice Exercise #7:

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments
(	department_id	number(10)	not null,
	department_name	varchar2(50)	not null,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

The following ALTER TABLE statement would rename the department_name column to dept_name in the departments table:

ALTER TABLE departments
RENAME COLUMN department_name to dept_name;

SQL: Global Temporary tables

Global temporary tables are distinct within SQL sessions.

The basic syntax is:

CREATE GLOBAL TEMPORARY TABLE table_name ( ...);

For example:

CREATE GLOBAL TEMPORARY TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50)
)

This would create a global temporary table called supplier .

SQL: VIEWS

A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.

Creating a VIEW

The syntax for creating a VIEW is:

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;

For example:

CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';

This would create a virtual table based on the result set of the select statement. You can now query the view as follows:

SELECT *
FROM sup_orders;

Updating a VIEW

You can update a VIEW without dropping it by using the following syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;

For example:

CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';

Dropping a VIEW

The syntax for dropping a VIEW is:

DROP VIEW view_name;

For example:

DROP VIEW sup_orders;

Frequently Asked Questions

Question: Can you update the data in a view?

Answer: A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.

So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.

Question: Does the view exist if the table is dropped from the database?

Answer: Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.

If you recreate the table (that you had dropped), the view will again be fine.

Oracle/PLSQL: Case Statement

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the case statement is:

CASE [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result
END

expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.

You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

Applies To:

· Oracle 9i, Oracle 10g, Oracle 11g

The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

For Example:

Here is an example that demonstrates how to use the case statement to compare different conditions:

select
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
from suppliers;

Frequently Asked Questions

Question: Can you create a case statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.

Answer: Yes, below is an example of a case statement that evaluates two different fields.

select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;

So if supplier_name field is IBM and the supplier_type field is Hardware, then the case statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the case statement will return South office.

Oracle/PLSQL: Sequences (Autonumber)

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

For example:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.

For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

Frequently Asked Questions

One common question about sequences is:

Question: While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Answer: With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.

Question: How do we set the LASTVALUE value in an Oracle Sequence?

Answer: You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.

For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the following commands.

alter sequence seq_name
increment by 124;

select seq_name.nextval from dual;

alter sequence seq_name
increment by 1;

Now, the next value to be served by the sequence will be 225.

Oracle/PLSQL: Set Transaction

There are three transaction control functions. These are:

1. SET TRANSACTION READ ONLY;

2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. SET TRANSACTION USE ROLLBACK SEGMENT name;

Oracle/PLSQL: Lock Table

The syntax for a Lock table is:

LOCK TABLE tables IN lock_mode MODE [NOWAIT];

Tables is a comma-delimited list of tables.

Lock_mode is one of:

ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE.

NoWait specifies that the database should not wait for a lock to be released.

Oracle/PLSQL Topics: Cursors

A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.

We've categorized cursors into the following topics:

Declare a Cursor

OPEN Statement

FETCH Statement

CLOSE Statement

Cursor Attributes (%FOUND, %NOTFOUND, etc)

SELECT FOR UPDATE Statement

WHERE CURRENT OF Statement

Cursor Examples:

Procedure that outputs a dynamic PLSQL cursor

Cursor within a cursor

Cursor with variable in an "IN CLAUSE"

Oracle/PLSQL: Declare a Cursor

A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code. We'll take a look at three different syntaxes for cursors.

Cursor without parameters (simplest)

The basic syntax for a cursor without parameters is:

CURSOR cursor_name
IS
SELECT_statement;

For example, you could define a cursor called c1 as below.

CURSOR c1
IS
    SELECT course_number
      from courses_tbl
      where course_name = name_in;

The result set of this cursor is all course_numbers whose course_name matches the variable called name_in.

Below is a function that uses this cursor.

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number
        from courses_tbl
        where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Cursor with parameters

The basic syntax for a cursor with parameters is:

CURSOR cursor_name (parameter_list)
IS
SELECT_statement;

For example, you could define a cursor called c2 as below.

CURSOR c2 (subject_id_in IN varchar2)
IS
    SELECT course_number
      from courses_tbl
      where subject_id = subject_id_in;

The result set of this cursor is all course_numbers whose subject_id matches the subject_id passed to the cursor via the parameter.

Cursor with return clause

The basic syntax for a cursor with a return clause is:

CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;

For example, you could define a cursor called c3 as below.

CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
    SELECT *
      from courses_tbl
      where subject = 'Mathematics';

The result set of this cursor is all columns from the course_tbl where the subject is Mathematics.

Oracle/PLSQL: OPEN Statement

Once you've declared your cursor, the next step is to open the cursor.

The basic syntax to OPEN the cursor is:

OPEN cursor_name;

For example, you could open a cursor called c1 with the following command:

OPEN c1;

Below is a function that demonstrates how to use the OPEN statement:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number
        from courses_tbl
        where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Oracle/PLSQL: FETCH Statement

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.

The basic syntax for a FETCH statement is:

FETCH cursor_name INTO <list of variables>;

For example, you could have a cursor defined as:

CURSOR c1
IS
    SELECT course_number
      from courses_tbl
      where course_name = name_in;

The command that would be used to fetch the data from this cursor is:

FETCH c1 into cnumber;

This would fetch the first course_number into the variable called cnumber;

Below is a function that demonstrates how to use the FETCH statement.

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number
        from courses_tbl
        where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Oracle/PLSQL: Cursor Attributes

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Attribute	Explanation
%ISOPEN	- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned.
%NOTFOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned.
%ROWCOUNT	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.

Below is an example of how you might use the %NOTFOUND attribute.

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number
        from courses_tbl
        where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Oracle/PLSQL: SELECT FOR UPDATE Statement

The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

CURSOR cursor_name
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];

For example, you could use the Select For Update statement as follows:

CURSOR c1
IS
   SELECT course_number, instructor
   from courses_tbl
   FOR UPDATE of instructor;

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

Oracle/PLSQL: WHERE CURRENT OF Statement

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

The syntax for the Where Current Of statement is either:

UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number
        from courses_tbl
        where course_name = name_in
        FOR UPDATE of instructor;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

else
     UPDATE courses_tbl
        SET instructor = 'SMITH'
        WHERE CURRENT OF c1;

COMMIT;

end if;

close c1;

RETURN cnumber;

END;

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number
        from courses_tbl
        where course_name = name_in
        FOR UPDATE of instructor;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;

COMMIT;

end if;

close c1;

RETURN cnumber;

END;

Oracle/PLSQL: Procedure that outputs a dynamic PLSQL cursor

Question: In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine" table.

I've created an HTML Form where the user can enter any combination of three values to retrieve results from the "wine" table. My problem is that I need a general "select" statement that will work no matter what value(s), the user enters.

Example:

parameter_1= "Chianti"
parameter_2= "10"
parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?

SELECT * FROM wine
WHERE column1 = parameter_1
AND column2 = parameter_2
AND column3 = parameter_3;.

The output of my stored procedure must be a cursor.

Answer: To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle.

Let's take a look at how we can do this. We've divided this process into 3 steps.

Step 1 - Table Definition

First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.

create table wine
( col1 varchar2(40),
col2 varchar2(40),
col3 varchar2(40)
);

We've made this table definition very simple, for demonstration purposes.

Step 2 - Create package

Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we can use a cursor as an output parameter in our stored procedure.

create or replace PACKAGE winepkg
IS
/* Define the REF CURSOR type. */
TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE;
END winepkg;

This cursor will accept all fields from the "wine" table.

Step 3 - Create stored procedure

Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.

The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).

create or replace procedure find_wine2
(col1_in in varchar2,
   col2_in in varchar2,
   col3_in in varchar2,
   c1 out winepkg.wine_type)
as

BEGIN

   /* all columns were entered */
   IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in
      and wine.col2 = col2_in
      and wine.col3 = col3_in;

   /* col1 and col2 were entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in
      and wine.col2 = col2_in;

   /* col1 and col3 were entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in
      and wine.col3 = col3_in;

   /* col2 and col3 where entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col2 = col2_in
      and wine.col3 = col3_in;

   /* col1 was entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in;

   /* col2 was entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col2 = col2_in;

   /* col3 was entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col3 = col3_in;

END IF;

END find_wine2;

Oracle/PLSQL: Cursor within a cursor

Question: In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?

Answer: Below is an example of how to declare a cursor within a cursor.

In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

create or replace procedure MULTIPLE_CURSORS_PROC is
    v_owner varchar2(40);
    v_table_name varchar2(40);
    v_column_name varchar2(100);

    /* First cursor */
    cursor get_tables is
    select distinct tbl.owner, tbl.table_name
    from all_tables tbl
    where tbl.owner = 'SYSTEM';

    /* Second cursor */
    cursor get_columns is
    select distinct col.column_name
    from all_tab_columns col
    where col.owner = v_owner
    and col.table_name = v_table_name;

begin

      -- Open first cursor
      open get_tables;
      loop
          fetch get_tables into v_owner, v_table_name;

          open get_columns;
          loop
              fetch get_columns into v_column_name;

end loop;
close get_columns;

end loop;
close get_tables;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

Oracle/PLSQL: Cursor with variable in an "IN CLAUSE"

Question: I'm trying to use a variable in an IN CLAUSE.

Assumptions & declarations:

1. Ref_cursor is of type REF CURSOR declared in Package

2. I will to pass a comma separated Numbers as a string

3. This should be used in the query in the IN Clause

4. Execute the Query and Return the Output as REF Cursor

Something similar to the following:

Create or Replace Function func_name (inNumbers in Varchar2)
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;

Begin
    Open out_cursor
    For Select * from Table_name
    where column_name in (inNumbers);

Return out_cursor;
End;

I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?

Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:

Option #1

Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:

Create or Replace Function func_name
   Return PackageName.ref_cursor
As
   out_cursor PackageName.Ref_cursor;
   v1 varchar(2);
   v2 varchar(2);
   v3 varchar(2);

Begin

    v1 := '1';
    v2 := '2';
    v3 := '3';

    Open out_cursor
    For Select * from Table_name
    where column_name in (v1, v2, v3);

Return out_cursor;

End;

Option #2

You could try storing your values in a table. Then use a sub-select to retrieve the values.
For example:

Create or Replace Function func_name
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;

Begin

    Open out_cursor
    For Select * from Table_name
    where column_name in (select values from list_table);

Return out_cursor;

End;

In this example, we've stored our list in a table called list_table.

Oracle/PLSQL: Primary Keys

What is a primary key?

A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

Note:

In Oracle, a primary key can not contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a primary key using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)
);

For example:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

Using an ALTER TABLE statement

The syntax for creating a primary key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

For example:

ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

Drop a Primary Key

The syntax for dropping a primary key is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

Disable a Primary Key

The syntax for disabling a primary key is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
disable CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

Enable a Primary Key

The syntax for enabling a primary key is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
enable CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called supplier_pk.

Oracle/PLSQL: Foreign Keys

What is a foreign key?

A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);

For example:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
(	product_id	numeric(10)	not null,
	supplier_id	numeric(10)	not null,
	CONSTRAINT fk_supplier
	FOREIGN KEY (supplier_id)
	REFERENCES supplier(supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
(	product_id	numeric(10)	not null,
	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	CONSTRAINT fk_supplier_comp
	FOREIGN KEY (supplier_id, supplier_name)
	REFERENCES supplier(supplier_id, supplier_name)
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

Using an ALTER TABLE statement

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);

For example:

ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);

In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);

Oracle/PLSQL: Foreign Keys with cascade delete

What is a foreign key?

A foreign key means that values in one table must also appear in another table.

A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.

A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

The cascade delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to be cascade deleted when a record in the supplier table is deleted, based on supplier_id and supplier_name.

Using an ALTER TABLE statement

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE;

For example:

ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;

In this example, we've created a foreign key (with a cascade delete) called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key (with a cascade delete) with more than one field as in the example below:

ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE;

Oracle/PLSQL: Foreign Keys with "set null on delete"

What is a foreign key?

A foreign key means that values in one table must also appear in another table.

A foreign key with a "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.

A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

The delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to have the supplier_id and supplier_name fields set to null when a record in the supplier table is deleted, based on supplier_id and supplier_name.

Using an ALTER TABLE statement

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE SET NULL;

For example:

ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL;

In this example, we've created a foreign key "with a set null on delete" called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key "with a set null on delete" with more than one field as in the example below:

ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE SET NULL;

Oracle/PLSQL: Unique Constraints

What is a unique constraint?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

Note:

In Oracle, a unique constraint can not contain more than 32 columns.

A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

What is the difference between a unique constraint and a primary key?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Oracle does not permit you to create both a primary key and unique constraint with the same columns.

Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);

For example:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_unique UNIQUE (supplier_id)
);

In this example, we've created a unique constraint on the supplier table called supplier_unique. It consists of only one field - the supplier_id field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name)
);

Using an ALTER TABLE statement

The syntax for creating a unique constraint in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

Oracle/PLSQL: Check Constraints

What is a check constraint?

A check constraint allows you to specify a condition on each row in a table.

Note:

· A check constraint can NOT be defined on a VIEW.

· The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.

· A check constraint can NOT include a SUBQUERY.

A check constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a check constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

For example:

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_id
	CHECK (supplier_id BETWEEN 100 and 9999)
);

In this first example, we've created a check constraint on the suppliers table called check_supplier_id. This constraint ensures that the supplier_id field contains values between 100 and 9999.

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_name
	CHECK (supplier_name = upper(supplier_name))
);

In this second example, we've created a check constraint called check_supplier_name. This constraint ensures that the supplier_name column always contains uppercase characters.

Using an ALTER TABLE statement

The syntax for creating a check constraint in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

Oracle/PLSQL: Indexes

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Create an Index

The syntax for creating a index is:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

For example:

CREATE INDEX supplier_idx
ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx
ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx
ON supplier (supplier_name, city)
COMPUTE STATISTICS;

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

The syntax for creating a function-based index is:

CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];

For example:

CREATE INDEX supplier_idx
ON supplier (UPPER(supplier_name));

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

Rename an Index

The syntax for renaming an index is:

ALTER INDEX index_name
RENAME TO new_index_name;

For example:

ALTER INDEX supplier_idx
RENAME TO supplier_index_name;

In this example, we're renaming the index called supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

The syntax for collecting statistics on an index is:

ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;

For example:

ALTER INDEX supplier_idx
REBUILD COMPUTE STATISTICS;

In this example, we're collecting statistics for the index called supplier_idx.

Drop an Index

The syntax for dropping an index is:

DROP INDEX index_name;

For example:

DROP INDEX supplier_idx;

In this example, we're dropping an index called supplier_idx.

Oracle/PLSQL: Creating Functions

In Oracle, you can create your own functions.

The syntax for a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [function_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.

2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

The following is a simple example of a function:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    cursor c1 is
    select course_number
      from courses_tbl
      where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.

You could then reference your new function in an SQL statement as follows:

select course_name, FindCourse(course_name) as course_id
from courses
where subject = 'Mathematics';

Oracle/PLSQL: Creating Procedures

In Oracle, you can create your own procedures.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.

2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

The following is a simple example of a procedure:

CREATE OR REPLACE Procedure UpdateCourse
( name_in IN varchar2 )
IS
cnumber number;

    cursor c1 is
    select course_number
      from courses_tbl
      where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

insert into student_courses
( course_name,
course_number)
values ( name_in,
cnumber );

commit;

close c1;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

This procedure is called UpdateCourse. It has one parameter called name_in. The procedure will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.

Oracle/PLSQL Topics: Creating Triggers

Insert Triggers:

BEFORE INSERT Trigger

AFTER INSERT Trigger

Update Triggers:

BEFORE UPDATE Trigger

AFTER UPDATE Trigger

Delete Triggers:

BEFORE DELETE Trigger

AFTER DELETE Trigger

Drop Triggers:

Drop a Trigger

Disable/Enable Triggers:

Disable a Trigger

Disable all Triggers on a table

Enable a Trigger

Enable all Triggers on a table

Oracle/PLSQL: BEFORE INSERT Trigger

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.

The syntax for an BEFORE INSERT Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

· You can not create a BEFORE trigger on a view.

· You can update the :NEW values.

· You can not update the :OLD values.

Oracle/PLSQL: AFTER INSERT Trigger

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.

The syntax for an AFTER INSERT Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

· You can not create an AFTER trigger on a view.

· You can not update the :NEW values.

· You can not update the :OLD values.

For example:

If you had a table created as follows:

CREATE TABLE orders
(	order_id	number(5),
	quantity	number(4),
	cost_per_item	number(6,2),
	total_cost	number(8,2)
);

We could then create an AFTER INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN
    -- Find username of person performing the INSERT into the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       username )
    VALUES
     ( :new.order_id,
       :new.quantity,
       :new.cost_per_item,
       :new.total_cost,
       v_username );

END;

For example:

If you had a table created as follows:

CREATE TABLE orders
(	order_id	number(5),
	quantity	number(4),
	cost_per_item	number(6,2),
	total_cost	number(8,2),
	create_date	date,
	created_by	varchar2(10)
);

We could then create a BEFORE INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing INSERT into table
    SELECT user INTO v_username
    FROM dual;

-- Update create_date field to current system date
:new.create_date := sysdate;

-- Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;

END;

Oracle/PLSQL: BEFORE UPDATE Trigger

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.

The syntax for an BEFORE UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

· You can not create a BEFORE trigger on a view.

· You can update the :NEW values.

· You can not update the :OLD values.

For example:

If you had a table created as follows:

CREATE TABLE orders
(	order_id	number(5),
	quantity	number(4),
	cost_per_item	number(6,2),
	total_cost	number(8,2),
	updated_date	date,
	updated_by	varchar2(10)
);

We could then create a BEFORE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE on the table
    SELECT user INTO v_username
    FROM dual;

-- Update updated_date field to current system date
:new.updated_date := sysdate;

-- Update updated_by field to the username of the person performing the UPDATE
:new.updated_by := v_username;

END;

Oracle/PLSQL: AFTER UPDATE Trigger

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.

The syntax for an AFTER UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

· You can not create an AFTER trigger on a view.

· You can not update the :NEW values.

· You can not update the :OLD values.

For example:

If you had a table created as follows:

CREATE TABLE orders
(	order_id	number(5),
	quantity	number(4),
	cost_per_item	number(6,2),
	total_cost	number(8,2)
);

We could then create an AFTER UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE into table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity_before,
       quantity_after,
       username )
    VALUES
     ( :new.order_id,
       :old.quantity,
       :new.quantity,
       v_username );

END;

Oracle/PLSQL: BEFORE DELETE Trigger

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.

The syntax for an BEFORE DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

· You can not create a BEFORE trigger on a view.

· You can update the :NEW values.

· You can not update the :OLD values.

For example:

If you had a table created as follows:

CREATE TABLE orders
(	order_id	number(5),
	quantity	number(4),
	cost_per_item	number(6,2),
	total_cost	number(8,2)
);

We could then create a BEFORE DELETE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by )
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

Oracle/PLSQL: AFTER DELETE Trigger

An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.

The syntax for an AFTER DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

· You can not create an AFTER trigger on a view.

· You can not update the :NEW values.

· You can not update the :OLD values.

For example:

If you had a table created as follows:

CREATE TABLE orders
(	order_id	number(5),
	quantity	number(4),
	cost_per_item	number(6,2),
	total_cost	number(8,2)
);

We could then create an DELETE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by)
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

Oracle/PLSQL: Disable a Trigger

The syntax for a disabling a Trigger is:

ALTER TRIGGER trigger_name DISABLE;

Oracle/PLSQL: Disable all Triggers on a table

The syntax for a disabling all Triggers on a table is:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Oracle/PLSQL: Named System Exceptions

What is a named system exception?

Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.

Oracle has a standard set of exceptions already named as follows:

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON	ORA-01012	You tried to execute a call to Oracle before logging in.
LOGIN_DENIED	ORA-01017	You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND	ORA-01403	You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	You tried to divide a number by zero.
INVALID_NUMBER	ORA-01722	You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR	ORA-06500	You ran out of memory or memory was corrupted.
PROGRAM_ERROR	ORA-06501	This is a generic "Contact Oracle support" message because an internal problem was encountered.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN	ORA-06511	You tried to open a cursor that is already open.

The syntax for the Named System Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

END [procedure_name];

The syntax for the Named System Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

END [function_name];

Here is an example of a procedure that uses a Named System Exception:

CREATE OR REPLACE PROCEDURE add_new_supplier
(supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
IS

BEGIN
INSERT INTO suppliers (supplier_id, supplier_name )
VALUES ( supplier_id_in, supplier_name_in );

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting a supplier.');

END;

In this example, we are trapping the Named System Exception called DUP_VAL_ON_INDEX. We are also using the WHEN OTHERS clause to trap all remaining exceptions.

Oracle/PLSQL: Named Programmer-Defined Exceptions

What is a named programmer-defined exception?

Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.

The syntax for the Named Programmer-Defined Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]

exception_name EXCEPTION;

BEGIN
executable_section

RAISE exception_name ;

EXCEPTION
WHEN exception_name THEN
[statements]

WHEN OTHERS THEN
[statements]

END [procedure_name];

The syntax for the Named Programmer-Defined Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]

exception_name EXCEPTION;

BEGIN
executable_section

RAISE exception_name ;

EXCEPTION
WHEN exception_name THEN
[statements]

WHEN OTHERS THEN
[statements]

END [function_name];

Here is an example of a procedure that uses a Named Programmer-Defined Exception:

CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;

BEGIN
IF sales_in = 0 THEN
RAISE no_sales;

ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;

EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');

END;

In this example, we have declared a Named Programmer-Defined Exception called no_sales in our declaration statement with the following code:

no_sales EXCEPTION;

We've then raised the exception in the executable section of the code:

IF sales_in = 0 THEN
RAISE no_sales;

Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception called no_sales.

Finally, we tell our procedure what to do when the no_sales exception is encountered by including code in the WHEN clause:

WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');

We are also using the WHEN OTHERS clause to trap all remaining exceptions:

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');

Oracle/PLSQL: WHEN OTHERS Clause

What is a WHEN OTHERS clause?

The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.

The syntax for the WHEN OTHERS clause in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

END [procedure_name];

The syntax for the WHEN OTHERS clause in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

END [function_name];

Here is an example of a procedure that uses a WHEN OTHERS clause:

CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;

BEGIN
IF sales_in = 0 THEN
RAISE no_sales;

ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate order_id.');

WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');

END;

In this example, if an exception is encountered that is not a DUP_VAL_ON_INDEX or a no_sales, it will be trapped by the WHEN OTHERS clause.

Frequently Asked Questions

Question: Is there any way to get the ORA error number (and/or description) for the errors that will fall into OTHERS?

Something like:

WHEN OTHERS THEN
'Error number ' & Err.Number& ' has happen.'

Answer: Yes, you can use SQLCODE function to retrieve the error number and SQLERRM function to retrieve the error message.

For example, you could raise the error as follows:

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;

Oracle/PLSQL: SQLCODE Function

What does the SQLCODE Function do?

The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:

EXCEPTION
WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

END [procedure_name];

You could use the SQLCODE function to raise an error as follows:

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;

Learn more about the SQLERRM Function.

Oracle/PLSQL: SQLERRM Function

What does the SQLERRM Function do?

The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:

EXCEPTION
WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

END [procedure_name];

You could use the SQLERRM function to raise an error as follows:

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;

Oracle/PLSQL Topics: Oracle Error Messages

The following is a listing of Oracle Error Messages:

00001-00899

00900-00999

ORA-00900	ORA-00917	ORA-00933	ORA-00957
ORA-00902	ORA-00918	ORA-00934	ORA-00960
ORA-00903	ORA-00919	ORA-00935	ORA-00962
ORA-00904	ORA-00920	ORA-00936	ORA-00971
ORA-00905	ORA-00923	ORA-00937	ORA-00972
ORA-00906	ORA-00924	ORA-00938	ORA-00975
ORA-00907	ORA-00925	ORA-00939	ORA-00979
ORA-00908	ORA-00926	ORA-00942	ORA-00980
ORA-00909	ORA-00927	ORA-00946	ORA-00984
ORA-00910	ORA-00928	ORA-00947	ORA-00985
ORA-00911	ORA-00931	ORA-00948	ORA-00995
ORA-00913	ORA-00932	ORA-00955

01000-01399

01400-01499

01500-01999

02000-06499

06500-09999

ORA-06500	ORA-06508	ORA-06514	ORA-06572
ORA-06501	ORA-06510	ORA-06515	ORA-06575
ORA-06502	ORA-06511	ORA-06550	ORA-08002
ORA-06503	ORA-06512	ORA-06564	ORA-08103

10000 - 12999

ORA-12154

Oracle/PLSQL: Grant/Revoke Privileges

Grant Privileges on Tables

You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Privilege	Description
Select	Ability to query the table with a select statement.
Insert	Ability to add new rows to the table with the insert statement.
Update	Ability to update rows in the table with the update statement.
Delete	Ability to delete rows from the table with the delete statement.
References	Ability to create a constraint that refers to the table.
Alter	Ability to change the table definition with the alter table statement.
Index	Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:

grant privileges on object to user;

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:

grant select, insert, update, delete on suppliers to smithj;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to smithj;

If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:

grant select on suppliers to public;

Revoke Privileges on Tables

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from user;

For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:

revoke delete on suppliers from anderson;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke all on suppliers from public;

Grant Privileges on Functions/Procedures

When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege	Description
Execute	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to user;

For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:

grant execute on Find_Value to smithj;

If you wanted to grant all users the ability to execute this function, you would execute the following:

grant execute on Find_Value to public;

Revoke Privileges on Functions/Procedures

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from user;

If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:

revoke execute on Find_Value from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke execute on Find_Value from public;

Oracle/PLSQL: Roles

A role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.

Creating a Role

To create a role, you must have CREATE ROLE system privileges.

The syntax for creating a role is:

CREATE ROLE role_name
[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

Note: If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.

The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.

For example:

CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_role
IDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

Grant Privileges (on Tables) to Roles

You can grant roles various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Privilege	Description
Select	Ability to query the table with a select statement.
Insert	Ability to add new rows to the table with the insert statement.
Update	Ability to update rows in the table with the update statement.
Delete	Ability to delete rows from the table with the delete statement.
References	Ability to create a constraint that refers to the table.
Alter	Ability to change the table definition with the alter table statement.
Index	Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:

grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a role named test_role, you would execute the following statement:

grant select, insert, update, delete on suppliers to test_role;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to test_role;

Revoke Privileges (on Tables) to Roles

The syntax for revoking privileges on a table is:

revoke privileges on object from role_name;

For example, if you wanted to revoke delete privileges on a table called suppliers from a role named test_role, you would execute the following statement:

revoke delete on suppliers from test_role;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from test_role;

Grant Privileges (on Functions/Procedures) to Roles

When dealing with functions and procedures, you can grant roles the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege	Description
Execute	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to role_name;

For example, if you had a function called Find_Value and you wanted to grant execute access to the role named test_role, you would execute the following statement:

grant execute on Find_Value to test_role;

Revoke Privileges (on Functions/Procedures) to Roles

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a role. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from role_name;

If you wanted to revoke execute privileges on a function called Find_Value from a role named test_role, you would execute the following statement:

revoke execute on Find_Value from test_role;

Granting the Role to a User

Now, that you've created the role and assigned the privileges to the role, you'll need to grant the role to specific users.

The syntax to grant a role to a user is:

GRANT role_name TO user_name;

For example:

GRANT test_role to smithj;

This example would grant the role called test_role to the user named smithj.

The SET ROLE statement

The SET ROLE statement allows you to enable or disable a role for a current session.

When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The syntax for the SET ROLE statement is:

SET ROLE
( role_name [ IDENTIFIED BY password ]
| ALL [EXCEPT role1, role2, ... ]
| NONE );

The role_name phrase is the name of the role that you wish to enable.

The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.

The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.

The NONE phrase disables all roles for the current session. (including all default roles)

For example:

SET ROLE test_role IDENTIFIED BY test123;

This example would enable the role called test_role with a password of test123.

Setting a role as DEFAULT Role

A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.

The syntax for setting a role as a DEFAULT role is:

ALTER USER user_name
DEFAULT ROLE
( role_name
| ALL [EXCEPT role1, role2, ... ]
| NONE );

The user_name phrase is the name of the user whose role you are setting as DEFAULT.

The role_name phrase is the name of the role that you wish to set as DEFAULT.

The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.

The NONE phrase disables all roles as DEFAULT.

For example:

ALTER USER smithj
DEFAULT ROLE
test_role;

This example would set the role called test_role as a DEFAULT role for the user named smithj.

ALTER USER smithj
DEFAULT ROLE
ALL;

This example would set all roles assigned to smithj as DEFAULT.

ALTER USER smithj
DEFAULT ROLE
ALL EXCEPT test_role;

This example would set all roles assigned to smithj as DEFAULT, except for the role called test_role.

Dropping a Role

It is also possible to drop a role. The syntax for dropping a role is:

DROP ROLE role_name;

For example:

DROP ROLE test_role;

This drop statement would drop the role called test_role that we defined earlier.

Oracle/PLSQL: Change a user's password in Oracle

Question: How do I change the password for a user in Oracle?

Answer: To change a user's password in Oracle, you need to execute the alter user command.

The syntax for changing a password is:

alter user user_name identified by new_password;

user_name is the user whose password you wish to change.

new_password is the new password to assign.

For example:

If you wanted to reset the password for a user named smithj, and you wanted to set the new password to autumn, you would run the following command:

alter user smithj identified by autumn;

Oracle/PLSQL: Synonyms

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Creating or replacing a synonym

The syntax for creating a synonym is:

create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];

The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:

table	package
view	materialized view
sequence	java class schema object
stored procedure	user-defined object
function	synonym

For example:

create public synonym suppliers
for app.suppliers;

This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

select * from suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

create or replace public synonym suppliers
for app.suppliers;

Dropping a synonym

It is also possible to drop a synonym. The syntax for dropping a synonym is:

drop [public] synonym [schema .] synonym_name [force];

The public phrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.

The force phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

For example:

drop public synonym suppliers;

This drop statement would drop the synonym called suppliers that we defined earlier.

ORA-00001	ORA-00051	ORA-00068	ORA-00257
ORA-00018	ORA-00054	ORA-00071	ORA-00301
ORA-00020	ORA-00057	ORA-00078	ORA-00304
ORA-00023	ORA-00058	ORA-00100	ORA-00361
ORA-00028	ORA-00060	ORA-00107	ORA-00401
ORA-00034	ORA-00063	ORA-00201

ORA-01000	ORA-01013	ORA-01039	ORA-01114
ORA-01001	ORA-01014	ORA-01040	ORA-01133
ORA-01002	ORA-01017	ORA-01042	ORA-01200
ORA-01004	ORA-01023	ORA-01045	ORA-01234
ORA-01005	ORA-01031	ORA-01052
ORA-01006	ORA-01033	ORA-01074
ORA-01007	ORA-01034	ORA-01089
ORA-01008	ORA-01035	ORA-01109
ORA-01012	ORA-01037	ORA-01113

ORA-01400	ORA-01422	ORA-01435	ORA-01452
ORA-01401	ORA-01423	ORA-01436	ORA-01453
ORA-01402	ORA-01424	ORA-01437	ORA-01454
ORA-01403	ORA-01425	ORA-01438	ORA-01461
ORA-01404	ORA-01426	ORA-01439	ORA-01465
ORA-01405	ORA-01427	ORA-01440	ORA-01468
ORA-01406	ORA-01428	ORA-01441	ORA-01471
ORA-01407	ORA-01429	ORA-01442	ORA-01476
ORA-01408	ORA-01430	ORA-01446
ORA-01416	ORA-01432	ORA-01448
ORA-01417	ORA-01433	ORA-01449
ORA-01418	ORA-01434	ORA-01451

ORA-01504	ORA-01719	ORA-01785	ORA-01846
ORA-01506	ORA-01722	ORA-01789	ORA-01847
ORA-01555	ORA-01723	ORA-01790	ORA-01858
ORA-01632	ORA-01724	ORA-01810	ORA-01861
ORA-01652	ORA-01727	ORA-01818	ORA-01916
ORA-01653	ORA-01728	ORA-01820	ORA-01918
ORA-01700	ORA-01747	ORA-01821	ORA-01950
ORA-01704	ORA-01756	ORA-01830
ORA-01710	ORA-01775	ORA-01839
ORA-01711	ORA-01779	ORA-01843

ORA-02003	ORA-02268	ORA-02437	ORA-04091
ORA-02011	ORA-02270	ORA-02444	ORA-04092
ORA-02024	ORA-02290	ORA-02449	ORA-04098
ORA-02069	ORA-02291	ORA-03113
ORA-02086	ORA-02292	ORA-04000
ORA-02256	ORA-02293	ORA-04031
ORA-02260	ORA-02298	ORA-04088

Pages

Friday, February 22, 2013

PL SQL Interview FAQs

No comments:

Post a Comment