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. Unlike the CASE statement - the CASE expression does not have the...
implicit CASE_NOT_FOUND exception
FETCH - INTO
drop procedure
query

2. _____ arrays cannot be used as the type of an Oracle table column.
suspended
DML
ALTER SESSION
associative

3. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
dynamic
..
null string
colon

4. Cursors: A cursor variable's type must be a ____ ______ type.
PRAGMA AUTONOMOUS_TRANSACTION;
FETCH
LOOP
REF CURSOR

5. Cursors: You cannot use cursor attributes in _______ statements.
hardware
loop
open C(p)
SQL

6. Pkgs: The _______ package gives access to such SQL things as ALTER SESSION.
closed
DBMS_SESSION
SUBTYPE numb IS number;
define

7. Default file extension of wrap's output file.
network
RECORDS
plb
anonymous

8. Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
4
bind
current
exception

9. The lowest index of a nested table is ___.
composite
# - $ - _
1
..

10. Subprograms are stored in __________ form.
executable
=>
TOO_MANY_ROWS
WHERE CURRENT OF

11. DynSQL: The colon-prefixed names used in EXECUTE IMMEDIATE are called ________.
nested
STANDARD
flushed
placeholders

12. The three sorts of date/time types are DATE - INTERVAL and _________.
machine
an AUTHID property
rollback to ab
TIMESTAMP.

13. A GOTO cannot branch into a...
national
lower sub-block
quoted
IN

14. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
PSP
FETCH - INTO
bind
TRUE - FALSE - NULL

15. Triggers: On a non-editioning view - you can only define __________ triggers.
VARCHAR2
INSTEAD OF
cannot
IS

16. A kind of variable used as a placeholder.
user-defined
bind
buffer
is not

17. Exceptions: Declare an exception called E.
PUT_LINE
E Exception;
single-line
CREATE TYPE

18. The DECLARE keyword is only required in an _______ _______.
CLOSE_CURSOR
sequence pseudo-columns
anonymous block
$$PLSQL_LINE

19. If a result-cached function's data source (based on the RELIES_ON clause) is updated - any cached results become _______.
..
Oracle
REUSE SETTINGS
invalid

20. Declare a number variable - A - initialized to 10.
Wrap
a number := 10
integer
Reserved words.

21. IDs: Predefined identifiers are declared in this package.
invoker's
context
WHERE CURRENT OF
STANDARD

22. The compiler directive governing conditional compilation.
database
$IF
aliases
specification - body

23. Declare a cursor c with no parameters - and return type rC.
iname
library
cursor c RETURN rC;
SUBTYPE numb IS number;

24. I/O: dbms_output.put_line puts output into a _______.
bytecode
buffer
can
production

25. IDs: The type of words that cannot be user-defined identifiers.
Reserved words.
PUT
=
procedure - function

26. The names of Java objects in Oracle can be up to __ characters in length.
4K
NESTED TABLE myColl STORE AS myCollTab
current transaction
mandatory

27. The FOR UPDATE OF clause references a (table / column).
column
RETURN
statement
bytecode

28. Triggers: A compound trigger can fire at more than one ______ point.
WHERE CURRENT OF
machine
timing
Oracle

29. Cursors: Only a _____ _____ cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement.
RESULT_CACHE
FOR UPDATE
manageable
4

30. The exception raised when a SELECT INTO statement returns more than one row.
$IF
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
TOO_MANY_ROWS
$$PLSQL_LINE

31. GOTO branches to a ________.
composite
label
True - false or NULL.
STANDARD

32. IDs: How an ordinary user-defined identifier must begin.
compiles
<> - != - ^=
can
With a letter.

33. Exceptions: The ________ function returns the error message associated with an error code.
machine
alter procedure
SQLERRM
exit when cgo%NOTFOUND

34. Advantage: PL/SQL programs are stored centrally - making them more ______.
NUMBER
manageable
sql
explicit

35. The meaning of 'FGA'.
anonymous
open cv for
fine-grained auditing
WHEN OTHERS THEN

36. Data dictionary view that shows the compiler settings for each compiled object.
4
%
USER_PLSQL_OBJECT_SETTINGS
suspended

37. The BETWEEN operator is (inclusive / exclusive).
USER_PLSQL_OBJECT_SETTINGS
view
inclusive
same arguments

38. DynSQL: EXECUTE IMMEDIATE can be used for any SQL statement or PL/SQL block - except for ________ queries.
definer's
multi-row
array
follows

39. The SET TRANSACTION statement affects only the ____________.
END CASE
colon
specification - body
current transaction

40. How many PL/SQL collection types are there?
invalid
3
network
DR

41. IDs: List the two types of user-defined identifiers.
Ordinary - Quoted.
DEFINER
virtual machine
scalable

42. The collection method that tells the number of elements in the collection.
COUNT
LOOP
v2 IN NUMBER default 20
SQL cursor

43. Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE - CORR - UPPER - SUM - COALESCE - DEREF.
close C
*/
upper - coalesce
closed

44. SIMPLE_INTEGER is like PLS_INTEGER - but it does not allow ____ values - and it does not raise overflow exceptions.

increment
static
current

45. A null string and a BOOLEAN null are _______ (same/not the same).
unhandled
$$
not the same
SQLPlus

46. Dbms_sql: The integer returned by OPEN_CURSOR is a _____ _____.
optional
4K
cursor number
autonomous triggers

47. Declare a package procedure P with no arguments - and give it definer's rights.
procedure P AUTHID definer;
UTL_SMTP
v2 IN NUMBER default 20
error code

48. Declare a package procedure P with no arguments - and give it invoker's rights.
query
PVM
error
procedure P AUTHID current_user;

49. In PL/SQL - what the variables in DML statements are turned into.
cached
cursor c;
value
bind arguments

50. Triggers: The code in a trigger is actually an ________ block.
constrained
can
FALSE
anonymous