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. Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
ALTER SESSION
names
:=
compile

2. The compiler directive used to select which code to compile.
$IF
manageable
'ENABLE:SEVERE' - 'ERROR:06002'
E Exception;

3. Cursors: If an exception is raised inside a cursor loop - the cursor (is / is not) also closed automatically.
PVM
is
expression
E Exception;

4. How to reference a variable V declared in a block labelled L: ____.
system global area
L.V
before insert or update on emps
RECORD OF ARRAYS

5. Triggers: You create a trigger with the _______ ________ statement.
order
UTL_HTTP
CREATE TRIGGER
DBMS_SESSION.RESET_PACKAGE

6. For an associative array - the FIRST method gets the (lowest / first-created) index.
RAISE
create procedure
Ada
lowest

7. In interpreted mode - PL/SQL code is run in a _______ _______.
virtual machine
RETURN
TOO_MANY_ROWS
bind

8. Pkgs: The _______ package gives access to such SQL things as ALTER SESSION.
DBMS_SESSION
DATE
DECLARE - BEGIN - EXCEPTION - END
do not

9. Subprograms are stored in __________ form.
executable
cannot
static boolean
object

10. DBMS_DDL.WRAP returns a string containing wrapped _____ _____.
source code
DML
run
RETURN

11. Assigning values to PL/SQL variables that appear in SQL statements is called _______.
4K
can
binding
nested

12. In a cursor loop - the clause added to an INSERT or UPDATE to point at the current record.
WHERE CURRENT OF
SESSION_USER
inclusive
512

13. This is a CASE (expression / statement): CASE num WHEN 1 THEN goto LABEL1 WHEN 2 THEN var := 2 END CASE;
-
enclosing
select
statement

14. Triggers: Unlike a stored procedure - you can enable and _____ a trigger.
parameter
512
disable
single-line

15. Language that PL/SQL is derived from.
boolean
RAISE
anonymous
Ada

16. Types: One of the 4 PL/SQL things that have a data type (begins with 'p').
parameter
NDS
library
RELIES_ON

17. Types: One of the scalar data types specific to PL/SQL: B_______
BINARY_INTEGER
Reserved words.
error
FETCH - BULK COLLECT INTO

18. A result-cached function caches its results in the _____ _____ ____ (SGA)
system global area
elements - type
unhandled
FETCH - BULK COLLECT INTO

19. Exceptions: The ________ function returns the error message associated with an error code.
index
SQLERRM
DBMS_OUTPUT
do not

20. To recompile a procedure - use the _____ ______ command.
LOOP - END LOOP
alter procedure
CURRENT_USER
EXIT

21. Bulk: The use of FORALL statements cuts down on ______ traffic.
dynamic
source code
flushed
network

22. I/O: The buffered output of dbms_output.put_line is retrieved by _______.
SQLPlus
# - $ - _
FALSE
SESSION_USER

23. Loops: What is the index range operator in a FOR loop?
FETCH - INTO
FETCH - BULK COLLECT INTO
..
collections

24. Exceptions: Type ______ to re-raise the current exception.
RAISE
HTP
FORALL i IN MyList.FIRST..MyList.LAST
END CASE

25. Kind of datatypes that support object-oriented programming in PL/SQL.
currval and nextval
Abstract
DATE
fail

26. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
compiles
elsif
PLS_INTEGER
PRAGMA EXCEPTION_INIT (E - -1);

27. Dbms_sql: A cursor opened with OPEN_CURSOR (can/cannot) be reused for multiple queries without first closing it.
explicit
can
finishes
REF CURSOR

28. Kind of comment not to put in a PL/SQL block to be processed dynamically by an Oracle Precompiler program.
single-line
$END
TYPE tV IS VARRAY(100) OF NUMBER
always

29. A record is a group of related data items stored in fields - each with its own...
network
SQL cursor
name and datatype
cannot

30. Conversion: It is best to use (implicit / explicit) conversion.
explicit
production
error code
savepoint ab

31. What is the simplest statement for conditionally getting out of simple loop?
EXIT WHEN
...
query
concatenation

32. DynSQL: In EXECUTE IMMEDIATE - the statement string can use either placeholders - or string ________.
END CASE
calls
concatenation
$IF $THEN $ELSE $END

33. A PL/SQL block that is not stored.
anonymous block
optional
ADT
CURRENT_USER

34. The _____ and ____ _____ collection types can be used in database table columns.
label
WHERE CURRENT OF
VARRAY - Nested table
EXECUTE IMMEDIATE

35. Cursors: The first two keywords in a Cursor FOR loop.
order
bytecode
Wrap
FOR - IN

36. Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
REF_CURSOR
bind
names
%ISOPEN.

37. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
server
flushed
SQLERRM
alter procedure

38. A FOR-loop counter is defined only within the _____.
cached
in the database
'
loop

39. Delimiters: Item separator.

names
procedure - function
DBMS_OUTPUT

40. I/O: The most basic PL/SQL way to give output to the user.
follows
dbms_output.put_line
RESULT_CACHE
VARRAYs

41. Characters that begin a single-line comment.
%BULK_ROWCOUNT
for update of
-
loop

42. Web: mod_plsql is an implementation of the PL/SQL _________.
open C
machine
IN - OUT - IN OUT
Gateway

43. DynSQL: The three modes of arguments in the USING clause of EXECUTE IMMEDIATE.
current transaction
bind arguments
IN - OUT - IN OUT
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));

44. The EXTEND method is not used with ______ _______.
ordinary
bind
associative arrays
composite

45. Fundamental: A label can only go directly before an _______ statement.
Web Toolkit
flushed
executable
versions

46. Searchable CASE statement: Each WHEN has its own _______ condition.
versions
numeric and cursor
selection
boolean

47. The FOR UPDATE OF clause references a (table / column).
column
savepoint ab
PUT_LINE
PSP

48. User-defined compiler directives are defined in this system compile parameter.
database
closed
PLSQL_CCFLAGS
NDS

49. A pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
cursor
v2 IN NUMBER default 20
REF CURSOR
propagates

50. Fundamental: One of the 4 types of lexical units: L_____
SQLCODE
EXIT
literals
UTL_FILE