Question 1

Define PL/SQL. Explain its purpose

Accepted Answer

PL/SQL is Procedural Language SQL that is an extension of SQL that results in a more structural language composed of blocks It is mainly used in writing applications that needs to be structured and has error handling

Question 2

What are the Advantages of PL/SQL?

Accepted 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

Question 3

SQL vs. PL/SQL

Accepted 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

Question 4

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?

Accepted 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

Question 5

Explain how to debug PL/SQL program.

Accepted Answer

One can debug PL/SQL program by printing the output using DBMS_OUTPUT package Put_line can be used to display a line as shown below: BEGIN dbms_outputput_line(‘Sample line'); END;

Question 6

How can we read and write operating system files from PL/SQL program?

Accepted Answer

The UTL_FILE database package can be used to read and write operating system files You need to have read /write access rights in that directory before the package can be used Example to write file: Fhandler is a variable of type UTL_FILEFILE_TYPE UTL_FILEPUTF(fHandler, 'Im writing to a file
'); Example to read file: UTL_FILEGET_LINE(fHandler, buf);

Question 7

How can we call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL?

Accepted Answer

EXECUTE IMMEDIATE command can be used to call DDL statements Example: BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE employee'; END;

Question 8

What is a Collection? Explain collection types.

Accepted 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;

Question 9

What is a PL/SQL Record data type?

Accepted Answer

A record data type represents a data type for that row in a database table It lets u define your own records and not your own fields

Question 10

Define cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN

Accepted Answer

- %FOUND This is a Boolean variable which evaluates to TRUE if the last row is successfully fetched - %NOTFOUND This is a Boolean variable which evaluates to TRUE if the last row is not successfully fetched This means there are no more rows to fetch - %ROWCOUNT Returns the number of rows fetched by the cursor - %ISOPEN If the cursor is open, it evaluates to TRUE else FALSE

Question 11

Explain some of the commonly used Predefined PL/SQL Exceptions.

Accepted Answer

1)Divide by zero – This is raised when any number is attempted to divide by zero 2)TOO MANY ROWS - A SELECT INTO statement returns more than one row 3)CASE_NOT_FOUND - No choice in the WHEN clause of a case statement is selected 4)LOGIN_DENIED - An attempt to login with an invalid username or password 5)PROGRAM_ERROR - An internal PL/SQL problem

Question 12

Explain how PL/SQL exceptions are raised.

Accepted 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;

Question 13

What is a PL/SQL package? what are its Advantages ?

Accepted Answer

A package is a collection of related PL/SQL objects The package contains a body and a specification The package specification has the declaration which is public and can be used in the PL/SQL sub programs inside the package The package body holds the implementation of all the PL/SQL objects declared in the specification Example of a PL/SQL Package CREATE OR REPLACE PACKAGE emp_data AS PROCEDURE add_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, deptno NUMBER); END emp_actions; CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE add_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seqNEXTVAL, ename, job, mgr, SYSDATE, comm, deptno); END add_employee; END emp_data; Advantages of PL/SQL packages : Packages are easier for application designing, encapsulating data, additional functionality and better performance An application has various modules which can be placed in packages and handled easier

Question 14

Define PL/SQL sequences and write syntax for a sequence

Accepted Answer

A sequence is a database object that is used to generate sequential number CREATE SEQUENCE seqname [increment] [minimum value][maximum value][start][cache][cycle] Nextval and currval lets us get the next value and current value from the sequence

Question 15

What does cache and no cache options mean while creating a sequence?

Accepted Answer

The CACHE option means how many sequences will be stored in memory for access by the application objects The performance is faster However in case of the database is down the data is memory is lost for the sequence The NO CACHE option means values are not stored in memory So there might be some performance issue

Question 16

What is an Index? Explain how to create an Index.

Accepted Answer

An index is a object which is used to improve performance during retrieval of records CREATE [UNIQUE] INDEX index_name ON employee[emp_id, emp_name,dept_id] [COMPUTE STATISTICS] The UNIQUE keyword is used when combined values of the index should be unique The COMPUTE STATISTICS during the creation of index optimizes the plan of execution of the SQL statement and improves performance

Question 17

Explain how to create users through SQL PLUS.

Accepted Answer

We need to first login to the data base as: SQLPLUS followed by user_name/password Then we need to execute the following query: CREATE USER user_name IDENTIFIED BY abcd; in this query a user_name suggest the user that is created which has a password abcd which is required for login in database

Question 18

Could you explain the areas where tuning of database is required?

Accepted Answer

The following are the areas of concern for tuning: - Memory Usage - Data Storage - Data Manipulation - Physical Storage - Logic Storage - Network Traffic Tuning Memory Usage: In Oracle 10g, we can use the Automatic Workload Repository (AWR) toolset to gather and manage statistical data but in case of Oracle 11g, we can use new initialization parameters such as MEMORY_TARGET to further control the overall memory used by Oracle It helps us to tune the database automatically when we don’t have time to read the AWR report Tuning Data Manipulation: There are two ways to tune Data manipulation in Oracle one way is through Conventional Path mode and second is Direct Path mode Tuning Physical Storage: We need to tune the physical storage because it limits the disk in database This can be done by: Distributing I/O, Striping and Mirroring

Question 19

Oracle Server supports two different forms of replication: Basic and Advanced replication. Explain difference between these.

Accepted Answer

Basic Replication : Basic replication is implemented using standard CREATE SNAPSHOT or CREATE MATERIALIZED VIEW statements It can only replicate data and not procedures, indexes replication is always one-way, and snapshot copies are read only Advanced Replication : Advanced replication supports various configurations of updatable snapshot, multi-master and update anywhere replication It is more difficult to configure but allows data and other database objects like indexes and procedures to be replicated Differences between Basic and Advanced replications: - With basic replication, data replicas provide read-only access to the table data whereas advanced replication features extend the capabilities of basic read-only replication by allowing applications to update table replicas throughout a replicated database system - With Basic Replication applications can query data from local data replicas On the other hand with advanced replication, data replicas anywhere in the system can provide both read and update access to a table's data

Question 20

What is the Oracle HTTP Server? How does it work?

Accepted Answer

Oracle HTTP Server is the Web server component of Oracle Database It is based on the Apache HTTP Server It is robust and reliable server due to following features: - Provide high availability infrastructure integration with Oracle Process Manager and Notification Server (OPMN), for process management, death detection and failover for Oracle HTTP Server processes - Provide Dynamic Monitoring Services (DMS) metrics that give runtime performance statistics for Oracle HTTP Server processes - Enable securing of transactions with Secure Sockets Layer (SSL) technology - Execute Perl scripts in the same process as the Oracle HTTP Server, or as CGI script

Technology Questions