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. SIMPLE_INTEGER is like PLS_INTEGER - but it does not allow ____ values - and it does not raise overflow exceptions.
production
is not
optional

2. Where an external subprogram is stored.
is not
error code
in the database
anonymous

3. Cursors: Opening keyword of a cursor FETCH loop.
information that changes infrequently
WHERE CURRENT OF
is certain
LOOP

4. The compiler directive used to select which code to compile.
execute
invoker's
$IF
database

5. Triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
EXIT WHEN
subtype
CHAR - VARCHAR2
always

6. Cursors: An explicit cursor (can/cannot) accept parameters.
select
True - false or NULL.
can
will not

7. An incomplete type only has a ____.
identifiers
name
predefined
composite

8. An incomplete type is a type created by a ______ type definition.
bind
IS
forward
CLOSE

9. Exceptions: Pragma that links an exception to an Oracle error code.
EXCEPTION_INIT
%TYPE
=
literals

10. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
query
colon
include
ordinary

11. DynSQL: In EXECUTE IMMEDIATE - the statement string can use either placeholders - or string ________.
concatenation
STANDARD
forward
REF_CURSOR

12. Triggers: INSTEAD OF triggers only run on ______ views.
ROLLBACK
FALSE
non-editioning views
is

13. Statement to exit a cursor loop on cursor cGo when there are no more records.
EDITION_NAME
exit when cgo%NOTFOUND
REF CURSOR
TYPE tV IS VARRAY(100) OF NUMBER

14. Web: PL/SQL _____ _______ lets you to develop Web applications made of PL/SQL procedures.
Web Toolkit
flushed
IN - OUT - IN OUT
scalable

15. Triggers: A compound trigger can fire at more than one ______ point.
timing
DBMS_SQL
machine code
after

16. CREATE TYPE cannot be used for _______ array types.
4K
RELIES_ON
Wrap
associative

17. IDs: What a quoted user-defined identifier is enclosed in.
index
DR
index
Double quotes.

18. Delimiters: The left label delimiter.
increment
PLSQL_CCFLAGS
<<
include

19. Delimiters: The association operator.
DEFINER
Wrap
=>
<<>>

20. Give the clause you add to a CREATE TABLE statement - when there is a collection column myColl - that you want to be stored in a table called myCollTab.
dense
1
exception
NESTED TABLE myColl STORE AS myCollTab

21. Declare an input/output NUMBER parameter - v3.
name-value
v3 IN OUT NUMBER
OPEN_CURSOR
;

22. The three types of compiler warnings.
DML
before insert or update on emps
EVERE - PERFORMANCE - INFORMATIONAL
after

23. This is a CASE (expression / statement): CASE num WHEN 1 THEN goto LABEL1 WHEN 2 THEN var := 2 END CASE;
closed
user-defined
1 million
statement

24. Triggers: The clause to make a trigger run before inserting or updating table EMPS.
invoker's
finishes
before insert or update on emps
CREATE TYPE

25. Dbms_sql: The return type DBMS_SQL.open_cursor.
integer
scalable
specification - body
UTL_SMTP

26. The two main parts of a package are the ____ and the _____.
specification - body
error
follows
NDS

27. How many types of compiler directives are there?
GET_LINES
:=
UTL_HTTP
3

28. Packaged constants can synchronize programs through use in _______ directives.
SIMPLE_INTEGER
selection
open cv for
scalable

29. Define a collection type - tA - which is an associative array of NUMBERS - indexed with varchar2(20) values.
dbms_output.put_line
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
DBMS_SQL
preprocessor

30. Cursors: Closing keywords of a cursor FETCH loop.
ADT
define
can
END LOOP

31. Type: An ________ subtype has all the values of its base type.
unconstrained
L.V
can
WHERE CURRENT OF

32. Cursors: The two keywords in a mere cursor declaration (one is optional).
statement
RETURN
CURSOR - RETURN
FETCH

33. Cursors: The value of SQL%ROWCOUNT after a SELECT INTO statement returns more than one row.
ADT
invoker's
exception handler
1

34. DynSQL: EXECUTE IMMEDIATE does both dynamic SQL and dynamic _______.
CREATE TYPE
PL/SQL
enclosing
INTO

35. The two kinds of CASE statements are simple and ________.
searched
%TYPE
implicit CASE_NOT_FOUND exception
integer

36. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
preprocessor
exit when cgo%NOTFOUND
nested tables - associative arrays
NUMBER

37. Triggers: The clause to make a trigger run once for every affected row.
fine-grained auditing
for each row
executable
..

38. In 11g you can directly reference _____________ in PL/SQL.
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
single-line
current
sequence pseudo-columns

39. Advantage: PL/SQL runs anywhere Oracle runs - so it is very ________.
portable
True
calls
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)

40. Types: Which type of arithmetic does PLS_INTEGER use - library or hardware?
DATE '2005-12-25'
DML
CURSOR - RETURN - IS
hardware

41. Are good for logging table DML operations.
definer's
'
DR
autonomous triggers

42. Command to create a procedure.
OPEN
is
create procedure
vname CONSTANT VARCHAR2 := 'x';

43. The _______ package lets you wrap dynamically generated PL/SQL.
elements - type
DBMS_DDL
significant
cannot

44. Bulk: The use of FORALL statements cuts down on ______ traffic.
network
FOR - IN
cursor variable
scalar

45. Delimiters: Single-line comment marker.
-
unhandled
unqualified
SESSION_USER

46. IDs: Maximum number of bytes in a user-defined identifier.
30
%BULK_ROWCOUNT
SESSION_USER
currval and nextval

47. GOTO branches to a ________.
STANDARD
label
cursor number
EXTEND

48. Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
optional
OPEN
mandatory
ALTER SESSION

49. Cursors: A cursor FOR loop cannot use a _______ variable.
PUT_LINE
optional
cursor
BINARY_INTEGER

50. Conversion: Implicit conversion can be (faster / slower) than explicit conversion.
binding
SQL cursor
NESTED TABLE myColl STORE AS myCollTab
slower