Oracle PL/Sql 10g MCQ (FREE Multiple Choice Questions)

1: In Pl/Sql, if the where clause evaluates to a set of data, which lock is used?

A. Row Level Lock

B. Page Level lock

C. Column Level lock

D. Exclusive Lock

2: Which of the following is a benefit of using procedures and functions?

A. Procedures and Function increases the number of calls to the database

B. Procedures and Function are reparsed for multiple users by exploiting shared SQL area

C. Procedures and Function avoid reparsing for multiple users by exploiting shared SQL areas

D. Testing of procedures and functions requires the database to be restarted, to clear out shared SQL areas and future access

3: How can migration be done from a LONG to a LOB data type for a column?

A. Use the DBMS_MANAGE_LOB.MIGRATE procedure

B. Use the UTL_MANAGE_LOB.MIGRATE procedure

C. Use the DBMS_LOB.MIGRATE procedure

D. You cannot migrate from a LONG to a LOB data type for a column

E. Using ALTER TABLE statement

4: Which of the following statements is true?

A. Stored functions can be called from the SELECT and WHERE clauses only

B. Stored functions do not permit calculations that involve database links in a distributed environment

C. Stored functions cannot manipulate new types of data, such as longitude and latitude

D. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application

5: Which Section deals with handling of errors that arise during execution of the data manipulation statements, which makeup the PL/SQL Block?

A. Declare

B. Exception

C. Begin

D. End

6: Which two statements among the following, regarding oracle database 10g PL/SQL support for LOB migration, are true?

A. Standard package functions accept LOBs as parameters

B. Standard package function do not accept LOBs as parameters

C. Implicit data conversion is supported for converting LOB to RAW

D. Implicit data conversion is not supported for converting varchar to LOB

7: Which precomplied word is called, which when encountered, immediately binds the numbered exception handler to a name?

A. Pragma

B. Raise

C. Trap

D. Exception_init

8: Examine the following code:
CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE);
END;
/
An UPDATE command is issued in the EMP table that results in changing 10 rows
How many rows are inserted into the AUDIT_TABLE ?

A. 1

B. 10

C. None

D. A value equal to the number of rows in the EMP table

9: If user defined error condition exists, Which of the following statements made a call to that exception?

A. Trap

B. Raise

C. call

D. call Exception

10: Which table should be queried to determine when the procedure was last compiled?

A. USER_PROCEDURES

B. USER_PROCS

C. USER_OBJECTS

D. USER_PLSQL_UNITS

11: Examine the following procedure:
PROCEDURE emp_salary
(v_bonus BOOLEAN,
V_raise BOOLEAN,
V_issue_check in out BOOEAN)
is
BEGIN
v_issue_check:=v_bonus or v_raise;
END;
If v_bonus=TRUE and v_raise=NULL,which value is assigned to v_issue_check?

A. TRUE

B. FALSE

C. NULL

D. none

12: A table has to be dropped from within a stored procedure. How can this be implemented?

A. A table cannot be dropped from a stored procedure

B. Use the DROP command in the procedure to drop the table

C. Use the DBMS_DDL packaged routines in the procedure to drop the table

D. Use the DBMS_DROP packaged routines in the procedure to drop the table.

13: Which datatype does the cursor attribute '%ISOPEN' return?

A. BOOLEAN

B. INTEGER

C. NUMBER

D. VARCHAR2

14: The CHECK_SAL procedure calls the UPD_SAL procedure. Both procedures are INVALID.Which command can be issued to recompile both procedures?

A. COMPILE PROCEDURE CHECK_SAL;

B. COMPILE PROCEDURE UPD_SAL;

C. ALTER PROCEDURE CHECK_SAL compile

D. ALTER PROCEDURE UPD_sAL compile

15: Which command is used to disable all triggers on the EMPLOYEES table?

A. Multiple triggers on a table in one command cannot be disabled

B. ALTER TRIGGERS ON TABLE employees DISABLE;

C. ALTER employees DISABLE ALL TRIGGERS;

D. ALTER TABLE employees DISABLE ALL TRIGGERS;

16: Which two among the following programming constructs can be grouped within a package?

A. Cursor

B. Constant

C. Trigger

D. Sequence

E. View

17: What is the maximum number of handlers processed before the PL/SQL block is exited, when an exception occurs?

A. Only one

B. All that apply

C. All referenced

D. None

18: All packages can be recompiled by using an Oracle utility called:

A. Dbms_Output

B. Dbms_Lob

C. Dbms_utility

D. Dbms_Error

19: Which type of variable should be used to assign the value TRUE, FALSE?

A. Constant

B. Scalar

C. Reference

D. Composite

20: SQL%ISOPEN always evaluates to false in case of a/an:

A. Explicit Cursor

B. Implicit Cursor

C. Paramertized Cursor

D. Cursor with Arguments

21: Which two statements, among the following, describe the state of a package variable after executing the package in which it is declared?

