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: SYS_REFCURSOR is a predefined type based on _____ ______.
REF CURSOR
optional
fail
PLS_INTEGER

2. How many PL/SQL collection types are there?
CURRENT_USER - DEFINER
1
3
object

3. A pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
L.V
cursor
FORALL i IN MyList.FIRST..MyList.LAST
FOR UPDATE

4. Assigning values to PL/SQL variables that appear in SQL statements is called _______.
RELIES_ON
binding
definer's
package

5. Exceptions: The ________ handler must always be the last handler.
OTHERS
fetch cv into myvar
SQL
not a number

6. I/O: The buffered output of dbms_output.put_line is retrieved by _______.
SQLPlus
$END
FOR UPDATE
2

7. Declare a package procedure P with no arguments - and give it definer's rights.
literal
procedure P AUTHID definer;
FGA
WHEN OTHERS THEN

8. In Native Mode - PL/SQL code is compiled into ______ ______.
machine code
names
DBMS_SESSION.RESET_PACKAGE
versions

9. A subprogram created inside a block is a _________ subprogram.
finishes
sequence pseudo-columns
obfuscates
nested

10. What is the exception that is implicitly raised in a CASE statement with no ELSE clause?
associative
CASE_NOT_FOUND
VARRAY - Nested table
SQLERRM

11. The best candidates for result-caching are functions that are invoked frequently but depend on __________________ or never.
VARRAYs
information that changes infrequently
fetch C into V
cannot

12. To insert or update on a table that has a collection column - you have to have _______ privilege on the collection type.
query
predefined
EXECUTE
LOOP - END LOOP

13. A unit with AUTHID = DEFINER is a ________ rights unit.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

14. A null string and a BOOLEAN null are _______ (same/not the same).
NDS
With a letter.
machine
not the same

15. A GOTO cannot branch into a...
associative
lower sub-block
BINARY_INTEGER
commit - rollback

16. Setting CURRENT_SCHEMA only changes what is prepended to __________ references.
cursor c RETURN rC;
unqualified
EXIT WHEN
binding

17. Define a record type REC with number num and varchar2(10) name.
elsif
definer's
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
ordinary

18. Advantage: PL/SQL runs anywhere Oracle runs - so it is very ________.
portable
30
DBMS_OUTPUT
currval and nextval

19. IDs: How an ordinary user-defined identifier must begin.
With a letter.
*/
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
RETURN

20. A large OUT or IN OUT parameter will waste a lot of space and CPU if it is passed by _____.
**
SQLPlus
value
external subprogram

21. Dbms_sql: The return type DBMS_SQL.open_cursor.
OUT
integer
error code
Oracle

22. Advantage: PL/SQL programs are stored centrally - making them more ______.
GET_LINES
WHEN OTHERS THEN
manageable
USER_PROCEDURES

23. With Edition-based Redefinition enabled - the unique specification of an object requires OWNER - OBJECT_NAME and ___________.
EDITION_NAME
source
an AUTHID property
CHAR - VARCHAR2

24. Are good for logging table DML operations.
timing
package
autonomous triggers
DECLARE - BEGIN - EXCEPTION - END

25. Cursors: Define a ref cursor type called T.
nested
EXIT
type T is ref cursor
exception handler

26. I/O: Max number of bytes DBMS_OUTPUT can handle in a session.
1 million
ROLLBACK
REF CURSOR
OTHERS

27. Exceptions: If there is no handler for an exception - PL/SQL returns an ________ exception error to the invoker or host environment.
function f (n number) return number result_cache relies_on (emps);
is certain
unhandled
CHARARR

28. Dbms_sql: DBMS_SQL package is compiled with _______ rights.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

29. I/O: The PL/SQL I/O packages (can/cannot) accept input directly from the keyboard.
cannot
system global area
procedure - function
*/

30. Cursors: WHERE _______ ______ makes an UPDATE or DELETE apply to the current row of a cursor.
CURRENT OF
.
TYPE tN IS TABLE of NUMBER
EXTEND

31. Searchable CASE statement: Each WHEN has its own _______ condition.
boolean
BINARY_INTEGER
open C(p)
identifiers

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

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

33. Which collection type is bounded?
END
...
varray
:=

34. Types: The PL/SQL type that is identical to PLS_INTEGER.
compilation
BINARY_INTEGER
PLS_INTEGER
PLSQL_CCFLAGS

35. The EXCEPTION section is (optional/mandatory).
not a number
create procedure
optional
injection

36. VARRAYs are (always / never) sparse.
package
compilation
non-editioning views
never

37. Cursors: The first two keywords of a basic fetch statement.
SQL
FETCH - INTO
USER_PROCEDURES
do not

38. Set flag bb true: ALTER SESSION SET PLSQL_CCFLAGS= '____'
system global area
bb:true
suspended
autonomous triggers

39. What is the simplest statement for conditionally getting out of simple loop?
EXIT WHEN
associative
aggregate - analytical
create procedure

40. I/O: This DBMS_OUTPUT proc retrieves one line from the buffer.
database
GET_LINE
LOOP - END LOOP
anonymous

41. I/O: This DBMS_OUTPUT proc retrieves multiple lines from the buffer.
TYPE rec IS RECORD (num NUMBER - name VARCHAR2(10));
GET_LINES
SUBTYPE numb IS number;
aliases

42. The LIMIT method is only meaningful for _______.
is
cursor
VARRAYs
UTL_HTTP

43. The 4 keywords in the $IF compiler directive.
not the same
TRUE - FALSE - NULL
FGA
$IF $THEN $ELSE $END

44. Triggers: Unlike a stored procedure - you can enable and _____ a trigger.
ENABLE
<<>>
disable
inquiry

45. I/O: This package handles HTTP.
True
Declaration
UTL_HTTP
lowest

46. %ROWTYPE gets the type of a table or _______.
RECORD OF ARRAYS
EXIT WHEN
cursor
Ordinary - Quoted.

47. The three possible values of a BOOLEAN literal.
error code
L.V
True - false or NULL.
:=

48. SIMPLE_INTEGER is like PLS_INTEGER - but it does not allow ____ values - and it does not raise overflow exceptions.
HTP
Keywords and predefined names.

BINARY_INTEGER

49. Types: Which type of arithmetic does PLS_INTEGER use - library or hardware?
IN OUT
CREATE TYPE
can
hardware

50. Exceptions: When an exception is raised inside a cursor FOR loop - the cursor is _______ implicitly.
placeholders
closed
for each row
SQLCODE