ORACLE PLSQL MCQ QUESTIONS Download
1. Under which situation is it necessary to use an explicit
cursor?
a.when a SELECT statement in a PL/SQL block retrieves more
than one row
b.when an UPDATE statement in a PL/SQL block has to modify
more than one row
c.when a DELETE statement in a PL/SQL block deletes more
than one row
d.when any DML or SELECT statement is used in a PL/SQL block
Answer:A
2. Which statement about implicit cursors is true?
a.Programmers need to close all the implicit cursors before
the end of the PL/SQL program.
b.Programmers can declare implicit cursors by using the
CURSOR type in the declaration section.
c.Implicit cursors are declared implicitly only for DML
statements.
d.Implicit cursors are declared implicitly for all the DML
and SELECT statements.
Answer:D
3. Within a PL/SQL loop, you need to test if the current
fetch was successful. Which SQL cursor attribute would you use to accomplish
this task?
a.A SQL cursor attribute cannot be used within a PL/SQL
loop.
b.This task cannot be accomplished with a SQL cursor
attribute.
c.SQL%ROWCOUNT
d.SQL%ISOPEN
e.SQL%FOUND
Answer :E
4. In the declarative section of a PL/SQL block, you created
but did not initialize a number variable. When the block executes, what will be
the initial value of the variable? "
a.The block will not execute because the variable was not
initialized.The block will not execute because the variable was not
initialized.
b.0
c.null
d.It depends on the scale and precision of the variable.
Answer:C
5. Examine the code: DECLARE CURSOR emp_cursor IS SELECT
ename, deptno FROM emp; emp_rec emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP
FETCH emp_cursor INTO emp_rec; EXIT WHEN emp_cursor%NOTFOUND; INSERT INTO
temp_emp(name, dno) VALUES (emp_rec.ename, emp_rec.deptno); END LOOP; CLOSE emp_cursor;
END; Using a cursor FOR loop, which PL/SQL block is equivalent to the above
code?
a.DECLARE CURSOR emp_cursor IS SELECT ename, deptno FROM
emp; BEGIN FOR emp_rec IN emp_cursor LOOP INSERT INTO temp_emp(name, dno)
VALUES (emp_rec.ename, emp_rec.deptno); END LOOP; CLOSE emp_cursor; END; "
b.DECLARE CURSOR emp_cursor IS SELECT ename, deptno FROM
emp; BEGIN FOR emp_rec IN emp_cursor LOOP INSERT INTO temp_emp(name, dno)
VALUES (emp_rec.ename, emp_rec.deptno); END LOOP; END;
c.DECLARE CURSOR emp_cursor IS SELECT ename, deptno FROM
emp; emp_rec emp_cursor%ROWTYPE; BEGIN FETCH emp_cursor INTO emp_rec; FOR
emp_rec IN emp_cursor LOOP INSERT INTO temp_emp(name, dno) VALUES
(emp_rec.ename, emp_rec.deptno); END LOOP; END;
d.DECLARE CURSOR emp_cursor IS SELECT ename, deptno FROM
emp; BEGIN FOR emp_rec IN emp_cursor LOOP OPEN emp_cursor; INSERT INTO
temp_emp(name, dno) VALUES (emp_rec.ename, emp_rec.deptno); END LOOP; END;
Answer:B
6. You want to retrieve the employee details from the EMP
table and process them in PL/SQL block. Which type of variable do you create in
the PL/SQL block to retrieve all the rows and columns using a single SELECT
statement from the EMP table? "
a.PL/SQL table of scalars
b.PL/SQL table of records
c.%ROWTYPE variable
d.PL/SQL record
Answer:B
7. Given this executable section of a PL/SQL block: BEGIN
FOR employee_record IN salary_cursor LOOP employee_id_table(employee_id) :=
employee_record.last_name; END LOOP; CLOSE salary_cursor; END; Why does this
section cause an error?
a.The cursor does not need to be closed.
b.No FETCH statements were issued.
c.Terminating conditions are missing.
d.The cursor needs to be opened.
Answer:A
8. Evaluate this PL/SQL block: DECLARE v_result NUMBER(2);
BEGIN DELETE FROM employee WHERE dept_id IN (10, 20, 30); v_result :=
SQL%ROWCOUNT; COMMIT; END; " What will be the value of V_RESULT if no rows
are deleted?
a.1
b.NULL
c.0
d.FALSE
e.TRUE
Answer :C
9. Evaluate this IF statement: IF v_value > 100 THEN
v_new_value := 2 * v_value; ELSIF v_value > 200 THEN v_new_value := 3 *
v_value; ELSIF v_value < 300 THEN v_new_value := 4 * v_value; ELSE
v_new_value := 5 * v_value; END IF; What would be assigned to V_NEW_VALUE if
V_VALUE is 250?
a.250
b.1250
c.750
d.500
e.1000
Answer:D
10. How do you declare a PL/SQL table of records to hold the
rows selected from the EMP table?
a.DECLARE emp_table IS TABLE OF emp%ROWTYPE;
b.DECLARE TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX
BY BINARY_INTEGER; emp_table emp_table_type;
c.BEGIN TYPE emp_table_type IS TABLE OF emp%ROWTYPE; emp_table
emp_table_type;
d.DECLARE TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX
BY WHOLE_NUMBER; emp_table emp_table_type;
Answer:B
11. " In which section of a PL/SQL block is a
user-defined exception raised?
a.exception handling
b.executable
c.heading
d.declarative
Answer:B
12. Which statement is true when writing a cursor FOR loop?
a.You do not explicitly open, fetch or close the cursor
within a cursor FOR loop .
b.You must explicitly close the cursor prior to the end of
the program.
c.You must explicitly fetch the rows within a cursor FOR
loop.
d.You must explicitly declare the record variable that holds
the row returned from the cursor.
e.You must explicitly open the cursor prior to the cursor
FOR loop.
Answer:A
13. You want to create a cursor that can be used several
times in a block, selecting a different active set each time when it is opened.
Which type of cursor do you create?
a.a cursor for each active set
b.a cursor FOR loop
c.a cursor that uses parameters
d.a multiple selection cursor
Answer:C
14. Examine this block of code: SET SERVEROUTPUT ON DECLARE
x NUMBER; v_sal NUMBER; v_found VARCHAR2(10) := 'TRUE'; BEGIN x := 1; v_sal :=
1000; DECLARE v_found VARCHAR2(10); y NUMBER; BEGIN " IF (v_sal > 500)
THEN v_found := 'YES'; END IF; DBMS_OUTPUT.PUT_LINE ('Value of v_found is '||
v_found); DBMS_OUTPUT.PUT_LINE ('Value of v_sal is '|| v_sal); y := 20; END;
DBMS_OUTPUT.PUT_LINE ('Value of v_found is '|| v_found); DBMS_OUTPUT.PUT_LINE
('Value of Y is '|| TO_CHAR(y)); END; SET SERVEROUTPUT OFF What is the result
of executing this block of code?
a.Value of v_found is YES Value of v_sal is 1000 Value of
v_found is YES Value of Y is 20
b.Value of v_found is YES Value of v_sal is 1000 Value of
v_found is TRUE Value of Y is 20
c.PLS-00201: identifier 'Y' must be declared
d.Value of v_found is YES Value of v_sal is 1000 Value of
v_found is TRUE
e.PLS-00201: identifier 'v_sal' must be declared PLS-00201:
identifier 'Y' must be declared
Answer:C
15. Which two conditions in a PL/SQL block cause an
exception to occur?(Choose two.)
a.The SELECT statement contains a GROUP BY clause.
b.The SELECT statement returns more than one row.
c.The datatypes in the SELECT list are inconsistent with the
datatypes in the INTO clause.
d.The SELECT statement does not return a row.
e.The SELECT statement does not have a WHERE clause.
Answer:B,D
16. Examine the code: " 1 DECLARE 2 i NUMBER := 0; 3
v_date DATE; 4 BEGIN 5 i := i 1; 6 LOOP 7 v_date := v_date 5; 8 i := i 1; 9
EXIT WHEN i = 5; 10 END LOOP; 11 END; You have encountered unexpected results
when the above block of code is executed. How can you trace the values of the
counter variable I and date variable V_DATE in the SQL*Plus environment?
a.by inserting the statement DBMS_OUTPUT.PUT_LINE (i ||' '||
TO_CHAR(v_date)); between lines 8 and 9
b.by setting the SQL*Plus session variable DEBUGGER = TRUE
c.by inserting the statement DBMS_OUTPUT.PUT_LINE (i,
v_date); between lines 8 and 9
d.by inserting the statement DBMS_OUTPUT.DEBUG_VAR (i,
v_date); between lines 8 and 9
Answer:A
17. Evaluate this PL/SQL block: BEGIN FOR i IN 1..10 LOOP IF
i = 4 OR i = 6 THEN null; ELSE INSERT INTO test(results) VALUES (I); END IF;
COMMIT; END LOOP; ROLLBACK; END; " How many values will be inserted into
the TEST table?
a.4
b.8
c.0
d.10
e.6
Answer:B
18. Which statement is valid within the executable section
of a PL/SQL block?
a.WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No records
found');
b.SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno
= 101;
c.PROCEDURE calc_max (n1 NUMBER,n2 NUMBER,p_max OUT NUMBER)
IS BEGIN IF n1 > n2 THEN p_max := n1; ELSE p_max := n2; END;
d.BEGIN emp_rec emp%ROWTYPE; END;
Answer:B
19. A DBA has updated Smith's account by adding the
privileges CREATE ANY TABLE and CREATE PROCEDURE. Which tasks can Smith
successfully perform?
a.Smith can create a table in any schema of the database but
can drop tables from and create procedures only in his schema.
b.Smith can create any table or procedure only in his
schema. Also, he can drop any table only from his schema.
c.Smith can create tables, drop tables and create procedures
in any schema of the database.
d.Smith can create a table or a procedure in any schema of
the database. Also, he can drop a table in any schema of the database.
Answer:A
20. Which should you do after each FETCH statement in a
PL/SQL block?
a.Open the cursor.
b.Initialize the loop.
c.Close the cursor.
d.Test for rows using a cursor attribute.
Answer :D
B
21. Examine this block of code: SET SERVEROUTPUT ON DECLARE
x NUMBER; v_sal NUMBER; v_found VARCHAR2(10) := 'TRUE'; BEGIN x := 1; v_sal :=
1000; DECLARE v_found VARCHAR2(10); y NUMBER; BEGIN IF (v_sal > 500) THEN
v_found := 'YES'; END IF; DBMS_OUTPUT.PUT_LINE ('Value of v_found is '||
v_found); DBMS_OUTPUT.PUT_LINE ('Value of v_sal is '|| v_sal); y := 20; END; DBMS_OUTPUT.PUT_LINE
('Value of v_found is '|| v_found); DBMS_OUTPUT.PUT_LINE ('Value of Y is '||
TO_CHAR(y)); END; SET SERVEROUTPUT OFF Why does this code produce an error when
executed?
a.The value of V_FOUND cannot be 'YES'.
b.Variable Y is declared in the inner block and referenced
in the outer block.
c.Variable V_FOUND is declared at more than one location.
d.Variable V_SAL is declared in the outer block and
referenced in the inner block.
Answer:B
22. Examine the declaration section: DECLARE CURSOR
emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT empno, ename FROM emp
WHERE deptno = p_deptno AND job = p_job; BEGIN ... Which statement opens this
cursor successfully?
a.OPEN emp_cursor('Clerk', 10);
b.OPEN emp_cursor;
c.OPEN emp_cursor(p_deptno, p_job);
d.OPEN emp_cursor(10,'Analyst');
Answer :D
23. Evaluate this PL/SQL block: DECLARE v_result NUMBER(2);
BEGIN DELETE FROM employee WHERE dept_id IN (10, 20, 30); v_result :=
SQL%ROWCOUNT; COMMIT; END; What will be the value of V_RESULT if no rows are
deleted?
a.0
b.NULL
c.FALSE
d.1
e.TRUE
Answer :A
24. Which two conditions in a PL/SQL block cause an
exception to occur?(Choose two.)
a.The SELECT statement does not have a WHERE clause.
b.The SELECT statement contains a GROUP BY clause.
c.The SELECT statement does not return a row.
d.The SELECT statement returns more than one row.
e.The datatypes in the SELECT list are inconsistent with the
datatypes in the INTO clause.
Answer:C,D
25. The structure of the DEPT table is as follows: Name
Null?Type ------------------------------- -------------- --------- DEPTNO NOT
NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) Examine the code: DECLARE
dept_rec dept%ROWTYPE; BEGIN SELECT * INTO dept_rec FROM dept WHERE deptno =
10; END; Which PL/SQL statement displays the location of the selected
department?
a.DBMS_OUTPUT.PUT_LINE (dept_rec.loc);
b.DBMS_OUTPUT.PUT_LINE (dept_rec);
c.You cannot display a single field in the record because
they are not explicitly identified in the declarative section.
d.DBMS_OUTPUT.PUT_LINE (dept_rec(1).loc);
Answer:A
26. You need to create a PL/SQL program to insert records
into the employee table. Which block of code successfully uses the INSERT
command?
a.DECLARE v_hiredate DATE := SYSDATE; BEGIN INSERT INTO
emp(empno, ename, hiredate, deptno) (empno_sequence.nextval, '&name',
v_hiredate, &deptno); END;
b.DECLARE v_hiredate DATE := SYSDATE; BEGIN INSERT INTO
emp(empno, ename, hiredate, deptno) VALUES(empno_sequence.nextval, '&name',
v_hiredate, &deptno); END;
c.DECLARE v_hiredate DATE := SYSDATE; BEGIN INSERT INTO
emp(empno, ename, hiredate, deptno) VALUES(empno_sequence.nextval, '&name',
v_hiredate, &deptno) WHERE job = 'CLERK'; END;
d.DECLARE v_hiredate DATE := SYSDATE; BEGIN INSERT INTO
emp(empno, ename, hiredate) VALUES(empno_sequence.nextval, '&name',
v_hiredate, &deptno); END;
Answer :B
27. Evaluate this PL/SQL block: BEGIN FOR i IN 1..10 LOOP IF
i = 4 OR i = 6 THEN null; ELSE INSERT INTO test(results) VALUES (I); END IF;
COMMIT; END LOOP; ROLLBACK; END; How many values will be inserted into the TEST
table?
a.8
b.10
c.4
d.6
e.0
Answer:A
28. The EMPLOYEE table contains these columns: BONUSNUMBER(7,2)
DEPT_ID NUMBER(9) There are 10 departments and each department has at least 1
employee. Bonus values are greater than 500; not all employees receive a bonus.
Evaluate this PL/SQL block: DECLARE v_bonusemployee.bonus%TYPE := 300; BEGIN
UPDATE employee SET bonus = bonus v_bonus WHERE dept_id IN (10, 20, 30);
COMMIT; END; What will be the result?
a.A subset of employees will be given a 300 bonus.
b.All employees will be given a 300 increase in bonus.
c.A subset of employees will be given a 300 increase in
bonus.
d.All employees will be given a 300 bonus.
Answer:C
29. Which statement is valid within the executable section
of a PL/SQL block?
a.PROCEDURE calc_max (n1 NUMBER,n2 NUMBER,p_max OUT NUMBER)
IS BEGIN IF n1 > n2 THEN p_max := n1; ELSE p_max := n2; END;
b.SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno
= 101;
c.WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No records
found');
d.BEGIN emp_rec emp%ROWTYPE; END;
Answer:B
30. Which statement is true about nesting blocks?
a.A variable defined in the inner block is visible in the
outer blocks.
b.A variable defined in the outer block is visible in the
inner blocks.
c.Variable names must be unique between blocks.
d.A variable in an inner block may have the same name as a
variable in an outer block only if the data types are different.
Answer:B
31. Examine the code: SET SERVEROUTPUT ON DECLARE v_char_val
VARCHAR2(100); BEGIN v_char_val := 'Hello World';
DBMS_OUTPUT.PUT_LINE(v_char_val); END; SET SERVEROUTPUT OFF This code is stored
in a script file named myproc.sql. Which statement executes the code in the
script file?
a.EXECUTE myproc.sql
b.RUN myproc.sql
c.BEGIN myproc.sql END;
d.myproc.sql
e.START myproc.sql
Answer :E
32. Given this CURSOR statement: DECLARE CURSOR query_cursor
(v_salary) IS SELECT last_name, salary, dept_no FROM employee WHERE salary >
v_salary; Why does this statement cause an error?
a.A scalar datatype was not specified for the parameter.
b.A WHERE clause is not allowed in a CURSOR statement.
c.The INTO clause is missing from the SELECT statement.
d.The parameter mode was not defined.
Answer:A
Nesting blocks
ReplyDelete