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. The LIMIT method is only meaningful for _______.
DECLARE - BEGIN - EXCEPTION - END
transaction
timing
VARRAYs

2. Cursors: The FOR UPDATE phrase goes at the (beginning/end) of the cursor definition.
reference
...
searched
end

3. FOR-Looping over a collection from FIRST to LAST only works if the collection is _______.
an enclosing block
dense
*/
simple and compound symbols

4. Declare a constrained subtype of NUMBER called N that is not null.
SUBTYPE n IS number not null;
explicit cursor
expression
REUSE SETTINGS

5. A PL/SQL block that is not stored.
p.v
API
anonymous block
wrap

6. Make a FOR loop that executes NULL 10 times counting in reverse - using index i.
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
SIMPLE_INTEGER
national
1

7. Exceptions: Type ______ to re-raise the current exception.
RAISE
USING
varray
object

8. Triggers: On a non-editioning view - you can only define __________ triggers.
INSTEAD OF
currval and nextval
slower
NEW

9. TCL: Create a savepoint called ab
loop
open C(p)
savepoint ab
bind

10. Give the value list in the PLSQL_WARNINGS parameter to turn on SEVERE compiler warnings and make compiler warning 06002 be an error.

11. Fundamental: identifiers (include/do not include) reserved words.
IS
machine code
HTF
include

12. Cursors: Give the statement to close cursor C.
procedure P AUTHID current_user;
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
close C
subtype

13. The _______ package lets you wrap dynamically generated PL/SQL.
error code
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
names
DBMS_DDL

14. A large OUT or IN OUT parameter will waste a lot of space and CPU if it is passed by _____.
scalar
FORALL and BULK COLLECT
current
value

15. Cursors: A pointer to a cursor is called a ______ _______.
column
cursor variable
searched
network

16. The three possible values of a BOOLEAN literal.
flushed
ex := tbl1 MULTISET EXCEPT tbl2
True - false or NULL.
EXIT

17. In a FORALL loop - you (can / cannot) use the index variable for purposes other than indexing into the collection.
DBMS_SESSION
cannot
varray
can

18. The three types of compiler warnings.
EVERE - PERFORMANCE - INFORMATIONAL
TOO_MANY_ROWS
definition
HTF

19. Fundamental: One of the 4 types of lexical units: L_____
OPEN_CURSOR
literals
REUSE SETTINGS
a number := 10

20. Declare a package procedure P with no arguments - and give it invoker's rights.
procedure P AUTHID current_user;
DBMS_DDL
strong
32 -000

21. The RESULT_CACHE clause comes (before / after) the RETURN clause.
API
finishes
after
cursor variable

22. Exceptions: An exception in the declarations section does not go to the __________ section.
exception
-20 -000
invoker's
sql

23. To create a collection type - use the TYPE command in PL/SQL and the ______ _____ command in SQL.
end
name-value
USER_PROCEDURES
CREATE TYPE

24. DynSQL: Native Dynamic SQL
stored
autonomous transaction
NDS
SQL cursor

25. You (can / cannot) wrap trigger source code.
finishes
%
cannot
$IF

26. The collection method that tells the number of elements in the collection.
upper - coalesce
IN - OUT - IN OUT
COUNT
compile

27. Cursors: The type definition of a WEAK ref cursor type has no _______ clause.
create
RETURN
a number := 10
CURSOR - RETURN - IS

28. DynSQL: The clause that receives single-row query output from EXECUTE IMMEDIATE.
%BULK_ROWCOUNT
ADT
INTO
FETCH

29. Triggers: The clause to make a trigger run after inserting into table EMPS.
selection
%BULK_ROWCOUNT
after insert on emps
associative

30. Exceptions: Pragma that links an exception to an Oracle error code.
injection
exception handler
EXCEPTION_INIT
varray

31. Bulk: The keywords of a bulk collect FETCH statement.
FETCH - BULK COLLECT INTO
identifiers
will not
create procedure

32. You can put a label before any executable PL/SQL _________.
bytecode
associative
statement
for each row

33. Associative array types (can / cannot) be defined in the database.
constant
bind
associative
cannot

34. For a collection - the NEXT method gets the next higher (element / index).
for update of
...
index
reference

35. Declare an input NUMBER parameter - v2 - that defaults to 20.
%ISOPEN.
WHEN
autonomous triggers
v2 IN NUMBER default 20

36. A basic PL/SQL datatype that SQL does not have.
cursor
dense
<> - != - ^=
boolean

37. Give the statement to make a program autonomous.
DBMS_PREPROCESSOR
disable
PRAGMA AUTONOMOUS_TRANSACTION;
30

38. Cursors: You can just declare a cursor at first - then _______ it later.
information that changes infrequently
disable
Ordinary - Quoted.
define

39. Which two collection types are unbounded?
suspended
variable
nested tables - associative arrays
bytecode

40. Triggers: To simulate having triggers on SELECT statements - you can try ___ (3-letter acronym).
flushed
FGA
not the same
expression

41. Web: The gateway that allows a web browser to invoke a PL/SQL procedure through an HTTP listener.
enclosing
function f (n number) return number result_cache relies_on (emps);
PL/SQL Gateway
Apache

42. An autonomous program runs in its own __________.
USER_PROCEDURES
associative
can
transaction

43. Fundamental: What are the 3 compound symbols for 'not equal to'?
DBMS_SESSION
dense
<> - != - ^=
DATE

44. Triggers: The clause to make a trigger run before inserting or updating table EMPS.
for update of
before insert or update on emps
FETCH
FOR - IN

45. IDs: The type of words that cannot be user-defined identifiers.
source
Reserved words.
machine
upper - coalesce

46. Type: An ________ subtype has all the values of its base type.
is
will not
unconstrained
$$PLSQL_LINE

47. Delimiters: Single-line comment marker.
null string
executable
-
<<>>

48. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
..
ADT
SESSION_USER
NUMBER

49. Cursors: Define a ref cursor type called T.
type T is ref cursor
cannot
for update of
searched

50. Exceptions: The keyword that begins an exception handler.
varray
name
WHEN
concatenation