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. A type of compiler directive - begins with 'e'.
error
variable
v3 IN OUT NUMBER
database

2. Declare an input NUMBER parameter - v2 - that defaults to 20.
v2 IN NUMBER default 20
execute
FETCH
session cursor

3. Simple CASE statement checks for different values of a single _________
associative
machine code
INTO
expression

4. With dynamic typing - type checking is done at ______time.
calls
bytecode
run
RECORDS

5. A subprogram created inside a block is a _________ subprogram.
DATE
nested
'
Gateway

6. A pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
constant
cursor
CREATE TRIGGER
scalar

7. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
colon
EXECUTE IMMEDIATE
multi-row
LOOP - END LOOP

8. A collection is an ordered group of ________ - all of the same ____
can
elements - type
PLSQL_CCFLAGS
...

9. Triggers: The clause to make a trigger run before inserting or updating table EMPS.
unconstrained
end
before insert or update on emps
SQL

10. I/O: SQLPlus command for turning on buffered output.
identifiers
set serveroutput on
v2 IN NUMBER default 20
define

11. The default value of the AUTHID property.
does not
Gateway
DEFINER
VARCHAR2

12. Cursors: Give the statement for getting out of a FETCH loop for cursor CV.
SESSION_USER
-
EXIT WHEN cv%NOTFOUND
EXIT

13. Cursors: The keyword that makes a ref cursor type STRONG.
RETURN
associative
PLSQL_OPTIMIZE_LEVEL
query

14. What clause makes a compilation default to the PLSQL_CCFLAGS settings from the last compilation?
END
implicit
REUSE SETTINGS
current BEGIN-END block

15. The PL/SQL virtual machine turns bytecode into system ______.
for update of
calls
searched
NEW

16. Cursors: The index variable in a cursor FOR loop (does/does not) need to be declared.
r t.%ROWTYPE;
boolean
does not
selection

17. Associative array types (can / cannot) be defined in the database.
pls_integer
OPEN_CURSOR
cannot
PVM

18. In Native Mode - PL/SQL code is compiled into ______ ______.
session cursor
machine code
invoker's
package

19. When a parameter is passed by reference - changes made to it inside the procedure cannot be undone. True/False.
%
True
SUBTYPE numb IS number;
CASE_NOT_FOUND

20. Searchable CASE statement: Each WHEN has its own _______ condition.
%TYPE
composite
boolean
can

21. The LIMIT method is only meaningful for _______.
cannot
VARRAYs
1
autonomous transaction

22. Exceptions: Declare an exception called E.
is not
Apache
INTO
E Exception;

23. The collection method that tells the number of elements in the collection.
collections
FETCH - BULK COLLECT INTO
True - false or NULL.
COUNT

24. I/O: The DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block _________.
PUT
varray
$IF
finishes

25. Cursors: An explicit cursor (can/cannot) accept parameters.
SYS_CONTEXT
1
can
-20 -000

26. Types: A ______ has the same valid operations as its base type.
subtype
never
can
oname

27. Dbms_sql: The DBMS_SQL procedure that will execute a DDL or DML statement.
RETURN
execute
3
DBMS_SESSION

28. Cursors: You can just declare a cursor at first - then _______ it later.
define
multi-row
array
boolean

29. The lowest index of a VARRAY is ___.
implicit CASE_NOT_FOUND exception
DBMS_PREPROCESSOR
1
EXECUTE IMMEDIATE

30. What are the two sequence pseudo-columns?
VARRAYs
currval and nextval
definer's
after

31. In a record - the internal components are called ______.
WHERE CURRENT OF
fields
EXIT WHEN
elements - type

32. Fundamental: A label can only go directly before an _______ statement.
machine code
executable
after insert on emps
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT

33. Internally - the NUMBER type is stored as ______. Therefore - decimal numbers (such as prices) are stored exactly - with no rounding.
set serveroutput on
decimal
closed
cursor

34. Types: The only PL/SQL type for which you can specify a range constraint.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
# - $ - _
alter procedure
PLS_INTEGER

35. I/O: This package handles HTTP.
EDITION_NAME
L.V
implicit cursor
UTL_HTTP

36. An incomplete type only has a ____.
memory
an AUTHID property
name
ADT

37. When an autonomous routine begins execution - the main transaction is ________.
UTL_SMTP
bind arguments
stored
suspended

38. The DECLARE keyword is only required in an _______ _______.
anonymous block
name and datatype
WHERE CURRENT OF
vname CONSTANT VARCHAR2 := 'x';

39. Cursors: The statement where you pass the parameters to a cursor.
LOOP - END LOOP
Abstract
sequence pseudo-columns
OPEN

40. True encryption is done with package __________.
stored
DBMS_CRYPTO
DBMS_SESSION.RESET_PACKAGE
END LOOP

41. The statement that is used to execute most (native) dynamic SQL.
execute immediate
transaction
exit when cgo%NOTFOUND
injection

42. Declare an output NUMBER parameter - v1 - that defaults to 10.
v1 OUT number default 10
cannot
reference
%BULK_ROWCOUNT

43. Exceptions: Pragma that links an exception to an Oracle error code.
sequence pseudo-columns
DML
object
EXCEPTION_INIT

44. A (simple / searched) CASE statement begins with CASE WHEN <boolean expression>.
exception
DBMS_SQL
suspended
searched

45. Delimiters: The left label delimiter.
<<
do not
PRAGMA AUTONOMOUS_TRANSACTION;
null string

46. Advantage: Performance: Stored subprograms are ______ in memory.
cached
anonymous block
in the database
invalid

47. Triggers: To simulate having triggers on SELECT statements - you can try ___ (3-letter acronym).
cursor variable
EVERE - PERFORMANCE - INFORMATIONAL
FGA
With a letter.

48. Declare a package procedure P with no arguments - and give it invoker's rights.
Gateway
OLD
procedure P AUTHID current_user;
VARCHAR2

49. Characters that begin a single-line comment.
ordinary
not the same
cannot
-

50. The 4 keywords in the $IF compiler directive.
$IF $THEN $ELSE $END
END
cursor number
WHERE CURRENT OF