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. Triggers: When using the NEW or OLD pseudo-records in the body of the trigger - they must be prefixed with a _______.
cursor c RETURN rC;
colon
not the same
error

2. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
native dynamic
composite
RETURN
compiles

3. Into nested table ex - put elements of tbl1 that are not in tbl2.
cursor number
ex := tbl1 MULTISET EXCEPT tbl2
implicit
composite

4. What are the two sequence pseudo-columns?
alter procedure
currval and nextval
DBMS_PREPROCESSOR
:=

5. Collections are one of the two kinds of PL/SQL _______ data types.
scalable
composite
memory
v1 OUT number default 10

6. Cursors: The type definition of a WEAK ref cursor type has no _______ clause.
FETCH
implicit cursor
current
RETURN

7. What are the starting and ending keywords of a simple loop?
identifiers
DBMS_PREPROCESSOR
1
LOOP - END LOOP

8. Triggers: The clause to make a trigger run after inserting into table EMPS.
aliases
implicit
buffer
after insert on emps

9. Cursors: A cursor that lives in session memory until the session ends.
SQL cursor
FGA
network
session cursor

10. A user-defined TYPE can have...
PL/SQL Gateway
an AUTHID property
TRUE - FALSE and NULL
ADT

11. Where an external subprogram is stored.
in the database
executable
explicit
predefined

12. What are the 3 values of the BOOLEAN data type?
oname
5
TRUE - FALSE and NULL
cannot

13. When an autonomous routine begins execution - the main transaction is ________.
$END
buffer
suspended
TOO_MANY_ROWS

14. Advantage: Having procedures stored and executed on the server makes PL/SQL ________.
$$PLSQL_LINE
cannot
scalar
scalable

15. Cursors: Statement to end a loop when MyCur is used up: _____ ______ MyCur%NOTFOUND.
flushed
EXIT WHEN
enabled
ALTER SESSION

16. The $ERROR directive is used to make compilation ____.
comments
placeholders
fail
FETCH

17. Fundamental: Which one of these is a compound symbol: = - :=
inquiry
ALTER SESSION
:=
cursor

18. I/O: dbms_output.put_line puts output into a _______.
buffer
VARRAYs
WHEN
raise_application_error

19. Cursors: An implicit cursor is also called a/n ________ ________ -
SQL cursor
v3 IN OUT NUMBER
elements
FORALL i IN MyList.FIRST..MyList.LAST

20. To insert or update on a table that has a collection column - you have to have _______ privilege on the collection type.
virtual machine
error code
EXECUTE
type T is ref cursor

21. With static typing - type checking is done at ______ time.
1
LOOP
hardware
compile

22. Delimiters: Host variable indicator.
aggregate - analytical
cached
is
...

23. Fundamental: What are the 3 compound symbols for 'not equal to'?
<> - != - ^=
optional
dynamic
searched

24. Exceptions: The ________ handler must always be the last handler.
OTHERS
SQLCODE
REF CURSOR
RETURN

25. Exceptions: Pragma that links an exception to an Oracle error code.
DBMS_CRYPTO
server
EXCEPTION_INIT
definer's

26. If a procedure fails - an OUT parameter passed to it by value (will / will not) be passed back with changes.
collections - records
PRAGMA EXCEPTION_INIT (E - -1);
will not
anonymous

27. Exceptions: You can pass an ______ ________ to SQLERRM.
error code
forward
:=
package

28. (from inside a package) Declare a function f - that takes a number n - returns a number - and caches results - depending on table EMPS.
function f (n number) return number result_cache relies_on (emps);
p.v
is not
transaction

29. In a sparse collection - the indexes of defined elements (do / do not) run in consecutive order.
network
not the same
INSTEAD OF
do not

30. Loops: What is the index range operator in a FOR loop?
create
code
raise_application_error
..

31. The Wrap utility ________ the source code.
%ISOPEN.
name-value
obfuscates
lower sub-block

32. Unlike the CASE statement - the CASE expression does not have the...
session cursor
END CASE
implicit CASE_NOT_FOUND exception
DATE

33. An ordinary character literal is composed of characters in the ________ character set.
before insert or update on emps
portable
searched
database

34. A kind of variable used as a placeholder.
RELIES_ON
comments
versions
bind

35. Exceptions: An exception in the declarations section does not go to the __________ section.
FORALL i IN MyList.FIRST..MyList.LAST
after insert on emps
exception
anonymous block

36. Declare an input/output NUMBER parameter - v3.
aliases
an autonomous routine
v3 IN OUT NUMBER
EXIT

37. Declare an input NUMBER parameter - v2 - that defaults to 20.
execute
user-defined
v2 IN NUMBER default 20
view

38. Searchable CASE statement: Each WHEN has its own _______ condition.
optional
boolean
bind
decimal

39. The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
RETURN
include
CURSOR - RETURN
inquiry

40. Pkgs: This procedure de-instantiates/resets all packages in this session.
STANDARD
%ROWTYPE
source
DBMS_SESSION.RESET_PACKAGE

41. Exceptions: The ________ function returns the current error code.
SQLCODE
REF CURSOR
commit - rollback
CURSOR - RETURN

42. Define a nested table type - tN - which is a table NUMBERs.
closed
WHEN
drop procedure
TYPE tN IS TABLE of NUMBER

43. Value of a character literal with zero characters.
label

REF CURSOR
IN OUT

44. Records are one of the two kinds of PL/SQL _______ data types.
..
composite
EXECUTE IMMEDIATE
create procedure

45. Static typing is also called _____ typing.
DBMS_DDL
identifiers
block
strong

46. A national character literal is composed of characters in the ________ character set.
DML
national
anonymous
CHAR - VARCHAR2

47. Value of PLSQL_OPTIMIZE_LEVEL that prevents optimization.
...
FALSE
%BULK_ROWCOUNT
1

48. Cursors: If no SELECT or DML statement has run - the value of SQL%ROWCOUNT is what?
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

statement
create procedure

49. A CASE expression ends with _____.
DATE
255
END
raise_application_error

50. With a result-cached function - it uses a cached value when the function is called again with the...
transaction
manageable
same arguments
PVM