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 variable showing the user id of the session owner.
SESSION_USER
statically
exit when cgo%NOTFOUND
<<>>

2. Exceptions: Start of the range of error codes for RAISE_APPLICATION_ERROR.
cannot
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
-20 -000
value

3. Set flag bb true: ALTER SESSION SET PLSQL_CCFLAGS= '____'
production
does not
bb:true
after insert on emps

4. Delimiters: The association operator.
definer's
=>
bind arguments
error

5. Fundamental: Which one of these is a simple symbol: a - = - !=
bind
=
EXCEPTION_INIT
PLS_INTEGER

6. Web: PL/SQL _______ enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener.
do not
SQL cursor
Gateway
/*

7. Fundamental: Labels furnish targets for GOTO and _____ statements.
CREATE TYPE
does not
EXIT
VARRAY - Nested table

8. Where an external subprogram is stored.
in the database
procedure P AUTHID current_user;
Double quotes.
NUMBER

9. IDs: You cannot use reserved words as ______ user-defined identifiers.
SQL
exception handler
ordinary
constrained

10. A (simple / searched) CASE statement begins with CASE WHEN <boolean expression>.
searched
after
1
EXIT

11. A type of compiler directive - begins with 'i'.
unqualified
USER_PROCEDURES
query
inquiry

12. The DECLARE keyword is only required in an _______ _______.
after insert on emps
anonymous block
HTF
bind arguments

13. Cursors: Give the statement to open cursor C.
return value
open C
error
FETCH - BULK COLLECT INTO

14. A national character literal is composed of characters in the ________ character set.
IN OUT
CREATE TYPE
national
CLOSE_CURSOR

15. The LIMIT method is only meaningful for _______.
FOR - IN
VARRAYs
aliases
With a letter.

16. Command to make a no-op (omit semicolon).
scalar
WHEN OTHERS THEN

:=

17. TCL: Undo changes back to savepoint ab.
<> - != - ^=
rollback to ab
exception handler
4

18. Exceptions: PL/SQL exceptions are of two types: internal and _________.
-20 -000
user-defined
executable
Ada

19. The valid values of the AUTHID property.
SUBTYPE numb IS number;
CURRENT_USER - DEFINER
SQL
bind

20. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
RAISE
NUMBER
name and datatype
OTHERS

21. The PL/SQL virtual machine turns bytecode into system ______.
calls
preprocessor
vname CONSTANT VARCHAR2 := 'x';
UTL_SMTP

22. Cursors: A Cursor FOR loop does not use a ______ statement.
$IF
FETCH
RAISE
'

23. To run TCL or DDL statements - a trigger (must / must not) be autonomous .
compiles
Abstract
must
return value

24. IDs: The type of words that cannot be user-defined identifiers.
Reserved words.
4
$END
SYS_CONTEXT

25. In interpreted mode - PL/SQL code is run in a _______ _______.
virtual machine
Web Toolkit
..
network

26. Cursors: What is the 'name' of the implicit cursor?
SQL
END IF
ALTER SESSION
type T is ref cursor

27. The statement that is used to execute most (native) dynamic SQL.
integrated
execute immediate
stored
machine code

28. PL/SQL does not automatically create bind variables in ________ SQL.
1
dynamic
multi-row
r t.%ROWTYPE;

29. A FOR loop can be indexed by numbers or by a ______.
FORALL i IN MyList.FIRST..MyList.LAST
scalable
exception
cursor

30. Types: One of the scalar data types specific to PL/SQL: P________
'ENABLE:SEVERE' - 'ERROR:06002'
same arguments
CLOSE_CURSOR
PLS_INTEGER

31. _________ exceptions can not be trapped with an error handler
collections - records
CASE_NOT_FOUND
DBMS_DDL
Declaration

32. Cursors: Give the first 3 words to open a cursor variable called cv.
open cv for
function f (n number) return number result_cache relies_on (emps);
variable
cannot

33. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
%BULK_ROWCOUNT
an autonomous routine
identifiers
will not

34. Fundamental: Maximum string length of a PL/SQL variable.
32 -000
CHAR - VARCHAR2
API
base

35. I/O: This package has hypertext procedures that generate HTML tags
HTP
INTO
anonymous
WHERE CURRENT OF

36. In interpreted mode - the PL/SQL VM interprets the _________.
implicit CASE_NOT_FOUND exception
HTP
bytecode
fields

37. Dbms_sql: The DBMS_SQL procedure that will execute a DDL or DML statement.
an autonomous routine
execute
context
open cv for

38. Fundamental: One of the 4 types of lexical units: c_____.
comments
Wrap
RESULT_CACHE
does not

39. DynSQL: The main command for Native Dynamic SQL.
subtype
EXECUTE IMMEDIATE
PVM
EVERE - PERFORMANCE - INFORMATIONAL

40. In Native Mode - PL/SQL code is compiled into ______ ______.
definer's
HTF
machine code
integer

41. Fundamental: One of the 4 types of lexical units: d_____
delimiters
system global area
is certain
return value

42. Mode designation for an input/output parameter.
column
UTL_HTTP
Web Toolkit
IN OUT

43. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
server
SQLPlus
raise_application_error
OPEN

44. In a FORALL loop - you (can / cannot) use the index variable for purposes other than indexing into the collection.
concatenation
after
r t.%ROWTYPE;
cannot

45. Exceptions: You can pass an ______ ________ to SQLERRM.
autonomous triggers
information that changes infrequently
REF_CURSOR
error code

46. The variable showing the user id that is automatically prepended to object names.
OTHERS
cannot
CURRENT_USER
%TYPE

47. Types: ________ data types have internal components.
inclusive
composite
INTO
bb:true

48. Exceptions: An unhandled exception ________ to the enclosing block.
elements
delimiters
propagates
elements - type

49. Declare an input/output NUMBER parameter - v3.
view
EDITION_NAME
v3 IN OUT NUMBER
v1 OUT number default 10

50. Bytecode is _______ _______ code.
cached
ordinary
v2 IN NUMBER default 20
virtual machine