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.
can
flushed
v3 IN OUT NUMBER
composite

2. A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
lock
RECORDS
autonomous triggers
collections - records

3. Triggers: The code in a trigger is actually an ________ block.
anonymous
quoted
END CASE
%

4. Command to assign to variables from a cursor.
DBMS_PIPE
FETCH
constant
virtual machine

5. Define a collection type - tV - which is a varray of 100 NUMBERs.
TYPE tV IS VARRAY(100) OF NUMBER
simple
base
Web Toolkit

6. The source for wrapping begins with the word ______.
r tRec := tRec(9 - 'Jones');
associative
**
create

7. Cursors: Give the statement to fetch data from cursor variable CV into MyVar.
user-defined
With a letter.
fetch cv into myvar
EXIT

8. The FIRST method gets the first (element / index) of a collection.
CLOSE_CURSOR
view
define
index

9. DynSQL: Maximum string length to pass to EXECUTE IMMEDIATE.
aliases
SUBTYPE numb IS number;
32 -000
$END

10. Types: One of the scalar data types specific to PL/SQL: P________
exception handler
PLS_INTEGER
significant
SIMPLE_INTEGER

11. An anonymous block always has _________ rights.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

12. Cursors: Opening keyword of a cursor FETCH loop.
FALSE
LOOP
define
UTL_SMTP

13. Exceptions: The built-in procedure for raising an application-defined error.
raise_application_error
index
PUT
.

14. I/O: This package has hypertext procedures that generate HTML tags
ROLLBACK
HTP
vname CONSTANT VARCHAR2 := 'x';
execute immediate

15. Triggers: By default - a trigger is created in the ______ state.
explicit
execute
5
enabled

16. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
open C
production
OTHERS
for each row

17. An autonomous program runs in its own __________.
follows
BOOLEAN
cannot
transaction

18. Cursors: A session cursor that is automatically created and managed by PL/SQL.
implicit cursor
ALTER SESSION
statically
finishes

19. Which two collection types are unbounded?
enabled
EXIT
OTHERS
nested tables - associative arrays

20. To use a TYPE declared in the database - a PL/SQL block has to have _______ privilege on the type.
select
TYPE tV IS VARRAY(100) OF NUMBER
dynamic
anonymous block

21. User-defined compiler directives are defined in this system compile parameter.
specification - body
timing
follows
PLSQL_CCFLAGS

22. The 4 keywords in the $IF compiler directive.
is certain
preprocessor
cannot
$IF $THEN $ELSE $END

23. IDs: You can use a reserved word as a _______ user-defined identifier.
open cv for
EDITION_NAME
is certain
quoted

24. Types: The predefined subtype of PLS_INTEGER that has the NOT NULL constraint.
EXIT WHEN
composite
SIMPLE_INTEGER
FALSE

25. Exceptions: If there is no handler for an exception - PL/SQL returns an ________ exception error to the invoker or host environment.
is
unhandled
r tRec := tRec(9 - 'Jones');
%BULK_ROWCOUNT

26. With conditional compilation you can avoid compiling debug code in the _______ environment.
plb
current transaction
production
include

27. Default file extension of wrap's input file.
REF CURSOR
Gateway
sql
1

28. Define a collection type - tA - which is an associative array of NUMBERS - indexed with varchar2(20) values.
API
cannot
definer's
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)

29. An INSTEAD OF trigger is used - e.g - when you want to insert into a complex _____.
type T is ref cursor
view
inquiry
can

30. A user-defined TYPE can have...
an AUTHID property
a number := 10
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
3

31. Give the statement to make a program autonomous.
binding
network
cursor c;
PRAGMA AUTONOMOUS_TRANSACTION;

32. Cursors: Give the statement to open cursor C.
RECORDS
library
implicit
open C

33. Exceptions: PL/SQL exceptions are of two types: internal and _________.
code
user-defined
PLS_INTEGER
cursor variable

34. What do you FETCH from?
stored
a cursor
user-defined
=>

35. A value that is neither represented by an identifier nor calculated from other values.
procedure - function
literal
Wrap
SQL

36. Cursors: The first two keywords of a basic fetch statement.
FETCH - INTO
invoker's
FORALL and BULK COLLECT
REF_CURSOR

37. Where an external subprogram is stored.
in the database
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
rollback to ab
SUBTYPE numb IS number;

38. Unlike the CASE statement - the CASE expression does not have the...
single-line
implicit CASE_NOT_FOUND exception
system global area
associative

39. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
32 -000
cannot
null string
compiles

40. Exceptions: The ________ handler must always be the last handler.
pls_integer
OTHERS
ex := tbl1 MULTISET EXCEPT tbl2
unqualified

41. Types: One of the 4 PL/SQL things that have a data type (begins with 'c').
BINARY_INTEGER
cached
END
constant

42. Triggers: In a DML trigger - the phrase that makes the trigger run for every affected record.
TOO_MANY_ROWS
for each row
SQLCODE
END CASE

43. Data dictionary view giving the AUTHID values on a user's objects.
PUT_LINE
USER_PROCEDURES
autonomous triggers
Web Toolkit

44. Advantage: Having procedures stored and executed on the server makes PL/SQL ________.
information that changes infrequently
query
cannot
scalable

45. Delimiters: The left label delimiter.
an AUTHID property
vname CONSTANT VARCHAR2 := 'x';
session cursor
<<

46. If an initialized collection has no elements - FIRST and LAST return _____.

WHEN OTHERS THEN
is
IF - CASE or LOOP

47. With static typing - type checking is done at ______ time.
create
sequence pseudo-columns
'ENABLE:SEVERE' - 'ERROR:06002'
compile

48. (from inside a package) Declare a function f - that takes a number n - returns a number - and caches results - depending on table EMPS.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
must
-
function f (n number) return number result_cache relies_on (emps);

49. The inquiry directive that gives the current line number in the procedure.
SESSION_USER
fail
$$PLSQL_LINE
PUT_LINE

50. The SET TRANSACTION statement affects only the ____________.
nested
once
current transaction
$$PLSQL_LINE