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. Delimiters: The remote access symbol.
@
NUMBER
-
in the database

2. The lowest index of a nested table is ___.
can
1
32 -000
%BULK_ROWCOUNT

3. An $ERROR directive ends with _____.
parameter
associative
unhandled
$END

4. A (simple / searched) CASE statement begins with CASE <expression>.
simple
after insert on emps
raise_application_error
1

5. Types: The storage size of a NUMBER value is based on the number of ______ digits.
PUT
in the database
bind
significant

6. User-defined compiler directives are defined in this system compile parameter.
SQLERRM
definer's
EXIT
PLSQL_CCFLAGS

7. Cursors: If no SELECT or DML statement has run - the value of SQL%ROWCOUNT is what?
parameter

package
%ISOPEN.

8. The NOCOPY option on an OUT or IN OUT parameter - causes it to be passed by ______.
LOOP
TRUE - FALSE and NULL
in the database
reference

9. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
integer
DML
buffer
DBMS_PIPE

10. Triggers: You (can/cannot) explicitly invoke a trigger.
error code
TYPE tV IS VARRAY(100) OF NUMBER
cannot
hardware

11. Cursors: A cursor that lives in session memory until the session ends.
an autonomous routine
source code
ALTER SESSION
session cursor

12. Cursors: The first two keywords of a basic fetch statement.
END
FETCH - INTO
<> - != - ^=
inquiry

13. With static typing - type checking is done at ______ time.
compile
EXIT
selection
include

14. Types: The only Oracle datetime type that doesn't begin with TIMESTAMP is ________.
WHERE CURRENT OF
RAISE
DATE
is

15. Exceptions: The keyword that begins an exception handler.
fine-grained auditing
ADT
WHEN
TYPE tV IS VARRAY(100) OF NUMBER

16. Triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
invoker's
current BEGIN-END block
fetch C into V
always

17. Cursors: How many implicit cursors can you access?
quoted
1
cannot
FORALL i IN MyList.FIRST..MyList.LAST

18. Types: One of the scalar data types specific to PL/SQL: R_________.
explicit
REF_CURSOR
always
TIMESTAMP.

19. The collection method that tells the number of elements in the collection.
END IF
integer
COUNT
DBMS_SESSION

20. Cursors: The SQL%FOUND attribute has how many possible values?
DML
3
NUMBER
procedure P AUTHID definer;

21. Triggers: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
does not
PRAGMA EXCEPTION_INIT (E - -1);
$IF $THEN $ELSE $END
follows

22. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
cursor c RETURN rC;
OTHERS
CURSOR - RETURN
sql

23. Into nested table ex - put elements of tbl1 that are not in tbl2.
object
server
ex := tbl1 MULTISET EXCEPT tbl2
<<

24. IDs: The type of words that cannot be user-defined identifiers.
INSTEAD OF
numeric and cursor
PL/SQL Gateway
Reserved words.

25. The valid values of the AUTHID property.
CURRENT_USER - DEFINER
simple
error code
Apache

26. Delimiters: The component indicator/selector.
view

.
constant

27. I/O: This package handles HTTP.
UTL_HTTP
fields
PLS_INTEGER
selection

28. An editioning view is like an ____ for a table.
disable
elements
API
stored

29. Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE - CORR - UPPER - SUM - COALESCE - DEREF.
upper - coalesce
STANDARD
cannot
END CASE

30. Wrapped code (is / is not) not backward compatible.
v3 IN OUT NUMBER
memory
is not
DR

31. A FOR-loop counter is defined only within the _____.
order
commit - rollback
loop
v1 OUT number default 10

32. DynSQL: In EXECUTE IMMEDIATE - the statement string can use either placeholders - or string ________.
concatenation
With a letter.
statement
system global area

33. Exceptions: The ________ function returns the current error code.
CHARARR
can
SQLCODE
PL/SQL

34. Where an external subprogram is stored.
cannot
savepoint ab
in the database
plb

35. Triggers: There are __ types of triggers.
EDITION_NAME
invalid
SQL
5

36. To recompile a procedure - use the _____ ______ command.
composite
cannot
quoted
alter procedure

37. What is the simplest statement for getting out of simple loop?
FORALL i IN MyList.FIRST..MyList.LAST
RESULT_CACHE
an AUTHID property
EXIT

38. Triggers: In a per-row trigger - the ____ record shows you the original state of an affected row.
%ISOPEN.
pls_integer
OLD
%BULK_ROWCOUNT

39. The exception raised when a SELECT INTO statement returns more than one row.
associative
sql
TOO_MANY_ROWS
null string

40. Pkgs: This procedure de-instantiates/resets all packages in this session.
optional
FALSE
can
DBMS_SESSION.RESET_PACKAGE

41. The BETWEEN operator is (inclusive / exclusive).
With a letter.
can
inclusive
ordinary

42. The SET TRANSACTION statement affects only the ____________.
OPEN
always
current transaction
BINARY_INTEGER

43. Nested tables can become sparse via ______.
cannot
deletions
PL/SQL
rollback to ab

44. Cursors: The values plugged into the DML in a FORALL must come from existing - populated _________.
same
collections
EXTEND
composite

45. Cursors: Give the statement for getting out of a FETCH loop for cursor CV.
/*
EXIT WHEN cv%NOTFOUND
definition
national

46. Cursors: An implicit cursor is also called a/n ________ ________ -
can
SQL cursor
a cursor
FGA

47. Declare a constrained subtype of NUMBER called N that is not null.
can
@
SESSION_USER
SUBTYPE n IS number not null;

48. Web: PL/SQL _____ _______ lets you to develop Web applications made of PL/SQL procedures.
Web Toolkit
expression
TRUE - FALSE - NULL
IN - OUT - IN OUT

49. Types: A data type family consists of a ______ type and its subtypes.
EXIT WHEN
base
Abstract
FOR UPDATE

50. Declare a package procedure P with no arguments - and give it invoker's rights.
slower
procedure P AUTHID current_user;
SQLERRM
fields