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. An $ERROR directive ends with _____.
suspended
EXIT WHEN cv%NOTFOUND
PL/SQL Gateway
$END

2. Cursors: A cursor FOR loop can use an explicit or an _______ cursor.
REF CURSOR
explicit
does not
implicit

3. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
static
delimiters
v1 OUT number default 10
VARRAYs

4. I/O: Max number of bytes to pass in a call to DBMS_OUTPUT.PUT_LINE.
255
SQL
procedure P AUTHID definer;
versions

5. I/O: This package sends text output to SQLPlus
E Exception;
package
IR
DBMS_OUTPUT

6. %ROWTYPE gets the type of a table or _______.
pseudocolumn
cursor
simple and compound symbols
delimiters

7. Type: A __________ subtype has only a subset of the values of its base type.
transaction
UTL_FILE
invalid
constrained

8. Advantage: Performance: Storing and runnng procedures on the server minimizes ______ traffic.
RETURN
cannot
placeholders
network

9. Parameter that controls how much Oracle optimizes your PL/SQL code on compilation.
PLSQL_OPTIMIZE_LEVEL
statement
searched
manageable

10. In PL/SQL - what the variables in DML statements are turned into.
bind arguments
FOR UPDATE
**
REUSE SETTINGS

11. Exceptions: A GOTO statement CAN branch from an exception handler into an ________ block.
enclosing
HTP
same arguments
TYPE tV IS VARRAY(100) OF NUMBER

12. To insert or update on a table that has a collection column - you have to have _______ privilege on the collection type.
IN OUT
EXECUTE
definition
DBMS_SQL

13. Cursors: The first two keywords in a Cursor FOR loop.
FOR - IN
query
single-line
:=

14. List the transaction control statements.
CREATE TRIGGER
flushed
current
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

15. Dbms_sql: The DBMS_SQL procedure that closes a cursor.
CLOSE_CURSOR
ROLLBACK
vname CONSTANT VARCHAR2 := 'x';
PRAGMA EXCEPTION_INIT (E - -1);

16. Cursors: WHERE _______ ______ makes an UPDATE or DELETE apply to the current row of a cursor.
Gateway
CURRENT OF
executable
$END

17. Triggers: On a non-editioning view - you can only define __________ triggers.
FETCH
INSTEAD OF
fields
...

18. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
BINARY_INTEGER
OPEN
ordinary
DML

19. Declare a package procedure P with no arguments - and give it definer's rights.
explicit
composite
procedure P AUTHID definer;
a number := 10

20. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
cannot
concatenation
IR
network

21. For a collection - the NEXT method returns ____ when there is no next index value.

/*
compiles
3

22. Web: Abbreviation of PL/SQL Server Page.
PSP
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
mandatory
1

23. By default - OUT and IN OUT parameters are passed by _____.
nested tables - associative arrays
...
DBMS_PREPROCESSOR
value

24. The best candidates for result-caching are functions that are invoked frequently but depend on __________________ or never.
information that changes infrequently
-20 -000
255
pseudocolumn

25. Native compilation turns bytecode into ________ code.
IN OUT
HTP
machine
sql

26. VARRAYs are (always / never) sparse.
never
SUBTYPE numb IS number;
versions
sql

27. IDs: You (can/cannot) use keywords as ordinary user-defined identifiers.
EXIT WHEN
invoker's
can
RESULT_CACHE

28. Types: PL/SQL has all the _____ data types.
Wrap
DML
SQL
current

29. Fundamental: A label can only go directly before an _______ statement.
preprocessor
null string
executable
NESTED TABLE myColl STORE AS myCollTab

30. Triggers: A compound trigger can fire at more than one ______ point.
timing
cursor number
'
WHERE CURRENT OF

31. Conditional ______ allows backward compability with older database versions.
IR
compilation
for each row
%ROWTYPE

32. The FOR UPDATE OF clause references a (table / column).
column
implicit
SQL
EXIT WHEN cv%NOTFOUND

33. Delimiters: Statement terminator.
WHEN
numeric and cursor
OPEN
;

34. The first SQL statement in an autonomous routine begins a _______.
transaction
selection
True
..

35. In the IF syntax - is if ENDIF or END IF?
E Exception;
...
fail
END IF

36. Define a collection type - tV - which is a varray of 100 NUMBERs.
TYPE tV IS VARRAY(100) OF NUMBER
SQL cursor
injection
wrap

37. A CASE statement ends with ____ ____.
2
PSP
END CASE
USING

38. Cursors: The four cursor attributes - in alphabetical order.
currval and nextval
statement
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
dynamic

39. The attribute used to get the type of a row without knowing what it is.
savepoint ab
%ISOPEN.
%ROWTYPE
block

40. The three possible values of a BOOLEAN literal.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
CURRENT_USER - DEFINER
True - false or NULL.
scalar

41. A unit with AUTHID = CURRENT_USER is an ________ rights unit.

42. Kind of datatypes that support object-oriented programming in PL/SQL.
Abstract
5
name-value
transaction

43. What are the starting and ending keywords of a simple loop?
context
GET_LINE
LOOP - END LOOP
executable

44. Triggers: The clause to make a trigger run once for every affected row.
for each row
package
cannot
EXIT

45. The variable showing the user id of the session owner.
DML
SESSION_USER
alter procedure
SYS_REFCURSOR

46. Declare an unconstrained subtype of NUMBER called NUMB.
cannot
mandatory
SUBTYPE numb IS number;
dynamic

47. Command to assign to variables from a cursor.
DBMS_PREPROCESSOR
FETCH
flushed
$END

48. DynSQL: The three modes of arguments in the USING clause of EXECUTE IMMEDIATE.
IN - OUT - IN OUT
integrated
EXIT WHEN
SQL cursor

49. Cursors: The index variable in a cursor FOR loop (does/does not) need to be declared.
inquiry
virtual machine
does not
$IF

50. The attribute used to get the type of a column or variable without knowing what it is.
OTHERS
%TYPE
boolean
PL/SQL Gateway