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. IDs: List the two types of user-defined identifiers.
plb
EXECUTE IMMEDIATE
Ordinary - Quoted.
PLSQL_CCFLAGS

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

3. I/O: The DBMS_OUTPUT function that writes output without a newline character.
PUT
FGA
autonomous triggers
RETURN

4. DynSQL: The standard package that does dynamic SQL.
can
..
code
DBMS_SQL

5. With Edition-based Redefinition enabled - the unique specification of an object requires OWNER - OBJECT_NAME and ___________.
END IF
EXIT
EDITION_NAME
OPEN

6. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
REF CURSOR
associative
DATE
..

7. What do you FETCH from?
EVERE - PERFORMANCE - INFORMATIONAL
2
a cursor
VARRAYs

8. With a result-cached function - it uses a cached value when the function is called again with the...
ENABLE
same arguments
injection
INSTEAD OF

9. _______ arrays can be sparse or dense - depending on how you fill them.
associative
v3 IN OUT NUMBER
IR
type T is ref cursor

10. Delimiters: Item separator.

after
statement
wrap

11. Official acronym for PL/SQL's runtime engine.
cannot
PVM
NDS
significant

12. Cursors: The four cursor attributes - in alphabetical order.
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
bytecode
function f (n number) return number result_cache relies_on (emps);
can

13. Cursors: An explicit cursor (can/cannot) accept parameters.
names
exception handler
native dynamic
can

14. A large OUT or IN OUT parameter will waste a lot of space and CPU if it is passed by _____.
RETURN
cursor c;
value
suspended

15. DynSQL: Maximum string length to pass to EXECUTE IMMEDIATE.
cursor
do not
%TYPE
32 -000

16. Types: PL/SQL has all the _____ data types.
SQL
512
Wrap
SYS_CONTEXT

17. Give the first line of the FOR loop with index i going from 1 to 10 in reverse.
composite
rollback to ab
explicit
FOR i IN REVERSE 1..10 LOOP

18. A CASE statement ends with ____ ____.
END CASE
binding
flushed
execute immediate

19. IDs: The type of words that cannot be user-defined identifiers.
pls_integer
Reserved words.
user-defined
EVERE - PERFORMANCE - INFORMATIONAL

20. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
sequence pseudo-columns
NUMBER
does not
SQL

21. Exceptions: If there is no handler for an exception - PL/SQL returns an ________ exception error to the invoker or host environment.
boolean
Declaration
unhandled
selection

22. I/O: This package has hypertext functions that generate HTML tags
32 -000
HTF
close C
USING

23. I/O: The most basic PL/SQL way to give output to the user.
reference
dbms_output.put_line
slower
scalable

24. What are the two sequence pseudo-columns?
currval and nextval
%ISOPEN.
timing
cursor

25. To insert or update on a table that has a collection column - you have to have _______ privilege on the collection type.
invoker's
simple and compound symbols
EXECUTE
%ROWTYPE

26. PLS_INTEGER has its arithmetic implemented in ________ - so it is very fast.
cursor c;
SQL
hardware
3

27. I/O: This package lets PL/SQL programs read and write OS files.
vname CONSTANT VARCHAR2 := 'x';
searched
preprocessor
UTL_FILE

28. Cursors: A cursor variable's type must be a ____ ______ type.
IR
static boolean
REF CURSOR
system global area

29. Triggers: On a non-editioning view - you can only define __________ triggers.
INSTEAD OF
index
DBMS_PIPE
can

30. Exceptions: A GOTO statement cannot branch into an _______ ________.
E Exception;
EXIT WHEN cv%NOTFOUND
exception handler
implicit CASE_NOT_FOUND exception

31. Exceptions: The ________ handler must always be the last handler.
name and datatype
OTHERS
include
database

32. The two kinds of CASE statements are simple and ________.
searched
associative
open C(p)
identifiers

33. Associative arrays (do / do not) have to be initialized with a constructor.
SQL
do not
drop procedure
invoker's

34. I/O: The PL/SQL I/O packages (can/cannot) accept input directly from the keyboard.
code
savepoint ab
cannot
implicit cursor

35. Triggers: There are __ types of triggers.
value
5
virtual machine
definer's

36. Exceptions: User-defined exceptions must have _______.
CHARARR
names
Double quotes.
IS

37. With conditional compilation you can avoid compiling debug code in the _______ environment.
production
binding
virtual machine
CHAR - VARCHAR2

38. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
cursor variable
value
cannot
DML

39. I/O: One of the 3 types of data that can be passed to the DBMS_OUTPUT buffer.
searched
create
binding
VARCHAR2

40. The FORALL statement to loop over a collection MyList using index i.
PLS_INTEGER
FORALL i IN MyList.FIRST..MyList.LAST
pseudocolumn
calls

41. The default value of the AUTHID property.
close C
comments
DEFINER
boolean

42. In a collection - the internal components are called _______.
inquiry
invoker's
elements
index

43. Cursors: The first two keywords of a basic fetch statement.
aggregate - analytical
finishes
FETCH - INTO
pseudocolumn

44. Triggers: The clause to make a trigger run once for every affected row.
variable
END
for each row
closed

45. The three possible values of a BOOLEAN literal.
True - false or NULL.
EXIT
<> - != - ^=
implicit

46. Triggers: INSTEAD OF triggers only run on ______ views.
view
do not
definer's
non-editioning views

47. If you are in a procedure inside a package - the compilation unit is the _______.
CLOSE_CURSOR
collections - records
package
OTHERS

48. I/O: The DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block _________.
**
OLD
finishes
follows

49. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
INTO
ROLLBACK
memory
CURSOR - RETURN

50. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
return value
WHEN OTHERS THEN
TRUE - FALSE and NULL
OTHERS