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. I/O: This package lets two or more sessions in the same instance communicate.
not a number
simple and compound symbols
DBMS_PIPE
FOR UPDATE

2. Pkgs: The _______ package gives access to such SQL things as ALTER SESSION.
for each row
create procedure
DBMS_SESSION
BOOLEAN

3. By default - OUT and IN OUT parameters are passed by _____.
Gateway
value
same arguments
cursor

4. Exceptions: Write the call (2 args) to associate message 'Bad' with error code -20 -100.

5. Static typing is also called _____ typing.
-
r t.%ROWTYPE;
strong
inquiry

6. An ordinary character literal is composed of characters in the ________ character set.
a number := 10
database
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
do not

7. Type: A __________ subtype has only a subset of the values of its base type.
constrained
cursor
SQL cursor
COUNT

8. Types: One of the 4 PL/SQL things that have a data type (begins with 'p').
parameter
non-editioning views
select
fine-grained auditing

9. Cursors: An explicit cursor (can/cannot) accept parameters.
EXIT WHEN
unconstrained
for each row
can

10. The four keywords that define a PL/SQL block.
EXIT
DECLARE - BEGIN - EXCEPTION - END
USING
PVM

11. ______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
static
after insert on emps
buffer
FETCH - BULK COLLECT INTO

12. Exceptions: Type ______ to re-raise the current exception.
RAISE
EXECUTE IMMEDIATE
hardware
finishes

13. _______ arrays can be sparse or dense - depending on how you fill them.
RETURN
plb
exception
associative

14. Cursors: Give the statement to open cursor C that takes parameter p.
open C(p)
EXECUTE IMMEDIATE
REF_CURSOR
an enclosing block

15. From a block nested inside a procedure P - reference variable V of the outer procedure.
p.v
mandatory
cannot
manageable

16. Types: ______ data types are those storing values with no internal components.
associative
slower
is not
scalar

17. Cursors: Which cursor attribute is not meaningful for an implicit cursor?
REUSE SETTINGS
PUT
%ISOPEN.
PRAGMA EXCEPTION_INIT (E - -1);

18. What is the exception that is implicitly raised in a CASE statement with no ELSE clause?
CASE_NOT_FOUND
array
sql
null string

19. List the transaction control statements.
cannot
pls_integer
GET_LINE
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

20. I/O: This package handles HTTP.
UTL_HTTP
PVM
exception handler
error code

21. Cursors: What is the 'name' of the implicit cursor?
Keywords and predefined names.
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
SQL
definer's

22. The meaning of 'FGA'.
cannot
fine-grained auditing
VARRAY - Nested table
%BULK_ROWCOUNT

23. Types: One of the scalar data types specific to PL/SQL: R_________.
bind
REF_CURSOR
propagates
delimiters

24. Cursors: The index variable in a cursor FOR loop (does/does not) need to be declared.
SQL
does not
END IF
alter procedure

25. Triggers: In a per-row trigger - the ____ record shows you the original state of an affected row.
OLD
SQL
base
execute immediate

26. What clause makes a compilation default to the PLSQL_CCFLAGS settings from the last compilation?
REUSE SETTINGS
SQL cursor
numeric and cursor
L.V

27. Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
server
<<>>
CHAR - VARCHAR2
FORALL and BULK COLLECT

28. The allowed data types of user-defined conditional compilation directives are boolean and _______.
RAISE
pls_integer
index
IF - CASE or LOOP

29. Types: PL/SQL has all the _____ data types.
ROLLBACK
cannot
-
SQL

30. How to reference a variable V declared in a block labelled L: ____.
ex := tbl1 MULTISET EXCEPT tbl2
PRAGMA AUTONOMOUS_TRANSACTION;
SQLCODE
L.V

31. Triggers: You (can/cannot) explicitly invoke a trigger.
cannot
DBMS_SESSION.RESET_PACKAGE
bind arguments
index

32. Cursors: Give the statement to fetch data from cursor variable CV into MyVar.
fetch cv into myvar
deletions
simple and compound symbols
SYS_CONTEXT

33. Exceptions: The _________ package defines the PL/SQL environment.
3
=>
$END
STANDARD

34. Delimiters: the attribute indicator.
a number := 10
%
-
SESSION_USER

35. The first SQL statement in an autonomous routine begins a _______.
DML
transaction
does not
HTF

36. A large OUT or IN OUT parameter will waste a lot of space and CPU if it is passed by _____.
512
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
value
STANDARD

37. Dbms_sql: The integer returned by OPEN_CURSOR is a _____ _____.
INSTEAD OF
machine code
fields
cursor number

38. Bulk: The implicit cursor attribute that gives the number of rows affected by each DML statement in a FORALL statement.
PL/SQL Gateway
%BULK_ROWCOUNT
compile
context

39. The exception raised when a SELECT INTO statement returns more than one row.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
close C
30
TOO_MANY_ROWS

40. The 4 keywords in the $IF compiler directive.
EXIT WHEN
$IF $THEN $ELSE $END
5
SUBTYPE n IS number not null;

41. When a parameter is passed by reference - changes made to it inside the procedure cannot be undone. True/False.
strong
True
SQLERRM
$IF

42. DynSQL: The clause that supplies bind arguments to EXECUTE IMMEDIATE.
PUT_LINE
v3 IN OUT NUMBER
USING
an AUTHID property

43. Give the statement to make a program autonomous.
a number := 10
PRAGMA AUTONOMOUS_TRANSACTION;
:=
FETCH

44. You can use the BULK COLLECT INTO clause in a SELECT or a ______.
FETCH
order
numeric and cursor
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

45. Types: Which type of arithmetic does NUMBER use - library or hardware?
base
variable
library
5

46. IDs: The two types of words that can - but should not - be used as user-defined identifiers.
context
Keywords and predefined names.
source
RETURN

47. Advantage: Having procedures stored and executed on the server makes PL/SQL ________.
FOR i IN REVERSE 1..10 - LOOP - NULL; END LOOP;
define
scalable
%ROWTYPE

48. Cursors: If you use an EXIT statement to exit a cursor FOR loop prematurely - the cursor (is / is not) closed automatically.
cannot
boolean
rollback to ab
is

49. The BETWEEN operator is (inclusive / exclusive).
null string
external subprogram
inclusive
True

50. Cursors: What implicit cursor attribute gives row counts for a FORALL statement?
enclosing
commit - rollback
%BULK_ROWCOUNT
value