Oracle Questions

Q:

What are the types of constraints avaialable in oracle?

Answer

Oracle constraints are used to maintain consistent of data and ensure the data is properly maintained. A constraint is more or less a restriction we try to apply on a table.
Types of constraints:
- Check constraints
- NOT NULL constraint
- PRIMARY KEY constraint
- REFERENCES constraint
- UNIQUE constraint

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1374
Q:

What is a Trigger? Explain Types of PL/SQL Triggers.

Answer

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.


Syntax:


CREATE OR REPLACE TRIGGER [Trigger Name] [Before / After / Instead Of]


ON [schema].[table]


<PL/SQL subprogram>


 


Types of PL/SQL triggers : 


> Row trigger          - The trigger fires for each ROW affected.


> Statement trigger - The trigger is fired once when the condition is matched


> Before and After trigger - The BEFORE trigger run the trigger action before the insert, update or delete statement. The AFTER trigger runs the trigger action after the insert, update or delete statement is executes.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

1 1370
Q:

How to recover a dropped table?

Answer

Dropped tables can be recovered using DROP TABLE flashback. It works the way recycle bin works.
Example:
FLASHBACK TABLE EMPLOYEE TO BEFORE DROP;
The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1362
Q:

Explain how PL/SQL exceptions are raised.

Answer

PL/SQL exceptions are raised using the RAISE command. This command is used when exceptions are defined by programmer and not implicit exceptions.


 


Example:


Declare and raising an exception:


DECLARE


short_of_attendance EXCEPTION;


min_attendance NUMBER(4);


BEGIN


...


IF min_attendance < 10 THEN


RAISE short_of_attendance;


END IF;


EXCEPTION


WHEN short_of_attendance THEN


-- handle the error


END;

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1337
Q:

What is a synonym? What are its various types?

Answer

A synonym can be called as an alias for a table, view, sequence or program unit. It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1336
Q:

What is a view?

Answer

It is virtual table which is defined as a stored procedure based on one or more tables.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1326
Q:

What are the uses of synonyms?

Answer

- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

1 1319
Q:

What do you mean by a tablespace?

Answer

- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1307