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. Declare an unconstrained subtype of NUMBER called NUMB.
DATE
EDITION_NAME
network
SUBTYPE numb IS number;

2. The LIMIT method is only meaningful for _______.
statement
simple and compound symbols
VARRAYs
never

3. _________ exceptions can not be trapped with an error handler
define
Declaration
TOO_MANY_ROWS
can

4. Advantage: PL/SQL supports ______-oriented programming.
object
expression
virtual machine
transaction

5. Define a record variable r - of type tRec - initialized with values 9 and 'Jones' in its two fields.

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

6. Nested tables are multisets - meaning that there is no inherent _____ of their elements.
composite
order
varray
fine-grained auditing

7. What do you FETCH from?
$$PLSQL_LINE
32 -000
a cursor
PLSQL_CCFLAGS

8. In the IF syntax - is if ENDIF or END IF?
procedure P AUTHID definer;
EXECUTE IMMEDIATE
END IF
$IF $THEN $ELSE $END

9. Cursors: The three keywords in a full cursor declaration/definition (before the SELECT).
%ROWTYPE
loop
SQL
CURSOR - RETURN - IS

10. With a result-cached function - it uses a cached value when the function is called again with the...
same arguments
single-line
is
anonymous

11. Advantage: Performance: Stored subprograms are ______ in memory.
bind
elsif
UTL_HTTP
cached

12. To exit an active autonomous transaction without causing an exception - you must issue _____ or ______.
commit - rollback
E Exception;
r tRec := tRec(9 - 'Jones');
UTL_FILE

13. With dynamic typing - type checking is done at ______time.
associative arrays
finishes
run
subtype

14. The variable showing the user id that is automatically prepended to object names.
is not
vname CONSTANT VARCHAR2 := 'x';
CURRENT_USER
E Exception;

15. I/O: The buffered output of dbms_output.put_line is retrieved by _______.
SQLPlus
$$
SQL
2

16. Dbms_sql: A cursor opened with OPEN_CURSOR (can/cannot) be reused for multiple queries without first closing it.
2
can
open C(p)
FGA

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

18. Language that PL/SQL is derived from.
PUT_LINE
Ada
OTHERS
drop procedure

19. Triggers and views always have _________ rights.

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

20. Triggers: In a per-row trigger - the ____ record shows you the original state of an affected row.
implicit
virtual machine
DECLARE - BEGIN - EXCEPTION - END
OLD

21. The FOR UPDATE OF clause references a (table / column).
PLS_INTEGER
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
column
package

22. Types: One of the scalar data types specific to PL/SQL: R_________.
REF_CURSOR
ALTER SESSION
REF CURSOR
base

23. The DECLARE keyword is only required in an _______ _______.
unqualified
numeric and cursor
anonymous block
inquiry

24. Abbreviation of invoker's rights
WHERE CURRENT OF
IR
unhandled
SUBTYPE numb IS number;

25. Bytecode is _______ _______ code.
virtual machine
1
static
compilation

26. Type: A __________ subtype has only a subset of the values of its base type.
constrained
unhandled
cursor number
view

27. I/O: If the current (outermost) PL/SQL block ends with an unhandled exception - the output buffer will not be ___________.
flushed
network
not a number
CLOSE_CURSOR

28. Exceptions: Declare an exception called E.
plb
quoted
SQLCODE
E Exception;

29. I/O: The most basic PL/SQL way to give output to the user.
dbms_output.put_line
BINARY_INTEGER
oname
virtual machine

30. Fundamental: One of the 4 types of lexical units: c_____.
obfuscates
..
comments
cannot

31. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
static
compilation
invalid
L.V

32. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
INTO
$END
FETCH - BULK COLLECT INTO
NUMBER

33. IDs: What a quoted user-defined identifier is enclosed in.
virtual machine
Double quotes.
%TYPE
calls

34. IDs: You can use a reserved word as a _______ user-defined identifier.
associative
query
GET_LINE
quoted

35. Characters that begin a single-line comment.
-
SUBTYPE numb IS number;
:=
definer's

36. Collections are one of the two kinds of PL/SQL _______ data types.
propagates
PLSQL_CCFLAGS
composite
simple and compound symbols

37. The PL/SQL virtual machine turns bytecode into system ______.
compiles
calls
-
searched

38. Into nested table ex - put elements of tbl1 that are not in tbl2.
return value
ex := tbl1 MULTISET EXCEPT tbl2
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
FETCH - INTO

39. Cursors: The type definition of a WEAK ref cursor type has no _______ clause.
INTO
RETURN
1
FETCH

40. Exceptions: The ________ handler must always be the last handler.
constructor
not the same
OTHERS
delimiters

41. A (simple / searched) CASE statement begins with CASE WHEN <boolean expression>.
SQLCODE
static boolean
searched
array

42. Fundamental: A label can be used to name an ______ block.
anonymous
exception handler
package
associative

43. Assigning values to PL/SQL variables that appear in SQL statements is called _______.
anonymous
IR
EXIT WHEN
binding

44. Command to create a procedure.
create procedure
server
cursor number
before insert or update on emps

45. Exceptions: Some internal exceptions have ________ names.
DBMS_SESSION.RESET_PACKAGE
unqualified
predefined
type T is ref cursor

46. Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
ALTER SESSION
E Exception;
STANDARD
CASE_NOT_FOUND

47. Exceptions: A GOTO statement cannot branch into an _______ ________.
selection
SQL
exception handler
USING

48. (from inside a package) Declare a function f - that takes a number n - returns a number - and caches results - depending on table EMPS.
With a letter.
elsif
SYS_REFCURSOR
function f (n number) return number result_cache relies_on (emps);

49. Give the first line of the FOR loop with index i going from 1 to 10 in reverse.
error code
cursor c RETURN rC;
FETCH
FOR i IN REVERSE 1..10 LOOP

50. By default - an IN parameter is passed by ______.
cannot
reference
information that changes infrequently
parameter