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. When a session starts - CURRENT_SCHEMA has the value of the schema owned by ___________.
cached
SESSION_USER
FETCH - BULK COLLECT INTO
order

2. Cursors: The statement where you pass the parameters to a cursor.
DEFINER
OPEN
30
preprocessor

3. I/O: SQLPlus command for turning on buffered output.
set serveroutput on
for each row
value
elements - type

4. Exceptions: An exception in the declarations section does not go to the __________ section.
EXECUTE IMMEDIATE
exception
type T is ref cursor
aggregate - analytical

5. I/O: This package does email.
Oracle
UTL_SMTP
optional
REUSE SETTINGS

6. Types: ______ data types are those storing values with no internal components.
delimiters
CURRENT_USER - DEFINER
an autonomous routine
scalar

7. Types: In addition to the SQL data types - PL/SQL has ___ more scalar data types.
bind
Web Toolkit
ROLLBACK
4

8. The first SQL statement in an autonomous routine begins a _______.
transaction
ADT
suspended
virtual machine

9. Pkgs: This procedure de-instantiates/resets all packages in this session.
GET_LINES
cursor variable
DBMS_SESSION.RESET_PACKAGE
%TYPE

10. Declare a constrained subtype of NUMBER called N that is not null.
open C(p)
scalar
information that changes infrequently
SUBTYPE n IS number not null;

11. Delimiters: The association operator.
DBMS_OUTPUT
'ENABLE:SEVERE' - 'ERROR:06002'
Server Pages
=>

12. Abbreviation for Abstract Data Type.
elements
=>
ADT
boolean

13. Types: One of the 4 PL/SQL things that have a data type (begins with 'c').
constant
$IF $THEN $ELSE $END
EXCEPTION_INIT
cannot

14. Wrap ______=xxxx oname=zzzzz
implicit CASE_NOT_FOUND exception
PSP
after insert on emps
iname

15. The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
inquiry
error code
deletions
..

16. Web: mod_plsql is an implementation of the PL/SQL _________.
SESSION_USER
compilation
Gateway
vname CONSTANT VARCHAR2 := 'x';

17. Exceptions: Start of the range of error codes for RAISE_APPLICATION_ERROR.
binding
EVERE - PERFORMANCE - INFORMATIONAL
-20 -000
fail

18. How many PL/SQL collection types are there?
dense
;
4
3

19. Labels for blocks and statements are enclosed in '__' brackets.
<<>>
cannot
will not
3

20. Web: mod_plsql is an _______ module.
PL/SQL
Apache
do not
fine-grained auditing

21. Command to create a procedure.
create procedure
UTL_HTTP
CURRENT_USER - DEFINER
FOR - IN

22. Fundamental: One of the 4 types of lexical units: i_____
RECORD OF ARRAYS
$$PLSQL_LINE
identifiers
fetch cv into myvar

23. Cursors: Give the statement to close cursor C.
close C
CURSOR - RETURN
WHERE CURRENT OF
%BULK_ROWCOUNT

24. Java - C or other program called directly in PL/SQL.
selection
raise_application_error
external subprogram
for each row

25. Triggers: In a DML trigger - the phrase that makes the trigger run for every affected record.
invoker's
cannot
deletions
for each row

26. The Wrap utility (does / does not) encrypt source code.
1
FORALL and BULK COLLECT
does not
explicit

27. Cursors: The SQL%FOUND attribute has how many possible values?
WHERE CURRENT OF
3
CURRENT_USER - DEFINER
TRUE - FALSE - NULL

28. Triggers: There are __ types of triggers.
%TYPE
open C(p)
5
cannot

29. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
server
end
must
REF CURSOR

30. Pkgs: RESET_PACKAGE only takes effect after the outermost PL/SQL proc that called it ________.
an AUTHID property
transaction
CHAR - VARCHAR2
finishes

31. Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
associative
bind
TRUE - FALSE - NULL
NESTED TABLE myColl STORE AS myCollTab

32. Bytecode is _______ _______ code.
True - false or NULL.
literal
ALTER SESSION
virtual machine

33. Cursors: How many implicit cursors can you access?
explicit
Gateway
binding
1

34. Records are one of the two kinds of PL/SQL _______ data types.
exception handler
explicit cursor
cannot
composite

35. TCL: Create a savepoint called ab
end
savepoint ab
TOO_MANY_ROWS
Wrap

36. A GOTO statement can branch from an exception handler into...
subtype
context
an enclosing block
set serveroutput on

37. The 2 characters that begin all inquiry directives.
..
1
can
$$

38. I/O: The DBMS_OUTPUT function that writes output without a newline character.
query
PUT
procedure P AUTHID current_user;
;

39. The method to add one or more elements to a nested table or VARRAY is ______.
not the same
EXTEND
always
OPEN

40. Exceptions: The three words that can be used to guarantee all exceptions will be handled.
4
disable
WHEN OTHERS THEN
view

41. Types: ________ data types have internal components.
after insert on emps
composite
TYPE tV IS VARRAY(100) OF NUMBER
WHERE CURRENT OF

42. To get the current value of CURRENT_SCHEMA - CURRENT_USER - or SESSION_USER - use the ____ function.
SYS_CONTEXT
CHARARR
-20 -000
1 million

43. The 4 keywords in the $IF compiler directive.
FOR UPDATE
2
$IF $THEN $ELSE $END
ALTER SESSION

44. A CASE statement (can / cannot) execute more than one group of statements.
cannot
implicit cursor
bind
TOO_MANY_ROWS

45. Cursors: An implicit cursor is also called a/n ________ ________ -
reference
PSP
INTO
SQL cursor

46. A FOR-loop counter is defined only within the _____.
DBMS_SESSION
strong
OLD
loop

47. You can put a label before any executable PL/SQL _________.
PRAGMA AUTONOMOUS_TRANSACTION;
Declaration
statement
searched

48. The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
definition
compile
SIMPLE_INTEGER
**

49. To enable result-caching for a function - use the _____________ clause.
Wrap
RESULT_CACHE
COUNT
raise_application_error

50. Define a nested table type - tN - which is a table NUMBERs.
DEFINER
TYPE tN IS TABLE of NUMBER
%BULK_ROWCOUNT
hardware