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. I/O: This package sends text output to SQLPlus
DBMS_OUTPUT
CLOSE_CURSOR
numeric and cursor
Abstract

2. With conditional compilation you can avoid compiling debug code in the _______ environment.
EXECUTE IMMEDIATE
currval and nextval
...
production

3. Types: One of the scalar data types specific to PL/SQL: B_______
can
BINARY_INTEGER
select
static boolean

4. Fundamental: What is the range operator?
2
server
cached
..

5. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
ROLLBACK
OUT
1
must

6. To exit an active autonomous transaction without causing an exception - you must issue _____ or ______.
parameter
external subprogram
commit - rollback
CHAR - VARCHAR2

7. Characters that begin a single-line comment.
<> - != - ^=
-
error code
aliases

8. An incomplete type is a type created by a ______ type definition.
optional
forward
preprocessor
binding

9. Define a record type REC with number num and varchar2(10) name.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
Abstract
IR
exit when cgo%NOTFOUND

10. Cursors: The two keywords in a mere cursor declaration (one is optional).
<> - != - ^=
CURSOR - RETURN
simple and compound symbols
NEW

11. I/O: This package has hypertext procedures that generate HTML tags
an autonomous routine
before insert or update on emps
flushed
HTP

12. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
Web Toolkit
PLSQL_CCFLAGS
base
suspended

13. In short-circuit evaluation - IF-conditions are only evaluated up to where the outcome ___________.
scalar
slower
is certain
STANDARD

14. A CASE expression ends with _____.
END
value
/*
END CASE

15. A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
nested tables - associative arrays
USING
definition
lock

16. An incomplete type only has a ____.
name
database
SQLCODE
DATE '2005-12-25'

17. Types: One of the 4 PL/SQL things that have a data type (begins with 'r').
anonymous block
return value
procedure P AUTHID current_user;
FETCH - BULK COLLECT INTO

18. Cursors: An implicit cursor is also called a/n ________ ________ -
Server Pages
SQL cursor
Ordinary - Quoted.
simple and compound symbols

19. Dbms_sql: This DBMS_SQL function opens a new cursor.
bb:true
optional
IN - OUT - IN OUT
OPEN_CURSOR

20. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
placeholders
DBMS_SESSION.RESET_PACKAGE
static
implicit CASE_NOT_FOUND exception

21. Declare an unconstrained subtype of NUMBER called NUMB.
SUBTYPE numb IS number;
once
network
END CASE

22. Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE - CORR - UPPER - SUM - COALESCE - DEREF.
RETURN
CHAR - VARCHAR2
upper - coalesce
RECORDS

23. Cursors: The value of SQL%ISOPEN is always ______.
FALSE
cursor
L.V
finishes

24. Exceptions: You cannot GOTO from an exception handler back into the __________ block.
implicit
collections
current
constructor

25. Cursors: If you use an EXIT statement to exit a cursor FOR loop prematurely - the cursor (is / is not) closed automatically.
integrated
%BULK_ROWCOUNT
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
is

26. Fundamental: One of the 4 types of lexical units: d_____
-
delimiters
wrap
optional

27. Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
IN - OUT - IN OUT
ALTER SESSION
finishes
alter procedure

28. The 2 characters that begin all inquiry directives.
DBMS_DDL
$$
OTHERS
cursor

29. TCL: Undo changes back to savepoint ab.
current
rollback to ab
mandatory
alter procedure

30. Types: Which type of arithmetic does NUMBER use - library or hardware?
UTL_SMTP
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
library
unqualified

31. The inquiry directive that gives the current line number in the procedure.
@
$$PLSQL_LINE
BOOLEAN
IS

32. Types: The predefined subtype of PLS_INTEGER that has the NOT NULL constraint.
stored
COUNT
reference
SIMPLE_INTEGER

33. To create a collection type - use the TYPE command in PL/SQL and the ______ _____ command in SQL.
1
decimal
literal
CREATE TYPE

34. The DEFINITION of a cursor is the _____ that returns the records.
query
PL/SQL Gateway
flushed
statement

35. IDs: The type of words that cannot be user-defined identifiers.
currval and nextval
cursor c;
Reserved words.
RECORDS

36. The two kinds of CASE statements are simple and ________.
SQL
searched
scalar
1

37. By default - an IN parameter is passed by ______.
cursor
@
reference
STANDARD

38. Types: ______ data types are those storing values with no internal components.
TRUE - FALSE - NULL
RETURN
FGA
scalar

39. Web: PL/SQL ______ ______ (PSPs) let you develop web pages with dynamic content.
Server Pages
.
Gateway
WHERE CURRENT OF

40. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
-
finishes
TRUE - FALSE - NULL
SQL

41. Declare a package procedure P with no arguments - and give it definer's rights.
procedure P AUTHID definer;
stored
Oracle
<<

42. I/O: SQLPlus command for turning on buffered output.
DBMS_OUTPUT
predefined
set serveroutput on
execute immediate

43. DynSQL: The clause that receives single-row query output from EXECUTE IMMEDIATE.
ex := tbl1 MULTISET EXCEPT tbl2
executable
INTO
END IF

44. A collection is an ordered group of ________ - all of the same ____
elements - type
USER_PROCEDURES
literal
API

45. Cursors: A cursor FOR loop can use an explicit or an _______ cursor.
vname CONSTANT VARCHAR2 := 'x';
implicit
machine
$IF $THEN $ELSE $END

46. The source for wrapping begins with the word ______.
create
decimal
reference
view

47. The command used to populate PLSQL_CCFLAGS.
preprocessor
ALTER SESSION
cannot
=>

48. Exceptions: When an exception is raised inside a cursor FOR loop - the cursor is _______ implicitly.
anonymous
closed
GET_LINES
WHEN

49. An INSTEAD OF trigger is used - e.g - when you want to insert into a complex _____.
VARRAYs
raise_application_error
view
REF CURSOR

50. The valid values of the AUTHID property.
statement
INTO
open C(p)
CURRENT_USER - DEFINER