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. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
cannot
static boolean
Server Pages
bind arguments

2. The compiler directive used to select which code to compile.
library
static
%BULK_ROWCOUNT
$IF

3. Triggers: To simulate having triggers on SELECT statements - you can try ___ (3-letter acronym).
FGA
after
<<>>
can

4. The allowed data types of user-defined conditional compilation directives are boolean and _______.
a cursor
composite
plb
pls_integer

5. This is a CASE (expression / statement): myvar := CASE num WHEN 1 THEN 'good' WHEN 2 THEN 'bad' END;
REF CURSOR
5
RECORDS
expression

6. Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
REF CURSOR
finishes
CHAR - VARCHAR2
fetch C into V

7. I/O: If the current (outermost) PL/SQL block ends with an unhandled exception - the output buffer will not be ___________.
flushed
close C
-
predefined

8. Simple CASE statement checks for different values of a single _________
dynamic
DBMS_SESSION.RESET_PACKAGE
expression
IF - CASE or LOOP

9. Types: ________ data types have no internal components.
pseudocolumn
scalar
selection
inquiry

10. The SET TRANSACTION statement affects only the ____________.
dynamic
CASE_NOT_FOUND
current transaction
procedure P AUTHID current_user;

11. Web: mod_plsql is an implementation of the PL/SQL _________.
composite
delimiters
Gateway
LOOP - END LOOP

12. What are the starting and ending keywords of a simple loop?
scalar
LOOP - END LOOP
PLSQL_CCFLAGS
*/

13. By default - an IN parameter is passed by ______.
execute immediate
reference
GET_LINES
30

14. TCL: Create a savepoint called ab
4
'
external subprogram
savepoint ab

15. Delimiters: The association operator.
=>
numeric and cursor
END LOOP
cursor

16. PL/SQL lets you define two kinds of composite data types: _______ and _______
follows
collections - records
%ROWTYPE
DR

17. IDs: You can use a reserved word as a _______ user-defined identifier.
:=
quoted
function f (n number) return number result_cache relies_on (emps);
cannot

18. A collection is an ordered group of ________ - all of the same ____
elements - type
SQL
=
single-line

19. Conversion: If the specific value is suitable - ____ or _______ values can convert to almost any datatype
1 million
=
CHAR - VARCHAR2
code

20. What is the exception that is implicitly raised in a CASE statement with no ELSE clause?
DBMS_SESSION
CASE_NOT_FOUND
create procedure
bind

21. I/O: The DBMS_OUTPUT function that writes output without a newline character.
END
PUT
loop
FETCH

22. A type of compiler directive - begins with 'i'.
create procedure
pls_integer
inquiry
close C

23. What are the two sequence pseudo-columns?
v2 IN NUMBER default 20
v1 OUT number default 10
currval and nextval
v3 IN OUT NUMBER

24. Command to create a procedure.
BINARY_INTEGER
hardware
create procedure
DBMS_SESSION

25. Cursors: You can just declare a cursor at first - then _______ it later.
FORALL and BULK COLLECT
define
CURSOR - RETURN - IS
finishes

26. Cursors: A cursor variable's type must be a ____ ______ type.
RESULT_CACHE
RETURN
REF CURSOR
RETURN

27. Exceptions: If there is no handler for an exception - PL/SQL returns an ________ exception error to the invoker or host environment.
1 million
current BEGIN-END block
unhandled
executable

28. IDs: Maximum number of bytes in a user-defined identifier.
nested
nested tables - associative arrays
subtype
30

29. Delimiters: The assignment operator.
decimal
:=
portable
procedure - function

30. I/O: The string table type in DBMS_OUTPUT for holding lines of text.
SUBTYPE n IS number not null;
CHARARR
after
1

31. Web: Abbreviation of PL/SQL Server Page.
PSP
nested
CURRENT_USER - DEFINER
-20 -000

32. Fundamental: What is the exponentiation operator?
STANDARD
block
**
procedure P AUTHID current_user;

33. A GOTO statement can branch from an exception handler into...
function f (n number) return number result_cache relies_on (emps);
INTO
bb:true
an enclosing block

34. Triggers: By default - a trigger is created in the ______ state.
PLS_INTEGER
USER_PROCEDURES
DBMS_SQL
enabled

35. Triggers: A compound trigger can fire at more than one ______ point.
composite
RETURN
dynamic
timing

36. Exceptions: An unhandled exception ________ to the enclosing block.
propagates
drop procedure
collections - records
OPEN_CURSOR

37. Command to make a no-op (omit semicolon).
SQLCODE

pseudocolumn
PLS_INTEGER

38. Conversion: Just about everything will implicitly convert to _____ or _________.
-
bytecode
portable
CHAR - VARCHAR2

39. PLS_INTEGER has its arithmetic implemented in ________ - so it is very fast.
hardware
not the same
STANDARD
function f (n number) return number result_cache relies_on (emps);

40. The _____ and ____ _____ collection types can be used in database table columns.
production
VARRAY - Nested table
procedure P AUTHID definer;
expression

41. A (simple / searched) CASE statement begins with CASE <expression>.
simple
LOOP
commit - rollback
block

42. Triggers: Programmers (as opposed to DBAs) mostly make use of _____ triggers.
upper - coalesce
DML
invoker's
constant

43. DynSQL: EXECUTE IMMEDIATE can be used for any SQL statement or PL/SQL block - except for ________ queries.
multi-row
cannot
WHEN
/*

44. The BETWEEN operator is (inclusive / exclusive).
integer
inclusive
bind
True - false or NULL.

45. Types: One of the 4 PL/SQL things that have a data type (begins with 'c').
...
255
constant
not a number

46. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
will not
create procedure
<<>>
simple

47. Cursors: What is the 'name' of the implicit cursor?
SQL
currval and nextval
before insert or update on emps
BINARY_INTEGER

48. How many types of compiler directives are there?
HTF
compilation
3
PLS_INTEGER

49. DynSQL: The clause that supplies bind arguments to EXECUTE IMMEDIATE.
RESULT_CACHE
function f (n number) return number result_cache relies_on (emps);
USING
DBMS_DDL

50. A ___________ behaves like a table column - but it is not stored in the table.
execute immediate
32 -000
TOO_MANY_ROWS
pseudocolumn