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. Conversion: Implicit conversion is not always predictable; it is _______-sensitive. And its rules might change.
loop
nested tables - associative arrays
context
specification - body

2. The EXTEND method is not used with ______ _______.
associative arrays
FOR UPDATE
..
1 million

3. Dbms_sql: The DBMS_SQL procedure that will execute a DDL or DML statement.
execute
Ada
iname
production

4. To create a collection type - use the TYPE command in PL/SQL and the ______ _____ command in SQL.
before insert or update on emps
CREATE TYPE
<<>>
r tRec := tRec(9 - 'Jones');

5. Declare a package procedure P with no arguments - and give it invoker's rights.
REUSE SETTINGS
RETURN
procedure P AUTHID current_user;
closed

6. Bulk: The use of FORALL statements cuts down on ______ traffic.
...
%
network
255

7. Kind of comment not to put in a PL/SQL block to be processed dynamically by an Oracle Precompiler program.
=>
executable
same
single-line

8. Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE - CORR - UPPER - SUM - COALESCE - DEREF.
constant
.
iname
upper - coalesce

9. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
lock
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
static
EXIT WHEN

10. Exceptions: An exception raised inside an _______ _______ propagates immediately to the enclosing block
searched
exception handler
%TYPE
static

11. Advantage: Caching of programs and sharing among users lowers _______ requirements.
=
memory
specification - body
PSP

12. You can define PL/SQL collections using TYPEs defined in the _______.
NUMBER
database
CHAR - VARCHAR2
injection

13. Exceptions: Write the call (2 args) to associate message 'Bad' with error code -20 -100.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

14. Cursors: The Oracle predefined REF CURSOR type.
DBMS_PIPE
CHAR - VARCHAR2
SYS_REFCURSOR
rollback to ab

15. A result-cached function caches its results in the _____ _____ ____ (SGA)
USING
compilation
system global area
WHERE CURRENT OF

16. Advantage: PL/SQL is PORTABLE to any OS where ________ runs.
Oracle
currval and nextval
EXIT
END LOOP

17. The $IF must evaluate a ______ _______ expression.
static boolean
STANDARD
composite
FORALL and BULK COLLECT

18. VARRAYs are (always / never) sparse.
delimiters
never
concatenation
follows

19. Triggers: INSTEAD OF triggers only run on ______ views.
selection
non-editioning views
WHERE CURRENT OF
do not

20. Statement to exit a cursor loop on cursor cGo when there are no more records.
1 million
procedure P AUTHID definer;
dynamic
exit when cgo%NOTFOUND

21. An editioning view is like an ____ for a table.
DML
API
lock
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

22. Exceptions: Some internal exceptions have ________ names.
predefined
1
anonymous block
RETURN

23. The DEFINITION of a cursor is the _____ that returns the records.
CHAR - VARCHAR2
memory
query
current

24. The LIMIT method is only meaningful for _______.
exception handler
RETURN
VARRAYs
NUMBER

25. The default value of the AUTHID property.
DEFINER
can
ROLLBACK
EDITION_NAME

26. A CASE statement ends with ____ ____.
composite
run
END CASE
cursor

27. Cursors: Statement to end a loop when MyCur is used up: _____ ______ MyCur%NOTFOUND.
DBMS_CRYPTO
EXIT WHEN
-
timing

28. Bulk: The two features that comprise Bulk SQL are:
NDS
FORALL and BULK COLLECT
composite
specification - body

29. The AUTHID property applies to PL/SQL objects that contain _____.
network
type T is ref cursor
versions
code

30. Mode designation for an output parameter.
OUT
versions
1
STANDARD

31. Types: A ______ has the same valid operations as its base type.
INTO
create
USING
subtype

32. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
disable
suspended
scalar
finishes

33. Cursors: The values plugged into the DML in a FORALL must come from existing - populated _________.
collections
buffer
;
3

34. To enable result-caching for a function - use the _____________ clause.
RESULT_CACHE
variable
EXECUTE IMMEDIATE
%ISOPEN.

35. With Edition-based Redefinition enabled - the unique specification of an object requires OWNER - OBJECT_NAME and ___________.
IN
FETCH
EDITION_NAME
DML

36. Packaged constants can synchronize programs through use in _______ directives.
3
composite
1
selection

37. The DEFINITION of a cursor is the part after the keyword __.
array
IS
static
procedure - function

38. An incomplete type only has a ____.
name
True
DATE
;

39. Cursors: What is the 'name' of the implicit cursor?
SQL
create procedure
1
buffer

40. With conditional compilation you can avoid compiling debug code in the _______ environment.
can
-
production
RESULT_CACHE

41. Types: ________ data types have internal components.
preprocessor
associative
composite
selection

42. A FOR-loop counter is defined only within the _____.
cannot
an autonomous routine
VARCHAR2
loop

43. Exceptions: The _________ package defines the PL/SQL environment.
index
IN OUT
STANDARD
v3 IN OUT NUMBER

44. In interpreted mode - PL/SQL code is run in a _______ _______.
dbms_output.put_line
transaction
must
virtual machine

45. I/O: This package sends text output to SQLPlus
hardware
UTL_HTTP
DBMS_OUTPUT
Gateway

46. Value of a character literal with zero characters.
not a number
anonymous

wrap

47. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
DEFINER
DML
current transaction
LOOP - END LOOP

48. Delimiters: Single-line comment marker.
Apache
machine
sql
-

49. I/O: If the current (outermost) PL/SQL block ends with an unhandled exception - the output buffer will not be ___________.
TIMESTAMP.
autonomous triggers
EVERE - PERFORMANCE - INFORMATIONAL
flushed

50. How to reference a variable V declared in a block labelled L: ____.
L.V
code
virtual machine
IN