Oracle SQL Commands: Part 4

13. ALTER USER Command

We generally we use the ALTER USER Command to create users in the database and once the users are created we need to do some changes, such as Password Change, which is one of the common changes we do and for this we use the ALTER USER Command.

Syntax

ALTER USER Username IDENTIFIED BY New_Password;

Example

ALTER USER Joseph IDENTIFIED BY JJJPh;

14. ALTER TRIGGER Command

In response to the specific event a trigger fires as a code block. ALTER TRIGGER changes the property of a trigger.

Syntax

ALTER TRIGGER trigger_name COMPILE;

ALTER TRIGGER trigger_name ENABLE;

ALTER TRIGGER trigger_name DISABLE;

Example

To Alter a Trigger first we create a table "STUDENTS" and then create a trigger as in the following:

Table

CREATE TABLE Product

(

Prod_ID Number(10),

Prod_Name Varchar2(20),

Quantity Number(5),

Price number(5,2),

Create_date date,

Created_by varchar2(10)

);

STUDENTS

Stu_ID	Stu_Name	RollNo	Stream
101	Shreya	5	Commerce
102	Rohan	9	Science
103	Ronit	17	Science

Trigger

CREATE OR REPLACE TRIGGER Product_before_insert
BEFORE INSERT
ON Products
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
-- Find username of person performing INSERT into table

SELECT user INTO username
FROM dual;

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

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

Now, Alter the trigger Product_before_insert as in the following:

ALTER TRIGGER Product_before_insert DISABLE;

15. ALTER SEQUENCE Command

To change the behavior of an existing sequence the ALTER SEQUENCE COMMAND is used. It will alter the values of an existing Oracle sequence.

Syntax

ALTER SEQUENCE seq_name

increment by Value(i);

Example

ALTER USER Username IDENTIFIED BY New_Password;

ALTER SEQUENCE Master

Increment by 124

SELECT Master.nextval from dual;

ALTER SEQUENCE master

IMZCREMENT BY 1;

16. ALTER SESSION Command

ALTER SESSION Command is used to modify any parameter or condition that can affect the connection to our database. Also it is used when the database has multiple schema owners.

Syntax and Example

To change the Schema

To add "SYSADM" to all your SQL statements, change the current schema setting to "SYSADM":

ALTERSESSION SETCURRENT_SCHEMA =SYSADM;
To change NLS_DATE_FORMAT

To set the date format instead of using the "TO_DATE" function in every SQL statement:
ALTERSESSION SETNLS_DATE_FORMAT ='YYYY-MM-DD';

17. DROP TABLE Command

To remove the objects from the database we use the DROP Command. Here we are talking about the DROP TABLE Command, while dropping a table all the rows, indexes and privileges will also be removed. It may also be used to remove a database link.

Syntax

DROP TABLE "table_Name";

Example

Suppose we want to drop the following table created as in the following:

CREATE TABLE Student

(

Stu_ID Number(10),

Stu_Name varchar2(20),

RollNo Number(10),

Stream varchar2(20)

);

STUDENTS

Stu_ID	Stu_Name	RollNo	Stream
101	Shreya	5	Commerce
102	Rohan	9	Science
103	Ronit	17	Science

DROP TABLE "Student";

18. DROP VIEW Command

If a view exists in in the database and we want to drop it, then a DROP VIEW command allows the user to select a view and drop it.

Syntax

DROP VIEW [IF EXISTS]

view_name [, view_name] ...

[RESTRICT | CASCADE]

DROP VIEW [schema.]view [CASCADE CONSTRAINTS]

Example

Suppose we want to drop the View created for the following table.

EMPLOYEE TABLE

Emp_ID	Emp_Name	ContactNo	Designation	Salary
111	Jimmy	CommercelNo	Soft. Eng	50Km
222	Alberta	ScienceNo	Tech Lead	65KM
333	Harrison	Scienceo	Sr. Soft. Engg	60km

CREATE VIEW MaterView AS

SELECT *

FROM Employee

WITH READ ONLY CONSTRAINT Master_view_read_only;

Now, drop the preceding View as in the following:

DROP VIEW MasterView;

19. DROP USER Command

The DROP USER command is used to drop the user that was created earlier. The user can be dropped using the following syntax:

Syntax

If the user does not own any object in the Schema:

DROPUSER user_name;
If the user owns an object in the Schema:

DROPUSER user_name[ CASCADE ];

Example

Suppose we have a user that ws created using the following:

CREATE USER Joseph
IDENTIFIED BY JSH123;

Now, If Joseph owns the objects in the Schema then use:

DROP USER Joseph CASCADE;

Otherwise:

DROP USER Joseph;

20. DROP INDEX Command

The purpose of the DROP INDEX Command is to delete the previously created index from the database. Dropping an index from an Oracle Database invalidates all objects that depend on the underlying table, including functions, procedures, views, packages and package bodies.

Syntax

DROP INDEX Index_Name;

Example

Assume the following Index created for a Customer Table:

CUSTOMERS

Cust_ID	Stu_Name	City
1001	Jimmy	Jalandar

Index

CREATE INDEX MyIndex
ON Customer (Cust_Name);

This query will create an index called "MyIndex" on the Customer table. Now, drop the index called "MyIndex".

DROP INDEX MyIndex; DROP TRIGGER trigger_name;

21. DROP TRIGGER Command

The DROP TRIGGER Command removes one or more triggers from the database. But, it is a necessity for the trigger to be dropped to be in your own schema and if you want to drop any trigger from another's schema then you must have ADMIN rights.

Syntax

DROP TRIGGER trigger_name;

Example: The following is the trigger created for the products table:

CREATE TABLE Product

(

Prod_ID Number(10),

Prod_Name Varchar2(20),

Quantity Number(5),

Price number(5,2),

Create_date date,

Created_by varchar2(10)

);

PRODUCTS

Prod_id	Prod_Name	Quantity	Price	Create_date	CCreated_by

Trigger using BEFORE UPDATE

CREATE OR REPLACE TRIGGER Products_before_update

BEFORE UPDATE

ON Products

FOR EACH ROW

DECLARE

username varchar2(15);

BEGIN

--To search username of person performing UPDATE on the table

SELECT user INTO username

FROM dual;

-- Update updated_date field to current system date

:new.updated_date := systemdate;

-- Update updated_by field to the username of the person performing the UPDATE

:new.updated_by := username;

END;

Now to drop it use the following query.

DROP TRIGGER Product_Before_Update;

22. DROP PROCEDURE Command

If you want to drop any procedure that is no longer in use you can remove using the DROP PROCEDURE Command.

Syntax

DROP PROCEDURE [schema.]procedure [,...n];

Example

The following is the procedure creating the Employee table.

Emp_ID	Emp_Name	Designation	Salary
TCS008	Vivan	Project Manager	1Lac
TCS030	Seema	Tech Lead	80K

CREATE OR REPLACE PROCEDURE
Employee_details
IS
Cursor Emp_Cur IS
SELECT Name, Designation, Salary FROM Employee
emp_rec emp_cur%rowtype;
BEGIN

FOR Emp_rec in Sales_Cur

LOOP

Dbms_output.put_line(Emp_cur.Name || '||emp_cur.Designation ||' '||Emp_cur.Salary);

END LOOP

END;

The procedure for the preceding table also created. Let's drop the procedure.

DROP PROCEDURE Employee_details;

Previous article: Oracle SQL Commands : Part 3
Next article:Oracle SQL Commands : Part 5