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 NOCOPY option on an OUT or IN OUT parameter - causes it to be passed by ______.
close C
512
reference
end

2. Make a FOR loop that executes NULL 10 times counting in reverse - using index i.
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
SQLERRM
associative
PUT_LINE

3. Records are one of the two kinds of PL/SQL _______ data types.
composite
current BEGIN-END block
unqualified
preprocessor

4. Mode designation for an output parameter.
finishes
a cursor
literal
OUT

5. Declare an associative array type - t - whose elements are of type rPerson - and whose index is PLS_INTEGER.
quoted
:=
query
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;

6. PL/SQL lets you define two kinds of composite data types: _______ and _______
collections - records
DBMS_DDL
DBMS_SQL
raise_application_error(-20 -100 - 'Bad');

7. Cursors: If no SELECT or DML statement has run - the value of SQL%ROWCOUNT is what?
oname

RAISE
EXECUTE

8. Command to delete a procedure.
scalable
drop procedure
r t.%ROWTYPE;
*/

9. The EXCEPTION section is (optional/mandatory).
expression
SQL
optional
current

10. I/O: This package handles HTTP.
array
UTL_HTTP
always
hardware

11. Unlike the CASE statement - the CASE expression does not have the...
implicit CASE_NOT_FOUND exception
virtual machine
=
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;

12. The four keywords that define a PL/SQL block.
dbms_output.put_line
DECLARE - BEGIN - EXCEPTION - END
nested
finishes

13. Dbms_sql: The return type DBMS_SQL.open_cursor.
integer
FETCH - BULK COLLECT INTO
2
OTHERS

14. Types: The storage size of a NUMBER value is based on the number of ______ digits.
significant
'ENABLE:SEVERE' - 'ERROR:06002'
DBMS_PREPROCESSOR
FORALL and BULK COLLECT

15. A national character literal is composed of characters in the ________ character set.
:=
national
CURRENT_USER - DEFINER
Keywords and predefined names.

16. Delimiters: Statement terminator.
STANDARD
end
;
30

17. Triggers: The clause to make a trigger run before inserting or updating table EMPS.
before insert or update on emps
REF_CURSOR
close C
query

18. The FIRST method gets the first (element / index) of a collection.
index
does not
upper - coalesce
slower

19. Cursors: Give the first 3 words to open a cursor variable called cv.
optional
%BULK_ROWCOUNT
fine-grained auditing
open cv for

20. To use a TYPE declared in the database - a PL/SQL block has to have _______ privilege on the type.
$$
select
SQLCODE
index

21. Abbreviation for Abstract Data Type.
VARRAY - Nested table
ADT
static boolean
NUMBER

22. Fundamental: One of the 4 types of lexical units: d_____
colon
PLS_INTEGER
virtual machine
delimiters

23. Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
DBMS_CRYPTO
CHAR - VARCHAR2
TRUE - FALSE - NULL
boolean

24. The compiler directive used to select which code to compile.
512
cursor c;
current
$IF

25. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
delimiters
colon
API
GET_LINES

26. Advantage: PL/SQL is PORTABLE to any OS where ________ runs.
a cursor
DBMS_PREPROCESSOR
Oracle
3

27. I/O: Max number of bytes DBMS_OUTPUT can handle in a session.
1 million
server
explicit
specification - body

28. Cursors: The four cursor attributes - in alphabetical order.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
include
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
END IF

29. When a session starts - CURRENT_SCHEMA has the value of the schema owned by ___________.
OPEN
SESSION_USER
can
DBMS_PIPE

30. Cursors: An explicit cursor (can/cannot) accept parameters.
can
3
32 -000
PVM

31. Types: One of the scalar data types specific to PL/SQL: B_______
BINARY_INTEGER
255
procedure P AUTHID definer;
compile

32. Fundamental: Maximum string length of a PL/SQL variable.
32 -000
searched
code
=>

33. When an autonomous routine begins execution - the main transaction is ________.
boolean
LOOP
suspended
EXECUTE

34. Fundamental: One of the 4 types of lexical units: c_____.
comments
always
1
sequence pseudo-columns

35. The default value of the AUTHID property.
DEFINER
searched
USER_PLSQL_OBJECT_SETTINGS
decimal

36. Default value of PLSQL_OPTIMIZE_LEVEL.
aliases
increment
%BULK_ROWCOUNT
2

37. Delimiters: Item separator.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
FETCH

cursor

38. In 11g you can directly reference _____________ in PL/SQL.
SESSION_USER
TOO_MANY_ROWS
sequence pseudo-columns
unconstrained

39. The Wrap utility ________ the source code.
obfuscates
Wrap
PL/SQL
L.V

40. Statement to exit a cursor loop on cursor cGo when there are no more records.
:=
composite
exit when cgo%NOTFOUND
$IF

41. Bytecode is _______ _______ code.
END LOOP
virtual machine
identifiers
SQLERRM

42. Types: The PL/SQL type that is identical to PLS_INTEGER.
cannot
VARCHAR2
BINARY_INTEGER
error code

43. Dbms_sql: The DBMS_SQL procedure that will execute a DDL or DML statement.
;
LOOP
execute
invoker's

44. Packaged constants can synchronize programs through use in _______ directives.
RESULT_CACHE
forward
selection
DBMS_SQL

45. You cannot bulk collect into an ARRAY OF ________.
open cv for
RECORDS
an AUTHID property
%BULK_ROWCOUNT

46. A kind of variable used as a placeholder.
WHERE CURRENT OF
is certain
bind
collections - records

47. Cursors: Opening keyword of a cursor FETCH loop.
statically
LOOP
propagates
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT

48. The allowed data types of user-defined conditional compilation directives are boolean and _______.
v2 IN NUMBER default 20
raise_application_error
pls_integer
SYS_CONTEXT

49. The three possible values of a BOOLEAN literal.
invalid
inquiry
database
True - false or NULL.

50. Collections are one of the two kinds of PL/SQL _______ data types.
placeholders
anonymous
definer's
composite