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. After the RESULT_CACHE clause - you can put a _________ clause to enable dependency-checking.
bind arguments
SESSION_USER
RELIES_ON
CREATE TYPE

2. The three types of compiler warnings.
EVERE - PERFORMANCE - INFORMATIONAL
True
anonymous block
$IF $THEN $ELSE $END

3. Declare a constrained subtype of NUMBER called N that is not null.
invoker's
PLSQL_OPTIMIZE_LEVEL
invoker's
SUBTYPE n IS number not null;

4. Subprograms are stored in __________ form.
PLS_INTEGER
invoker's
USING
executable

5. Define a collection type - tA - which is an associative array of NUMBERS - indexed with varchar2(20) values.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
cursor
error code
scalar

6. Types: In addition to the SQL data types - PL/SQL has ___ more scalar data types.
cannot
1
4
network

7. In PL/SQL - what the variables in DML statements are turned into.
block
3
bind arguments
FETCH - BULK COLLECT INTO

8. An editioning view is like an ____ for a table.
API
specification - body
PLSQL_OPTIMIZE_LEVEL
VARCHAR2

9. Delimiters: Statement terminator.
associative
base
code
;

10. I/O: This package does email.
UTL_SMTP
dbms_output.put_line
expression
hardware

11. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
<<
colon
anonymous block
execute immediate

12. Cursors: You can just declare a cursor at first - then _______ it later.
simple and compound symbols
define
SUBTYPE n IS number not null;
DML

13. PL/SQL does not automatically create bind variables in ________ SQL.
buffer
PLSQL_OPTIMIZE_LEVEL
CURSOR - RETURN
dynamic

14. An INSTEAD OF trigger is used - e.g - when you want to insert into a complex _____.
<<>>
view
can
# - $ - _

15. Bulk: The two features that comprise Bulk SQL are:
*/
USING
FORALL and BULK COLLECT
;

16. Delimiters: The component indicator/selector.
1
predefined
.
EVERE - PERFORMANCE - INFORMATIONAL

17. The _____ and ____ _____ collection types can be used in database table columns.
static
does not
VARRAY - Nested table
32 -000

18. Mode designation for an input parameter.
IN
ordinary
simple and compound symbols
DEFINER

19. A type of compiler directive - begins with 'e'.
USER_PROCEDURES
DBMS_SQL
follows
error

20. Default file extension of wrap's input file.
STANDARD
PUT_LINE
definer's
sql

21. Declare an input/output NUMBER parameter - v3.
definer's
an autonomous routine
never
v3 IN OUT NUMBER

22. I/O: This package lets PL/SQL programs read and write OS files.
UTL_FILE
machine code
anonymous
HTP

23. Cursors: Define a ref cursor type called T.
OLD
literal
type T is ref cursor
CHAR - VARCHAR2

24. Triggers: Programmers (as opposed to DBAs) mostly make use of _____ triggers.
$$
# - $ - _
DML
network

25. DynSQL: EXECUTE IMMEDIATE does both dynamic SQL and dynamic _______.
optional
PL/SQL
for each row
error

26. Type: An ________ subtype has all the values of its base type.
current transaction
RECORD OF ARRAYS
unconstrained
colon

27. Where an external subprogram is stored.
r t.%ROWTYPE;
implicit cursor
in the database
for each row

28. You have to use _______ SQL to run DDL statements n PL/SQL.
dynamic
**
DBMS_SESSION.RESET_PACKAGE
=

29. Types: One of the 4 PL/SQL things that have a data type (begins with 'r').
NUMBER
return value
binding
cannot

30. Clause added to a cursor to use it with WHERE CURRENT OF.
integrated
for update of
TRUE - FALSE and NULL
FORALL i IN MyList.FIRST..MyList.LAST

31. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
compiles
%BULK_ROWCOUNT
Declaration
unhandled

32. IDs: Maximum number of bytes in a user-defined identifier.
IS
30
NESTED TABLE myColl STORE AS myCollTab
CURRENT_USER

33. Cursors: Give the first 3 words to open a cursor variable called cv.
open cv for
ALTER SESSION
object
SQL

34. I/O: The DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block _________.
compile
finishes
invoker's
value

35. Conversion: It is best to use (implicit / explicit) conversion.
definer's
explicit
SESSION_USER
loop

36. A PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE
=
searched
explicit
WHERE CURRENT OF

37. Triggers: The clause to make a trigger run after inserting into table EMPS.
after insert on emps
<> - != - ^=
fetch cv into myvar
null string

38. Declare a cursor c with no parameters or return type.
bb:true
DBMS_OUTPUT
unconstrained
cursor c;

39. Advantage: PL/SQL runs anywhere Oracle runs - so it is very ________.
DML
BOOLEAN
VARRAYs
portable

40. Delimiters: Item separator.
versions

fetch cv into myvar
Gateway

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

42. Types: The PL/SQL type that is identical to PLS_INTEGER.
3
BINARY_INTEGER
ENABLE
nested

43. The _______ package lets you wrap dynamically generated PL/SQL.
HTP
DBMS_DDL
open C(p)
30

44. The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
inquiry
32 -000
is
2

45. The PL/SQL ________ evaluates compiler directives.
GET_LINES
preprocessor
CURRENT OF
composite

46. Characters that begin a multi-line comment.
error
REF CURSOR
/*
'

47. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
placeholders
fields
EXIT WHEN cv%NOTFOUND
TRUE - FALSE - NULL

48. A PL/SQL block that is not stored.
2
a cursor
Gateway
anonymous block

49. The PL/SQL preprocessor passes ______ code to the compiler.
EXIT WHEN
NEW
sequence pseudo-columns
source

50. Records are one of the two kinds of PL/SQL _______ data types.
HTP
EXIT
composite
cached