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. An editioning view is like an ____ for a table.
DECLARE - BEGIN - EXCEPTION - END
API
explicit cursor
not a number

2. The compiler directive governing conditional compilation.
$IF
source code
current BEGIN-END block
%BULK_ROWCOUNT

3. A record is a group of related data items stored in fields - each with its own...
OPEN
Keywords and predefined names.
open C(p)
name and datatype

4. The default value of the AUTHID property.
RECORD OF ARRAYS
memory
DEFINER
executable

5. Fundamental: Labels furnish targets for GOTO and _____ statements.
EXIT
2
mandatory
@

6. Cursors: WHERE _______ ______ makes an UPDATE or DELETE apply to the current row of a cursor.
commit - rollback
CURRENT OF
view
Ordinary - Quoted.

7. Exceptions: Declare an exception called E.
E Exception;
STANDARD
drop procedure
ex := tbl1 MULTISET EXCEPT tbl2

8. The RESULT_CACHE clause comes (before / after) the RETURN clause.
hardware
source code
after
associative arrays

9. To get the current value of CURRENT_SCHEMA - CURRENT_USER - or SESSION_USER - use the ____ function.
DBMS_PIPE
collections
r t.%ROWTYPE;
SYS_CONTEXT

10. Types: The predefined subtype of PLS_INTEGER that has the NOT NULL constraint.
database
SIMPLE_INTEGER
cannot
REF_CURSOR

11. DBMS_DDL.WRAP returns a string containing wrapped _____ _____.
DML
source code
LOOP
DBMS_OUTPUT

12. Wrapping can be done with the ____ executable.
query
ENABLE
wrap
index

13. An incomplete type is a type created by a ______ type definition.
suspended
forward
cursor
for update of

14. Conversion: Just about everything will implicitly convert to _____ or _________.
literals
;
name and datatype
CHAR - VARCHAR2

15. Exceptions: Associate exception E with error code -1.
PRAGMA EXCEPTION_INIT (E - -1);
simple and compound symbols
*/
DBMS_CRYPTO

16. Cursors: The value of SQL%ISOPEN is always ______.
names
FALSE
$IF
TOO_MANY_ROWS

17. To use a TYPE declared in the database - a PL/SQL block has to have _______ privilege on the type.
select
FETCH - INTO
IF - CASE or LOOP
cannot

18. PL/SQL is a ________ typed programming language.
statically
implicit cursor
DBMS_PREPROCESSOR
...

19. The $IF must evaluate a ______ _______ expression.
executable
after insert on emps
literal
static boolean

20. Declare an input/output NUMBER parameter - v3.
information that changes infrequently
PUT
RETURN
v3 IN OUT NUMBER

21. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
OPEN
specification - body
DEFINER
cannot

22. Associative array types (can / cannot) be defined in the database.
after
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
cannot
DBMS_SESSION

23. IDs: You can use a reserved word as a _______ user-defined identifier.
system global area
SQL
quoted
=

24. TCL: Undo changes back to savepoint ab.
bytecode
rollback to ab
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
binding

25. Cursors: A Cursor FOR loop does not use a ______ statement.
FETCH
PRAGMA AUTONOMOUS_TRANSACTION;
SQL
$IF

26. Value of a character literal with zero characters.

TYPE tV IS VARRAY(100) OF NUMBER
cursor
name-value

27. Triggers: In a DML trigger - the phrase that makes the trigger run for every affected record.
cursor c RETURN rC;
database
for each row
END CASE

28. How many times are a FOR loop's index bounds evaluated?
OUT
numeric and cursor
once
package

29. Types: A data type family consists of a ______ type and its subtypes.
preprocessor
RECORD OF ARRAYS
base
fine-grained auditing

30. Cursors: Which cursor attribute is not meaningful for an implicit cursor?
%ISOPEN.
compiles
definition
execute immediate

31. To recompile a procedure - use the _____ ______ command.
fields
bytecode
alter procedure
static

32. Types: One of the 4 PL/SQL things that have a data type (begins with 'v').
PLS_INTEGER
column
aliases
variable

33. Triggers: Programmers (as opposed to DBAs) mostly make use of _____ triggers.
DML
NESTED TABLE myColl STORE AS myCollTab
inquiry
network

34. A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
%TYPE
PLSQL_OPTIMIZE_LEVEL
FGA
lock

35. Define a record type REC with number num and varchar2(10) name.
simple and compound symbols
CURRENT_USER
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
30

36. What are two categories of SQL functions that cannot be used in procedural statements?
aggregate - analytical
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
NESTED TABLE myColl STORE AS myCollTab
3

37. An INSTEAD OF trigger is used - e.g - when you want to insert into a complex _____.
not the same
GET_LINE
view
anonymous

38. The $ERROR directive is used to make compilation ____.
implicit CASE_NOT_FOUND exception
Server Pages
fail
explicit

39. When an autonomous routine begins execution - the main transaction is ________.
DBMS_PREPROCESSOR
PSP
suspended
END LOOP

40. Declare an input NUMBER parameter - v2 - that defaults to 20.
v2 IN NUMBER default 20
PL/SQL Gateway
EXCEPTION_INIT
DML

41. Types: ______ data types are those storing values with no internal components.
slower
scalar
SQL
dynamic

42. Triggers: By default - a trigger is created in the ______ state.
anonymous
enabled
suspended
strong

43. The allowed data types of user-defined conditional compilation directives are boolean and _______.
virtual machine
is not
@
pls_integer

44. Kind of comment not to put in a PL/SQL block to be processed dynamically by an Oracle Precompiler program.
single-line
dbms_output.put_line
STANDARD
composite

45. Types of blocks: Procedures and functions are _______ blocks.
nested
stored
$IF $THEN $ELSE $END
EXECUTE IMMEDIATE

46. Declare a constrained subtype of NUMBER called N that is not null.
autonomous triggers
SQL cursor
1
SUBTYPE n IS number not null;

47. Pkgs: This procedure de-instantiates/resets all packages in this session.
DBMS_SESSION.RESET_PACKAGE
Double quotes.
selection
pls_integer

48. The source for wrapping begins with the word ______.
CHARARR
create
concatenation
context

49. Triggers: On a non-editioning view - you can only define __________ triggers.
*/
INSTEAD OF
DR
v3 IN OUT NUMBER

50. Cursors: The first two keywords in a Cursor FOR loop.
cannot
FOR - IN
1
rollback to ab