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. Cursors: You can just declare a cursor at first - then _______ it later.
define
concatenation
enabled
implicit

2. If you are in a procedure inside a package - the compilation unit is the _______.
raise_application_error
follows
name and datatype
package

3. In a collection - the internal components always have the _____ data type.
numeric and cursor
bytecode
EVERE - PERFORMANCE - INFORMATIONAL
same

4. I/O: This package sends text output to SQLPlus
associative
constructor
DBMS_OUTPUT
$END

5. In a record - the internal components are called ______.
CURSOR - RETURN
fields
selection
ALTER SESSION

6. In the special IF syntax - is it ELSIF - or ELSEIF?
REUSE SETTINGS
CURSOR - RETURN - IS
elsif
=>

7. You (can/cannot) put a label before an END LOOP statement.
never
cannot
enclosing
DBMS_SESSION

8. In interpreted mode - the PL/SQL VM interprets the _________.
stored
REUSE SETTINGS
bytecode
$IF

9. In Native Mode - PL/SQL code is compiled into ______ ______.
simple and compound symbols
database
machine code
lower sub-block

10. Fundamental: One of the 4 types of lexical units: c_____.
%BULK_ROWCOUNT
sequence pseudo-columns
explicit
comments

11. Fundamental: What is the range operator?
compilation
Web Toolkit
ex := tbl1 MULTISET EXCEPT tbl2
..

12. Abbreviation for Abstract Data Type.
drop procedure
statement
ADT
-

13. To run DDL statements - a trigger must be autonomous and must use ______ ______ SQL.
searched
native dynamic
placeholders
-

14. With conditional compilation you can avoid compiling debug code in the _______ environment.
value
:=
strong
production

15. The _______ package lets you wrap dynamically generated PL/SQL.
<<>>
CASE_NOT_FOUND
calls
DBMS_DDL

16. A kind of variable used as a placeholder.
dbms_output.put_line
anonymous block
cursor
bind

17. I/O: The string table type in DBMS_OUTPUT for holding lines of text.
production
CHARARR
DML
Declaration

18. A value that is neither represented by an identifier nor calculated from other values.
does not
literal
dynamic
transaction

19. Delimiters: Host variable indicator.
FALSE
512
...
VARRAYs

20. The meaning of 'FGA'.
PRAGMA EXCEPTION_INIT (E - -1);
preprocessor
mandatory
fine-grained auditing

21. PL/SQL is a ________ typed programming language.
end
set serveroutput on
statically
VARRAYs

22. I/O: To make sure the DBMS_OUTPUT buffer will be flushed - put a WHEN ______ clause in the EXCEPTION section of your outer block.
USING
colon
OTHERS
column

23. Cursors: The SQL%FOUND attribute has how many possible values?
raise_application_error
PRAGMA AUTONOMOUS_TRANSACTION;
EXECUTE IMMEDIATE
3

24. Web: mod_plsql is an implementation of the PL/SQL _________.
OTHERS
CURSOR - RETURN
Gateway
context

25. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
server
preprocessor
.
@

26. The attribute used to get the type of a row without knowing what it is.
Reserved words.
explicit cursor
SESSION_USER
%ROWTYPE

27. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
subtype
ROLLBACK
OLD
propagates

28. Name the two types of FOR loop.
OPEN_CURSOR
CURRENT OF
numeric and cursor
-20 -000

29. A basic PL/SQL datatype that SQL does not have.
not the same
explicit
boolean
exception handler

30. Conversion: Just about everything will implicitly convert to _____ or _________.
INTO
PLS_INTEGER
TYPE tN IS TABLE of NUMBER
CHAR - VARCHAR2

31. Value of a character literal with zero characters.
name and datatype
never

cursor c;

32. Collections are one of the two kinds of PL/SQL _______ data types.
IN - OUT - IN OUT
unqualified
UTL_SMTP
composite

33. Advantage: Performance: Stored subprograms are ______ in memory.
stored
definition
cursor
cached

34. Type: A __________ subtype has only a subset of the values of its base type.
autonomous transaction
identifiers
..
constrained

35. Give the first line of the FOR loop with index i going from 1 to 10 in reverse.
FOR i IN REVERSE 1..10 LOOP
CASE_NOT_FOUND
...
DBMS_DDL

36. After the RESULT_CACHE clause - you can put a _________ clause to enable dependency-checking.
RELIES_ON
cursor
static
commit - rollback

37. Cursors: The Oracle predefined REF CURSOR type.
enabled
SYS_REFCURSOR
forward
same arguments

38. You can define PL/SQL collections using TYPEs defined in the _______.
searched
database
name
1

39. Define a collection type - tV - which is a varray of 100 NUMBERs.
USER_PROCEDURES
Ordinary - Quoted.
TYPE tV IS VARRAY(100) OF NUMBER
SUBTYPE n IS number not null;

40. Pkgs: This procedure de-instantiates/resets all packages in this session.
Abstract
DBMS_SESSION.RESET_PACKAGE
an autonomous routine
**

41. A CASE expression ends with _____.
compile
IS
cannot
END

42. If none of a CASE expression's conditions are TRUE - it returns _____.

definer's
object
anonymous block

43. Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
propagates
non-editioning views
scalable
ALTER SESSION

44. IDs: Predefined identifiers are declared in this package.
DBMS_SESSION.RESET_PACKAGE
unconstrained
STANDARD
boolean

45. Delimiters: Statement terminator.
non-editioning views
constant
FORALL and BULK COLLECT
;

46. The first SQL statement in an autonomous routine begins a _______.
binding
transaction
names
PRAGMA EXCEPTION_INIT (E - -1);

47. An editioning view can only select a subset of the columns from the base table and provide ________ for them
aliases
..
NEW
select

48. Internally - the NUMBER type is stored as ______. Therefore - decimal numbers (such as prices) are stored exactly - with no rounding.
dynamic
timing
placeholders
decimal

49. Triggers: In a per-row trigger - the ____ record shows you the original state of an affected row.
REF_CURSOR
VARRAYs
inquiry
OLD

50. Types: One of the scalar data types specific to PL/SQL: R_________.
expression
FOR i IN REVERSE 1..10 LOOP
REF_CURSOR
.