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. The statement that is used to execute most (native) dynamic SQL.
execute immediate
INTO
executable
$IF

2. The three types of compiler warnings.
EVERE - PERFORMANCE - INFORMATIONAL
cursor variable
cursor c RETURN rC;
implicit cursor

3. SIMPLE_INTEGER is like PLS_INTEGER - but it does not allow ____ values - and it does not raise overflow exceptions.

timing
fine-grained auditing
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;

4. Give the statement to make a program autonomous.
PRAGMA AUTONOMOUS_TRANSACTION;
calls
create procedure
FETCH

5. An editioning view is like an ____ for a table.
API
REF CURSOR
suspended
elements

6. Where an external subprogram is stored.
INTO
session cursor
lower sub-block
in the database

7. In a collection - the internal components are called _______.
manageable
after insert on emps
<<
elements

8. The 4 keywords in the $IF compiler directive.
ALTER SESSION
implicit
scalar
$IF $THEN $ELSE $END

9. Abbreviation of invoker's rights
in the database
IR
HTF
after insert on emps

10. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
LOOP
1
compiles
server

11. From a block nested inside a procedure P - reference variable V of the outer procedure.
255
p.v
..
PRAGMA AUTONOMOUS_TRANSACTION;

12. Declare a variable r whose type is that of the records in table t.
hardware
r t.%ROWTYPE;
significant
composite

13. Wrap iname=xxxx _____=zzzzz
EXIT WHEN
FORALL i IN MyList.FIRST..MyList.LAST
not the same
oname

14. I/O: This package lets PL/SQL programs read and write OS files.
UTL_HTTP
%
UTL_FILE
execute immediate

15. DynSQL: In EXECUTE IMMEDIATE - the statement string can use either placeholders - or string ________.
USER_PROCEDURES
library
concatenation
fields

16. A collection is an ordered group of ________ - all of the same ____
GET_LINES
cannot
1
elements - type

17. Declare an input NUMBER parameter - v2 - that defaults to 20.
v3 IN OUT NUMBER
selection
v2 IN NUMBER default 20
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;

18. What is the simplest statement for conditionally getting out of simple loop?
include

EXIT WHEN
SYS_REFCURSOR

19. IDs: The type of words that cannot be user-defined identifiers.
code
Reserved words.
decimal
can

20. Triggers: A compound trigger can fire at more than one ______ point.
must
timing
END CASE
255

21. The compiler directive governing conditional compilation.
expression
UTL_SMTP
decimal
$IF

22. Cursors: The three keywords in a full cursor declaration/definition (before the SELECT).
CURSOR - RETURN - IS
inclusive
anonymous
after

23. The valid values of the AUTHID property.
CURRENT_USER - DEFINER
comments
FETCH
cursor variable

24. Triggers: INSTEAD OF triggers only run on ______ views.
array
non-editioning views
512
USER_PROCEDURES

25. Types: ________ data types have no internal components.
scalar
flushed
savepoint ab
significant

26. Cursors: The first two keywords of a basic fetch statement.
$END
FETCH - INTO
definer's
dynamic

27. I/O: SQLPlus command for turning on buffered output.
scalar
set serveroutput on
FORALL and BULK COLLECT
r tRec := tRec(9 - 'Jones');

28. Statement to exit a cursor loop on cursor cGo when there are no more records.
dbms_output.put_line
label
exit when cgo%NOTFOUND
compilation

29. A type of compiler directive - begins with 's'.
selection
TIMESTAMP.
EXECUTE IMMEDIATE
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;

30. Set flag bb true: ALTER SESSION SET PLSQL_CCFLAGS= '____'
bb:true
cannot
is
fine-grained auditing

31. Dbms_sql: The DBMS_SQL procedure that closes a cursor.
inquiry
CLOSE_CURSOR
GET_LINE
32 -000

32. Types: The storage size of a NUMBER value is based on the number of ______ digits.
significant
Gateway
;
can

33. A pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
cursor
PRAGMA EXCEPTION_INIT (E - -1);
PLS_INTEGER
definition

34. An anonymous block always has _________ rights.

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

35. Conversion: Implicit conversion can be (faster / slower) than explicit conversion.
invoker's
dynamic
slower
CURSOR - RETURN - IS

36. Declare a number variable - A - initialized to 10.
multi-row
scalable
NUMBER
a number := 10

37. Internally - the NUMBER type is stored as ______. Therefore - decimal numbers (such as prices) are stored exactly - with no rounding.
nested tables - associative arrays
create procedure
decimal
varray

38. Exceptions: A GOTO statement cannot branch into an _______ ________.
exception handler
constructor
finishes
run

39. Exceptions: The _________ package defines the PL/SQL environment.
fetch cv into myvar
FETCH
CREATE TYPE
STANDARD

40. A CASE statement ends with ____ ____.
exception handler
implicit CASE_NOT_FOUND exception
1
END CASE

41. IDs: The 3 non-alphanumeric characters that can be in an ordinary user-defined identifier.
suspended
# - $ - _
execute immediate
INSTEAD OF

42. What are the starting and ending keywords of a simple loop?
constrained
cursor
elsif
LOOP - END LOOP

43. With an sparse - integer-indexed associative array - you (can / cannot) use a regular FOR loop.
invoker's
cannot
PLS_INTEGER
multi-row

44. The two kinds of CASE statements are simple and ________.
SESSION_USER
virtual machine
searched
must

45. Clause added to a cursor to use it with WHERE CURRENT OF.
for update of
GET_LINE
Wrap
lock

46. Cursors: The four cursor attributes - in alphabetical order.
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
commit - rollback
can
PLS_INTEGER

47. Cursors: If an exception is raised inside a cursor loop - the cursor (is / is not) also closed automatically.
execute immediate
is
compilation
;

48. The four keywords that define a PL/SQL block.
value
..
DECLARE - BEGIN - EXCEPTION - END
ordinary

49. Into nested table ex - put elements of tbl1 that are not in tbl2.
decimal
ex := tbl1 MULTISET EXCEPT tbl2
HTP
dbms_output.put_line

50. Web: The gateway that allows a web browser to invoke a PL/SQL procedure through an HTTP listener.
in the database
varray
PL/SQL Gateway
nested