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. Exceptions: A GOTO statement cannot branch into an _______ ________.
associative
exception handler
API
$END

2. Dbms_sql: A cursor opened with OPEN_CURSOR (can/cannot) be reused for multiple queries without first closing it.
SIMPLE_INTEGER
FETCH
EXIT WHEN
can

3. The Wrap utility (does / does not) encrypt source code.
cursor variable
does not
RETURN
not the same

4. Labels for blocks and statements are enclosed in '__' brackets.
<<>>
boolean
explicit
closed

5. Exceptions: PL/SQL exceptions are of two types: internal and _________.
user-defined
portable
VARCHAR2
EXIT WHEN

6. An anonymous block is a(n) ____ statement.
3
SQL
can
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

7. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
DATE
does not
savepoint ab
SUBTYPE n IS number not null;

8. Name the two types of FOR loop.
REF_CURSOR
literals
numeric and cursor
SYS_REFCURSOR

9. I/O: This DBMS_OUTPUT proc retrieves one line from the buffer.
<> - != - ^=
DATE '2005-12-25'
GET_LINE
WHERE CURRENT OF

10. Cursors: What implicit cursor attribute gives row counts for a FORALL statement?
STANDARD
%BULK_ROWCOUNT
not the same
bind

11. Bulk: The use of FORALL statements cuts down on ______ traffic.
DML
network
for each row
SYS_REFCURSOR

12. Cursors: An implicit cursor is also called a/n ________ ________ -
..
SQL cursor
Wrap
ALTER SESSION

13. The allowed data types of user-defined conditional compilation directives are boolean and _______.
pls_integer
OPEN_CURSOR
EDITION_NAME
ALTER SESSION

14. Cursors: The SQL%BULK_ROWCOUNT attribute is an _________ -
RELIES_ON
END CASE
executable
array

15. Mode designation for an output parameter.
OUT
cannot
STANDARD
multi-row

16. Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
-20 -000
REF CURSOR
ADT
compiles

17. Give the statement to make a program autonomous.
can
PLS_INTEGER
currval and nextval
PRAGMA AUTONOMOUS_TRANSACTION;

18. In 11g you can directly reference _____________ in PL/SQL.
parameter
sequence pseudo-columns
4
bytecode

19. The PL/SQL virtual machine turns bytecode into system ______.
database
autonomous triggers
RECORDS
calls

20. GOTO branches to a ________.
CURSOR - RETURN - IS
1
label
cursor c;

21. You can use the BULK COLLECT INTO clause in a SELECT or a ______.
FETCH
rollback to ab
associative
alter procedure

22. The DEFINITION of a cursor is the _____ that returns the records.
return value
integer
delimiters
query

23. A subprogram created inside a block is a _________ subprogram.
EXCEPTION_INIT
procedure P AUTHID current_user;
nested
IS

24. Exceptions: An exception in the declarations section does not go to the __________ section.
cannot
after
exception
RELIES_ON

25. Triggers: An INSTEAD OF trigger (can/cannot) be conditional.
DATE
cannot
CREATE TRIGGER
elements - type

26. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
obfuscates
static
composite
USER_PLSQL_OBJECT_SETTINGS

27. What are the two sequence pseudo-columns?
currval and nextval
$IF
4
package

28. Types: One of the scalar data types specific to PL/SQL: B_______
type T is ref cursor
SQL
BINARY_INTEGER
an autonomous routine

29. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
-20 -000
<<>>
compiles
fetch cv into myvar

30. Command to create a procedure.
create procedure
explicit
1
bind arguments

31. A CASE expression ends with _____.
quoted
variable
END
1

32. The attribute used to get the type of a column or variable without knowing what it is.
%TYPE
1
SQL
CURSOR - RETURN - IS

33. Cursors: A cursor FOR loop can use an explicit or an _______ cursor.
implicit
an enclosing block
definer's
IN - OUT - IN OUT

34. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
not the same
SYS_REFCURSOR
never
TRUE - FALSE - NULL

35. Exceptions: User-defined exceptions must have _______.
names
index
explicit
explicit cursor

36. The FOR UPDATE OF clause references a (table / column).
implicit CASE_NOT_FOUND exception
column
END
dense

37. Advantage: Caching of programs and sharing among users lowers _______ requirements.
Abstract
memory
1
optional

38. Conversion: Implicit conversion can be (faster / slower) than explicit conversion.
STANDARD
slower
fail
server

39. Searchable CASE statement: Each WHEN has its own _______ condition.
select
boolean
True
system global area

40. Exceptions: The _________ package defines the PL/SQL environment.
concatenation
STANDARD
EXIT
server

41. Define a record type REC with number num and varchar2(10) name.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
SQL
CHAR - VARCHAR2
native dynamic

42. The BETWEEN operator is (inclusive / exclusive).
inclusive
can
memory
RETURN

43. Types: The PL/SQL type that is identical to PLS_INTEGER.
BINARY_INTEGER
OPEN
name and datatype
DBMS_OUTPUT

44. Cursors: A session cursor that is automatically created and managed by PL/SQL.
after insert on emps
USER_PROCEDURES
implicit cursor
run

45. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
view
explicit
colon
portable

46. Triggers: You (can/cannot) explicitly invoke a trigger.
in the database
..
cannot
..

47. Command to make a no-op (omit semicolon).
exception handler
DATE
is

48. Parameter that controls how much Oracle optimizes your PL/SQL code on compilation.
WHEN
source code
$$
PLSQL_OPTIMIZE_LEVEL

49. Pkgs: The _______ package gives access to such SQL things as ALTER SESSION.
query
FOR i IN REVERSE 1..10 LOOP
FGA
DBMS_SESSION

50. Term for a character literal with zero characters.
null string
error code
4K
NUMBER