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. Triggers: You (can/cannot) explicitly invoke a trigger.
cannot
Gateway
server
ordinary

2. A nested table has to be initialized using a ________ - named the same as the type.
wrap
constructor
cannot
in the database

3. Internally - the NUMBER type is stored as ______. Therefore - decimal numbers (such as prices) are stored exactly - with no rounding.
32 -000
anonymous
decimal
follows

4. A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
lock
DML
VARRAY - Nested table
:=

5. A type of compiler directive - begins with 'i'.
error code
inquiry
IN - OUT - IN OUT
colon

6. A type of compiler directive - begins with 's'.
selection
before insert or update on emps
Reserved words.
RETURN

7. The SET TRANSACTION statement affects only the ____________.
TIMESTAMP.
current transaction
constrained
calls

8. Cursors: The Oracle predefined REF CURSOR type.
INSTEAD OF
IN - OUT - IN OUT
SYS_REFCURSOR
searched

9. The FIRST method gets the first (element / index) of a collection.
multi-row
END CASE
index
DECLARE - BEGIN - EXCEPTION - END

10. Delimiters: Item separator.
-
composite
IF - CASE or LOOP

11. Cursors: Give the statement to fetch data from cursor variable CV into MyVar.
fetch cv into myvar
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
$IF $THEN $ELSE $END
exit when cgo%NOTFOUND

12. Fundamental: What is the exponentiation operator?
nested
**
ALTER SESSION
portable

13. I/O: The DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block _________.
BINARY_INTEGER
finishes
same arguments
PRAGMA AUTONOMOUS_TRANSACTION;

14. I/O: This package lets PL/SQL programs read and write OS files.
error code
BINARY_INTEGER
WHEN OTHERS THEN
UTL_FILE

15. What word is in a function header but not in a procedure header?
calls
fields
for update of
RETURN

16. A subprogram created inside a block is a _________ subprogram.
nested
BINARY_INTEGER
an AUTHID property
once

17. The lowest index of a VARRAY is ___.
FOR i IN REVERSE 1..10 LOOP
$END
database
1

18. Type: A __________ subtype has only a subset of the values of its base type.
constructor
v2 IN NUMBER default 20
5
constrained

19. A (simple / searched) CASE statement begins with CASE WHEN <boolean expression>.
library
searched
cursor
an autonomous routine

20. Pkgs: This procedure de-instantiates/resets all packages in this session.
nested
RAISE
DBMS_SESSION.RESET_PACKAGE
forward

21. Types: The PL/SQL type that is identical to PLS_INTEGER.
BINARY_INTEGER
unqualified
REF CURSOR
before insert or update on emps

22. What is the simplest statement for conditionally getting out of simple loop?
FETCH
strong
EXIT WHEN
boolean

23. Exceptions: An exception in the declarations section does not go to the __________ section.
once
exception
integrated
view

24. The declaration section is (optional/mandatory).
context
cannot
optional
r tRec := tRec(9 - 'Jones');

25. The valid values of the AUTHID property.
for each row
HTF
CURRENT_USER - DEFINER
a number := 10

26. Nested tables can become sparse via ______.
Oracle
NUMBER
error
deletions

27. Data dictionary view that shows the compiler settings for each compiled object.
variable
USER_PLSQL_OBJECT_SETTINGS
cannot
With a letter.

28. Types: Which type of arithmetic does PLS_INTEGER use - library or hardware?
hardware
NESTED TABLE myColl STORE AS myCollTab
can
subtype

29. Delimiters: The component indicator/selector.
DML
.
network
after

30. Give the clause you add to a CREATE TABLE statement - when there is a collection column myColl - that you want to be stored in a table called myCollTab.
NESTED TABLE myColl STORE AS myCollTab
r tRec := tRec(9 - 'Jones');
4K
memory

31. IDs: Maximum number of bytes in a user-defined identifier.
an AUTHID property
30
EDITION_NAME
boolean

32. To insert or update on a table that has a collection column - you have to have _______ privilege on the collection type.
autonomous triggers
package
invoker's
EXECUTE

33. DynSQL: The standard package that does dynamic SQL.
DBMS_SQL
ENABLE
FORALL i IN MyList.FIRST..MyList.LAST
names

34. Types: The only Oracle datetime type that doesn't begin with TIMESTAMP is ________.
invoker's
$$PLSQL_LINE
DATE
PLSQL_CCFLAGS

35. In a record - the internal components are called ______.
does not
fields
VARRAYs
PRAGMA AUTONOMOUS_TRANSACTION;

36. Collections are one of the two kinds of PL/SQL _______ data types.
must
SQL
composite
cannot

37. From a block nested inside a procedure P - reference variable V of the outer procedure.
error code
is not
SYS_REFCURSOR
p.v

38. Cursors: You give an explicit cursor a name and associate it with a _______.
WHEN OTHERS THEN
EXIT WHEN
**
query

39. A GOTO cannot branch into a...
dbms_output.put_line
lower sub-block
DBMS_SESSION
SUBTYPE numb IS number;

40. Define a nested table type - tN - which is a table NUMBERs.
autonomous triggers
nested
sql
TYPE tN IS TABLE of NUMBER

41. Delimiters: The left label delimiter.
Wrap
constrained
<<
explicit

42. IDs: You can use a reserved word as a _______ user-defined identifier.
quoted
literal
SQL
disable

43. You can use the BULK COLLECT INTO clause in a SELECT or a ______.
source
FETCH
ADT
fine-grained auditing

44. If a result-cached function's data source (based on the RELIES_ON clause) is updated - any cached results become _______.
invalid
associative arrays
invoker's
implicit cursor

45. To run a PIPE ROW statement in your autonomous routine - you must close any _____________.
anonymous block
open cv for
an autonomous routine
autonomous transaction

46. What are the 3 values of the BOOLEAN data type?
FETCH
CHAR - VARCHAR2
TRUE - FALSE and NULL
API

47. The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
iname
variable
INSTEAD OF
inquiry

48. Types: A data type family consists of a ______ type and its subtypes.
cannot
base
native dynamic
=

49. FOR-Looping over a collection from FIRST to LAST only works if the collection is _______.
dense
national
DATE
FETCH - INTO

50. Triggers: INSTEAD OF triggers only run on ______ views.
non-editioning views
RETURN
CHARARR
...