A. It persists across transactions within a session

B. It persists from session to session for the same user

C. It does not persist across transaction within a session

D. It persists from user to user when the package is invoked

E. It does not persist from session to session for the same user

22: Which table and column can be queried to see all procedures and functions that have been marked invalid?

A. USER_ERRORS table,STATUS column

B. USER_OBJECTS table,STATUS column

C. USER_ERRORS table,INVALID column

D. USER_OBJECTS table,INVALID column

23: What type of trigger is created on the EMP table that monitors every row that is changed, and places this information into the AUDIT_TABLE?

A. FOR EACH ROW trigger on the EMP table

B. Statement-level trigger on the EMP table

C. FOR EACH ROW trigger on the AUDIT_TABLE table

D. Statement-level trigger on the AUDIT_TABLE table

24: Which of the following statements is true regarding stored procedures?

A. A stored procedure uses the DECLARE keyword in the procedure specification to declare formal parameters

B. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification

C. A stored procedure must have at least one executable statement in the procedure body

D. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters

25: Which package construct must be declared and defined within the packages body?

A. Exception

B. Boolean Variable

C. Public Procedure

D. Private Procedure

26: When the procedure or function is invoked, the Oracle engine loads the compiled procedure or function in the memory area called:

A. PGA

B. SGA

C. Redo Log Buffer

D. Data base buffer cache

27: Evaluate the following PL/SQL block:
DECLARE
v_low NUMBER:=2;
v_upp NUMBER:=100;
v_count NUMBER:=1;
BEGIN
FOR i IN v_low..v_low LOOP
INSERT INTO test(results)
VALUES (v_count)
v_count:=v_count+1;
END LOOP;
END;
How many times will the executable statements inside the FOR LOOP execute?

A. 0

B. 1

C. 2

D. 98

E. 100

28: Which table should be queried to check the status of a function?

A. USER_PROCS

B. USER_OBJECTS

C. USER_PROCEDURES

D. USER_PLSQL_UNITS

29: The technique employed by the Oracle engine to protect table data, when several people are accessing it is called:

A. Concurrency Control

B. Program Control

C. PL/SQL Control

D. Locking

30: Which two statements out of the following regarding packages are true?

A. Both the specification and body are required components of a package

B. The package specification is optional, but the package body is required

C. The package specification is required, but the package body is optional

D. The specification and body of the package are stored separately in the database

31: Evaluate the following PL/SQL block:
DECLARE
result BOOLEAN;
BEGIN
DELETE FROM EMPloyee
WHERE dept_id IN (10,40,50);
result:=SQL%ISOPEN;
COMMIT:
END;

What will be the value of RESULT if three rows are deleted?

A. 0

B. 3

C. TRUE

D. NULL

E. FALSE

32: In which type of trigger can the OLD and NEW qualifiers can be used?

A. Row level DML trigger

B. Row level system trigger

C. Statement level DML trigger

D. Row level application trigger

33: Which of the following are identified by the "INSTEAD OF" clause in a trigger?

A. The view associated with the trigger

B. The table associated with the trigger

C. The event associated with the trigger

D. The package associated with the trigger

E. The statement level or for each row association to the trigger

34: What happens when rows are found using a FETCH statement?

A. The cursor opens

B. The cursor closes

C. The current row values are loaded into variables

D. Variables are created to hold the current row values

35: An internal LOB is _____.

A. A table

B. A column that is a primary key

C. Stored in the database

D. A file stored outside of the database, with an internal pointer to it from a database column.

A. They encapsulate native resources and must be properly disposed to prevent potential exhausting of resources.

B. They are all MarshalByRef derived classes, but functionality across AppDomains has specific limitations.

C. You can inherit from these classes to provide enhanced or customized functionality.

D. They are Value Type objects.

37: Which of the following characteristics is present in the DateTime type?

A. It always references the UTC (GMT) time

B. It always references the Local time

C. It contains a member indicating which time zone it refers to

D. It contains a member indicating the time is UTC, Local, or Unspecified

38: Which of the following are true about System.GC under version 3.5 of the Framework?

A. You can request that the garbage collector processes a generation if it determines that it is appropriate at specific points in your code

B. You can control the intrusiveness of the garbage collector (i.e. how often it performs collections) while your program is running

C. You can control the intrusiveness of the garbage collector (i.e. how often it performs collections) only during application initialization

D. You should specify LowLatency when using Concurrent Server Garbage Collection to improve memory utilization

39: Which of the following do the advantages of Lambda Expressions over Anonymous methods include?

A. More concise syntax

B. The types for a Lambda Expression may be omitted

C. The body of an anonymous method can not be an expression

D. Lambda Expressions permit deferred type interference that anonymous methods do not

E. All of the above

40: Which of the following statements do Expression Trees fit best?

A. Expression trees are a data structure which can be initially composed using language syntax.

B. Expression trees are a dynamically generated code which is executed to perform the desired function.

