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: If two or more triggers are defined with the same timing point - then you can control the firing order using the ________ clause.
cannot
follows
sequence pseudo-columns
calls

2. Fundamental: Which one of these is a compound symbol: = - :=
:=
VARRAY - Nested table
production
associative

3. The declaration section is (optional/mandatory).
is
optional
Server Pages
3

4. Characters that begin a multi-line comment.
/*
autonomous triggers
decimal
1

5. DynSQL: Maximum string length to pass to EXECUTE IMMEDIATE.
32 -000
v1 OUT number default 10
same arguments
composite

6. DBMS_DDL.WRAP returns a string containing wrapped _____ _____.
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
source code
decimal
anonymous block

7. A basic PL/SQL datatype that SQL does not have.
*/
EXECUTE IMMEDIATE
NUMBER
boolean

8. Cursors: The type definition of a WEAK ref cursor type has no _______ clause.
scalable
'
CURRENT OF
RETURN

9. A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
open C
integrated
not a number
lock

10. Exceptions: The three words that can be used to guarantee all exceptions will be handled.
optional
WHEN OTHERS THEN
CHARARR
cursor c RETURN rC;

11. DynSQL: The three modes of arguments in the USING clause of EXECUTE IMMEDIATE.
specification - body
IN - OUT - IN OUT
run
RETURN

12. Exceptions: Associate exception E with error code -1.
boolean
TYPE tV IS VARRAY(100) OF NUMBER
return value
PRAGMA EXCEPTION_INIT (E - -1);

13. Cursors: You cannot use cursor attributes in _______ statements.
block
index
SQL
bind arguments

14. If none of a CASE expression's conditions are TRUE - it returns _____.
after

RETURN
aggregate - analytical

15. You can put a label before any executable PL/SQL _________.
statement
WHEN OTHERS THEN
SUBTYPE n IS number not null;
-

16. Default file extension of wrap's output file.
cannot
plb
open cv for
v3 IN OUT NUMBER

17. Cursors: The second keyword of a FETCH statement.
INTO
cannot
is
concatenation

18. Kind of code created by the PL/SQL compiler.
sequence pseudo-columns
$$
CLOSE_CURSOR
bytecode

19. The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
iname
REF CURSOR
definition
dynamic

20. The two main parts of a package are the ____ and the _____.
hardware
RETURN
variable
specification - body

21. Kind of comment not to put in a PL/SQL block to be processed dynamically by an Oracle Precompiler program.
REF_CURSOR
single-line
name and datatype
statement

22. Assigning values to PL/SQL variables that appear in SQL statements is called _______.
DECLARE - BEGIN - EXCEPTION - END
4K
system global area
binding

23. $$PLSQL_LINE and $$PLSQL_UNIT are ______-ly defined inquiry directives.
drop procedure
SESSION_USER
implicit
significant

24. Triggers: You (can/cannot) explicitly invoke a trigger.
Gateway
for update of
package
cannot

25. Exceptions: An exception in the declarations section propagates to the enclosing _______.
IS
EXIT WHEN cv%NOTFOUND
block
include

26. The _____ and ____ _____ collection types can be used in database table columns.
:=
DML
VARRAY - Nested table
1 million

27. Triggers: The clause to make a trigger run after inserting into table EMPS.
r t.%ROWTYPE;
after insert on emps
5
WHERE CURRENT OF

28. Cursors: The statement that closes a cursor.
PRAGMA AUTONOMOUS_TRANSACTION;
OTHERS
CLOSE
DATE

29. Dbms_sql: A cursor opened with OPEN_CURSOR (can/cannot) be reused for multiple queries without first closing it.
open cv for
can
index
placeholders

30. Types: ________ data types have internal components.
ALTER SESSION
composite
procedure P AUTHID definer;
EXTEND

31. PL/SQL does not automatically create bind variables in ________ SQL.
oname
1
dynamic
finishes

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

33. Types: ________ data types have no internal components.
scalar
implicit CASE_NOT_FOUND exception
variable
always

34. Cursors: The SQL%BULK_ROWCOUNT attribute is an _________ -
array
RETURN
DBMS_PIPE
scalar

35. Types: PL/SQL has all the _____ data types.
iname
SQL
PRAGMA EXCEPTION_INIT (E - -1);
enclosing

36. A type of compiler directive - begins with 'i'.
simple and compound symbols
:=
inquiry
UTL_HTTP

37. Triggers: To simulate having triggers on SELECT statements - you can try ___ (3-letter acronym).
statically
anonymous
FGA
constructor

38. Value of PLSQL_OPTIMIZE_LEVEL that prevents optimization.
bind
1
alter procedure
DATE

39. IDs: You cannot use reserved words as ______ user-defined identifiers.
ordinary
preprocessor
Oracle
explicit cursor

40. Cursors: A session cursor that a PL/SQL user creates and manages.
cannot
multi-row
explicit cursor
fetch cv into myvar

41. Cursors: A cursor variable's type must be a ____ ______ type.
REF CURSOR
procedure - function
suspended
OTHERS

42. Nested tables are multisets - meaning that there is no inherent _____ of their elements.
order
1
always
manageable

43. Exceptions: If there is no handler for an exception - PL/SQL returns an ________ exception error to the invoker or host environment.
exit when cgo%NOTFOUND
unhandled
WHERE CURRENT OF
selection

44. Delimiters: The left label delimiter.
<<
suspended
1
DATE '2005-12-25'

45. INSTEAD OF triggers are alternatives to ___ triggers
a cursor
DML
propagates
bytecode

46. Conversion: It is best to use (implicit / explicit) conversion.
value
DML
explicit
Gateway

47. Command to delete a procedure.
cannot
drop procedure
*/
mandatory

48. In short-circuit evaluation - IF-conditions are only evaluated up to where the outcome ___________.
will not
is certain
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
run

49. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
static
%
RESULT_CACHE
procedure - function

50. GOTO branches to a ________.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
label
iname
implicit cursor