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. To recompile a procedure - use the _____ ______ command.
alter procedure
REUSE SETTINGS
collections - records
quoted

2. Advantage: Performance: Stored subprograms are ______ in memory.
cached
rollback to ab
iname
placeholders

3. Exceptions: The keyword that begins an exception handler.
-20 -000
WHEN
L.V
...

4. How to reference a variable V declared in a block labelled L: ____.
literals
procedure P AUTHID definer;
L.V
30

5. To enable result-caching for a function - use the _____________ clause.
DML
value
RESULT_CACHE
SQL cursor

6. VARRAYs are (always / never) sparse.
DECLARE - BEGIN - EXCEPTION - END
anonymous block
anonymous
never

7. Type: An ________ subtype has all the values of its base type.
FALSE
unconstrained
5
:=

8. Set flag bb true: ALTER SESSION SET PLSQL_CCFLAGS= '____'
FETCH - BULK COLLECT INTO
bb:true
current
Wrap

9. The BEGIN section is (optional/mandatory)
GET_LINE
$$
suspended
mandatory

10. A record is a group of related data items stored in fields - each with its own...
name and datatype
INTO
4K
PL/SQL Gateway

11. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
cannot
DATE '2005-12-25'
binding
suspended

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

13. Default file extension of wrap's input file.
UTL_HTTP
sql
OPEN
END LOOP

14. Delimiters: The assignment operator.
:=
current
cursor c;
enclosing

15. Characters that end a multi-line comment.
E Exception;
%ISOPEN.
*/
stored

16. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
32 -000
static
-
ROLLBACK

17. Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
ALTER SESSION
...
autonomous transaction
$$

18. Exceptions: Start of the range of error codes for RAISE_APPLICATION_ERROR.
portable
dynamic
END IF
-20 -000

19. With conditional compilation you can avoid compiling debug code in the _______ environment.
statically
production
TIMESTAMP.
PVM

20. What is the simplest statement for conditionally getting out of simple loop?
PSP
FETCH - BULK COLLECT INTO
bind
EXIT WHEN

21. The lowest index of a nested table is ___.
1
injection
OPEN
cursor

22. Triggers: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
follows
TOO_MANY_ROWS
HTP
package

23. Advantage: PL/SQL is tightly _______ with Oracle SQL.
*/
integrated
cannot
True - false or NULL.

24. By default - an IN parameter is passed by ______.
function f (n number) return number result_cache relies_on (emps);
sequence pseudo-columns
reference
enabled

25. Mode designation for an input/output parameter.
production
IN OUT
implicit
$$PLSQL_LINE

26. Loops: What is the index range operator in a FOR loop?
True - false or NULL.
1
..
CASE_NOT_FOUND

27. The PL/SQL preprocessor passes ______ code to the compiler.
1
machine code
source
do not

28. The attribute used to get the type of a column or variable without knowing what it is.
DATE
PVM
SIMPLE_INTEGER
%TYPE

29. Wrap ______=xxxx oname=zzzzz
iname
.
obfuscates
GET_LINES

30. The RESULT_CACHE clause comes (before / after) the RETURN clause.
after
name
STANDARD
obfuscates

31. Triggers: Unlike a stored procedure - you can enable and _____ a trigger.
EXIT
disable
elements - type
integrated

32. User-defined compiler directives are defined in this system compile parameter.
%ISOPEN.
%TYPE
PLSQL_CCFLAGS
CLOSE

33. Fundamental: Which one of these is a compound symbol: = - :=
:=
selection
can
REF CURSOR

34. A unit with AUTHID = DEFINER is a ________ rights unit.

35. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
plb
DBMS_OUTPUT
OTHERS
INTO

36. Cursors: What implicit cursor attribute gives row counts for a FORALL statement?
numeric and cursor
%BULK_ROWCOUNT
after
strong

37. Advantage: Caching of programs and sharing among users lowers _______ requirements.
memory
REF CURSOR
ordinary
transaction

38. Exceptions: An exception in the declarations section does not go to the __________ section.
run
cursor c RETURN rC;
FETCH
exception

39. From a block nested inside a procedure P - reference variable V of the outer procedure.
an enclosing block
p.v
package
constructor

40. The exception raised when a SELECT INTO statement returns more than one row.
static
current transaction
TOO_MANY_ROWS
INTO

41. Triggers: In a per-row trigger - the ____ record shows you the changed state of an affected row.
composite
network
NEW
SQLCODE

42. To run TCL or DDL statements - a trigger (must / must not) be autonomous .
must
30
LOOP - END LOOP
compiles

43. Types of blocks: Procedures and functions are _______ blocks.
stored
oname
RETURN
external subprogram

44. I/O: One of the 3 types of data that can be passed to the DBMS_OUTPUT buffer.
statically
associative arrays
invalid
VARCHAR2

45. I/O: The DBMS_OUTPUT function that writes output without a newline character.
%TYPE
PUT
value
current

46. Types: One of the scalar data types specific to PL/SQL: P________
database
END
PLS_INTEGER
SQLERRM

47. Bytecode is _______ _______ code.
run
virtual machine
DBMS_SESSION.RESET_PACKAGE
DML

48. Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE - CORR - UPPER - SUM - COALESCE - DEREF.
context
upper - coalesce
1
invoker's

49. The BETWEEN operator is (inclusive / exclusive).
inclusive
cannot
FETCH
procedure P AUTHID current_user;

50. _____ arrays cannot be used as the type of an Oracle table column.
associative
'
obfuscates
for each row