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. Fundamental: What is the exponentiation operator?
**
for update of
cursor
PLSQL_OPTIMIZE_LEVEL

2. Exceptions: The _________ package defines the PL/SQL environment.
FETCH - BULK COLLECT INTO
inclusive
REF CURSOR
STANDARD

3. Types of blocks: Procedures and functions are _______ blocks.
INTO
network
varray
stored

4. The PL/SQL ________ evaluates compiler directives.
NDS
preprocessor
v2 IN NUMBER default 20
associative arrays

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

1

6. PL/SQL lets you define two kinds of composite data types: _______ and _______
NEW
static
currval and nextval
collections - records

7. Fundamental: What is the range operator?
a number := 10
expression
:=
..

8. Exceptions: User-defined exceptions must have _______.
LOOP - END LOOP
CASE_NOT_FOUND
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
names

9. Cursors: A cursor with a name is an _________ cursor.
flushed
explicit
currval and nextval
for each row

10. The lowest index of a VARRAY is ___.
1
DBMS_SQL
Abstract
Keywords and predefined names.

11. The four keywords that define a PL/SQL block.
DECLARE - BEGIN - EXCEPTION - END
OPEN
associative
wrap

12. IDs: List the two types of user-defined identifiers.
expression
production
expression
Ordinary - Quoted.

13. By default - an IN parameter is passed by ______.
reference
autonomous triggers
autonomous transaction
%ROWTYPE

14. Wrap ______=xxxx oname=zzzzz
iname
inclusive
PLSQL_OPTIMIZE_LEVEL
PUT_LINE

15. Define a collection type - tV - which is a varray of 100 NUMBERs.
machine
procedure P AUTHID current_user;
2
TYPE tV IS VARRAY(100) OF NUMBER

16. Conversion: It is best to use (implicit / explicit) conversion.
explicit
ALTER SESSION
512
PRAGMA EXCEPTION_INIT (E - -1);

17. DynSQL: Native Dynamic SQL
NDS
REF CURSOR
NESTED TABLE myColl STORE AS myCollTab
collections

18. Triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
sql
PUT_LINE
always
WHEN OTHERS THEN

19. To enable result-caching for a function - use the _____________ clause.
IN OUT
RESULT_CACHE
national
disable

20. The allowed data types of user-defined conditional compilation directives are boolean and _______.
machine
REF_CURSOR
procedure P AUTHID definer;
pls_integer

21. If none of a CASE expression's conditions are TRUE - it returns _____.
create
RETURN

$$

22. Advantage: Performance: Stored subprograms are ______ in memory.
cached
ordinary
loop
1

23. Types: One of the 4 PL/SQL things that have a data type (begins with 'c').
constant
closed
;
fetch C into V

24. The attribute used to get the type of a column or variable without knowing what it is.
TRUE - FALSE and NULL
%TYPE
VARCHAR2
DML

25. You have to use _______ SQL to run DDL statements n PL/SQL.
dynamic
autonomous transaction
LOOP - END LOOP
mandatory

26. Types: One of the scalar data types specific to PL/SQL: B_______
BINARY_INTEGER
INTO
RETURN
compilation

27. The valid values of the AUTHID property.
loop
for each row
elements - type
CURRENT_USER - DEFINER

28. Dbms_sql: The return type DBMS_SQL.open_cursor.
scalar
integer
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
INSTEAD OF

29. A CASE statement (can / cannot) execute more than one group of statements.
machine code
CURRENT_USER
statement
cannot

30. Language that PL/SQL is derived from.
invoker's
Ada
EXECUTE IMMEDIATE
%

31. Types: ________ data types have no internal components.
DATE
execute
enabled
scalar

32. Exceptions: An exception in the declarations section propagates to the enclosing _______.
explicit
PUT
block
DML

33. With a result-cached function - it uses a cached value when the function is called again with the...
same arguments
sequence pseudo-columns
FETCH
timing

34. An ordinary character literal is composed of characters in the ________ character set.
in the database
$END
database
quoted

35. Setting CURRENT_SCHEMA only changes what is prepended to __________ references.
Abstract
unqualified
WHERE CURRENT OF
finishes

36. An incomplete type is a type created by a ______ type definition.
<> - != - ^=
upper - coalesce
forward
...

37. Exceptions: Declare an exception called E.
E Exception;
EDITION_NAME
$$PLSQL_LINE
savepoint ab

38. Associative array types (can / cannot) be defined in the database.
exception
cannot
disable
elsif

39. IDs: You (can/cannot) use keywords as ordinary user-defined identifiers.
type T is ref cursor
CURRENT OF
%BULK_ROWCOUNT
can

40. Internally - the NUMBER type is stored as ______. Therefore - decimal numbers (such as prices) are stored exactly - with no rounding.
EXECUTE
r tRec := tRec(9 - 'Jones');
decimal
cannot

41. The AUTHID property applies to PL/SQL objects that contain _____.
is certain
for each row
disable
code

42. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
nested tables - associative arrays
source code
;
NUMBER

43. Command to delete a procedure.
strong
drop procedure
national
HTP

44. In the special IF syntax - is it ELSIF - or ELSEIF?
unhandled
p.v
elsif
NESTED TABLE myColl STORE AS myCollTab

45. In a collection - the internal components always have the _____ data type.
RECORDS
Keywords and predefined names.
same
Apache

46. Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE - CORR - UPPER - SUM - COALESCE - DEREF.
upper - coalesce
an autonomous routine
composite
variable

47. Declare a package procedure P with no arguments - and give it invoker's rights.
executable
WHEN
procedure P AUTHID current_user;
hardware

48. Triggers: The code in a trigger is actually an ________ block.
manageable
EXECUTE IMMEDIATE
END
anonymous

49. Exceptions: An unhandled exception ________ to the enclosing block.
propagates
select
virtual machine
anonymous block

50. One good use of bind variables is to guard against SQL _______.
scalar
..
injection
implicit cursor