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. Define a collection type - tA - which is an associative array of NUMBERS - indexed with varchar2(20) values.
BINARY_INTEGER
INTO
do not
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)

2. The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
PUT_LINE
composite
definition
follows

3. Exceptions: An unhandled exception ________ to the enclosing block.
CHAR - VARCHAR2
255
propagates
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

4. A __________ can have an AUTHID property
cannot
cannot
anonymous
package

5. Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
multi-row
$IF $THEN $ELSE $END
REF_CURSOR
REF CURSOR

6. INSTEAD OF triggers are alternatives to ___ triggers
current BEGIN-END block
production
DML
PUT_LINE

7. Exceptions: Declare an exception called E.
compile
in the database
%ISOPEN.
E Exception;

8. Kind of comment not to put in a PL/SQL block to be processed dynamically by an Oracle Precompiler program.
=
single-line
ENABLE
Keywords and predefined names.

9. Delimiters: Item separator.
multi-row

name-value
Web Toolkit

10. I/O: One of the 3 types of data that can be passed to the DBMS_OUTPUT buffer.
VARCHAR2
END CASE

fine-grained auditing

11. The attribute used to get the type of a column or variable without knowing what it is.
%TYPE
FETCH
WHERE CURRENT OF
rollback to ab

12. A GOTO statement can branch from an exception handler into...
an enclosing block
type T is ref cursor
TOO_MANY_ROWS
**

13. Delimiters: The component indicator/selector.
.
sequence pseudo-columns
fetch C into V
GET_LINES

14. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
executable
RETURN
RECORD OF ARRAYS
iname

15. Web: PL/SQL _____ _______ lets you to develop Web applications made of PL/SQL procedures.
HTP
Web Toolkit
CURSOR - RETURN
SQLPlus

16. DynSQL: Native Dynamic SQL
EVERE - PERFORMANCE - INFORMATIONAL
NDS
512
source code

17. Advantage: PL/SQL is tightly _______ with Oracle SQL.
variable
follows
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
integrated

18. Cursors: A session cursor that a PL/SQL user creates and manages.
fail
explicit cursor
..
collections - records

19. DynSQL: The clause that receives single-row query output from EXECUTE IMMEDIATE.
INTO
SESSION_USER
ordinary
label

20. How many types of compiler directives are there?
3
executable
SYS_CONTEXT
USER_PROCEDURES

21. Exceptions: Write the call (2 args) to associate message 'Bad' with error code -20 -100.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

22. Cursors: Give the statement to open cursor C.
open C
rollback to ab
DECLARE - BEGIN - EXCEPTION - END
SESSION_USER

23. Cursors: The two keywords in a mere cursor declaration (one is optional).
CURSOR - RETURN
FETCH
network
can

24. Fundamental: Character used to escape a single quote inside a literal.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

25. Exceptions: A GOTO statement cannot branch into an _______ ________.
SESSION_USER
RECORDS
exception handler
CHAR - VARCHAR2

26. Types: ______ data types are those storing values with no internal components.
32 -000
names
scalar
1

27. With static typing - type checking is done at ______ time.
lowest
exception handler
does not
compile

28. Cursors: Give the statement to fetch data from cursor variable CV into MyVar.
SIMPLE_INTEGER
EXIT WHEN cv%NOTFOUND
bytecode
fetch cv into myvar

29. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
-20 -000
dynamic
Double quotes.
will not

30. Triggers: You create a trigger with the _______ ________ statement.
PUT_LINE
CREATE TRIGGER
NEW
base

31. A nested table has to be initialized using a ________ - named the same as the type.
IF - CASE or LOOP
lowest
increment
constructor

32. Wrapped code (is / is not) not backward compatible.
is not
executable
SQLERRM
propagates

33. A CASE statement ends with ____ ____.
32 -000
'ENABLE:SEVERE' - 'ERROR:06002'
END CASE
binding

34. Exceptions: The _________ package defines the PL/SQL environment.
STANDARD
an AUTHID property
is certain
selection

35. If none of a CASE expression's conditions are TRUE - it returns _____.
is

PVM
execute immediate

36. Triggers: The clause to make a trigger run once for every affected row.
context
for each row
label
SUBTYPE numb IS number;

37. Triggers: There are __ types of triggers.
WHEN
explicit cursor
EXCEPTION_INIT
5

38. Term for a character literal with zero characters.
-
bind
null string
stored

39. Mode designation for an input parameter.
IN
RETURN
source code
CREATE TYPE

40. Triggers and views always have _________ rights.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

41. To run DDL statements - a trigger must be autonomous and must use ______ ______ SQL.
...
database
native dynamic
Abstract

42. Conversion: If the specific value is suitable - ____ or _______ values can convert to almost any datatype
CHAR - VARCHAR2
unqualified
execute immediate
TYPE tV IS VARRAY(100) OF NUMBER

43. Delimiters: Statement terminator.
type T is ref cursor
BINARY_INTEGER
context
;

44. The $ERROR directive is used to make compilation ____.
PUT
finishes
do not
fail

45. The two kinds of CASE statements are simple and ________.
pls_integer
virtual machine
searched
<<

46. The RESULT_CACHE clause comes (before / after) the RETURN clause.
REF_CURSOR
Ada
nested
after

47. Where an external subprogram is stored.
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
RESULT_CACHE
in the database
DBMS_PIPE

48. Define a collection type - tV - which is a varray of 100 NUMBERs.
colon
executable
end
TYPE tV IS VARRAY(100) OF NUMBER

49. _______ arrays can be sparse or dense - depending on how you fill them.
slower
associative
colon
sequence pseudo-columns

50. Cursors: A cursor that lives in session memory until the session ends.
session cursor
open C
DBMS_OUTPUT
order