Friday, February 22, 2013

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:


Cursor Examples:


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;
OR
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.
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 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.
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 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:
Update Triggers:
Delete Triggers:
Drop Triggers:
Disable/Enable Triggers:


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:
  1. You executed a SELECT INTO statement and no rows were returned.
  2. You referenced an uninitialized row in a table.
  3. 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


01000-01399






01400-01499





01500-01999




02000-06499





06500-09999

10000 - 12999




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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect