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. Command to delete a procedure.
cannot
drop procedure
for each row
in the database

2. The source for wrapping begins with the word ______.
obfuscates
production
create
vname CONSTANT VARCHAR2 := 'x';

3. An editioning view is like an ____ for a table.
r tRec := tRec(9 - 'Jones');
<<>>
bind arguments
API

4. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
type T is ref cursor
set serveroutput on
concatenation
cannot

5. You have to use _______ SQL to run DDL statements n PL/SQL.
EXECUTE
invoker's
dynamic
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));

6. Types: PL/SQL has all the _____ data types.
1 million
DATE '2005-12-25'
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
SQL

7. What are the 3 values of the BOOLEAN data type?
nested
varray
variable
TRUE - FALSE and NULL

8. I/O: The DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block _________.
dbms_output.put_line
finishes
-
select

9. Types: One of the scalar data types specific to PL/SQL: b_____.
simple and compound symbols
True
BOOLEAN
DATE

10. Triggers: To simulate having triggers on SELECT statements - you can try ___ (3-letter acronym).
portable
cannot
FGA
INTO

11. DynSQL: In EXECUTE IMMEDIATE - the statement string can use either placeholders - or string ________.
inquiry
CREATE TYPE
concatenation
:=

12. The EXTEND method is not used with ______ _______.
increment
associative arrays
END
wrap

13. IDs: The two types of words that can - but should not - be used as user-defined identifiers.
include
colon
CREATE TYPE
Keywords and predefined names.

14. The NOCOPY option on an OUT or IN OUT parameter - causes it to be passed by ______.
END IF
index
reference
error

15. Cursors: A cursor variable's type must be a ____ ______ type.
PRAGMA EXCEPTION_INIT (E - -1);
REF CURSOR
subtype
suspended

16. In a collection - the internal components always have the _____ data type.
same
placeholders
multi-row
DML

17. The compiler directive used to select which code to compile.
$IF
expression
is not
SYS_REFCURSOR

18. Cursors: Closing keywords of a cursor FETCH loop.
fetch C into V
:=
include
END LOOP

19. A subprogram created inside a block is a _________ subprogram.
nested
Apache
WHERE CURRENT OF
executable

20. A numeric FOR loop cannot _________ by other than 1?
increment
preprocessor
L.V
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

21. Cursors: You can just declare a cursor at first - then _______ it later.
once
DATE
HTF
define

22. Declaration of a varchar2 constant - called vname - with value 'x'.

23. Exceptions: User-defined exceptions must have _______.
names
...
never
execute

24. Default value of PLSQL_OPTIMIZE_LEVEL.
2
define
4
user-defined

25. Declare an associative array type - t - whose elements are of type rPerson - and whose index is PLS_INTEGER.
Double quotes.
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
RETURN
exit when cgo%NOTFOUND

26. Fundamental: A label can only go directly before an _______ statement.
name-value
close C
executable
execute immediate

27. Fundamental: What is the exponentiation operator?
**
cannot
CLOSE_CURSOR
calls

28. TCL: Undo changes back to savepoint ab.
rollback to ab
PL/SQL
lowest
function f (n number) return number result_cache relies_on (emps);

29. A type of compiler directive - begins with 's'.
;
NDS
dynamic
selection

30. One good use of bind variables is to guard against SQL _______.
injection
specification - body
inquiry
cursor

31. Cursors: A cursor with a name is an _________ cursor.
True
explicit
aggregate - analytical
after insert on emps

32. Define a nested table type - tN - which is a table NUMBERs.
close C
Abstract
network
TYPE tN IS TABLE of NUMBER

33. Types: Which type of arithmetic does NUMBER use - library or hardware?
TRUE - FALSE and NULL
delimiters
library
alter procedure

34. A pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
select
1
database
cursor

35. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
will not
ordinary
same arguments
OUT

36. PL/SQL is a ________ typed programming language.
statically
optional
OTHERS
..

37. Fundamental: Which one of these is a compound symbol: = - :=
slower
DBMS_PREPROCESSOR
:=
STANDARD

38. In short-circuit evaluation - IF-conditions are only evaluated up to where the outcome ___________.
is certain
CURSOR - RETURN - IS
names
r t.%ROWTYPE;

39. 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.
1 million
STANDARD
NESTED TABLE myColl STORE AS myCollTab
transaction

40. A CASE statement (can / cannot) execute more than one group of statements.
Apache
query
cannot
column

41. The allowed data types of user-defined conditional compilation directives are boolean and _______.
EDITION_NAME
PLSQL_OPTIMIZE_LEVEL
name
pls_integer

42. What word is in a function header but not in a procedure header?
collections
RETURN
invoker's
PUT_LINE

43. Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
session cursor
a number := 10
bind
national

44. Delimiters: The association operator.
bytecode
integer
TOO_MANY_ROWS
=>

45. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
CURRENT_USER - DEFINER
suspended

-

46. DynSQL: The standard package that does dynamic SQL.
DBMS_SQL
portable
rollback to ab
dbms_output.put_line

47. IDs: List the two types of user-defined identifiers.
OPEN
Ordinary - Quoted.
BOOLEAN
True

48. IDs: The type of words that cannot be user-defined identifiers.
query
Abstract
Gateway
Reserved words.

49. Term for a character literal with zero characters.
IN OUT
ENABLE
1
null string

50. This is a CASE (expression / statement): CASE num WHEN 1 THEN goto LABEL1 WHEN 2 THEN var := 2 END CASE;
CLOSE_CURSOR
statement
never
a cursor