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. A subprogram created inside a block is a _________ subprogram.
nested
@
DBMS_SESSION
identifiers

2. Cursors: A cursor variable's type must be a ____ ______ type.
REF CURSOR
quoted
savepoint ab
bb:true

3. Dbms_sql: The integer returned by OPEN_CURSOR is a _____ _____.
network
lower sub-block
constant
cursor number

4. Triggers: In a per-row trigger - the ____ record shows you the changed state of an affected row.
unconstrained
collections
SIMPLE_INTEGER
NEW

5. Parameter that controls how much Oracle optimizes your PL/SQL code on compilation.
...
PLSQL_OPTIMIZE_LEVEL
dynamic
selection

6. Assigning values to PL/SQL variables that appear in SQL statements is called _______.
binding
dynamic
EXIT WHEN
raise_application_error(-20 -100 - 'Bad');

7. Characters that begin a multi-line comment.
/*
bytecode
after
DBMS_PREPROCESSOR

8. Pkgs: RESET_PACKAGE only takes effect after the outermost PL/SQL proc that called it ________.
finishes
calls
execute immediate
fields

9. Define a nested table type - tN - which is a table NUMBERs.
label
TYPE tN IS TABLE of NUMBER
database
CURSOR - RETURN

10. When a session starts - CURRENT_SCHEMA has the value of the schema owned by ___________.
SESSION_USER
DBMS_SESSION
-
run

11. %ROWTYPE gets the type of a table or _______.
a cursor
DBMS_CRYPTO
cursor
network

12. Cursors: If an exception is raised inside a cursor loop - the cursor (is / is not) also closed automatically.
transaction
is
for each row
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;

13. DynSQL: The three modes of arguments in the USING clause of EXECUTE IMMEDIATE.
selection
IN - OUT - IN OUT
r tRec := tRec(9 - 'Jones');
PL/SQL

14. Conversion: Just about everything will implicitly convert to _____ or _________.
CURRENT_USER - DEFINER
ALTER SESSION
SQL
CHAR - VARCHAR2

15. Cursors: The statement where you pass the parameters to a cursor.
bind
elements
OPEN
names

16. Types: One of the 4 PL/SQL things that have a data type (begins with 'v').
subtype
associative
UTL_SMTP
variable

17. Cursors: An explicit cursor (can/cannot) accept parameters.
can
/*
memory
EXIT WHEN

18. Dbms_sql: The return type DBMS_SQL.open_cursor.
cannot
integer
set serveroutput on
composite

19. Associative array types (can / cannot) be defined in the database.
IR
lock
cached
cannot

20. The DEFINITION of a cursor is the part after the keyword __.
IS
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
implicit
hardware

21. Cursors: The second keyword of a FETCH statement.
INTO
FETCH
delimiters
4

22. An editioning view can only select a subset of the columns from the base table and provide ________ for them
4
cached
aliases
GET_LINE

23. Exceptions: Declare an exception called E.
..
bb:true
constructor
E Exception;

24. A GOTO cannot branch into a...
PLSQL_OPTIMIZE_LEVEL
placeholders
lower sub-block
expression

25. Web: mod_plsql is an _______ module.
-20 -000
Apache
context
invalid

26. Cursors: You can just declare a cursor at first - then _______ it later.
define
CREATE TYPE
;
FOR UPDATE

27. The SET TRANSACTION statement affects only the ____________.
EDITION_NAME
current transaction
CURSOR - RETURN - IS
significant

28. PL/SQL is a ________ typed programming language.
DBMS_PIPE
elements - type
network
statically

29. 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

30. I/O: SQLPlus command for turning on buffered output.
set serveroutput on
ADT
bb:true
elsif

31. Define a collection type - tA - which is an associative array of NUMBERS - indexed with varchar2(20) values.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
plb
p.v
IF - CASE or LOOP

32. IDs: List the two types of user-defined identifiers.
error
Ordinary - Quoted.
null string
Keywords and predefined names.

33. IDs: Maximum number of bytes in a user-defined identifier.
COUNT
nested tables - associative arrays
scalar
30

34. I/O: The buffered output of dbms_output.put_line is retrieved by _______.
unqualified
RESULT_CACHE
SQLPlus
database

35. DynSQL: In EXECUTE IMMEDIATE - the statement string can use either placeholders - or string ________.
SIMPLE_INTEGER
REUSE SETTINGS
IN
concatenation

36. Triggers: INSTEAD OF triggers only run on ______ views.
=>
PUT
non-editioning views
scalable

37. The LIMIT method is only meaningful for _______.
PRAGMA AUTONOMOUS_TRANSACTION;
SQL
API
VARRAYs

38. The four keywords that define a PL/SQL block.
DECLARE - BEGIN - EXCEPTION - END
set serveroutput on
deletions
DBMS_OUTPUT

39. I/O: This DBMS_OUTPUT proc retrieves multiple lines from the buffer.
explicit cursor
DATE '2005-12-25'
GET_LINES
integer

40. I/O: If the current (outermost) PL/SQL block ends with an unhandled exception - the output buffer will not be ___________.
EXECUTE
fields
flushed
user-defined

41. Fundamental: identifiers (include/do not include) reserved words.
is certain
delimiters
object
include

42. In the special IF syntax - is it ELSIF - or ELSEIF?
machine code
PRAGMA EXCEPTION_INIT (E - -1);
elsif
bind

43. Where an external subprogram is stored.
in the database
r tRec := tRec(9 - 'Jones');
dense
RESULT_CACHE

44. With a result-cached function - it uses a cached value when the function is called again with the...
CHAR - VARCHAR2
single-line
;
same arguments

45. Command to assign to variables from a cursor.
L.V
comments
FETCH
an autonomous routine

46. Fundamental: What are the 3 compound symbols for 'not equal to'?
DBMS_SESSION
<> - != - ^=
VARRAY - Nested table
exception handler

47. Delimiters: Statement terminator.
cannot
;
Ordinary - Quoted.
an autonomous routine

48. PL/SQL does not automatically create bind variables in ________ SQL.
STANDARD
integer
DML
dynamic

49. Types: ______ data types are those storing values with no internal components.
predefined
ALTER SESSION
Ordinary - Quoted.
scalar

50. What clause makes a compilation default to the PLSQL_CCFLAGS settings from the last compilation?
REUSE SETTINGS
user-defined
GET_LINES
fetch C into V