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. PLSQL_CCFLAGS hold a comma-separated list of ______ pairs (hyphenated).
scalar
commit - rollback
name-value
ROLLBACK

2. An incomplete type is a type created by a ______ type definition.
forward
bb:true
nested tables - associative arrays
Keywords and predefined names.

3. Fundamental: Maximum string length of a PL/SQL variable.
drop procedure
1 million
definer's
32 -000

4. An anonymous block is a(n) ____ statement.
bind
elements - type
SQL
wrap

5. Triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
anonymous
v3 IN OUT NUMBER
always
constant

6. Characters that begin a single-line comment.
open C(p)
-
True - false or NULL.
ADT

7. A nested table has to be initialized using a ________ - named the same as the type.
source code
DBMS_OUTPUT
constructor
quoted

8. A record is a group of related data items stored in fields - each with its own...
name and datatype
dynamic
RECORDS
collections

9. Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
ALTER SESSION
Keywords and predefined names.
EVERE - PERFORMANCE - INFORMATIONAL
loop

10. TCL: Create a savepoint called ab
512
ALTER SESSION
v3 IN OUT NUMBER
savepoint ab

11. Triggers: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
elsif
follows
END
Web Toolkit

12. Declare an output NUMBER parameter - v1 - that defaults to 10.
loop
v1 OUT number default 10
PVM
searched

13. The DEFINITION of a cursor is the part after the keyword __.
end
integer
TYPE tV IS VARRAY(100) OF NUMBER
IS

14. Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
EVERE - PERFORMANCE - INFORMATIONAL
Reserved words.
REF CURSOR
2

15. I/O: The most basic PL/SQL way to give output to the user.
fetch cv into myvar
EXCEPTION_INIT
quoted
dbms_output.put_line

16. Types: One of the 4 PL/SQL things that have a data type (begins with 'v').
integer
decimal
RECORDS
variable

17. The declaration section is (optional/mandatory).
composite
optional
information that changes infrequently
32 -000

18. Delimiters: Host variable indicator.
...
SQL
<> - != - ^=
network

19. Exceptions: Declare an exception called E.
definition
suspended
information that changes infrequently
E Exception;

20. DBMS_DDL.WRAP returns a string containing wrapped _____ _____.
bind
USER_PROCEDURES
END CASE
source code

21. PLS_INTEGER has its arithmetic implemented in ________ - so it is very fast.
$$
hardware
UTL_FILE
NUMBER

22. Cursors: Which cursor attribute is not meaningful for an implicit cursor?
%ISOPEN.
anonymous block
composite
mandatory

23. What are two categories of SQL functions that cannot be used in procedural statements?
source code
EXIT WHEN
%BULK_ROWCOUNT
aggregate - analytical

24. Keyword for doing assignment to a variable with SELECT.
REUSE SETTINGS
binding
INTO
bb:true

25. Command to delete a procedure.
INTO
drop procedure
bind
RAISE

26. A CASE statement ends with ____ ____.
implicit
DATE
END CASE
collections

27. Cursors: Define a ref cursor type called T.
type T is ref cursor
database
DBMS_SESSION.RESET_PACKAGE
integer

28. Associative array types (can / cannot) be defined in the database.
cannot
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
CURRENT_USER - DEFINER
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;

29. The collection method that tells the number of elements in the collection.
RETURN
base
**
COUNT

30. A PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE
cannot
..
reference
WHERE CURRENT OF

31. Define a record type REC with number num and varchar2(10) name.
machine
cursor number
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
context

32. Mode designation for an input parameter.
Gateway
BINARY_INTEGER
will not
IN

33. True encryption is done with package __________.
invoker's
closed
query
DBMS_CRYPTO

34. Cursors: A cursor with a name is an _________ cursor.
SIMPLE_INTEGER
increment
explicit
virtual machine

35. A subprogram defined within another subprogram is a _______ subprogram.
currval and nextval
nested
cannot
context

36. Triggers: INSTEAD OF triggers only run on ______ views.
compilation
non-editioning views
'ENABLE:SEVERE' - 'ERROR:06002'
IF - CASE or LOOP

37. You (can / cannot) wrap trigger source code.
WHEN OTHERS THEN
OPEN_CURSOR
Oracle
cannot

38. List the transaction control statements.
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
RELIES_ON
forward
user-defined

39. What are the starting and ending keywords of a simple loop?
in the database
calls
<<>>
LOOP - END LOOP

40. Default file extension of wrap's input file.
sql
RELIES_ON
DBMS_PIPE
expression

41. GOTO branches to a ________.
3
$END
varray
label

42. Web: Abbreviation of PL/SQL Server Page.
DML
PSP
DBMS_CRYPTO
E Exception;

43. A type of compiler directive - begins with 's'.
OPEN_CURSOR
after insert on emps
With a letter.
selection

44. Exceptions: A GOTO statement CAN branch from an exception handler into an ________ block.
enclosing
=>
SYS_CONTEXT
set serveroutput on

45. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
END CASE
comments
TRUE - FALSE - NULL
after insert on emps

46. In a collection - the internal components are called _______.
autonomous transaction
does not
elements
closed

47. Cursors: A cursor that lives in session memory until the session ends.
L.V
'ENABLE:SEVERE' - 'ERROR:06002'
session cursor
BINARY_INTEGER

48. The default value of the AUTHID property.
DEFINER
slower
Ada
implicit CASE_NOT_FOUND exception

49. IDs: Predefined identifiers are declared in this package.
STANDARD
INTO
OTHERS
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT

50. For a collection - the NEXT method gets the next higher (element / index).
fields
colon
UTL_HTTP
index