Test your basic knowledge |

Oracle PL - SQL

Instructions:

Answer 50 questions in 15 minutes.
If you are not ready to take this test, you can study here.
Match each statement with the correct term.
Don't refresh. All questions and answers are randomly picked and ordered every time you load a test.

This is a study tool. The 3 wrong answers for each question are randomly chosen from answers to other questions. So, you might find at times the answers obvious, but you will see it re-enforces your understanding as you take the test each time.

1. To use a TYPE declared in the database - a PL/SQL block has to have _______ privilege on the type.
select
once
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
same

2. An editioning view is like an ____ for a table.
API
before insert or update on emps
disable
TIMESTAMP.

3. Advantage: PL/SQL programs are stored centrally - making them more ______.
manageable
associative
expression
IF - CASE or LOOP

4. Exceptions: User-defined exceptions must have _______.
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
information that changes infrequently
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
names

5. Cursors: What is the 'name' of the implicit cursor?
will not
forward
pls_integer
SQL

6. The _____ utility turns PL/SQL source code into unreadable text.
procedure P AUTHID current_user;
selection
Wrap
error code

7. With conditional compilation you can avoid compiling debug code in the _______ environment.
is not
cursor
production
session cursor

8. Exceptions: An unhandled exception ________ to the enclosing block.
NESTED TABLE myColl STORE AS myCollTab
value
identifiers
propagates

9. Cursors: WHERE _______ ______ makes an UPDATE or DELETE apply to the current row of a cursor.
CURRENT OF
FGA
RETURN
FETCH

10. DynSQL: EXECUTE IMMEDIATE can be used for any SQL statement or PL/SQL block - except for ________ queries.
multi-row
IN - OUT - IN OUT
DECLARE - BEGIN - EXCEPTION - END
RELIES_ON

11. Triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
always
FETCH - BULK COLLECT INTO
null string
nested tables - associative arrays

12. After the RESULT_CACHE clause - you can put a _________ clause to enable dependency-checking.
RELIES_ON
sql
$$
injection

13. Default file extension of wrap's input file.
cursor c;
sql
once
aliases

14. The SET TRANSACTION statement affects only the ____________.
Gateway
END CASE
$IF $THEN $ELSE $END
current transaction

15. I/O: The string table type in DBMS_OUTPUT for holding lines of text.
definer's
ROLLBACK
cannot
CHARARR

16. An ordinary character literal is composed of characters in the ________ character set.
FOR i IN REVERSE 1..10 LOOP
OTHERS
database
DECLARE - BEGIN - EXCEPTION - END

17. The best candidates for result-caching are functions that are invoked frequently but depend on __________________ or never.
bb:true
information that changes infrequently
nested
database

18. Types: The only PL/SQL type for which you can specify a range constraint.
PLS_INTEGER
simple and compound symbols
RESULT_CACHE
API

19. (from inside a package) Declare a function f - that takes a number n - returns a number - and caches results - depending on table EMPS.
function f (n number) return number result_cache relies_on (emps);
query
library
cannot

20. Abbreviation of invoker's rights
hardware
cannot
IR
RAISE

21. Cursors: Only a _____ _____ cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement.
FOR UPDATE
PLS_INTEGER
lock
invoker's

22. Types: In addition to the SQL data types - PL/SQL has ___ more scalar data types.
4
1
FETCH - INTO
error code

23. Default value of PLSQL_OPTIMIZE_LEVEL.
composite
2
OTHERS
will not

24. Types: One of the scalar data types specific to PL/SQL: b_____.
database
BOOLEAN
invoker's
anonymous block

25. Exceptions: The package that defines pre-defined exceptions.
parameter
SQLERRM
STANDARD
name and datatype

26. CREATE TYPE cannot be used for _______ array types.
TRUE - FALSE and NULL
simple and compound symbols
stored
associative

27. Data dictionary view that shows the compiler settings for each compiled object.
USER_PLSQL_OBJECT_SETTINGS
IR
IF - CASE or LOOP
anonymous block

28. Cursors: The first two keywords in a Cursor FOR loop.
HTP
3
can
FOR - IN

29. Cursors: A pointer to a cursor is called a ______ _______.
PSP
cursor variable
VARRAY - Nested table
4K

30. Nested tables are multisets - meaning that there is no inherent _____ of their elements.
CHAR - VARCHAR2
REUSE SETTINGS
TRUE - FALSE - NULL
order

31. To get the current value of CURRENT_SCHEMA - CURRENT_USER - or SESSION_USER - use the ____ function.
invoker's
$IF
SYS_CONTEXT
bytecode

32. Types: One of the 4 PL/SQL things that have a data type (begins with 'v').
national
stored
IN - OUT - IN OUT
variable

33. Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
;
REF CURSOR
PUT
multi-row

34. Types: One of the 4 PL/SQL things that have a data type (begins with 'c').
REUSE SETTINGS
optional
constant
EXTEND

35. Fundamental: One of the 4 types of lexical units: i_____
**
EXIT WHEN
predefined
identifiers

36. In short-circuit evaluation - IF-conditions are only evaluated up to where the outcome ___________.
implicit cursor
is certain
hardware
user-defined

37. Fundamental: Delimiters are _____ and _____ symbols.
CREATE TYPE
for each row
can
simple and compound symbols

38. The three types of compiler warnings.
cannot
memory
EVERE - PERFORMANCE - INFORMATIONAL
DML

39. A collection is an ordered group of ________ - all of the same ____
elements - type
expression
can
cannot

40. I/O: This package sends text output to SQLPlus
SQL
upper - coalesce
bind
DBMS_OUTPUT

41. I/O: Max number of bytes DBMS_OUTPUT can handle in a session.
disable
1 million
-
EXIT WHEN cv%NOTFOUND

42. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
RECORD OF ARRAYS
enclosing
rollback to ab
cannot

43. Bulk: The implicit cursor attribute that gives the number of rows affected by each DML statement in a FORALL statement.
composite
DBMS_SESSION.RESET_PACKAGE
statement
%BULK_ROWCOUNT

44. _________ exceptions can not be trapped with an error handler
=
Declaration
cannot
CASE_NOT_FOUND

45. Clause added to a cursor to use it with WHERE CURRENT OF.
PL/SQL Gateway
virtual machine
inclusive
for update of

46. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
**
exit when cgo%NOTFOUND
NUMBER
ROLLBACK

47. Cursors: You can just declare a cursor at first - then _______ it later.
EXECUTE IMMEDIATE
comments
define
ALTER SESSION

48. GOTO branches to a ________.
fail
a number := 10
label
enabled

49. Cursors: Give the statement to fetch data from cursor C into variable V.
fetch C into V
ex := tbl1 MULTISET EXCEPT tbl2
inquiry
5

50. The _______ package lets you wrap dynamically generated PL/SQL.
DBMS_DDL
package
context
ex := tbl1 MULTISET EXCEPT tbl2