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. Triggers: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
session cursor
follows
DATE '2005-12-25'
an autonomous routine

2. Cursors: What implicit cursor attribute gives row counts for a FORALL statement?
wrap
VARRAY - Nested table
fetch cv into myvar
%BULK_ROWCOUNT

3. The _______ package lets you wrap dynamically generated PL/SQL.
DBMS_DDL
set serveroutput on
WHERE CURRENT OF
end

4. Declare a package procedure P with no arguments - and give it definer's rights.
procedure P AUTHID definer;
cannot
rollback to ab
machine code

5. _____ arrays cannot be used as the type of an Oracle table column.
associative
PLSQL_OPTIMIZE_LEVEL
..
CASE_NOT_FOUND

6. Conversion: If the specific value is suitable - ____ or _______ values can convert to almost any datatype
collections - records
CHAR - VARCHAR2
reference
cursor c;

7. Java - C or other program called directly in PL/SQL.
external subprogram
end
:=
multi-row

8. Cursors: Give the statement for getting out of a FETCH loop for cursor CV.
alter procedure
FOR UPDATE
server
EXIT WHEN cv%NOTFOUND

9. What do you FETCH from?
base
PRAGMA EXCEPTION_INIT (E - -1);
does not
a cursor

10. The 4 keywords in the $IF compiler directive.
memory
increment
$IF $THEN $ELSE $END
does not

11. Command used to execute dynamic SQL.
cannot
VARCHAR2
multi-row
EXECUTE IMMEDIATE

12. Fundamental: One of the 4 types of lexical units: c_____.
comments
virtual machine
OTHERS
nested

13. A national character literal is composed of characters in the ________ character set.
1
national
placeholders
significant

14. The FORALL statement to loop over a collection MyList using index i.
EVERE - PERFORMANCE - INFORMATIONAL
decimal
FORALL i IN MyList.FIRST..MyList.LAST
preprocessor

15. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
RECORD OF ARRAYS
upper - coalesce
forward
select

16. I/O: If the current (outermost) PL/SQL block ends with an unhandled exception - the output buffer will not be ___________.
inquiry
flushed
SESSION_USER
IN

17. I/O: This package has hypertext procedures that generate HTML tags
significant
after
HTP
%ISOPEN.

18. Delimiters: The left label delimiter.
<<
GET_LINE
pseudocolumn
Declaration

19. A GOTO cannot branch into a...
iname
procedure - function
anonymous
lower sub-block

20. Fundamental: What is the range operator?
closed
COUNT
..
1

21. A user-defined TYPE can have...
searched
vname CONSTANT VARCHAR2 := 'x';
1
an AUTHID property

22. Associative array types (can / cannot) be defined in the database.
FETCH
compilation
CASE_NOT_FOUND
cannot

23. I/O: The DBMS_OUTPUT procedure that sets the size of the output buffer.
execute immediate
procedure P AUTHID current_user;
ENABLE
forward

24. Advantage: Performance: Storing and runnng procedures on the server minimizes ______ traffic.
can
network
is not
hardware

25. IDs: Predefined identifiers are declared in this package.
index
STANDARD
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
UTL_HTTP

26. In the special IF syntax - is it ELSIF - or ELSEIF?
elsif
propagates
rollback to ab
1

27. Collections are one of the two kinds of PL/SQL _______ data types.
system global area
composite
executable
FORALL i IN MyList.FIRST..MyList.LAST

28. A type of compiler directive - begins with 'e'.
INSTEAD OF
error
column
1

29. The PL/SQL virtual machine turns bytecode into system ______.
PUT
rollback to ab
DBMS_SESSION.RESET_PACKAGE
calls

30. I/O: Max number of bytes DBMS_OUTPUT can handle in a session.
manageable
REF CURSOR
1 million
Oracle

31. Advantage: PL/SQL is tightly _______ with Oracle SQL.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
integrated
cached
executable

32. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
EXECUTE IMMEDIATE
OPEN
DML
associative

33. Exceptions: Each internal exception has an Oracle ______ _______.
constrained
error code
expression
BINARY_INTEGER

34. You (can / cannot) wrap trigger source code.
DATE '2005-12-25'
SQL
SQLCODE
cannot

35. Delimiters: the attribute indicator.
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
after
%
implicit

36. Exceptions: Start of the range of error codes for RAISE_APPLICATION_ERROR.
timing
external subprogram
-20 -000
PUT

37. In a collection - the internal components always have the _____ data type.
DBMS_DDL
END CASE
decimal
same

38. INSTEAD OF triggers are alternatives to ___ triggers
STANDARD
parameter
reference
DML

39. A type of compiler directive - begins with 'i'.
before insert or update on emps
inquiry
WHEN OTHERS THEN
$IF $THEN $ELSE $END

40. To run a PIPE ROW statement in your autonomous routine - you must close any _____________.
EXIT WHEN
PLSQL_CCFLAGS
autonomous transaction
closed

41. Delimiters: The remote access symbol.
cursor number
exception handler
@
name

42. An autonomous program runs in its own __________.
transaction
bytecode
E Exception;
OPEN_CURSOR

43. The compiler directive used to select which code to compile.
-
SQL
$IF
RETURN

44. This is a CASE (expression / statement): CASE num WHEN 1 THEN goto LABEL1 WHEN 2 THEN var := 2 END CASE;
a number := 10
statement
delimiters
RECORDS

45. A PL/SQL block that is not stored.
preprocessor
*/
anonymous block
after insert on emps

46. Triggers: In a per-row trigger - the ____ record shows you the original state of an affected row.
literals
compiles
unhandled
OLD

47. Types: ________ data types have internal components.
source code
ALTER SESSION
composite
oname

48. Cursors: A session cursor that a PL/SQL user creates and manages.
..
explicit cursor
DBMS_SESSION.RESET_PACKAGE
elsif

49. When an autonomous routine begins execution - the main transaction is ________.
RECORD OF ARRAYS
associative
REUSE SETTINGS
suspended

50. By default - an IN parameter is passed by ______.
follows
compile
reference
integer