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. Exceptions: The three words that can be used to guarantee all exceptions will be handled.
FETCH
WHEN OTHERS THEN
unqualified
collections - records

2. Data dictionary view that shows the compiler settings for each compiled object.
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
USER_PLSQL_OBJECT_SETTINGS
cursor
TOO_MANY_ROWS

3. By default - an IN parameter is passed by ______.
r t.%ROWTYPE;
scalar
1
reference

4. In a collection - the internal components are called _______.
elements
WHERE CURRENT OF
timing
OLD

5. Cursors: A Cursor FOR loop does not use a ______ statement.
RETURN
=>
network
FETCH

6. I/O: This package does email.
identifiers
EXTEND
UTL_SMTP
same arguments

7. What are the 3 values of the BOOLEAN data type?
RECORDS
ADT
TRUE - FALSE and NULL
rollback to ab

8. Web: PL/SQL _______ enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener.
reference
compile
invalid
Gateway

9. The attribute used to get the type of a row without knowing what it is.
%ROWTYPE
hardware
stored
IR

10. Exceptions: Some internal exceptions have ________ names.
names
FORALL i IN MyList.FIRST..MyList.LAST
predefined
UTL_HTTP

11. Fundamental: What are the 3 compound symbols for 'not equal to'?
parameter
END IF
unconstrained
<> - != - ^=

12. The PL/SQL virtual machine turns bytecode into system ______.
dbms_output.put_line
VARCHAR2
calls
.

13. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
TYPE tN IS TABLE of NUMBER
VARRAYs
elements - type
ROLLBACK

14. Characters that end a multi-line comment.
after insert on emps
*/
1
GET_LINE

15. A (simple / searched) CASE statement begins with CASE WHEN <boolean expression>.
searched
forward
after
NESTED TABLE myColl STORE AS myCollTab

16. Cursors: If you use an EXIT statement to exit a cursor FOR loop prematurely - the cursor (is / is not) closed automatically.
is
user-defined
same
is not

17. Give the statement to make a program autonomous.
3
4
ROLLBACK
PRAGMA AUTONOMOUS_TRANSACTION;

18. The BETWEEN operator is (inclusive / exclusive).
scalable
order
inclusive
cursor c RETURN rC;

19. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
upper - coalesce
1
RECORD OF ARRAYS
quoted

20. The three types of compiler warnings.
package
EVERE - PERFORMANCE - INFORMATIONAL
3
EXECUTE IMMEDIATE

21. Default value of PLSQL_OPTIMIZE_LEVEL.
32 -000
2
bytecode
index

22. Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
bind
:=
FETCH
error

23. To use a TYPE declared in the database - a PL/SQL block has to have _______ privilege on the type.
cursor
is certain
-
select

24. SIMPLE_INTEGER is like PLS_INTEGER - but it does not allow ____ values - and it does not raise overflow exceptions.
NEW
obfuscates
optional

25. %ROWTYPE gets the type of a table or _______.
loop
cursor
DECLARE - BEGIN - EXCEPTION - END
OPEN

26. Triggers: A compound trigger can fire at more than one ______ point.
loop
REF_CURSOR
timing
system global area

27. Triggers: An INSTEAD OF trigger (can/cannot) be conditional.
bind
cannot
Keywords and predefined names.
set serveroutput on

28. Fundamental: One of the 4 types of lexical units: L_____
slower
literals
bb:true
lower sub-block

29. Cursors: A session cursor that a PL/SQL user creates and manages.
explicit cursor
exception handler
literal
BINARY_INTEGER

30. Triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
array
always
TIMESTAMP.
USING

31. Give the first line of the FOR loop with index i going from 1 to 10 in reverse.
statement
BINARY_INTEGER
versions
FOR i IN REVERSE 1..10 LOOP

32. The variable showing the user id that is automatically prepended to object names.
CURRENT_USER
iname
rollback to ab
buffer

33. Exceptions: Associate exception E with error code -1.
SQL
SYS_CONTEXT
PRAGMA EXCEPTION_INIT (E - -1);
transaction

34. An editioning view is like an ____ for a table.
API
ENABLE
CLOSE
source code

35. _______ arrays can be sparse or dense - depending on how you fill them.
EXIT WHEN
associative
CASE_NOT_FOUND
TRUE - FALSE and NULL

36. Fundamental: identifiers (include/do not include) reserved words.
PL/SQL
1
session cursor
include

37. Cursors: Give the statement to open cursor C.
array
%
increment
open C

38. Types: A data type family consists of a ______ type and its subtypes.
create
library
-20 -000
base

39. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
significant
names
suspended
name

40. I/O: This package handles HTTP.
FOR - IN
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
UTL_HTTP
TOO_MANY_ROWS

41. DBMS_DDL.WRAP returns a string containing wrapped _____ _____.
source code
is
SQL
PSP

42. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
*/
compiles
strong
RETURN

43. With a result-cached function - it uses a cached value when the function is called again with the...
'ENABLE:SEVERE' - 'ERROR:06002'
searched
same arguments
DBMS_PIPE

44. In a collection - the internal components always have the _____ data type.
DR
same

current transaction

45. Exceptions: The package that defines pre-defined exceptions.
cursor
STANDARD
255
error code

46. Delimiters: the attribute indicator.
error code
preprocessor
%
:=

47. Labels for blocks and statements are enclosed in '__' brackets.
iname
scalable
current
<<>>

48. The default value of the AUTHID property.
DEFINER
32 -000
GET_LINES
transaction

49. Two types of stored subprograms.
nested
collections
procedure - function
variable

50. DynSQL: The three modes of arguments in the USING clause of EXECUTE IMMEDIATE.
USER_PLSQL_OBJECT_SETTINGS
IN - OUT - IN OUT
names
significant