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 input/output NUMBER parameter - v3.
FOR - IN
SYS_REFCURSOR
exit when cgo%NOTFOUND
v3 IN OUT NUMBER

2. A null string and a BOOLEAN null are _______ (same/not the same).
not the same
4
HTP
procedure P AUTHID current_user;

3. Kind of datatypes that support object-oriented programming in PL/SQL.
multi-row
Abstract
name-value
exit when cgo%NOTFOUND

4. The meaning of 'FGA'.
fine-grained auditing
explicit cursor
name
E Exception;

5. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
current BEGIN-END block
procedure - function
30
will not

6. The 4 keywords in the $IF compiler directive.
RELIES_ON
USER_PROCEDURES
$IF $THEN $ELSE $END
1

7. In a sparse collection - the indexes of defined elements (do / do not) run in consecutive order.
do not
fail
REF CURSOR
context

8. Delimiters: Statement terminator.
TRUE - FALSE - NULL
;
bind
NUMBER

9. Advantage: Having procedures stored and executed on the server makes PL/SQL ________.
SQL
IN
scalable
STANDARD

10. What are the two sequence pseudo-columns?
:=
SESSION_USER
currval and nextval
RECORDS

11. The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
autonomous triggers
definition
statically
SUBTYPE numb IS number;

12. The EXCEPTION section is (optional/mandatory).
optional
flushed
ENABLE
function f (n number) return number result_cache relies_on (emps);

13. Loops: What is the index range operator in a FOR loop?
BOOLEAN
..
transaction
PRAGMA EXCEPTION_INIT (E - -1);

14. Fundamental: Maximum string length of a PL/SQL variable.
32 -000
will not
slower
savepoint ab

15. I/O: This package lets two or more sessions in the same instance communicate.
IN - OUT - IN OUT
implicit cursor
once
DBMS_PIPE

16. Make a FOR loop that executes NULL 10 times counting in reverse - using index i.
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
Keywords and predefined names.
Declaration
reference

17. Cursors: Give the statement to close cursor C.
$$PLSQL_LINE
ROLLBACK
quoted
close C

18. In interpreted mode - PL/SQL code is run in a _______ _______.
upper - coalesce
virtual machine
FORALL and BULK COLLECT
DR

19. Cursors: Only a _____ _____ cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement.
sequence pseudo-columns
EXECUTE
cursor
FOR UPDATE

20. I/O: Max number of bytes DBMS_OUTPUT can handle in a session.
an enclosing block
WHERE CURRENT OF
identifiers
1 million

21. The NOCOPY option on an OUT or IN OUT parameter - causes it to be passed by ______.
reference
INTO
associative arrays
open C

22. Cursors: The two keywords in a mere cursor declaration (one is optional).
LOOP - END LOOP
bind arguments
CURSOR - RETURN
FETCH

23. Type: An ________ subtype has all the values of its base type.
unconstrained
CURRENT_USER
raise_application_error
concatenation

24. Assignment of PLSQL_CCFLAGS (cannot / can) be included in ALTER PROCEDURE ... COMPILE ...
$$PLSQL_LINE
can
is not
expression

25. Declare a cursor c with no parameters - and return type rC.
sql
cursor c RETURN rC;
NESTED TABLE myColl STORE AS myCollTab
DBMS_SESSION.RESET_PACKAGE

26. The FORALL statement to loop over a collection MyList using index i.
select
FORALL i IN MyList.FIRST..MyList.LAST
implicit cursor
associative

27. Default file extension of wrap's input file.
fetch C into V
sql
OTHERS
PLS_INTEGER

28. With a result-cached function - it uses a cached value when the function is called again with the...
before insert or update on emps
same arguments
fine-grained auditing
DATE

29. You can define PL/SQL collections using TYPEs defined in the _______.
r t.%ROWTYPE;
exit when cgo%NOTFOUND
database
enclosing

30. In 11g you can directly reference _____________ in PL/SQL.
TRUE - FALSE - NULL
is certain
implicit cursor
sequence pseudo-columns

31. Types of blocks: A pl/sql block that is not stored in the database.
EVERE - PERFORMANCE - INFORMATIONAL
virtual machine
anonymous
:=

32. Wrap iname=xxxx _____=zzzzz
FETCH - BULK COLLECT INTO
IS
RETURN
oname

33. You can have many autonomous transactions in ___________.
an autonomous routine
can
wrap
iname

34. Types: A ______ has the same valid operations as its base type.
subtype
once
255
colon

35. Exceptions: User-defined exceptions must have _______.
RELIES_ON
source code
names
definer's

36. Where an external subprogram is stored.
CURRENT OF
package
in the database
null string

37. Types: ________ data types have internal components.
injection
<<
composite
autonomous transaction

38. Exceptions: A GOTO statement cannot branch into an _______ ________.
national
implicit CASE_NOT_FOUND exception
GET_LINE
exception handler

39. Advantage: PL/SQL is PORTABLE to any OS where ________ runs.
transaction
current BEGIN-END block
Oracle
STANDARD

40. I/O: The buffered output of dbms_output.put_line is retrieved by _______.
procedure P AUTHID definer;
SQLPlus
cannot
REF CURSOR

41. Exceptions: An unhandled exception ________ to the enclosing block.
SQL cursor
query
function f (n number) return number result_cache relies_on (emps);
propagates

42. Cursors: Define a ref cursor type called T.
bytecode
preprocessor
type T is ref cursor
DATE '2005-12-25'

43. Data dictionary view giving the AUTHID values on a user's objects.
$$PLSQL_LINE
USER_PROCEDURES
EXIT
%BULK_ROWCOUNT

44. SIMPLE_INTEGER is like PLS_INTEGER - but it does not allow ____ values - and it does not raise overflow exceptions.
PUT
SYS_CONTEXT

NDS

45. Cursors: A cursor that lives in session memory until the session ends.
buffer
EXIT WHEN cv%NOTFOUND
cursor number
session cursor

46. Cursors: The Oracle predefined REF CURSOR type.
PVM
Keywords and predefined names.
database
SYS_REFCURSOR

47. A GOTO statement can branch from an exception handler into...
$END
session cursor
an enclosing block
database

48. I/O: The DBMS_OUTPUT function that writes output WITH a newline character.
PUT_LINE
current BEGIN-END block
variable
NUMBER

49. Kind of code created by the PL/SQL compiler.
bytecode
-
colon
COUNT

50. List the transaction control statements.
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
INSTEAD OF
transaction
PLS_INTEGER