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. Pkgs: The _______ package gives access to such SQL things as ALTER SESSION.
Keywords and predefined names.
plb
SQL
DBMS_SESSION

2. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
will not
exception
pls_integer
Double quotes.

3. Types of blocks: Procedures and functions are _______ blocks.
=>
CREATE TYPE
stored
EXIT WHEN

4. Setting CURRENT_SCHEMA only changes what is prepended to __________ references.
network
IF - CASE or LOOP
name
unqualified

5. Types: One of the 4 PL/SQL things that have a data type (begins with 'p').
parameter
cannot
r t.%ROWTYPE;
unhandled

6. One good use of bind variables is to guard against SQL _______.
COUNT
injection
dense
associative

7. Records are one of the two kinds of PL/SQL _______ data types.
composite
multi-row
INTO
STANDARD

8. Cursors: Closing keywords of a cursor FETCH loop.
in the database
END LOOP
virtual machine
literals

9. Advantage: PL/SQL runs anywhere Oracle runs - so it is very ________.
query
portable
FETCH
NDS

10. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
STANDARD
same
suspended
REF CURSOR

11. A GOTO statement cannot branch from an exception handler back into the...
DBMS_PIPE
current BEGIN-END block
EVERE - PERFORMANCE - INFORMATIONAL
EXIT WHEN cv%NOTFOUND

12. The first SQL statement in an autonomous routine begins a _______.
an AUTHID property
v1 OUT number default 10
transaction
composite

13. Fundamental: A label can be used to name an ______ block.
explicit cursor
predefined
PVM
anonymous

14. You cannot bulk collect into an ARRAY OF ________.
RECORDS
oname
SESSION_USER
cursor

15. An autonomous program runs in its own __________.
searched
transaction
null string
multi-row

16. Exceptions: An exception raised inside an _______ _______ propagates immediately to the enclosing block
concatenation
UTL_HTTP
exception handler
not the same

17. What are the 3 values of the BOOLEAN data type?
open cv for
TRUE - FALSE and NULL
follows
must

18. The DEFINITION of a cursor is the _____ that returns the records.
DATE '2005-12-25'
query
boolean
32 -000

19. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
suspended
SQL
FOR i IN REVERSE 1..10 LOOP
cannot

20. In Native Mode - PL/SQL code is compiled into ______ ______.
END IF
obfuscates
implicit
machine code

21. The DECLARE keyword is only required in an _______ _______.
anonymous block
@
sql
network

22. Default file extension of wrap's input file.
sql
INTO
BINARY_INTEGER
CURRENT_USER

23. Define a record variable r - of type tRec - initialized with values 9 and 'Jones' in its two fields.

24. Name the two types of FOR loop.
4
BINARY_INTEGER
numeric and cursor
name-value

25. Declare an input NUMBER parameter - v2 - that defaults to 20.
:=
EVERE - PERFORMANCE - INFORMATIONAL
vname CONSTANT VARCHAR2 := 'x';
v2 IN NUMBER default 20

26. Triggers: An INSTEAD OF trigger (can/cannot) be conditional.
associative
1
error code
cannot

27. Cursors: If you use an EXIT statement to exit a cursor FOR loop prematurely - the cursor (is / is not) closed automatically.
CREATE TYPE
bind
is
RECORD OF ARRAYS

28. Fundamental: One of the 4 types of lexical units: c_____.
scalar
lowest
comments
EDITION_NAME

29. The NOCOPY option on an OUT or IN OUT parameter - causes it to be passed by ______.
reference
ordinary
never
quoted

30. For a collection - the NEXT method returns ____ when there is no next index value.
RECORD OF ARRAYS

SQL cursor
DBMS_PREPROCESSOR

31. What is the simplest statement for getting out of simple loop?
query
CHAR - VARCHAR2
DBMS_OUTPUT
EXIT

32. Exceptions: The built-in procedure for raising an application-defined error.
raise_application_error
is not
cannot
executable

33. Exceptions: An unhandled exception ________ to the enclosing block.
propagates
autonomous triggers
SESSION_USER
LOOP - END LOOP

34. An incomplete type only has a ____.
GET_LINES
DML
PLSQL_CCFLAGS
name

35. Conditional ______ allows backward compability with older database versions.
PRAGMA AUTONOMOUS_TRANSACTION;
associative arrays
compilation
raise_application_error(-20 -100 - 'Bad');

36. Statement to exit a cursor loop on cursor cGo when there are no more records.
exit when cgo%NOTFOUND
SESSION_USER
ENABLE
always

37. Into nested table ex - put elements of tbl1 that are not in tbl2.
predefined
ex := tbl1 MULTISET EXCEPT tbl2
DBMS_CRYPTO
optional

38. I/O: The DBMS_OUTPUT function that writes output WITH a newline character.
EXIT WHEN
r tRec := tRec(9 - 'Jones');
function f (n number) return number result_cache relies_on (emps);
PUT_LINE

39. Declare a cursor c with no parameters - and return type rC.
increment
cursor c RETURN rC;
Oracle
L.V

40. Delimiters: The component indicator/selector.
FOR UPDATE
database
.
virtual machine

41. When a session starts - CURRENT_SCHEMA has the value of the schema owned by ___________.
CREATE TRIGGER
WHEN OTHERS THEN
SESSION_USER
1

42. Exceptions: The _________ package defines the PL/SQL environment.
1
INSTEAD OF
mandatory
STANDARD

43. Cursors: Give the first 3 words to open a cursor variable called cv.
manageable
open cv for
cannot
EXECUTE IMMEDIATE

44. The three types of compiler warnings.
EVERE - PERFORMANCE - INFORMATIONAL
significant
set serveroutput on
RECORD OF ARRAYS

45. Exceptions: An exception in the declarations section does not go to the __________ section.
statement
exception
pseudocolumn
RETURN

46. Exceptions: The three words that can be used to guarantee all exceptions will be handled.
SQLCODE
in the database
WHEN OTHERS THEN
raise_application_error

47. List the transaction control statements.
COUNT
autonomous transaction
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
scalar

48. A basic PL/SQL datatype that SQL does not have.
reference
boolean
finishes
cannot

49. Triggers: Programmers (as opposed to DBAs) mostly make use of _____ triggers.
view
significant
bind
DML

50. Set flag bb true: ALTER SESSION SET PLSQL_CCFLAGS= '____'
bb:true
do not
SESSION_USER
invoker's