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. Cursors: The SQL%BULK_ROWCOUNT attribute is an _________ -
do not
statement
object
array

2. Cursors: Give the statement to fetch data from cursor C into variable V.
1 million
fetch C into V
USER_PLSQL_OBJECT_SETTINGS
1

3. Cursors: An explicit cursor (can/cannot) accept parameters.
code
variable
invalid
can

4. The collection method that tells the number of elements in the collection.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
DBMS_SESSION.RESET_PACKAGE
COUNT
Wrap

5. Declare a variable r whose type is that of the records in table t.
network
r t.%ROWTYPE;
cursor variable
associative

6. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
DBMS_CRYPTO
system global area
finishes
compiles

7. Exceptions: User-defined exceptions must have _______.
TIMESTAMP.
associative
value
names

8. The allowed data types of user-defined conditional compilation directives are boolean and _______.
native dynamic
before insert or update on emps
pls_integer
v2 IN NUMBER default 20

9. Exceptions: Each internal exception has an Oracle ______ _______.
aggregate - analytical
dbms_output.put_line
error code
DBMS_SQL

10. I/O: SQLPlus command for turning on buffered output.
FETCH
PLS_INTEGER
set serveroutput on
OLD

11. Types: A data type family consists of a ______ type and its subtypes.
EXECUTE IMMEDIATE
an autonomous routine
base
DBMS_CRYPTO

12. A FOR loop can be indexed by numbers or by a ______.
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
index
value
cursor

13. When a session starts - CURRENT_SCHEMA has the value of the schema owned by ___________.
will not
anonymous
SESSION_USER
v2 IN NUMBER default 20

14. In a cursor loop - the clause added to an INSERT or UPDATE to point at the current record.
pseudocolumn
Abstract
FETCH
WHERE CURRENT OF

15. Triggers: In a per-row trigger - the ____ record shows you the changed state of an affected row.
NEW
exception handler
VARRAYs
query

16. The _____ utility turns PL/SQL source code into unreadable text.
Wrap
bind
DML
INTO

17. For an associative array - the FIRST method gets the (lowest / first-created) index.
3
varray
cannot
lowest

18. Declare a package procedure P with no arguments - and give it definer's rights.
implicit
procedure P AUTHID definer;
WHERE CURRENT OF
integer

19. Cursors: The index variable in a cursor FOR loop (does/does not) need to be declared.
DEFINER
function f (n number) return number result_cache relies_on (emps);
does not
REF CURSOR

20. The lowest index of a VARRAY is ___.
ex := tbl1 MULTISET EXCEPT tbl2
after
1
DBMS_DDL

21. Name the two types of FOR loop.
ALTER SESSION
1
numeric and cursor
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));

22. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
BOOLEAN
TRUE - FALSE - NULL
SUBTYPE numb IS number;
ENABLE

23. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
query
ROLLBACK
fail
constrained

24. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
specification - body
boolean
implicit
cannot

25. A large OUT or IN OUT parameter will waste a lot of space and CPU if it is passed by _____.
BOOLEAN
value
composite
FOR - IN

26. A numeric FOR loop cannot _________ by other than 1?
WHERE CURRENT OF
increment
error code
raise_application_error

27. Types: PL/SQL has all the _____ data types.
SQL
PSP
OUT
HTF

28. Fundamental: identifiers (include/do not include) reserved words.
iname
include
USER_PLSQL_OBJECT_SETTINGS
WHEN

29. Web: Abbreviation of PL/SQL Server Page.
ADT
DEFINER
reference
PSP

30. Default value of PLSQL_OPTIMIZE_LEVEL.
Gateway
2
slower
3

31. I/O: This DBMS_OUTPUT proc retrieves multiple lines from the buffer.
view
wrap
GET_LINES
CURRENT_USER - DEFINER

32. The command used to populate PLSQL_CCFLAGS.
ALTER SESSION
raise_application_error(-20 -100 - 'Bad');
RETURN
STANDARD

33. Give the statement to make a program autonomous.
fields
END LOOP
FETCH
PRAGMA AUTONOMOUS_TRANSACTION;

34. GOTO branches to a ________.
DBMS_PREPROCESSOR
label
CURRENT_USER - DEFINER
error code

35. Cursors: Give the statement to open cursor C.
SQLCODE
elements - type
open C
DBMS_PREPROCESSOR

36. Delimiters: The remote access symbol.
@
SUBTYPE numb IS number;
Declaration
cannot

37. The AUTHID property applies to PL/SQL objects that contain _____.
code
predefined
EXECUTE IMMEDIATE
bind arguments

38. How many times are a FOR loop's index bounds evaluated?
once
inclusive
procedure P AUTHID current_user;
cannot

39. The $ERROR directive is used to make compilation ____.
fail
injection
wrap
GET_LINE

40. Exceptions: The maximum length of an Oracle error message is ____ characters
delimiters
definer's
512
propagates

41. Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
REF CURSOR
CURSOR - RETURN - IS
nested
STANDARD

42. Delimiters: The association operator.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
STANDARD
=>
query

43. Advantage: PL/SQL is PORTABLE to any OS where ________ runs.
statement
Oracle
currval and nextval
information that changes infrequently

44. A pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
open cv for
pseudocolumn
aggregate - analytical
cursor

45. A CASE statement ends with ____ ____.
anonymous block
package
IF - CASE or LOOP
END CASE

46. Pkgs: This procedure de-instantiates/resets all packages in this session.
OPEN
exit when cgo%NOTFOUND
associative
DBMS_SESSION.RESET_PACKAGE

47. Exceptions: The ________ handler must always be the last handler.
USER_PROCEDURES
forward
WHEN OTHERS THEN
OTHERS

48. The DEFINITION of a cursor is the _____ that returns the records.
is
query
BINARY_INTEGER
must

49. An $ERROR directive ends with _____.
return value
Ordinary - Quoted.
PUT_LINE
$END

50. Types: ________ data types have internal components.
current BEGIN-END block
follows
virtual machine
composite