About us |  Help us improve
intelliVERB - Get a fair search of the Internet !
Visual studio .net
SQL Server
Oracle
Main  >  DCL Commands Email this page to friend
COMMIT
GRANT
REVOKE
ROLLBACK
SAVEPOINT
SET TRANSACTION
 
 
 
 
 

DCL: Data Control Language

DCL are used to configure and control database objects.

GRANT : gives user rights and privileges on database objects or schema. 

REVOKE : removes or restricts user rights or privileges on database objects.

COMMIT : make durable all changes done to the data.

ROLLBACK : restore data to its state prior to user changes

SAVEPOINT : set a point to which a ROLLBACK is possible.

SET TRANSACTION : Modify a transaction properties.


PLSQL COMMIT Top

COMMIT [WORK]: Makes changes permanent

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA00120’,’Max‘, ‘Johnson’, ‘Sales’);
COMMIT;
INSERT INTO EMPL_Salaries (Empl_ID, Amount , Vacation ) VALUES (‘SA00120’,1500,5);
COMMIT WORK;

COMMIT COMMENT [Comment_Text]; Add a text to the commit instruction. The maximum size for Comment_Text=255. Oracle stores COMMIT COMMENT into data dictionary.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘HR0457’,’Michelle‘, ‘LEE’, HR’);
COMMIT COMMENT 'New employee in the house!';

COMMIT WRITE FORCE [Text], [Number]; This command Manually force changes to be permanent. It is mostly used in case of doubtful distributed transactions.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘ACT45210’,’Vincent‘, ‘DeAndre’, ‘Accounting’);
COMMIT WRITE FORCE;

COMMIT WRITE BATCH [WAIT | NOWAIT]; This commit statement is perform while redo instructions are written in the buffer redo to be batch move into the online redo log.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA45010’,Jackie‘, ‘Fullton’, ‘Sales’);
COMMIT WRITE BATCH WAIT;

COMMIT WRITE [IMMEDIATE | WAIT | NOWAIT]; Making a commit with these options defines the way Oracle log writer process will proceed towrite the redo instruction in the redo log.

COMMIT WRITE IMMEDIATE; Makes a commit and forces Oracle Log writer process to write the redo instruction in the online redo log immediately.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA004520’,’Denise‘, ‘Brown’, ‘Sales’);
COMMIT WRITE IMMEDIATE;

COMMIT WRITE NOWAIT: When this command is run, Commit statement will not wait for the redo instruction to be written into the redo log before it takes effects.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA450120’,’Eunice‘, ‘Hernandez’, ‘Sales’);
COMMIT WRITE NOWAIT;

COMMIT WRITE WAIT: This command forces Commit instruction to return the handle only when the corresponding redo statement is permanently written in the redo lo.
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA451450’,’Gerard‘, ‘Dufour’, ‘Sales’);
COMMIT WRITE WAIT;


PLSQL GRANT Top

The GRANT command is use to assign Object Privileges to users or group of users.

Syntax:
GRANT [privilege|privilege list|ALL|EXECUTE] ON [object] TO [schema];

Specify just one privilege name to Grant A Single Privilege
If multiple comma separated privilege are specify as a list the Grant will be made for Multiple Privileges.
Use the keyword ALL to Grant All Privileges
Use the keyword EXECUTE to Grant Execute privilege to a schema.

Example:
GRANT INSERT, UPDATE ON Empl TO HR_Clerk;
GRANT ALL ON Payroll TO HR_Manager;
GRANT EXECUTE ON fCalculateTaxes TO HR_Assc;

Grant Column Privileges

Syntax:
GRANT [privilege] ([column]) ON [table] TO [schema];

Example:
GRANT UPDATE (Salary, Taxes)ON Payroll TO HR_Assc;


PLSQL REVOKE Top

The REVOKE command is use to retrieve Object Privileges from  users or group of users.

Syntax:
REVOKE [privilege|privilege list|ALL|EXECUTE] ON [object] FROM [schema];

Specify just one privilege name to Revoke A Single Privilege
If multiple comma separated privilege are specify as a list the Revoke will be made for Multiple Privileges.
Use the keyword ALL to Revoke All Privileges
Use the keyword EXECUTE to Revoke Execute privilege from a schema.

Example:
REVOKE INSERT, UPDATE ON Empl FROM HR_Clerk;
REVOKE ALL ON Payroll FROM HR_Manager;
REVOKE EXECUTE ON fCalculateTaxes FROM HR_Assc;

Revoke Column Privileges

Syntax:
REVOKE [privilege] ([column]) ON [table] FROM [schema];

Example:
REVOKE UPDATE (Salary, Taxes) ON Payroll FROM HR_Assc;


PLSQL ROLLBACK Top

ROLLBACK [WORK]: Ends the current transaction and will undo any changes made since the transaction began. If used with save point it will undo part of a transaction.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA457580’,’Amanda‘, ‘Suarez’, ‘Sales’);
ROLLBACK;


PLSQL SAVEPOINT Top

SAVEPOINT [Point_Id]: Marks the current point in the processing of a transaction. For Rollback to work with savepoint use the complete word ‘ROLLBACK’, just ‘ROLL’ will fail.

BEGIN
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘HR12054’,’Jeremie‘, ‘Yates’, ‘HR’);
  SAVEPOINT Benefits;

 INSERT INTO EMPL_Salaries (Empl_ID, Amount , Vacation ) VALUES (‘HR12054’,1750,3);
 INSERT INTO EMPL_Parking (Empl_ID, Days , Amount ) VALUES (‘HR12054’,5,600);
 INSERT INTO EMPL_Lunch (Empl_ID, Amount ) VALUES (‘HR12054’,450);

COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO SAVEPOINT Benefits;
    COMMIT;
END RegisterEmployee;

You last visited
Event log
Triggers Oracle
Windows service
DDL Oracle
Oracle basic functions
Remoting
Web Objects
Inheritance C# .net
XML
Assemblies
DDL Sql Server
Sql Server Basic functions
COM +
DML Sql Server
 
 
Make a secure donation now with PayPal!