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. You can use the BULK COLLECT INTO clause in a SELECT or a ______.
<> - != - ^=
create
invoker's
FETCH

2. An incomplete type is a type created by a ______ type definition.
same
machine code
cursor
forward

3. Exceptions: Declare an exception called E.
STANDARD
reference
pls_integer
E Exception;

4. A __________ can have an AUTHID property
Reserved words.
4K
package
cursor c RETURN rC;

5. A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
anonymous block
lock
integrated
statement

6. You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
will not
PSP
DBMS_PIPE
RECORD OF ARRAYS

7. You can have many autonomous transactions in ___________.
block
DR
CASE_NOT_FOUND
an autonomous routine

8. Exceptions: You can pass an ______ ________ to SQLERRM.
COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION
pls_integer
error code
return value

9. Fundamental: Which one of these is a compound symbol: = - :=
'
:=
Reserved words.
cursor c RETURN rC;

10. The DECLARE keyword is only required in an _______ _______.
anonymous block
%
system global area
optional

11. Value of PLSQL_OPTIMIZE_LEVEL that prevents optimization.
v3 IN OUT NUMBER
inquiry
1
UTL_HTTP

12. Data dictionary view giving the AUTHID values on a user's objects.
dense
USER_PLSQL_OBJECT_SETTINGS
True
USER_PROCEDURES

13. When a session starts - CURRENT_SCHEMA has the value of the schema owned by ___________.
cannot
SESSION_USER
aliases
statement

14. Advantage: PL/SQL runs anywhere Oracle runs - so it is very ________.
GET_LINE
cursor
DBMS_PREPROCESSOR
portable

15. Wrapped code (is / is not) not backward compatible.
PL/SQL Gateway
is not
SYS_REFCURSOR
inquiry

16. Triggers: By default - a trigger is created in the ______ state.
optional
enabled
open cv for
virtual machine

17. Fundamental: One of the 4 types of lexical units: d_____
CURRENT OF
injection
delimiters

18. The two main parts of a package are the ____ and the _____.
DR
PRAGMA AUTONOMOUS_TRANSACTION;
specification - body
never

19. The statement that is used to execute most (native) dynamic SQL.
predefined
execute immediate
explicit
package

20. Declare a constrained subtype of NUMBER called N that is not null.
lock
HTP
SUBTYPE n IS number not null;
Ada

21. Bytecode is _______ _______ code.
virtual machine
same arguments
an AUTHID property
plb

22. Cursors: If an exception is raised inside a cursor loop - the cursor (is / is not) also closed automatically.
is
literal
OLD
3

23. Cursors: Opening keyword of a cursor FETCH loop.
%FOUND - %ISOPEN - %NOTFOUND - %ROWCOUNT
LOOP
timing
RETURN

24. NAN stands for _________.
not a number
cannot
procedure P AUTHID current_user;
inquiry

25. With Edition-based Redefinition enabled - the unique specification of an object requires OWNER - OBJECT_NAME and ___________.
aggregate - analytical
CREATE TYPE
EDITION_NAME
1

26. For an associative array - the FIRST method gets the (lowest / first-created) index.
lowest
CURRENT_USER - DEFINER
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
IF - CASE or LOOP

27. PLS_INTEGER has its arithmetic implemented in ________ - so it is very fast.
hardware
return value
current transaction
@

28. Triggers: In a trigger - you cannot issue a COMMIT or a ______.
ROLLBACK
native dynamic
DBMS_DDL
ordinary

29. _________ exceptions can not be trapped with an error handler
PSP
national
Declaration
OUT

30. Cursors: Only a _____ _____ cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement.
bytecode
CURSOR - RETURN - IS
index
FOR UPDATE

31. Subprograms are stored in __________ form.
INSTEAD OF
executable
savepoint ab
cannot

32. PL/SQL is a ________ typed programming language.
END IF
name and datatype
CLOSE_CURSOR
statically

33. Cursors: A session cursor that is automatically created and managed by PL/SQL.
fine-grained auditing
implicit cursor
1
DECLARE - BEGIN - EXCEPTION - END

34. Nested tables are multisets - meaning that there is no inherent _____ of their elements.
order
FOR - IN
database
invalid

35. Delimiters: Item separator.
cannot
CHAR - VARCHAR2
RETURN

36. Cursors: A cursor variable's type must be a ____ ______ type.
an enclosing block
machine
REF CURSOR
can

37. _____ arrays cannot be used as the type of an Oracle table column.
associative
ENABLE
dynamic
constrained

38. Into nested table ex - put elements of tbl1 that are not in tbl2.
ex := tbl1 MULTISET EXCEPT tbl2
composite
SQL
NEW

39. The best candidates for result-caching are functions that are invoked frequently but depend on __________________ or never.
transaction
explicit cursor
information that changes infrequently
will not

40. I/O: This package handles HTTP.
32 -000
enabled
UTL_HTTP
placeholders

41. Bulk: The keywords of a bulk collect FETCH statement.
WHEN
elements
UTL_SMTP
FETCH - BULK COLLECT INTO

42. Clause added to a cursor to use it with WHERE CURRENT OF.
:=
explicit
for update of
NUMBER

43. Web: PL/SQL _____ _______ lets you to develop Web applications made of PL/SQL procedures.
statically
*/
block
Web Toolkit

44. After the RESULT_CACHE clause - you can put a _________ clause to enable dependency-checking.
RELIES_ON
cannot
constructor
cursor number

45. The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
definition
after insert on emps
exit when cgo%NOTFOUND
exception

46. DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
v3 IN OUT NUMBER
STANDARD
compiles
inclusive

47. Exceptions: An exception in the declarations section does not go to the __________ section.
lower sub-block
INTO
exception
DBMS_CRYPTO

48. What are the 3 values of the BOOLEAN data type?
TRUE - FALSE and NULL
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
nested tables - associative arrays
nested

49. In the IF syntax - is if ENDIF or END IF?
END IF
optional
CURSOR - RETURN - IS
:=

50. Cursors: Give the statement for getting out of a FETCH loop for cursor CV.
elements - type
cursor c;
EXIT WHEN cv%NOTFOUND
bytecode