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. Dbms_sql: This DBMS_SQL function opens a new cursor.
EXECUTE IMMEDIATE
OPEN_CURSOR
VARCHAR2
RETURN

2. Exceptions: User-defined exceptions must have _______.
names
alter procedure
boolean
reference

3. Triggers: You (can/cannot) explicitly invoke a trigger.
cannot
GET_LINES
collections
fetch C into V

4. Cursors: The first two keywords of a basic fetch statement.
UTL_SMTP
1
type T is ref cursor
FETCH - INTO

5. Type: An ________ subtype has all the values of its base type.
FETCH - BULK COLLECT INTO
disable
unconstrained
enabled

6. Searchable CASE statement: Each WHEN has its own _______ condition.
3
composite
boolean
upper - coalesce

7. The variable showing the user id that is automatically prepended to object names.
nested
CURRENT_USER
anonymous block
name and datatype

8. A (simple / searched) CASE statement begins with CASE <expression>.
subtype
..
ex := tbl1 MULTISET EXCEPT tbl2
simple

9. Triggers: The clause to make a trigger run before inserting or updating table EMPS.
buffer
before insert or update on emps
IN OUT
inquiry

10. Triggers: You create a trigger with the _______ ________ statement.
1
block
CREATE TRIGGER
searched

11. Package for getting the postprocessed source of a program.
DBMS_PREPROCESSOR
FGA
explicit
'

12. $$PLSQL_LINE and $$PLSQL_UNIT are ______-ly defined inquiry directives.
nested tables - associative arrays
$IF $THEN $ELSE $END
implicit
significant

13. Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE - CORR - UPPER - SUM - COALESCE - DEREF.
CURSOR - RETURN
INTO
quoted
upper - coalesce

14. Cursors: The statement where you pass the parameters to a cursor.
implicit
EXTEND
OPEN
Abstract

15. What is the simplest statement for getting out of simple loop?
EXIT
an autonomous routine
Reserved words.
code

16. How to reference a variable V declared in a block labelled L: ____.
L.V
deletions
placeholders
preprocessor

17. I/O: The DBMS_OUTPUT function that writes output without a newline character.
end
USER_PLSQL_OBJECT_SETTINGS
PUT
dense

18. An editioning view can only select a subset of the columns from the base table and provide ________ for them
a cursor
current transaction
aliases
CHAR - VARCHAR2

19. The four keywords that define a PL/SQL block.
reference
always
OPEN_CURSOR
DECLARE - BEGIN - EXCEPTION - END

20. Characters that end a multi-line comment.
current transaction
*/
execute
SQLPlus

21. I/O: This package does email.
PUT_LINE
UTL_SMTP
implicit
SESSION_USER

22. I/O: This package lets PL/SQL programs read and write OS files.
CURSOR - RETURN - IS
elsif
UTL_FILE
cursor

23. The lowest index of a VARRAY is ___.
1
TYPE tN IS TABLE of NUMBER
current
invoker's

24. Triggers: There are __ types of triggers.
inquiry
5
autonomous triggers
searched

25. Fundamental: What is the exponentiation operator?
fine-grained auditing
/*
**
:=

26. Collections are one of the two kinds of PL/SQL _______ data types.
VARRAYs
SQL
FGA
composite

27. With dynamic typing - type checking is done at ______time.
run
simple
oname
reference

28. The command used to populate PLSQL_CCFLAGS.
lower sub-block
SYS_REFCURSOR
unqualified
ALTER SESSION

29. Cursors: Give the statement to fetch data from cursor C into variable V.
cursor
network
fetch C into V
NESTED TABLE myColl STORE AS myCollTab

30. When a session starts - CURRENT_SCHEMA has the value of the schema owned by ___________.
include
TRUE - FALSE - NULL
SQLERRM
SESSION_USER

31. The RESULT_CACHE clause comes (before / after) the RETURN clause.
transaction
Web Toolkit
after
lower sub-block

32. Fundamental: Labels furnish targets for GOTO and _____ statements.
nested
EXIT
UTL_SMTP
for update of

33. The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
definition
savepoint ab
cached
cannot

34. Kind of comment not to put in a PL/SQL block to be processed dynamically by an Oracle Precompiler program.
IF - CASE or LOOP
single-line
explicit
USER_PROCEDURES

35. Cursors: The value of SQL%ISOPEN is always ______.
currval and nextval
PSP
DECLARE - BEGIN - EXCEPTION - END
FALSE

36. SIMPLE_INTEGER is like PLS_INTEGER - but it does not allow ____ values - and it does not raise overflow exceptions.
ALTER SESSION
definer's
4

37. Triggers: By default - a trigger is created in the ______ state.
Server Pages
explicit cursor
package
enabled

38. The statement that is used to execute most (native) dynamic SQL.
execute immediate
in the database
currval and nextval
compilation

39. The best candidates for result-caching are functions that are invoked frequently but depend on __________________ or never.
5
GET_LINE
LOOP - END LOOP
information that changes infrequently

40. The two kinds of CASE statements are simple and ________.
database
searched
ordinary
enclosing

41. (from inside a package) Declare a function f - that takes a number n - returns a number - and caches results - depending on table EMPS.
composite
definition
function f (n number) return number result_cache relies_on (emps);
ex := tbl1 MULTISET EXCEPT tbl2

42. With static typing - type checking is done at ______ time.
SESSION_USER
compilation
compile
INSTEAD OF

43. IDs: Predefined identifiers are declared in this package.
NDS
USING
STANDARD
END LOOP

44. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
composite
invoker's
SESSION_USER
DATE

45. The SET TRANSACTION statement affects only the ____________.
current transaction
SIMPLE_INTEGER
integer
fetch C into V

46. Assignment of PLSQL_CCFLAGS (cannot / can) be included in ALTER PROCEDURE ... COMPILE ...
True - false or NULL.
Apache
can
elements

47. The method to add one or more elements to a nested table or VARRAY is ______.
INTO
RETURN
colon
EXTEND

48. In a collection - the internal components always have the _____ data type.
optional
cannot
OUT
same

49. For a collection - the NEXT method returns ____ when there is no next index value.

mandatory
IF - CASE or LOOP
REF_CURSOR

50. Java - C or other program called directly in PL/SQL.
cursor c RETURN rC;
statement
INTO
external subprogram