C. Expression trees can be created only from Lambda Expressions

D. Expression trees can be modified directly

E. All of the above

41:

Which of the following is not a legal declaration?

A. declare v number(4);

B. declare x,y varchar2(10);

C. declare birthdate date not null;

D. declare Sex boolean:=1;

42:

Examine the following trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Employee
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM employee;
DMBS_OUTPUT.PUT_LINE( 'There are now' || n || 'employees');
END;
This trigger results in an error after this SQL statement is
entered: DELETE FROM Employee WHERE Empno = 7499;
How should the error be corrected?

A. Change the trigger type to a BEFORE DELETE

B. Take out the COUNT function because it is not allowed in a trigger

C. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger

D. Change the trigger to a statement-level trigger by removing FOR EACH ROW

43:

Examine the following code:
CREATE OR REPLACE FUNCTION gen_email (first_name VARCHAR2,
last_name VARCHAR2,
id NUMBER)
RETURN VARCHAR2 IS
email_name VARCHAR2(19);
BEGIN
email_name := SUBSTR(first_name, 1, 1) ||
SUBSTR(last_name, 1, 7) ||.@Oracle.com .;
UPDATE employees SET email = email_name
WHERE employee_id = id;
RETURN email_name;
END;
Which of the following statements removes the function?

A. DROP gen_email;

B. REMOVE gen_email;

C. DELETE gen_email;

D. DROP FUNCTION gen_email;

44:

CREATE OR REPLACE PACKAGE manage_emp IS
tax_rate CONSTANT NUMBER(5,2) := .28;
v_id NUMBER;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp;
FUNCTION cal_tax (p_sal NUMBER) RETURN NUMBER;
END manage_emp;
/
CREATE OR REPLACE PACKAGE BODY manage_emp IS
PROCEDURE update_sal (p_raise_amt NUMBER) IS
BEGIN
UPDATE emp SET sal = (sal * p_raise_emt) + sal
WHERE empno = v_id;
END;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS
BEGIN
INSERT INTO emp(empno, deptno, sal) VALUES
(v_id, p_depntno, p_sal);
END insert_emp;
PROCEDURE delete_emp IS
BEGIN
DELETE FROM emp WHERE empno = v_id;
END delete_emp;
PROCEDURE update_emp IS
v_sal NUMBER(10,2);
v_raise NUMBER(10, 2);
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = v_id;
IF v_sal < 500 THEN v_raise := .05;
ELSIP v_sal < 1000 THEN v_raise := .07;
ELSE v_raise := .04;
END IF;
update_sal(v_raise);
END update_emp;
FUNCTION cal_tax (p_sal NUMBER)RETURN NUMBER IS
BEGIN
RETURN p_sal * tax_rate;
END cal_tax;
END manage_emp;
/
What is the name of the private procedure in this package?

A. CAL_TAX

B. INSERT_EMP

C. UPDATE_SAL

D. DELETE_EMP

E. UPDATE_EMP

45:

Which procedure is called after a row has been fetched to transfer the value, from the select list of the cursor into a local
variable?

A. Row_value

B. Column_value

C. Raise_application

D. Exception_init

46:

Which cursor dynamically allows passing values to a cursor while opening another cursor?

A. Implicit Cursor

B. User Defined Cursor

C. Parameterized Cursor

D. Explicit Cursor

47:

What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?

A. The validity of the SQL statement is established

B. An area of memory is established to process the SQL statement

C. The SQL statement is run and the number of rows processed is returned

D. The area of memory established to process the SQL statement is released

48:

Examine the following package specification:
CREATE OR REPLACE PACKAGE combine_all
IS
v_string VARCHAR2(100);
PROCEDURE combine (p_num_val NUMBER);
PROCEDURE combine (p_date_val DATE);
PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);
END combine_all;
/
Which overloaded COMBINE procedure declaration can be added to this
package specification?

A. PROCEDURE combine;

B. PROCEDURE combine (p_no NUMBER);

C. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER);

D. PROCEDURE combine_all(p_num_val VARCHAR2, p_char_val NUMBER);

49:

Which part of a database trigger determines the number of times the trigger body executes?

A. Trigger type

B. Trigger body

C. Trigger event

D. Trigger timing

50:

Examine the following code:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE LOGON ON employees
BEGIN
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR
(TO_CHAR(SYSDATE, 'HH24:MI')
NOT BETWEEN '08:00' AND '18:00')
THEN RAISE_APPLICATION_ERROR (-20500, 'You may
insert into the EMPLOYEES table only during
business hours.');
END IF;
END;
/
What type of trigger is it?

A. DML trigger

B. INSTEAD OF trigger

C. Application trigger

D. This is an invalid trigger

Oracle PL/Sql 10g MCQs

Oracle PL/Sql 10g MCQs

List of Oracle PL/Sql 10g MCQs...

Related Oracle PL/Sql 10g MCQs:

Available in:

Latest MCQs:

Popular MCQs: