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. I/O: The DBMS_OUTPUT procedure that sets the size of the output buffer.
ENABLE
error
nested
function f (n number) return number result_cache relies_on (emps);

2. Cursors: The values plugged into the DML in a FORALL must come from existing - populated _________.
inclusive
collections
associative
DBMS_OUTPUT

3. The PL/SQL virtual machine turns bytecode into system ______.
after
calls
enclosing
statically

4. Exceptions: Pragma that links an exception to an Oracle error code.
3
FORALL i IN MyList.FIRST..MyList.LAST
EXCEPTION_INIT
1

5. Cursors: The first two keywords of a basic fetch statement.
$END
FETCH - INTO
EXIT WHEN cv%NOTFOUND
dynamic

6. A subprogram created inside a block is a _________ subprogram.
expression
nested
database
pseudocolumn

7. Name the two types of FOR loop.
'
DBMS_SESSION
significant
numeric and cursor

8. To use a TYPE declared in the database - a PL/SQL block has to have _______ privilege on the type.
alter procedure
DBMS_SESSION.RESET_PACKAGE
not the same
select

9. _______ arrays can be sparse or dense - depending on how you fill them.
EXECUTE IMMEDIATE
REF_CURSOR
inquiry
associative

10. Exceptions: The package that defines pre-defined exceptions.
definer's
STANDARD
optional
DATE

11. Triggers: The code in a trigger is actually an ________ block.
constructor
Web Toolkit
anonymous
alter procedure

12. Exceptions: User-defined exceptions must have _______.
an enclosing block
exit when cgo%NOTFOUND
an autonomous routine
names

13. DynSQL: The colon-prefixed names used in EXECUTE IMMEDIATE are called ________.
IS
collections - records
EXIT WHEN cv%NOTFOUND
placeholders

14. The Wrap utility (does / does not) encrypt source code.
virtual machine
NESTED TABLE myColl STORE AS myCollTab
does not
:=

15. Cursors: The index variable in a cursor FOR loop (does/does not) need to be declared.
does not
anonymous
define
null string

16. Triggers: You (can/cannot) explicitly invoke a trigger.
bind arguments
PVM
does not
cannot

17. With Edition-based Redefinition enabled - the unique specification of an object requires OWNER - OBJECT_NAME and ___________.
external subprogram
EDITION_NAME
FETCH
DBMS_PREPROCESSOR

18. A subprogram defined within another subprogram is a _______ subprogram.
nested
will not
...
cannot

19. In a cursor loop - the clause added to an INSERT or UPDATE to point at the current record.
quoted
session cursor
NESTED TABLE myColl STORE AS myCollTab
WHERE CURRENT OF

20. IDs: The two types of words that can - but should not - be used as user-defined identifiers.
Keywords and predefined names.
%BULK_ROWCOUNT
constrained
FOR UPDATE

21. Command used to execute dynamic SQL.
UTL_HTTP
1
EXECUTE IMMEDIATE
composite

22. The FOR UPDATE OF clause references a (table / column).
column
name
SQLERRM
elements - type

23. Cursors: Give the statement to fetch data from cursor variable CV into MyVar.
upper - coalesce
bytecode
fetch cv into myvar
aliases

24. Fundamental: What is the range operator?
..
constant
compile
is

25. An anonymous block is a(n) ____ statement.
lock
quoted
SQL
%BULK_ROWCOUNT

26. (from inside a package) Declare a function f - that takes a number n - returns a number - and caches results - depending on table EMPS.
OUT
close C
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
function f (n number) return number result_cache relies_on (emps);

27. _____ arrays cannot be used as the type of an Oracle table column.
CURSOR - RETURN - IS
1 million
value
associative

28. Advantage: Having procedures stored and executed on the server makes PL/SQL ________.
EXECUTE IMMEDIATE
scalable
RAISE
plb

29. Cursors: The three keywords in a full cursor declaration/definition (before the SELECT).
bytecode
=
CURSOR - RETURN - IS
close C

30. I/O: The DBMS_OUTPUT function that writes output without a newline character.
array
anonymous
PUT
executable

31. Fundamental: What are the 3 compound symbols for 'not equal to'?
autonomous transaction
not the same
<> - != - ^=
FETCH

32. Exceptions: An unhandled exception ________ to the enclosing block.
name
machine code
propagates
closed

33. Triggers: To simulate having triggers on SELECT statements - you can try ___ (3-letter acronym).
SYS_REFCURSOR
FGA
# - $ - _
block

34. What is the simplest statement for conditionally getting out of simple loop?
CLOSE_CURSOR
EXIT WHEN
information that changes infrequently
USER_PLSQL_OBJECT_SETTINGS

35. Cursors: The statement that closes a cursor.
delimiters
return value
CLOSE
cursor

36. To enable result-caching for a function - use the _____________ clause.
SUBTYPE n IS number not null;
buffer
RESULT_CACHE
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));

37. I/O: This DBMS_OUTPUT proc retrieves multiple lines from the buffer.
GET_LINES
upper - coalesce
TYPE tN IS TABLE of NUMBER
column

38. Cursors: A cursor variable's type must be a ____ ______ type.
pseudocolumn
2
E Exception;
REF CURSOR

39. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
RECORD OF ARRAYS
transaction
an autonomous routine
execute immediate

40. The SET TRANSACTION statement affects only the ____________.
INTO
RETURN
current transaction
enabled

41. Triggers: In a per-row trigger - the ____ record shows you the changed state of an affected row.
not a number
can
NEW
With a letter.

42. The first SQL statement in an autonomous routine begins a _______.
transaction
DBMS_SESSION
inquiry
column

43. Fundamental: Delimiters are _____ and _____ symbols.
simple and compound symbols
flushed
32 -000
Declaration

44. An INSTEAD OF trigger is used - e.g - when you want to insert into a complex _____.
FORALL and BULK COLLECT
WHEN
view
decimal

45. A type of compiler directive - begins with 'e'.
a number := 10
error
cannot
CHARARR

46. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
will not
error code
EXIT WHEN
DBMS_PIPE

47. Advantage: PL/SQL programs are stored centrally - making them more ______.
Declaration
specification - body
manageable
will not

48. Types: In addition to the SQL data types - PL/SQL has ___ more scalar data types.
4
r tRec := tRec(9 - 'Jones');
unqualified
versions

49. Types: One of the 4 PL/SQL things that have a data type (begins with 'r').
lock
return value
REF_CURSOR
concatenation

50. The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
invoker's
inquiry
query
database