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. Records are one of the two kinds of PL/SQL _______ data types.
can
EXIT WHEN
1
composite

2. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
production
COUNT
CHAR - VARCHAR2
cannot

3. In interpreted mode - the PL/SQL VM interprets the _________.
implicit cursor
bytecode
FETCH
$IF

4. Exceptions: You can pass an ______ ________ to SQLERRM.
STANDARD
error code
DBMS_DDL
for each row

5. I/O: This DBMS_OUTPUT proc retrieves multiple lines from the buffer.
varray
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
can
GET_LINES

6. Triggers: The clause to make a trigger run once for every affected row.
:=
E Exception;
invoker's
for each row

7. The DEFINITION of a cursor is the part after the keyword __.
IS
L.V
CURSOR - RETURN
RETURN

8. Triggers: The code in a trigger is actually an ________ block.
reference
predefined
anonymous
system global area

9. Official acronym for PL/SQL's runtime engine.
obfuscates
FETCH
PVM
cannot

10. Cursors: The statement where you pass the parameters to a cursor.
OPEN
SESSION_USER
wrap
USER_PROCEDURES

11. Delimiters: Item separator.
bind
SQL

rollback to ab

12. Types: One of the 4 PL/SQL things that have a data type (begins with 'v').
cursor
variable
255
timing

13. Declare a package procedure P with no arguments - and give it invoker's rights.
EDITION_NAME
**
ALTER SESSION
procedure P AUTHID current_user;

14. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
CLOSE_CURSOR
name
1
ROLLBACK

15. Triggers: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
flushed
reference
production
follows

16. The three types of compiler warnings.
VARRAY - Nested table
cursor
EVERE - PERFORMANCE - INFORMATIONAL
close C

17. Exceptions: Start of the range of error codes for RAISE_APPLICATION_ERROR.
-20 -000
LOOP - END LOOP
DBMS_SQL
procedure P AUTHID definer;

18. Fundamental: A label can be used to name an ______ block.
suspended
TOO_MANY_ROWS
after
anonymous

19. Cursors: Give the first 3 words to open a cursor variable called cv.
open cv for
BINARY_INTEGER
%
after

20. The command used to populate PLSQL_CCFLAGS.
ALTER SESSION
END LOOP
name-value
composite

21. The valid values of the AUTHID property.
WHERE CURRENT OF
block
aggregate - analytical
CURRENT_USER - DEFINER

22. The BEGIN section is (optional/mandatory)
literal
<<
mandatory
raise_application_error

23. Web: mod_plsql is an implementation of the PL/SQL _________.
machine code
Gateway
CLOSE
scalar

24. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
<> - != - ^=
suspended
will not
OPEN

25. Triggers: There are __ types of triggers.
5
executable
is
context

26. Triggers: Programmers (as opposed to DBAs) mostly make use of _____ triggers.
IN - OUT - IN OUT
DML
EXECUTE
CLOSE

27. Cursors: The first two keywords in a Cursor FOR loop.
FOR - IN
DR
package
dense

28. Type: A __________ subtype has only a subset of the values of its base type.
constrained
index
END IF
cannot

29. An anonymous block is a(n) ____ statement.
label
SUBTYPE numb IS number;
TYPE tV IS VARRAY(100) OF NUMBER
SQL

30. DynSQL: EXECUTE IMMEDIATE can be used for any SQL statement or PL/SQL block - except for ________ queries.
EXIT WHEN
r t.%ROWTYPE;
multi-row
injection

31. The $ERROR directive is used to make compilation ____.
SYS_CONTEXT
fields
fail
SUBTYPE n IS number not null;

32. Cursors: What is the 'name' of the implicit cursor?
E Exception;
SESSION_USER
raise_application_error(-20 -100 - 'Bad');
SQL

33. Wrapping can be done with the ____ executable.
wrap
literals
procedure - function
cursor

34. The meaning of 'FGA'.
composite
executable
finishes
fine-grained auditing

35. Labels for blocks and statements are enclosed in '__' brackets.
an enclosing block
<<>>
Declaration
procedure - function

36. Exceptions: The maximum length of an Oracle error message is ____ characters
suspended
index
512
definer's

37. An ordinary character literal is composed of characters in the ________ character set.
SQLCODE
database
scalar
strong

38. Assignment of PLSQL_CCFLAGS (cannot / can) be included in ALTER PROCEDURE ... COMPILE ...
exception
can
bind arguments
REF CURSOR

39. Fundamental: Which one of these is a compound symbol: = - :=
execute immediate
a number := 10
hardware
:=

40. Bulk: The implicit cursor attribute that gives the number of rows affected by each DML statement in a FORALL statement.
is
not the same
%BULK_ROWCOUNT
HTP

41. Exceptions: A GOTO statement cannot branch into an _______ ________.
multi-row
must
exception handler
=

42. Simple CASE statement checks for different values of a single _________
4
never
RETURN
expression

43. DynSQL: The standard package that does dynamic SQL.
commit - rollback
current
DBMS_SQL
array

44. Exceptions: The package that defines pre-defined exceptions.
STANDARD
DBMS_PREPROCESSOR
EXECUTE
timing

45. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
OTHERS
<> - != - ^=
sequence pseudo-columns
nested tables - associative arrays

46. The 2 characters that begin all inquiry directives.
Wrap
$$
True - false or NULL.
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT

47. Value of a character literal with zero characters.
Ada
autonomous transaction
RETURN

48. A result-cached function caches its results in the _____ _____ ____ (SGA)
# - $ - _
system global area
IS
32 -000

49. To enable result-caching for a function - use the _____________ clause.
loop
will not
aliases
RESULT_CACHE

50. Cursors: A session cursor that is automatically created and managed by PL/SQL.
value
implicit cursor
EXIT WHEN
FETCH - BULK COLLECT INTO