Set - 5

Question 1 :

If left out, which of the following would cause an infinite loop to occur in a simple loop?

Answer :

1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT


Question 2 :

Which line in the following statement will produce an error?

Answer :

1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.


Question 3 :

The command used to open a CURSOR FOR loop is

Answer :

1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.


Question 4 :

What happens when rows are found using a FETCH statement

Answer :

1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values


Question 5 :

Under which circumstance must you recompile the package body after recompiling the package specification?

Answer :

1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs


Question 6 :

Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?

Answer :

1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed


Question 7 :

Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?

Answer :

1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE


Question 8 :

Examine this code

Answer :

BEGIN
theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
END;


Question 9 :

For this code to be successful, what must be true?

Answer :

1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.


Question 10 :

A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?

Answer :

1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL


Question 11 :

A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?

Answer :

1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL


Question 12 :

How to implement ISNUMERIC function in SQL *Plus ?

Answer :

Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)

Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
It returns 0 if it is a number, 1 if it is not.


Question 13 :

How to Select last N records from a Table?

Answer :

select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)
Here N = 10
The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.
Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno


Question 14 :

What are steps required tuning this query to improve its performance?

Answer :

-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO

-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:

SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;


Question 15 :

What is the difference between Truncate and Delete interms of Referential Integrity?

Answer :

DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.


Question 16 :

What does preemptive in preemptive multitasking mean ?

Answer :

Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.


Question 17 :

What does the OLTP stands for ?

Answer :

OLTP stands for On Line Transaction Processing


Question 18 :

What is the most important requirement for OLTP ?

Answer :

OLTP requires real time response.


Question 19 :

In a client server environment, what would be the major work that the client deals with ?

Answer :

The client deals with the user interface part of the system.


Question 20 :

Why is the most of the processing done at the sever ?

Answer :

To reduce the network traffic and for application sharing and implementing business rules.


Question 21 :

What does teh term upsizing refer to ?

Answer :

Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing.


Question 22 :

What does one do when one is rightsizing ?

Answer :

With rightsizing, one would move applications to the most appropriate server platforms.


Question 23 :

What does the term downsizing refer to ?

Answer :

A host based application is re-engineered to run in smaller or LAN based environment.


Question 24 :

What is event trigger ?

Answer :

An event trigger, a segment of code which is associated with each event and is fired when the event occurs.


Question 25 :

Why do stored procedures reduce network traffic ?

Answer :

When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.