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 used to execute dynamic SQL.
EXECUTE IMMEDIATE
dbms_output.put_line
CURSOR - RETURN - IS
literals

2. Subprograms are stored in __________ form.
plb
With a letter.
v2 IN NUMBER default 20
executable

3. A type of compiler directive - begins with 's'.
sequence pseudo-columns
order
selection
for update of

4. Cursors: The keyword that makes a ref cursor type STRONG.
invoker's
OPEN
RETURN
statement

5. Types: ________ data types have internal components.
boolean
invalid
do not
composite

6. In a collection - the internal components always have the _____ data type.
for each row
TRUE - FALSE - NULL
cannot
same

7. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
selection
cannot
context
DML

8. Fundamental: One of the 4 types of lexical units: d_____
Reserved words.
delimiters
TOO_MANY_ROWS
hardware

9. Types: One of the 4 PL/SQL things that have a data type (begins with 'r').
PLSQL_CCFLAGS
PLS_INTEGER
..
return value

10. Into nested table ex - put elements of tbl1 that are not in tbl2.
<<>>
p.v
END CASE
ex := tbl1 MULTISET EXCEPT tbl2

11. A FOR-loop counter is defined only within the _____.
DBMS_SESSION
loop
strong
a number := 10

12. The exception raised when a SELECT INTO statement returns more than one row.
TOO_MANY_ROWS
reference
DECLARE - BEGIN - EXCEPTION - END
package

13. Cursors: An explicit cursor (can/cannot) accept parameters.
TRUE - FALSE and NULL
cursor c RETURN rC;
can
error code

14. Cursors: If no SELECT or DML statement has run - the value of SQL%ROWCOUNT is what?

database
FETCH
ordinary

15. Triggers: The clause to make a trigger run before inserting or updating table EMPS.
With a letter.
PRAGMA EXCEPTION_INIT (E - -1);
before insert or update on emps
NESTED TABLE myColl STORE AS myCollTab

16. Cursors: Opening keyword of a cursor FETCH loop.
scalar
USING
LOOP
will not

17. In interpreted mode - the PL/SQL VM interprets the _________.
BINARY_INTEGER
PUT_LINE
bytecode
boolean

18. Triggers: In a per-row trigger - the ____ record shows you the changed state of an affected row.
NEW
REF CURSOR
HTF
L.V

19. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
server
exception handler
varray
package

20. Triggers: In a DML trigger - the phrase that makes the trigger run for every affected record.
collections
a number := 10
for each row
<<>>

21. The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
elements
not a number
include
inquiry

22. Cursors: How many DML statements can a FORALL statement contain?
implicit CASE_NOT_FOUND exception
Keywords and predefined names.
1
OPEN_CURSOR

23. In a sparse collection - the indexes of defined elements (do / do not) run in consecutive order.
WHEN OTHERS THEN
E Exception;
selection
do not

24. A result-cached function caches its results in the _____ _____ ____ (SGA)
inquiry
1
system global area
mandatory

25. Fundamental: identifiers (include/do not include) reserved words.
CURSOR - RETURN
OUT
WHEN
include

26. Triggers: INSTEAD OF triggers only run on ______ views.
OTHERS
non-editioning views
statically
for each row

27. Keyword for doing assignment to a variable with SELECT.
CLOSE
INTO
RETURN
disable

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

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

29. _________ exceptions can not be trapped with an error handler
Declaration
NESTED TABLE myColl STORE AS myCollTab
NEW
cursor

30. I/O: This DBMS_OUTPUT proc retrieves one line from the buffer.
FOR UPDATE
composite
GET_LINE
elements

31. Cursors: If you use an EXIT statement to exit a cursor FOR loop prematurely - the cursor (is / is not) closed automatically.
is
cannot
With a letter.
colon

32. Types: One of the scalar data types specific to PL/SQL: B_______
SQL
BINARY_INTEGER
an enclosing block
wrap

33. Dbms_sql: A cursor opened with OPEN_CURSOR (can/cannot) be reused for multiple queries without first closing it.
rollback to ab
REF_CURSOR
can
BOOLEAN

34. I/O: The string table type in DBMS_OUTPUT for holding lines of text.
CHARARR
PUT_LINE
REF CURSOR
cached

35. Conversion: Implicit conversion is not always predictable; it is _______-sensitive. And its rules might change.
associative arrays
obfuscates
REF CURSOR
context

36. Characters that begin a single-line comment.
code
-
end
TOO_MANY_ROWS

37. DynSQL: The clause that receives single-row query output from EXECUTE IMMEDIATE.
FOR - IN
INTO
wrap
array

38. The command used to populate PLSQL_CCFLAGS.
database
ALTER SESSION
Ordinary - Quoted.
CREATE TRIGGER

39. A PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE
WHERE CURRENT OF
same arguments
inclusive
return value

40. Cursors: A session cursor that is automatically created and managed by PL/SQL.
user-defined
transaction
implicit cursor
FALSE

41. Name the two types of FOR loop.
execute immediate
a number := 10
numeric and cursor
rollback to ab

42. Associative array types (can / cannot) be defined in the database.
inquiry
cannot
after insert on emps
OPEN

43. Command to make a no-op (omit semicolon).
True - false or NULL.

set serveroutput on
dense

44. Triggers: In a per-row trigger - the ____ record shows you the original state of an affected row.
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
OLD
IN
EXCEPTION_INIT

45. An anonymous block is a(n) ____ statement.
collections
SQL
SQLERRM
finishes

46. Exceptions: The maximum length of an Oracle error message is ____ characters
PRAGMA EXCEPTION_INIT (E - -1);
include
512
3

47. Characters that begin a multi-line comment.
lower sub-block
DEFINER
statically
/*

48. Command to assign to variables from a cursor.
Abstract
transaction
CASE_NOT_FOUND
FETCH

49. Define a collection type - tV - which is a varray of 100 NUMBERs.
TYPE tV IS VARRAY(100) OF NUMBER
flushed
before insert or update on emps
dynamic

50. Triggers: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
exception handler
parameter
anonymous
follows