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 two kinds of CASE statements are simple and ________.
UTL_SMTP
definition
boolean
searched

2. The EXCEPTION section is (optional/mandatory).
DEFINER
optional
cursor
r t.%ROWTYPE;

3. With a result-cached function - it uses a cached value when the function is called again with the...
same arguments
elements - type
scalar
512

4. The command used to populate PLSQL_CCFLAGS.
anonymous
REF CURSOR
ALTER SESSION
procedure - function

5. Assignment of PLSQL_CCFLAGS (cannot / can) be included in ALTER PROCEDURE ... COMPILE ...
1
ALTER SESSION
can
RETURN

6. In interpreted mode - PL/SQL code is run in a _______ _______.
cursor
virtual machine
PLS_INTEGER
subtype

7. What are the 3 values of the BOOLEAN data type?
IR
decimal
does not
TRUE - FALSE and NULL

8. Setting CURRENT_SCHEMA only changes what is prepended to __________ references.
SESSION_USER
Oracle
Ada
unqualified

9. Exceptions: When an exception is raised inside a cursor FOR loop - the cursor is _______ implicitly.
Abstract
VARCHAR2
varray
closed

10. Exceptions: A GOTO statement CAN branch from an exception handler into an ________ block.
OUT
unconstrained
enclosing
%TYPE

11. Exceptions: The keyword that begins an exception handler.
STANDARD
optional
WHEN
collections

12. PL/SQL is a ________ typed programming language.
statically
DBMS_OUTPUT
E Exception;
EXIT WHEN

13. An incomplete type only has a ____.
=>
simple
name
information that changes infrequently

14. Types of blocks: A pl/sql block that is not stored in the database.
same
anonymous
ALTER SESSION
cannot

15. In a collection - the internal components are called _______.
TOO_MANY_ROWS
cursor c;
elements
order

16. In a collection - the internal components always have the _____ data type.
iname
same
SESSION_USER
is

17. I/O: The most basic PL/SQL way to give output to the user.
dbms_output.put_line
IF - CASE or LOOP
Wrap
FORALL i IN MyList.FIRST..MyList.LAST

18. I/O: SQLPlus command for turning on buffered output.
OTHERS
same
set serveroutput on
bind arguments

19. Types: One of the 4 PL/SQL things that have a data type (begins with 'p').
exception handler
<<>>
unqualified
parameter

20. PL/SQL lets you define two kinds of composite data types: _______ and _______
CHAR - VARCHAR2
FOR - IN
USING
collections - records

21. Cursors: Give the first 3 words to open a cursor variable called cv.
does not
elements - type
forward
open cv for

22. The variable showing the user id of the session owner.
set serveroutput on
comments
block
SESSION_USER

23. IDs: The 3 non-alphanumeric characters that can be in an ordinary user-defined identifier.
# - $ - _
END IF
upper - coalesce
SQL

24. A CASE expression ends with _____.
'ENABLE:SEVERE' - 'ERROR:06002'
cursor c;
END
library

25. The AUTHID property applies to PL/SQL objects that contain _____.
code
v1 OUT number default 10
INSTEAD OF
identifiers

26. In a record - the internal components are called ______.
PLS_INTEGER
can
fields
;

27. The 2 characters that begin all inquiry directives.
$$
Declaration
# - $ - _
invoker's

28. Cursors: A cursor with a name is an _________ cursor.
5
E Exception;
explicit
ENABLE

29. The Wrap utility (does / does not) encrypt source code.
do not
varray
.
does not

30. When an autonomous routine begins execution - the main transaction is ________.
suspended
SYS_REFCURSOR
definer's
cannot

31. Unlike the CASE statement - the CASE expression does not have the...
package
do not
index
implicit CASE_NOT_FOUND exception

32. Loops: What is the index range operator in a FOR loop?
..
CURRENT OF
3
varray

33. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
Gateway
implicit
DML
predefined

34. The method to add one or more elements to a nested table or VARRAY is ______.
DML
literals
EXTEND
iname

35. _______ arrays can be sparse or dense - depending on how you fill them.
collections
Gateway
anonymous
associative

36. Cursors: The second keyword of a FETCH statement.
NESTED TABLE myColl STORE AS myCollTab
INTO
invoker's
searched

37. The two main parts of a package are the ____ and the _____.
INTO
specification - body
after
statement

38. Declare an associative array type - t - whose elements are of type rPerson - and whose index is PLS_INTEGER.
names
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
BOOLEAN
upper - coalesce

39. The source for wrapping begins with the word ______.
cannot
query
create
NESTED TABLE myColl STORE AS myCollTab

40. Web: mod_plsql is an implementation of the PL/SQL _________.
SQL
for update of
Gateway
OPEN

41. A FOR-loop counter is defined only within the _____.
unqualified
always
REF CURSOR
loop

42. An incomplete type is a type created by a ______ type definition.
Gateway
INTO
forward
SQLPlus

43. Cursors: Give the statement to fetch data from cursor C into variable V.
specification - body
fetch C into V
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
True - false or NULL.

44. Types: In addition to the SQL data types - PL/SQL has ___ more scalar data types.
numeric and cursor
follows
4
enabled

45. Cursors: Give the statement to fetch data from cursor variable CV into MyVar.
anonymous
fetch cv into myvar
FORALL i IN MyList.FIRST..MyList.LAST
nested tables - associative arrays

46. The NOCOPY option on an OUT or IN OUT parameter - causes it to be passed by ______.
1 million
SUBTYPE numb IS number;
reference
composite

47. The BEGIN section is (optional/mandatory)
open cv for
mandatory
Double quotes.
3

48. The allowed data types of user-defined conditional compilation directives are boolean and _______.
pls_integer
do not
Ordinary - Quoted.
static boolean

49. What is the simplest statement for conditionally getting out of simple loop?
REF CURSOR
EXIT WHEN
**
value

50. Cursors: The two keywords in a mere cursor declaration (one is optional).
CURSOR - RETURN - IS
CURSOR - RETURN
end
can