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 attribute used to get the type of a row without knowing what it is.
%ROWTYPE
composite
error code
anonymous

2. With a result-cached function - it uses a cached value when the function is called again with the...
SQL
same arguments
raise_application_error(-20 -100 - 'Bad');
boolean

3. I/O: The buffered output of dbms_output.put_line is retrieved by _______.
INSTEAD OF
SQLPlus
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
associative

4. A CASE statement (can / cannot) execute more than one group of statements.
cannot
quoted
anonymous
PLS_INTEGER

5. IDs: You (can/cannot) use keywords as ordinary user-defined identifiers.
EXCEPTION_INIT
can
cursor
system global area

6. Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
OTHERS
%TYPE
REF CURSOR
bind

7. Types: A data type family consists of a ______ type and its subtypes.
enclosing
base
cursor
block

8. A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
lock
collections
%ROWTYPE
SYS_CONTEXT

9. PL/SQL does not automatically create bind variables in ________ SQL.
L.V
INTO
dynamic
bind arguments

10. Declare a variable r whose type is that of the records in table t.
implicit cursor
open cv for
r t.%ROWTYPE;
specification - body

11. Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
REF CURSOR
DBMS_PREPROCESSOR
autonomous transaction
varray

12. The allowed data types of user-defined conditional compilation directives are boolean and _______.
pls_integer
OPEN
CREATE TRIGGER
cannot

13. In a FORALL loop - you (can / cannot) use the index variable for purposes other than indexing into the collection.
$$PLSQL_LINE
cannot
inclusive
Keywords and predefined names.

14. Cursors: Give the statement to open cursor C.
function f (n number) return number result_cache relies_on (emps);
define
ENABLE
open C

15. The PL/SQL preprocessor passes ______ code to the compiler.
database
source
dbms_output.put_line
database

16. $$PLSQL_LINE and $$PLSQL_UNIT are ______-ly defined inquiry directives.
END CASE
timing
implicit
OPEN

17. Advantage: Performance: Stored subprograms are ______ in memory.
WHEN OTHERS THEN
BOOLEAN
cached
information that changes infrequently

18. An incomplete type is a type created by a ______ type definition.
once
forward
literals
varray

19. Bulk: The keywords of a bulk collect FETCH statement.
error
fetch C into V
UTL_HTTP
FETCH - BULK COLLECT INTO

20. A basic PL/SQL datatype that SQL does not have.
cached
DATE
boolean
explicit

21. Triggers: A compound trigger can fire at more than one ______ point.
timing
PLSQL_OPTIMIZE_LEVEL
virtual machine
cannot

22. TCL: Undo changes back to savepoint ab.
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
finishes
rollback to ab
$IF $THEN $ELSE $END

23. I/O: dbms_output.put_line puts output into a _______.
virtual machine
UTL_HTTP
buffer
exception handler

24. I/O: One of the 3 types of data that can be passed to the DBMS_OUTPUT buffer.
VARCHAR2
iname
invoker's
bind arguments

25. Two types of stored subprograms.
deletions
procedure - function
p.v
implicit CASE_NOT_FOUND exception

26. Exceptions: User-defined exceptions must have _______.
INSTEAD OF
names
TRUE - FALSE and NULL
transaction

27. Advantage: Having procedures stored and executed on the server makes PL/SQL ________.
static boolean
not the same
error code
scalable

28. Wrapping can be done with the ____ executable.
cannot
32 -000
PSP
wrap

29. You cannot bulk collect into an ARRAY OF ________.
r tRec := tRec(9 - 'Jones');
integrated
EXIT WHEN cv%NOTFOUND
RECORDS

30. A CASE expression ends with _____.
sql
END CASE
Web Toolkit
END

31. Triggers: Programmers (as opposed to DBAs) mostly make use of _____ triggers.
alter procedure
-
STANDARD
DML

32. A GOTO statement cannot branch from an exception handler back into the...
name
current BEGIN-END block
parameter
PRAGMA AUTONOMOUS_TRANSACTION;

33. Types: PL/SQL has all the _____ data types.
server
SQL
ENABLE
machine

34. The valid values of the AUTHID property.
IF - CASE or LOOP
bind arguments
TYPE tV IS VARRAY(100) OF NUMBER
CURRENT_USER - DEFINER

35. 5 types of events that can have triggers: DML - DDL - INSTEAD OFs - database events - and ________ statements.
CURSOR - RETURN - IS
suspended
before insert or update on emps
block

36. DynSQL: The standard package that does dynamic SQL.
:=
DBMS_SQL
Apache
before insert or update on emps

37. Cursors: A pointer to a cursor is called a ______ _______.
1
cursor variable
1
view

38. A (simple / searched) CASE statement begins with CASE WHEN <boolean expression>.
elements
searched
error
current BEGIN-END block

39. Declare a number variable - A - initialized to 10.
:=
a number := 10
is not
literals

40. After the RESULT_CACHE clause - you can put a _________ clause to enable dependency-checking.
code
OUT
RELIES_ON
disable

41. Wrap ______=xxxx oname=zzzzz
iname
associative
after
RELIES_ON

42. INSTEAD OF triggers are alternatives to ___ triggers
Apache
upper - coalesce
DML
after

43. Types: In addition to the SQL data types - PL/SQL has ___ more scalar data types.
4
collections - records
lowest
source code

44. Exceptions: Start of the range of error codes for RAISE_APPLICATION_ERROR.
-20 -000
True
CURRENT OF
OPEN_CURSOR

45. Wrap iname=xxxx _____=zzzzz
create procedure
open cv for
OLD
oname

46. To run DDL statements - a trigger must be autonomous and must use ______ ______ SQL.
statically
associative arrays
cannot
native dynamic

47. With Edition-based Redefinition enabled - the unique specification of an object requires OWNER - OBJECT_NAME and ___________.
before insert or update on emps
EDITION_NAME
DATE
E Exception;

48. DynSQL: The clause that supplies bind arguments to EXECUTE IMMEDIATE.
view
USING
timing
composite

49. An INSTEAD OF trigger specifies what to do instead of a ___ statement.
database
DML
suspended
RETURN

50. Cursors: An explicit cursor (can/cannot) accept parameters.
STANDARD
source
SQL
can