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: The clause to make a trigger run once for every affected row.
nested
index
inquiry
for each row

2. User-defined compiler directives are defined in this system compile parameter.
RETURN
EXECUTE IMMEDIATE
suspended
PLSQL_CCFLAGS

3. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
CURSOR - RETURN - IS
cannot
OUT
CREATE TRIGGER

4. The valid values of the AUTHID property.
IN - OUT - IN OUT
query
create procedure
CURRENT_USER - DEFINER

5. I/O: The DBMS_OUTPUT procedure that sets the size of the output buffer.
ENABLE
portable
:=
machine code

6. The two main parts of a package are the ____ and the _____.
predefined
executable
specification - body
INTO

7. Cursors: You cannot use cursor attributes in _______ statements.
exit when cgo%NOTFOUND
UTL_FILE
True
SQL

8. The _____ and ____ _____ collection types can be used in database table columns.
VARRAY - Nested table
'ENABLE:SEVERE' - 'ERROR:06002'
True
FOR - IN

9. IDs: The two types of words that can - but should not - be used as user-defined identifiers.
an autonomous routine
DBMS_PIPE
Keywords and predefined names.
RETURN

10. The declaration section is (optional/mandatory).
FOR - IN
exception
optional
FETCH - INTO

11. NAN stands for _________.
CURRENT_USER
not a number
definer's
always

12. Unlike the CASE statement - the CASE expression does not have the...
comments
USER_PLSQL_OBJECT_SETTINGS
implicit CASE_NOT_FOUND exception
source code

13. A basic PL/SQL datatype that SQL does not have.
%TYPE
invoker's
boolean
<<>>

14. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
exit when cgo%NOTFOUND
scalar
OPEN
TRUE - FALSE - NULL

15. I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
DATE
END
array
IN OUT

16. PLSQL_CCFLAGS hold a comma-separated list of ______ pairs (hyphenated).
DEFINER
run
name-value
searched

17. I/O: If the current (outermost) PL/SQL block ends with an unhandled exception - the output buffer will not be ___________.
STANDARD
EXECUTE IMMEDIATE
flushed
%ROWTYPE

18. Advantage: PL/SQL runs anywhere Oracle runs - so it is very ________.
non-editioning views
definer's
PL/SQL
portable

19. Delimiters: Host variable indicator.
...
'
EXTEND
searched

20. Delimiters: Item separator.
cannot

STANDARD
PLSQL_OPTIMIZE_LEVEL

21. Cursors: The three keywords in a full cursor declaration/definition (before the SELECT).
CURSOR - RETURN - IS
cursor
inquiry
view

22. Cursors: The FOR UPDATE phrase goes at the (beginning/end) of the cursor definition.
selection
is
end
RETURN

23. Advantage: Caching of programs and sharing among users lowers _______ requirements.
memory
procedure - function
fine-grained auditing
can

24. I/O: The buffered output of dbms_output.put_line is retrieved by _______.
<> - != - ^=
SQLPlus
cursor
OTHERS

25. Cursors: The first two keywords in a Cursor FOR loop.
FOR - IN
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
placeholders
%TYPE

26. Fundamental: A label can only go directly before an _______ statement.
raise_application_error(-20 -100 - 'Bad');
executable
FETCH
associative

27. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
will not
pseudocolumn
$IF
nested

28. Types: A data type family consists of a ______ type and its subtypes.
BINARY_INTEGER
FETCH - INTO
base
index

29. In Native Mode - PL/SQL code is compiled into ______ ______.
composite
machine code
SQL cursor
non-editioning views

30. I/O: The DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block _________.
network
single-line
...
finishes

31. Assignment of PLSQL_CCFLAGS (cannot / can) be included in ALTER PROCEDURE ... COMPILE ...
system global area
EXECUTE IMMEDIATE
can
object

32. Labels for blocks and statements are enclosed in '__' brackets.
once
return value
versions
<<>>

33. Cursors: The statement that closes a cursor.
INTO
3
dense
CLOSE

34. Dbms_sql: The DBMS_SQL procedure that closes a cursor.
anonymous block
DBMS_PIPE
CLOSE_CURSOR
cannot

35. Declaration of a varchar2 constant - called vname - with value 'x'.

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

36. Pkgs: RESET_PACKAGE only takes effect after the outermost PL/SQL proc that called it ________.
finishes
create procedure
ROLLBACK
EXCEPTION_INIT

37. Cursors: Which cursor attribute is not meaningful for an implicit cursor?
machine
%ISOPEN.
Abstract
constrained

38. Triggers: The clause to make a trigger run after inserting into table EMPS.
after insert on emps
anonymous
p.v
%BULK_ROWCOUNT

39. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
ENABLE
suspended
value
:=

40. To get the current value of CURRENT_SCHEMA - CURRENT_USER - or SESSION_USER - use the ____ function.
null string
DBMS_PIPE
obfuscates
SYS_CONTEXT

41. Cursors: The value of SQL%ISOPEN is always ______.
SQLERRM
UTL_FILE
FALSE
VARRAYs

42. Command to make a no-op (omit semicolon).

value
HTP
DBMS_SQL

43. What do you FETCH from?
Ordinary - Quoted.
variable
cursor
a cursor

44. Command to create a procedure.
IN - OUT - IN OUT
rollback to ab
create procedure
PLSQL_OPTIMIZE_LEVEL

45. This is a CASE (expression / statement): myvar := CASE num WHEN 1 THEN 'good' WHEN 2 THEN 'bad' END;
expression
collections
name-value
3

46. You can have many autonomous transactions in ___________.
cursor
error
an autonomous routine
code

47. IDs: Predefined identifiers are declared in this package.
sql
anonymous
**
STANDARD

48. By default - OUT and IN OUT parameters are passed by _____.
injection
PL/SQL
-
value

49. Dbms_sql: This DBMS_SQL function opens a new cursor.
FETCH - INTO
p.v
OPEN_CURSOR
literals

50. DynSQL: EXECUTE IMMEDIATE can be used for any SQL statement or PL/SQL block - except for ________ queries.
OUT
multi-row
explicit
Gateway