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. DynSQL: Maximum string length to pass to EXECUTE IMMEDIATE.
bind arguments
32 -000
cannot
FGA

2. Advantage: Caching of programs and sharing among users lowers _______ requirements.
finishes
system global area
slower
memory

3. Cursors: You cannot use cursor attributes in _______ statements.
bytecode
for update of
LOOP
SQL

4. This is a CASE (expression / statement): myvar := CASE num WHEN 1 THEN 'good' WHEN 2 THEN 'bad' END;
OTHERS
an autonomous routine
expression
<> - != - ^=

5. Exceptions: The built-in procedure for raising an application-defined error.
raise_application_error
-
package
INTO

6. A __________ can have an AUTHID property
package
boolean
drop procedure
STANDARD

7. The 4 keywords in the $IF compiler directive.
parameter
$IF $THEN $ELSE $END
value
column

8. Cursors: The three keywords in a full cursor declaration/definition (before the SELECT).
CURSOR - RETURN - IS
production
implicit CASE_NOT_FOUND exception
once

9. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
CURRENT_USER - DEFINER
anonymous
can
DML

10. Default file extension of wrap's input file.
cannot
PLS_INTEGER
sql
TOO_MANY_ROWS

11. Cursors: Define a ref cursor type called T.
...
definer's
open C(p)
type T is ref cursor

12. Triggers: The clause to make a trigger run once for every affected row.
1
TYPE tN IS TABLE of NUMBER
for each row
FGA

13. To recompile a procedure - use the _____ ______ command.
UTL_HTTP
return value
nested
alter procedure

14. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
OUT
reference
compiles
ordinary

15. The Wrap utility (does / does not) encrypt source code.
$$
invoker's
savepoint ab
does not

16. Exceptions: An unhandled exception ________ to the enclosing block.
Ordinary - Quoted.
bind arguments
propagates
a cursor

17. Types: One of the scalar data types specific to PL/SQL: b_____.
BOOLEAN
scalar
invoker's
nested

18. With a result-cached function - it uses a cached value when the function is called again with the...
same arguments
DATE
RETURN
virtual machine

19. Bulk: The use of FORALL statements cuts down on ______ traffic.
library
network
INTO
is

20. Types: ________ data types have no internal components.
elements
constructor
scalar
raise_application_error

21. Cursors: A pointer to a cursor is called a ______ _______.
SQL
STANDARD
cursor variable
compiles

22. Types: One of the scalar data types specific to PL/SQL: B_______
BINARY_INTEGER
simple and compound symbols
hardware
SYS_REFCURSOR

23. Cursors: A cursor that lives in session memory until the session ends.
CLOSE_CURSOR
session cursor
255
current transaction

24. Declare a package procedure P with no arguments - and give it invoker's rights.
increment
define
procedure P AUTHID current_user;
aliases

25. Conversion: It is best to use (implicit / explicit) conversion.
Double quotes.
in the database
DML
explicit

26. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
strong
server
END CASE
concatenation

27. In a sparse collection - the indexes of defined elements (do / do not) run in consecutive order.
do not
cursor c RETURN rC;
**
delimiters

28. Labels for blocks and statements are enclosed in '__' brackets.
return value
REUSE SETTINGS
static boolean
<<>>

29. The collection method that tells the number of elements in the collection.
bind arguments
COUNT
PLSQL_CCFLAGS
DML

30. The _____ and ____ _____ collection types can be used in database table columns.
VARRAY - Nested table
nested tables - associative arrays
set serveroutput on
ADT

31. The compiler directive used to select which code to compile.
constant
$IF
subtype
1 million

32. Advantage: PL/SQL is PORTABLE to any OS where ________ runs.
CLOSE_CURSOR
elsif
Oracle
for each row

33. How many types of compiler directives are there?
3
for each row
savepoint ab
predefined

34. In a record - the internal components are called ______.
fields
Oracle
r t.%ROWTYPE;
collections - records

35. If a result-cached function's data source (based on the RELIES_ON clause) is updated - any cached results become _______.
invalid
unhandled
2
32 -000

36. You can define PL/SQL collections using TYPEs defined in the _______.
database
return value
EXIT WHEN
query

37. Triggers: The clause to make a trigger run after inserting into table EMPS.
after insert on emps
True
# - $ - _
finishes

38. Exceptions: Associate exception E with error code -1.
PRAGMA EXCEPTION_INIT (E - -1);
plb
an enclosing block
Abstract

39. Declare an input NUMBER parameter - v2 - that defaults to 20.
associative
v2 IN NUMBER default 20
cannot
elements

40. To insert or update on a table that has a collection column - you have to have _______ privilege on the collection type.
is
EXECUTE
null string
inquiry

41. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
%ROWTYPE
cannot
buffer
OTHERS

42. Web: mod_plsql is an implementation of the PL/SQL _________.
;
SUBTYPE n IS number not null;
REUSE SETTINGS
Gateway

43. Web: PL/SQL _______ enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener.
cursor c;
RECORD OF ARRAYS
Gateway
null string

44. A type of compiler directive - begins with 's'.
executable
PSP
selection
EXECUTE

45. I/O: This package lets PL/SQL programs read and write OS files.
finishes
compile
UTL_FILE
anonymous

46. Fundamental: Labels furnish targets for GOTO and _____ statements.
EXIT
EXIT WHEN cv%NOTFOUND
parameter
specification - body

47. To run TCL or DDL statements - a trigger (must / must not) be autonomous .
# - $ - _
execute immediate
must
portable

48. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
TRUE - FALSE - NULL
implicit CASE_NOT_FOUND exception
view
loop

49. Cursors: The second keyword of a FETCH statement.
FORALL and BULK COLLECT
RESULT_CACHE
1
INTO

50. The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
INTO
inquiry
SESSION_USER
EXIT