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. List the transaction control statements.
%BULK_ROWCOUNT
do not
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
OUT

2. I/O: The DBMS_OUTPUT procedure that sets the size of the output buffer.
cursor
WHEN OTHERS THEN
open C
ENABLE

3. Exceptions: If there is no handler for an exception - PL/SQL returns an ________ exception error to the invoker or host environment.
executable
unhandled
RECORDS
TYPE tN IS TABLE of NUMBER

4. Triggers: A compound trigger can fire at more than one ______ point.
name-value
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
timing
SQL

5. This is a CASE (expression / statement): CASE num WHEN 1 THEN goto LABEL1 WHEN 2 THEN var := 2 END CASE;
cannot
statement
bb:true
DEFINER

6. Mode designation for an output parameter.
CLOSE_CURSOR
OUT
iname
names

7. Cursors: The first two keywords of a basic fetch statement.
Wrap
FETCH - INTO
EXTEND
for update of

8. Declare a constrained subtype of NUMBER called N that is not null.
SUBTYPE n IS number not null;
EXIT
5
RAISE

9. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
Reserved words.
ROLLBACK
anonymous block
EXIT

10. Statement to exit a cursor loop on cursor cGo when there are no more records.
exit when cgo%NOTFOUND
*/
integer
IS

11. IDs: The 3 non-alphanumeric characters that can be in an ordinary user-defined identifier.
OPEN
# - $ - _
open C
create

12. Triggers: In a per-row trigger - the ____ record shows you the original state of an affected row.
will not
Oracle
block
OLD

13. Cursors: A cursor FOR loop cannot use a _______ variable.
colon
dbms_output.put_line
cursor
unconstrained

14. Command to make a no-op (omit semicolon).
invalid
SQL

production

15. The exception raised when a SELECT INTO statement returns more than one row.
definer's
TOO_MANY_ROWS
vname CONSTANT VARCHAR2 := 'x';
executable

16. The lowest index of a nested table is ___.
statement
cursor c;
1
IN

17. Which collection type is bounded?
FOR - IN
varray
4K
With a letter.

18. A null string and a BOOLEAN null are _______ (same/not the same).
%ROWTYPE
select
STANDARD
not the same

19. Subprograms are stored in __________ form.
UTL_HTTP
SUBTYPE n IS number not null;
ALTER SESSION
executable

20. A FOR loop can be indexed by numbers or by a ______.
$$
autonomous triggers
single-line
cursor

21. Cursors: A session cursor that a PL/SQL user creates and manages.
fields
explicit cursor
selection
OTHERS

22. Types: The predefined subtype of PLS_INTEGER that has the NOT NULL constraint.
iname
SIMPLE_INTEGER
bind
exception

23. What is the simplest statement for conditionally getting out of simple loop?
EXIT WHEN
scalar
stored
CURRENT_USER - DEFINER

24. The four keywords that define a PL/SQL block.
ROLLBACK
True - false or NULL.
DECLARE - BEGIN - EXCEPTION - END
in the database

25. A user-defined TYPE can have...

an AUTHID property
IS
32 -000

26. A ___________ behaves like a table column - but it is not stored in the table.
CURSOR - RETURN
deletions
RETURN
pseudocolumn

27. From a block nested inside a procedure P - reference variable V of the outer procedure.
r tRec := tRec(9 - 'Jones');
preprocessor
p.v
SQL cursor

28. Dbms_sql: The DBMS_SQL procedure that closes a cursor.
single-line
CLOSE_CURSOR
PUT
r t.%ROWTYPE;

29. Triggers: By default - a trigger is created in the ______ state.
TYPE tN IS TABLE of NUMBER
cursor
enabled
EXIT

30. Java - C or other program called directly in PL/SQL.
non-editioning views
reference
external subprogram
API

31. Cursors: The statement where you pass the parameters to a cursor.
WHEN OTHERS THEN
OPEN
elements
statically

32. Parameter that controls how much Oracle optimizes your PL/SQL code on compilation.
PLSQL_OPTIMIZE_LEVEL
..
value
code

33. Exceptions: Pragma that links an exception to an Oracle error code.
DML
Gateway
EXCEPTION_INIT
1

34. DynSQL: The three modes of arguments in the USING clause of EXECUTE IMMEDIATE.
v2 IN NUMBER default 20
define
:=
IN - OUT - IN OUT

35. Cursors: Statement to end a loop when MyCur is used up: _____ ______ MyCur%NOTFOUND.
cursor number
EXIT WHEN
<<>>
VARRAY - Nested table

36. Triggers: Unlike a stored procedure - you can enable and _____ a trigger.
disable
GET_LINES
mandatory
implicit cursor

37. You have to use _______ SQL to run DDL statements n PL/SQL.
array
after
dynamic
executable

38. Exceptions: Each internal exception has an Oracle ______ _______.
commit - rollback
raise_application_error(-20 -100 - 'Bad');
dense
error code

39. Associative arrays (do / do not) have to be initialized with a constructor.
do not
512
anonymous
DBMS_SQL

40. FOR-Looping over a collection from FIRST to LAST only works if the collection is _______.
constrained
timing
dense
literals

41. IDs: How an ordinary user-defined identifier must begin.
=
Reserved words.
With a letter.
DBMS_OUTPUT

42. Fundamental: What is the exponentiation operator?
DEFINER
**
can
procedure - function

43. Define a record type REC with number num and varchar2(10) name.
ex := tbl1 MULTISET EXCEPT tbl2
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
Gateway
RELIES_ON

44. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
machine
cannot
compiles
PLSQL_OPTIMIZE_LEVEL

45. The $IF must evaluate a ______ _______ expression.
INTO
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
REUSE SETTINGS
static boolean

46. Declare a cursor c with no parameters - and return type rC.
variable
cursor c RETURN rC;

current BEGIN-END block

47. A type of compiler directive - begins with 'e'.
error
1
IR
DEFINER

48. Cursors: The Oracle predefined REF CURSOR type.
compile
CHARARR
raise_application_error
SYS_REFCURSOR

49. A (simple / searched) CASE statement begins with CASE <expression>.
simple
WHERE CURRENT OF
never
DBMS_PIPE

50. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
selection
colon
lowest
label