Oracle PL/SQL 9i MCQs
Oracle PL/SQL 9i MCQs
Our experts have gathered these Oracle PL/SQL 9i MCQs through research, and we hope that you will be able to see how much knowledge base you have for the subject of Oracle PL/SQL 9i by answering these 40 multiple-choice questions.
Get started now by scrolling down!
1: The oracle server implicitly opens a cursor to process:
A. Sql select statement
B. PL/SQL Select statement
C. DML Statements
D. DDL Statements
2: Which two programming constructs can be grouped within a package?
A. Cursor
B. Constant
C. Trigger
D. Sequence
E. View
3: All the packages can be recompiled by using an Oracle utility called:
A. Dbms_Output
B. Dbms_Lob
C. Dbms_utility
D. Dbms_Error
4: A CALL statement inside the trigger body enables you to call:
A. A package
B. A stored function
C. A stored procedure
D. Another database trigger
5: An internal LOB is _____.
A. A table
B. Stored in the database
C. A file stored outside the database
D. None of the above
6: In Explicit lock, two database operations wait for each other to release a lock.
A. True
B. False
7: Examine the following function:
CREATE OR REPLACE FUNCTION PLAYER_AVG
(V_ID in PLAYER.PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
BEGIN
SELECT HITS INTO V_AVG
FROM PLAYER
WHERE PLAYER_ID = V_ID;
RETURN (V_AVG);
END;
Which of the following statements will successfully invoke this function in SQL *Plus?
A. SELECT PLAYER_AVG(PLAYER_ID)
FROM PLAYER;
B. EXECUTE PLAYER_AVG(31);
C. PLAYER_AVG('RUTH');
D. PLAYER_AVG(31);
8: Which of the following commands is used to disable all the triggers on the EMPLOYEES table?
A. ALTER TRI GGERS ON TABLE employees DI SABLE;
B. ALTER employees DISABLE ALL TRI GGERS;
C. ALTER TABLE employees DISABLE ALL TRIGGERS;
D. None of the above
9: Which of the following is not a legal declaration?
A. declare
v_id number(4);
B. declare
v_x,V_y varchar2(10);
C. declare
V_birthdate date not null;
D. declare
V_Sex boolean:=1;
10: A function CALTAX is given below:
CREATE OR REPLACE FUNCTION caltax
(sal NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (sal * 0.05);
END CALTAX;
Which of the following statements is correct in case you want to run this function from the SQL *Plus prompt?
A. You need to execute the command CALTAX(1000);
B. You need to execute the command EXECUTE FUNCTION caltax;
C. You need to create a SQL *Plus environment variable X and issue the command
:X := CALTAX(1000);
D. You need to create a SQL *Plus environment variable X and issue the command
EXECUTE :X := CALTAX(1000);
11: You need to drop a table from within a stored procedure. How would you do this?
A. You cannot drop a table from a stored procedure
B. By using the DROP command
C. By using the DBMS_DDL packaged routines
D. By using the DBMS_DROP packaged routines
12: Examine the following code:
CREATE OR REPLACE PROCEDURE
add_dept ( p_name dept.dname%TYPE DEFAULT "unknown",
p_loc dept.locE%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO dept VALUES (dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;
/
You created the add_dept procedure above. Now you want to invoke the procedure in SQL *Plus. Which of the following are the valid invocations?
A. EXECUTE add_dept(p_loc=>2500)
B. EXECUTE add_dept( 'Education', 2500)
C. EXECUTE add_dept( .2500 ,p_loc =>2500)
D. EXECUTE add_dept(p_name=>'Education', 2500)
13: Which lock is used in Pl/Sql if the where clause evaluates to a set of data?
A. Row Level Lock
B. Page Level lock
C. Column Level lock
D. Exclusive Lock
14: Which part of a database trigger determines the number of times a trigger body executes?
A. Trigger type
B. Trigger body
C. Trigger event
D. Trigger timing
15: Examine the code given below: 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 this?
A. DML trigger
B. INSTEAD OF trigger
C. Application trigger
D. This is an invalid trigger
16: You need to create a trigger on the EMP table, which monitors every row that is changed and places this information in the AUDIT_TABLE. What type of trigger would you create?
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
17: Which of the following types of argument passes a value from a procedure to the calling environment?
A. VARCHAR2
B. BOOLEAN
C. OUT
D. IN
18: Which of the following Triggers is fired each time a row in the table is affected by the triggering statement?
A. Statement Trigger
B. Application Trigger
C. Row
D. Database Trigger
19: Which of the following exceptions is raised when the data type or data size is invalid?
A. VALUE_ERROR
B. NO_DATA_FOUND
C. STORAGE_ERROR
D. PROGRAM_ERROR
20: The Technique employed by the Oracle engine to protect table data when several people are accessing is called
A. Concurrency Control
B. Program Control
C. PL/SQL Control
D. Locking
21: The OLD and NEW qualifiers can be used in Row level system trigger.
A. True
B. False
22: Examine the Block given below: declare v_no number:=2; v_msg varchar2(20):="Goodbye"; begin case When v_no=1 then dbms_output.put_line("One!"); dbms_output.put_line("Another One!"); When v_no>1 then dbms_output.put_line(">1!"); dbms_output.put_line("Still>1!"); when v_msg="Goodbye" then dbms_output.put_line("Goodbye"); dbms_output.put_line("Adios"); else dbms_output.put_line("No Match"); end case; end; / What is the output of this block?
A. 1!
Still>1!
B. Goodbye
Adios
C. 1!
D. Adios
23: Which cursor allows passing values dynamically to a cursor?
A. Implicit Cursor
B. User Defined Cursor
C. Parameterized Cursor
D.
Explicit Cursor
24: Examine the code given below:
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;
25: __________ is present in Oralce9i only.
A. Timestamp
B. Date
C. Rowid
26: Which of the following statements is correct with regard to 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
27: Which procedure is called after a row has been fetched to transfer the value from the select list of the cursor to a local variable?
A. Row_value
B. Column_value
C. Raise_application
D. Exception_init
28: Which of the following program declarations are correct for a stored program unit?
A. CREATE OR REPLACE FUNCTION tax(p_id NUMBER) RETURN NUMBER
B. CREATE OR REPLACE PROCEDURE tax(p_id NUMBER) RETURN NUMBER
C. CREATE OR REPLACE PROCEDURE tax(p_id NUMBER, p_amount OUT NUMBER)
D. CREATE OR REPLACE FUNCTION tax(p_id NUMBER) RETURN NUMBER(10,2)
29: Examine the code given below:
Declare
cursor emps is
select Empno,ename,sal,deptno,job from emp;
begin
for rec in emps loop
open emps;
dbms_output.put_line(rec.empno||rec.ename||rec.sal||rec.deptno||rec.job);
end loop;
end;
/
What is wrong in above declaration?
A. Record Rec is not defined
B. Fetch statement is not specified
C. Close cursor in not specified
D. There is no need to open cursor in the for loop
30: Which of the following statements is correct?
A. You can use the DBMS_LOB.WRITE procedure to write data to a BFILE
B. You can use the DBMS_LOB.BFILENAME function to locate an external BFILE
C. You can use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE
D. You can use the DBMS_LOB.FILECLOSE procedure to close the file being accessed
31: Examine the code given below:
CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE);
END;
/
You issue an UPDATE command in the EMP table, which results in changing 10 rows.
How many rows are inserted in the AUDIT_TABLE ?
A. 1
B. 10
C. None
D. 2
32: 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
33: Which of the following statements are correct with regard to packages?
A. The package specification is optional, but the package body is required
B. The package specification is required, but the package body is optional
C. The specification and body of the package are stored separately in the database
D. None of the above
34: Which of the following commands is used to disable all the triggers on an EMP table?
A. ALTER TRIGGERS ON TABLE emp DISABLE;
B. ALTER e mp l o y e e s DI SABLE ALL TRI GGERS;
C. ALTER TABLE emp DISABLE ALL TRIGGERS;
D. None of the above
35: When the procedure or function is invoked, 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
36: Examine the package given below:
CREATE OR REPLACE PACKAGE discounts IS
g_id NUMBER := 7829;
discount_rate NUMBER := 0.00;
PROCEDURE display_price (p_price NUMBER);
END discounts;
/
CREATE OR REPLACE PACKAGE BODY discounts IS
PROCEDURE display_price (p_price NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE( .Discounted .|| TO_CHAR(p_price*NVL(discount_rate, 1)));
END display_price;
BEGIN
discount_rate :=0.10;
END discounts;
/
Which of the following statements is correct?
A. The value of DISCOUNT_RATE always remains 0.00 in a session
B. The value of DISCOUNT_RATE is set to 0.10 each time the package is invoked in a session
C. The value of DISCOUNT_RATE is set to 1.00 each time the procedure DISPLAY_PRICE is invoked
D. The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session
37: Examine the code given below:
CREATE OR REPLACE PACKAGE comm_package IS
g_comm NUMBER := 10;
PROCEDURE reset_comm(g_comm IN NUMBER);
END comm_package;
User Jones executes the following code at 9:01am:
EXECUTE comm_package.g_comm := 15
User Smith executes the following code at 9:05am:
EXECUTE comm_paclage.g_comm := 20
Which of the following statements is correct?
A. g_comm has a value of 15 at 9:06am for Smith
B. g_comm has a value of 15 at 9:06am for Jones
C. g_comm has a value of 20 at 9:06am for both Jones and Smith
D. g_comm has a value of 15 at 9:03 am for both Jones and Smith
38: Which table should be queried to determine when a procedure was last compiled?
A. USER_PROCEDURES
B. USER_PROCS
C. USER_OBJECTS
D. USER_PLSQL_UNITS
39: Consider the following code: declare v_id number(3):=50; V_message(30):="Product 10012"; begin declare v_id number(3):=40; V_message(30):="welcome to world"; v_locn:="Europe; begin v_id:=v_id+20; end; end; Determine the value of V_ID variable in the outer block.
A. 50
B. 80
C. 40
D. 60
40: The process of Breaking Sql sentence into words and then checking them for syntax and object privileges is called:
A. Binding
B. Parsing
C. Sequence
D. Control Flow
41: SQL%ISOPEN always evaluates to false in case of:
A. Explicit Cursor
B. Implicit Cursor
C. Parameterized Cursor
D. Cursor with Arguments
42: Which cursor attribute returns the number of rows fetched from the active set in the case of an explicit cursor?
A. %ISOPEN
B. %ROWCOUNT
C. %FOUND
D. %NOTFOUND
43: Database triggers are designed:
A. To duplicate the functionality of other triggers
B. To guarantee that the related actions are performed when a specific operation is performed
C. Both a and b
D. None of the above
44: How can you migrate from a LONG to a LOB data type for a column?
A. By using the DBMS_MANAGE_LOB.MIGRATE procedure
B. By using the UTL_MANAGE_LOB.MIGRATE procedure
C. By using the DBMS_LOB.MIGRATE procedure
D. By using DBMS_REDEFINITION.START_REDEF_TABLE procedure
List of Oracle PL/SQL 9i MCQs ...
Available in:
Oracle PL/SQL 9i MCQs
