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. An incomplete type is a type created by a ______ type definition.
COUNT
enclosing
forward
object

2. Types: The PL/SQL type that is identical to PLS_INTEGER.
cannot
include
BINARY_INTEGER
must

3. PL/SQL is a ________ typed programming language.
statically
value
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
cursor variable

4. I/O: This DBMS_OUTPUT proc retrieves one line from the buffer.
4K
SESSION_USER
CLOSE
GET_LINE

5. Triggers: A compound trigger can fire at more than one ______ point.
timing
DBMS_SESSION.RESET_PACKAGE
server
DATE '2005-12-25'

6. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
REF CURSOR
compiles
column
propagates

7. With Edition-based Redefinition enabled - the unique specification of an object requires OWNER - OBJECT_NAME and ___________.
EDITION_NAME
OTHERS
suspended
True - false or NULL.

8. The compiler directive used to select which code to compile.
:=
not the same
$IF
PLS_INTEGER

9. Types: ______ data types are those storing values with no internal components.
'ENABLE:SEVERE' - 'ERROR:06002'
scalar
close C
invoker's

10. Delimiters: Single-line comment marker.
-
national
DML
PRAGMA AUTONOMOUS_TRANSACTION;

11. In interpreted mode - the PL/SQL VM interprets the _________.
...
1
CHAR - VARCHAR2
bytecode

12. Triggers: An INSTEAD OF trigger (can/cannot) be conditional.
RELIES_ON
..
EDITION_NAME
cannot

13. An editioning view can only select a subset of the columns from the base table and provide ________ for them
associative
r t.%ROWTYPE;
OUT
aliases

14. Which collection type is bounded?
$END
anonymous
varray
deletions

15. Characters that begin a single-line comment.
-
procedure P AUTHID definer;
run
an AUTHID property

16. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
RECORD OF ARRAYS
anonymous block
server
sequence pseudo-columns

17. An $ERROR directive ends with _____.
$END
ordinary
information that changes infrequently
$IF

18. Cursors: A cursor that lives in session memory until the session ends.
cursor
user-defined
an enclosing block
session cursor

19. What is the simplest statement for getting out of simple loop?
RETURN
EXIT
...
statically

20. What word is in a function header but not in a procedure header?
unqualified
RETURN
strong
static boolean

21. Cursors: The first two keywords in a Cursor FOR loop.
slower
FETCH
iname
FOR - IN

22. The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
dbms_output.put_line
definition
upper - coalesce
is certain

23. Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
bind
open cv for
invoker's
cannot

24. Triggers: INSTEAD OF triggers only run on ______ views.
5
=>
executable
non-editioning views

25. I/O: This package handles HTTP.
compiles
UTL_HTTP
name-value
error code

26. User-defined compiler directives are defined in this system compile parameter.
ALTER SESSION
PLSQL_CCFLAGS
**
DML

27. NAN stands for _________.
fine-grained auditing
not a number
bind
scalar

28. DBMS_DDL.WRAP returns a string containing wrapped _____ _____.
30
HTF
predefined
source code

29. Nested tables are multisets - meaning that there is no inherent _____ of their elements.
never
INTO
1
order

30. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
lock
SYS_REFCURSOR
TRUE - FALSE - NULL
Wrap

31. Where an external subprogram is stored.
VARRAYs
can
in the database
sql

32. Cursors: Give the first 3 words to open a cursor variable called cv.
open cv for
DBMS_PREPROCESSOR
255
scalar

33. Triggers: The clause to make a trigger run once for every affected row.
r tRec := tRec(9 - 'Jones');
for each row
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
cursor

34. Cursors: The statement that closes a cursor.
<<>>
CLOSE
for each row
DATE

35. How many times are a FOR loop's index bounds evaluated?
ROLLBACK
user-defined
raise_application_error
once

36. What are two categories of SQL functions that cannot be used in procedural statements?
aggregate - analytical
currval and nextval
=>
procedure P AUTHID current_user;

37. Triggers: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
can
WHERE CURRENT OF
executable
follows

38. Pkgs: RESET_PACKAGE only takes effect after the outermost PL/SQL proc that called it ________.
1
WHERE CURRENT OF
finishes
iname

39. IDs: The 3 non-alphanumeric characters that can be in an ordinary user-defined identifier.
stored
end
# - $ - _
package

40. A FOR-loop counter is defined only within the _____.
optional
boolean
loop
DECLARE - BEGIN - EXCEPTION - END

41. Dbms_sql: The DBMS_SQL procedure that will execute a DDL or DML statement.
ordinary
Oracle
cannot
execute

42. Fundamental: One of the 4 types of lexical units: L_____
virtual machine
value
literals
transaction

43. IDs: What a quoted user-defined identifier is enclosed in.
quoted
Double quotes.
CURRENT_USER - DEFINER
create procedure

44. Fundamental: Which one of these is a compound symbol: = - :=
:=
HTF
varray
cached

45. Default file extension of wrap's input file.
FETCH - INTO
does not
in the database
sql

46. 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.
executable
open C(p)
anonymous block
NESTED TABLE myColl STORE AS myCollTab

47. Fundamental: Maximum string length of a PL/SQL variable.
simple and compound symbols
DBMS_CRYPTO
a number := 10
32 -000

48. Exceptions: You can pass an ______ ________ to SQLERRM.
IF - CASE or LOOP
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
cannot
error code

49. By default - OUT and IN OUT parameters are passed by _____.
value
PUT
finishes
optional

50. Cursors: A Cursor FOR loop does not use a ______ statement.
user-defined
FETCH
invoker's
RESULT_CACHE