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: If an exception is raised inside a cursor loop - the cursor (is / is not) also closed automatically.
SUBTYPE numb IS number;
is
DEFINER
REUSE SETTINGS

2. I/O: This package lets PL/SQL programs read and write OS files.
3
for each row
UTL_FILE
SQLCODE

3. By default - an IN parameter is passed by ______.
boolean
reference
pls_integer
is

4. To create a collection type - use the TYPE command in PL/SQL and the ______ _____ command in SQL.
FOR - IN
expression
unhandled
CREATE TYPE

5. Type: A __________ subtype has only a subset of the values of its base type.
constrained
set serveroutput on
VARRAYs
ADT

6. User-defined compiler directives are defined in this system compile parameter.
Web Toolkit
slower
concatenation
PLSQL_CCFLAGS

7. You have to use _______ SQL to run DDL statements n PL/SQL.
dynamic
CREATE TYPE
wrap
current BEGIN-END block

8. Cursors: Give the statement to close cursor C.
cursor
does not
close C
plb

9. The PL/SQL ________ evaluates compiler directives.
3
value
preprocessor
executable

10. Cursors: The value of SQL%ROWCOUNT after a SELECT INTO statement returns more than one row.
END CASE
label
1
bytecode

11. Triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
always
TIMESTAMP.
simple and compound symbols
cannot

12. Cursors: Give the statement to fetch data from cursor C into variable V.
fetch C into V
BINARY_INTEGER
**
*/

13. The $ERROR directive is used to make compilation ____.
SQLPlus
fail
DML
UTL_HTTP

14. Declare a cursor c with no parameters or return type.
cursor c;
invoker's
reference
cannot

15. A __________ can have an AUTHID property
PL/SQL Gateway
compilation
package
ALTER SESSION

16. Cursors: What is the 'name' of the implicit cursor?
user-defined
SQL
1
can

17. Cursors: A cursor that lives in session memory until the session ends.
mandatory
session cursor
versions
increment

18. Advantage: Performance: Stored subprograms are ______ in memory.
cached
not a number
open cv for
OPEN_CURSOR

19. Conditional ______ allows backward compability with older database versions.
compilation
BINARY_INTEGER
DBMS_PREPROCESSOR
index

20. PLSQL_CCFLAGS hold a comma-separated list of ______ pairs (hyphenated).
name-value
slower
currval and nextval
binding

21. Triggers: On a non-editioning view - you can only define __________ triggers.
invoker's
is certain
$IF $THEN $ELSE $END
INSTEAD OF

22. Abbreviation for Abstract Data Type.
%BULK_ROWCOUNT
define
procedure - function
ADT

23. Define a collection type - tV - which is a varray of 100 NUMBERs.
upper - coalesce
TYPE tV IS VARRAY(100) OF NUMBER
not the same
stored

24. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
30
stored
RECORD OF ARRAYS
RAISE

25. Wrapping can be done with the ____ executable.
wrap
query
EXIT WHEN
True

26. Cursors: The index variable in a cursor FOR loop (does/does not) need to be declared.
does not
define
END IF
DBMS_SQL

27. If an initialized collection has no elements - FIRST and LAST return _____.

anonymous block
STANDARD
%BULK_ROWCOUNT

28. I/O: Max number of bytes DBMS_OUTPUT can handle in a session.
255
1
1 million
FETCH

29. What are the starting and ending keywords of a simple loop?
calls
1
<> - != - ^=
LOOP - END LOOP

30. Loops: What is the index range operator in a FOR loop?
..
CASE_NOT_FOUND
-20 -000
searched

31. Language that PL/SQL is derived from.
Ada
IF - CASE or LOOP
specification - body
create procedure

32. Triggers: INSTEAD OF triggers only run on ______ views.
non-editioning views
nested
$IF
error code

33. Delimiters: the attribute indicator.
%
EXIT WHEN
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
composite

34. Command to create a procedure.
does not
Reserved words.
create procedure
cursor c;

35. DynSQL: In EXECUTE IMMEDIATE - the statement string can use either placeholders - or string ________.
DML
concatenation
function f (n number) return number result_cache relies_on (emps);
upper - coalesce

36. Exceptions: An unhandled exception ________ to the enclosing block.
delimiters
collections
propagates
DBMS_PIPE

37. Dbms_sql: DBMS_SQL package is compiled with _______ rights.

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

38. GOTO branches to a ________.
label
after insert on emps
EXIT WHEN cv%NOTFOUND
OTHERS

39. Statement to exit a cursor loop on cursor cGo when there are no more records.
1 million
exit when cgo%NOTFOUND
selection
DBMS_PREPROCESSOR

40. Delimiters: The association operator.
invoker's
UTL_SMTP
=>
static boolean

41. If none of a CASE expression's conditions are TRUE - it returns _____.
%
machine
bind

42. I/O: This package has hypertext functions that generate HTML tags
HTF
is certain
raise_application_error(-20 -100 - 'Bad');
DBMS_PIPE

43. Give the value list in the PLSQL_WARNINGS parameter to turn on SEVERE compiler warnings and make compiler warning 06002 be an error.

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

44. The two kinds of CASE statements are simple and ________.
current
searched
name and datatype
PRAGMA AUTONOMOUS_TRANSACTION;

45. Value of PLSQL_OPTIMIZE_LEVEL that prevents optimization.
same arguments
not a number
elements - type
1

46. IDs: You cannot use reserved words as ______ user-defined identifiers.
ordinary
PLSQL_CCFLAGS
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
currval and nextval

47. Declare an output NUMBER parameter - v1 - that defaults to 10.
v1 OUT number default 10
definition
ADT
Gateway

48. PLS_INTEGER has its arithmetic implemented in ________ - so it is very fast.
hardware
SQL cursor
UTL_SMTP
scalar

49. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
machine code
CURRENT_USER - DEFINER
DML
preprocessor

50. Cursors: The four cursor attributes - in alphabetical order.
boolean
machine code
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
national