Oracle Questions

Q:

SQL vs. PL/SQL

Answer

SQL is a structured query language while PL/SQL is an extension of SQL by introducing a procedural flow. PL/SQL has blocks of statements. PL/SQL works like other procedural languages and has concepts like control statements, sequential statements, exception handling etc.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1180
Q:

What are transaction isolation levels supported by Oracle?

Answer

Oracle supports 3 transaction isolation levels: 


- Read committed (default)


- Serializable transactions


- Read only

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

1 1128
Q:

What is the Oracle Web Cache?

Answer

Oracle Web Cache is a secure reverse proxy cache and a compression engine deployed between Browser and HTTP server, Browser and Content Management server to improve the performance of web sites by caching frequently accessed content. Oracle Web Cache supports:


- Static content caching


- Dynamic content caching


- Partial Page catching


- Request Filtering.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1048
Q:

What are the Advantages of PL/SQL?

Answer

- Because of the block nature, multiple statements are processed at once thereby improving performance. 


- PL/SQL handles exceptions on catching which, action can be taken. 


- PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges.


 

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1047
Q:

Both PL/SQL and Java (or) .NET code can be used to create Oracle stored procedures and triggers. Which of the one should be used and why?

Answer

Even though both PL/SQL and Java (or) .NET can be used, PL/SQL stands above these two in terms of integration overhead. This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java. 

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1023
Q:

Explain foreign key constraint.

Answer

A foreign key is a reference to another table. It is used to establish relationships between tables. For example, relationship between employee and professor table. One employee can have multiple professors. The Primary key of employee becomes foreign key of professor.
Example:
create table employee ( id number NOT NULL, professor_id NOT NULL, Name varchar(200) Constraint prim_id Foreign key(id) references professor(professor_id) );

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1019
Q:

What is a Collection? Explain collection types.

Answer

A collection just like an array is an ordered group of elements of the same type. Each elements position is determined by a unique subscript.


 


Index by tables:- They are similar to hash arrays that allows to search for subscript values using arbitrary numbers and strings.


They can be declared as:


TYPE type_name IS TABLE OF element_type [NOT NULL]


INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];


INDEX BY key_type;


Example:


TYPE studenttyp IS TABLE OF emp%ROWTYPE


INDEX BY BINARY_INTEGER;


stud_tab studenttyp;


 


Nested tables:- they hold random number of elements and use sequential numbers as sub scripts.


They can be declared as:


TYPE type_name IS TABLE OF element_type [NOT NULL];


Example: TYPE employee_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);


 


Varrays: Holds a fixed number of elements which can be changed in run time. 


They can be declared as:


TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];


Example: TYPE Calendar IS VARRAY(366) OF DATE;

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1012
Q:

Explain the difference between trigger and stored procedure.

Answer

-  A stored procedure can accept parameters while a trigger cannot.
-  A trigger can’t return any value while stored procedures can.
-  A trigger is executed automatically on some event while a stored procedure needs to be explicitly called.
-  Triggers are used for insertions, update and deletions on tables while stored procedures are often using independently in the database.
-  A trigger cannot be written in a stored procedure. However, the reverse is not possible.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1005