Upgrade to remove ads
Review of information about Oracle PL/SQL, for taking the Oracle certification exam.
Terms in this set (543)
IDs: Maximum number of bytes in a user-defined identifier.
IDs: The type of words that cannot be user-defined identifiers.
Keywords and predefined names.
IDs: The two types of words that can, but should not, be used as user-defined identifiers.
A value that is neither represented by an identifier nor calculated from other values.
IDs: List the two types of user-defined identifiers.
#, $, _
IDs: The 3 non-alphanumeric characters that can be in an ordinary user-defined identifier.
With a letter.
IDs: How an ordinary user-defined identifier must begin.
IDs: What a quoted user-defined identifier is enclosed in.
A PL/SQL block that is not stored.
Java, C or other program called directly in PL/SQL.
in the database
Where an external subprogram is stored.
Web: The gateway that allows a web browser to invoke a PL/SQL procedure through an HTTP listener.
Web: Abbreviation of PL/SQL Server Page.
In PL/SQL, what the variables in DML statements are turned into.
Two types of stored subprograms.
Command used to execute dynamic SQL.
The attribute used to get the type of a column or variable without knowing what it is.
The attribute used to get the type of a row without knowing what it is.
Kind of datatypes that support object-oriented programming in PL/SQL.
Abbreviation for Abstract Data Type.
An anonymous block is a(n) ____ statement.
A basic PL/SQL datatype that SQL does not have.
True, false or NULL.
The three possible values of a BOOLEAN literal.
Value of a character literal with zero characters.
Term for a character literal with zero characters.
not the same
A null string and a BOOLEAN null are _______ (same/not the same).
An ordinary character literal is composed of characters in the ________ character set.
A national character literal is composed of characters in the ________ character set.
Characters that begin a single-line comment.
Characters that begin a multi-line comment.
Characters that end a multi-line comment.
Kind of comment not to put in a PL/SQL block to be processed dynamically by an Oracle Precompiler program.
vname CONSTANT VARCHAR2 := 'x';
Declaration of a varchar2 constant, called vname, with value 'x'.
Keyword for doing assignment to a variable with SELECT.
Command to assign to variables from a cursor.
A subprogram created inside a block is a _________ subprogram.
Command to create a procedure.
Command to delete a procedure.
Subprograms are stored in __________ form.
The declaration section is (optional/mandatory).
The BEGIN section is (optional/mandatory)
The EXCEPTION section is (optional/mandatory).
Command to make a no-op (omit semicolon).
The DECLARE keyword is only required in an _______ _______.
The valid values of the AUTHID property.
The default value of the AUTHID property.
Data dictionary view giving the AUTHID values on a user's objects.
A unit with AUTHID = CURRENT_USER is an ________ rights unit.
A unit with AUTHID = DEFINER is a ________ rights unit.
Abbreviation of invoker's rights
Abbreviation of definer's rights
An anonymous block always has _________ rights.
Triggers and views always have _________ rights.
Setting CURRENT_SCHEMA only changes what is prepended to __________ references.
When a session starts, CURRENT_SCHEMA has the value of the schema owned by ___________.
To get the current value of CURRENT_SCHEMA, CURRENT_USER, or SESSION_USER, use the ____ function.
Statement to set current schema to x: _____ _____ SET CURRENT_SCHEMA = 'x';
Advantage: PL/SQL is tightly _______ with Oracle SQL.
Advantage: PL/SQL gives SCALABILITY because stored procedures are executed on the _______.
Advantage: Having procedures stored and executed on the server makes PL/SQL ________.
Advantage: PL/SQL is PORTABLE to any OS where ________ runs.
Advantage: PL/SQL runs anywhere Oracle runs, so it is very ________.
The variable showing the user id of the session owner.
The variable showing the user id that is automatically prepended to object names.
DECLARE, BEGIN, EXCEPTION, END
The four keywords that define a PL/SQL block.
Types: The storage size of a NUMBER value is based on the number of ______ digits.
%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT
Cursors: The four cursor attributes, in alphabetical order.
Cursors: Which cursor attribute is not meaningful for an implicit cursor?
Cursors: What is the "name" of the implicit cursor?
Cursors: How many implicit cursors can you access?
Cursors: You cannot use cursor attributes in _______ statements.
Cursors: A cursor with a name is an _________ cursor.
Cursors: The SQL%FOUND attribute has how many possible values?
TRUE, FALSE, NULL
Cursors: What are the 3 possible values of SQL%FOUND and SQL%NOTFOUND?
Cursors: The value of SQL%ISOPEN is always ______.
Cursors: If no SELECT or DML statement has run, the value of SQL%ROWCOUNT is what?
Cursors: What implicit cursor attribute gives row counts for a FORALL statement?
Parameter that controls how much Oracle optimizes your PL/SQL code on compilation.
Value of PLSQL_OPTIMIZE_LEVEL that prevents optimization.
Default value of PLSQL_OPTIMIZE_LEVEL.
Advantage: Performance: Storing and runnng procedures on the server minimizes ______ traffic.
Advantage: Performance: PL/SQL automatically creates ______ variables for variables in WHERE and VALUES clauses.
PL/SQL does not automatically create bind variables in ________ SQL.
Advantage: Performance: Stored subprograms are ______ in memory.
Advantage: Caching of programs and sharing among users lowers _______ requirements.
Advantage: PL/SQL programs are stored centrally, making them more ______.
Advantage: PL/SQL supports ______-oriented programming.
Web: mod_plsql is an _______ module.
Web: PL/SQL _______ enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener.
Web: mod_plsql is an implementation of the PL/SQL _________.
Web: PL/SQL _____ _______ lets you to develop Web applications made of PL/SQL procedures.
Web: PL/SQL ______ ______ (PSPs) let you develop web pages with dynamic content.
Labels for blocks and statements are enclosed in "__" brackets.
Types of blocks: A pl/sql block that is not stored in the database.
Types of blocks: Procedures and functions are _______ blocks.
I/O: The most basic PL/SQL way to give output to the user.
I/O: dbms_output.put_line puts output into a _______.
I/O: The buffered output of dbms_output.put_line is retrieved by _______.
set serveroutput on
I/O: SQLPlus command for turning on buffered output.
Types: ______ data types are those storing values with no internal components.
Types: One of the 4 PL/SQL things that have a data type (begins with "c").
Types: ________ data types have internal components.
Types: A data type family consists of a ______ type and its subtypes.
Types: A ______ has the same valid operations as its base type.
Types: PL/SQL has all the _____ data types.
Types: In addition to the SQL data types, PL/SQL has ___ more scalar data types.
Types: One of the scalar data types specific to PL/SQL: b_____.
Types: One of the scalar data types specific to PL/SQL: P________
Types: One of the scalar data types specific to PL/SQL: B_______
Types: One of the scalar data types specific to PL/SQL: R_________.
not a number
NAN stands for _________.
I/O: This package lets two or more sessions in the same instance communicate.
I/O: This package has hypertext functions that generate HTML tags
I/O: This package has hypertext procedures that generate HTML tags
I/O: This package lets PL/SQL programs read and write OS files.
I/O: This package sends text output to SQLPlus
I/O: This package handles HTTP.
I/O: This package does email.
I/O: The PL/SQL I/O packages (can/cannot) accept input directly from the keyboard.
I/O: The DBMS_OUTPUT function that writes output without a newline character.
I/O: The DBMS_OUTPUT function that writes output WITH a newline character.
I/O: The DBMS_OUTPUT procedure that sets the size of the output buffer.
I/O: One of the 3 types of data that can be passed to the DBMS_OUTPUT buffer.
I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
I/O: One of the 3 types of data stored in the DBMS_OUTPUT buffer.
I/O: The output from DBMS_OUTPUT will display (during/after) program execution.
I/O: Max number of bytes DBMS_OUTPUT can handle in a session.
I/O: Max number of bytes to pass in a call to DBMS_OUTPUT.PUT_LINE.
I/O: This DBMS_OUTPUT proc retrieves one line from the buffer.
I/O: This DBMS_OUTPUT proc retrieves multiple lines from the buffer.
I/O: The string table type in DBMS_OUTPUT for holding lines of text.
I/O: The DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block _________.
I/O: If the current (outermost) PL/SQL block ends with an unhandled exception, the output buffer will not be ___________.
I/O: To make sure the DBMS_OUTPUT buffer will be flushed, put a WHEN ______ clause in the EXCEPTION section of your outer block.
Pkgs: This procedure de-instantiates/resets all packages in this session.
Pkgs: RESET_PACKAGE only takes effect after the outermost PL/SQL proc that called it ________.
Pkgs: The _______ package gives access to such SQL things as ALTER SESSION.
Fundamental: One of the 4 types of lexical units: d_____
Fundamental: One of the 4 types of lexical units: i_____
Fundamental: One of the 4 types of lexical units: L_____
Fundamental: One of the 4 types of lexical units: c_____.
Fundamental: identifiers (include/do not include) reserved words.
simple and compound symbols
Fundamental: Delimiters are _____ and _____ symbols.
Fundamental: Which one of these is a compound symbol: =, :=
Fundamental: Which one of these is a simple symbol: a, =, !=
Fundamental: What is the range operator?
Fundamental: What is the exponentiation operator?
<>, !=, ^=
Fundamental: What are the 3 compound symbols for "not equal to"?
Cursors: A session cursor that is automatically created and managed by PL/SQL.
Cursors: A cursor that lives in session memory until the session ends.
Cursors: A session cursor that a PL/SQL user creates and manages.
A pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
Cursors: An implicit cursor is also called a/n ________ ________,
The exception raised when a SELECT INTO statement returns more than one row.
Cursors: The value of SQL%ROWCOUNT after a SELECT INTO statement returns more than one row.
Cursors: You give an explicit cursor a name and associate it with a _______.
CURSOR, RETURN, IS
Cursors: The three keywords in a full cursor declaration/definition (before the SELECT).
Cursors: The two keywords in a mere cursor declaration (one is optional).
Cursors: You can just declare a cursor at first, then _______ it later.
Cursors: An explicit cursor (can/cannot) accept parameters.
Cursors: The statement where you pass the parameters to a cursor.
Cursors: The second keyword of a FETCH statement.
Cursors: The statement that closes a cursor.
Cursors: How many DML statements can a FORALL statement contain?
Cursors: The values plugged into the DML in a FORALL must come from existing, populated _________.
Bulk: The use of FORALL statements cuts down on ______ traffic.
Bulk: The implicit cursor attribute that gives the number of rows affected by each DML statement in a FORALL statement.
Cursors: The SQL%BULK_ROWCOUNT attribute is an _________,
Cursors: Opening keyword of a cursor FETCH loop.
Cursors: Closing keywords of a cursor FETCH loop.
Loops: What is the index range operator in a FOR loop?
Cursors: Statement to end a loop when MyCur is used up: _____ ______ MyCur%NOTFOUND.
Cursors: The first two keywords in a Cursor FOR loop.
Cursors: The first two keywords of a basic fetch statement.
What do you FETCH from?
FETCH, BULK COLLECT INTO
Bulk: The keywords of a bulk collect FETCH statement.
Cursors: A Cursor FOR loop does not use a ______ statement.
Cursors: The index variable in a cursor FOR loop (does/does not) need to be declared.
WHERE CURRENT OF
A PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE
Cursors: Only a _____ _____ cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement.
Cursors: The FOR UPDATE phrase goes at the (beginning/end) of the cursor definition.
Cursors: WHERE _______ ______ makes an UPDATE or DELETE apply to the current row of a cursor.
A SELECT statement with the FOR UPDATE clause puts a ______ on the selected rows.
Delimiters: The remote access symbol.
Delimiters: Single-line comment marker.
Delimiters: Statement terminator.
Delimiters: The left label delimiter.
Delimiters: the attribute indicator.
Delimiters: The association operator.
Delimiters: The assignment operator.
Delimiters: The component indicator/selector.
Delimiters: Host variable indicator.
Delimiters: Item separator.
IDs: Predefined identifiers are declared in this package.
IDs: You cannot use reserved words as ______ user-defined identifiers.
IDs: You (can/cannot) use keywords as ordinary user-defined identifiers.
IDs: You can use a reserved word as a _______ user-defined identifier.
Exceptions: PL/SQL exceptions are of two types: internal and _________.
Exceptions: User-defined exceptions must have _______.
Exceptions: Pragma that links an exception to an Oracle error code.
Exceptions: The _________ package defines the PL/SQL environment.
WHEN OTHERS THEN
Exceptions: The three words that can be used to guarantee all exceptions will be handled.
Exceptions: Type ______ to re-raise the current exception.
Exceptions: The keyword that begins an exception handler.
Exceptions: The ________ handler must always be the last handler.
Exceptions: When an exception is raised inside a cursor FOR loop, the cursor is _______ implicitly.
Exceptions: An exception in the declarations section propagates to the enclosing _______.
Exceptions: An exception in the declarations section does not go to the __________ section.
Exceptions: An exception raised inside an _______ _______ propagates immediately to the enclosing block
Exceptions: A GOTO statement cannot branch into an _______ ________.
Exceptions: You cannot GOTO from an exception handler back into the __________ block.
Exceptions: A GOTO statement CAN branch from an exception handler into an ________ block.
Exceptions: The maximum length of an Oracle error message is ____ characters
Exceptions: The ________ function returns the error message associated with an error code.
Exceptions: The ________ function returns the current error code.
Exceptions: You can pass an ______ ________ to SQLERRM.
Exceptions: If there is no handler for an exception, PL/SQL returns an ________ exception error to the invoker or host environment.
Exceptions: Declare an exception called E.
PRAGMA EXCEPTION_INIT (E, -1);
Exceptions: Associate exception E with error code -1.
Exceptions: Each internal exception has an Oracle ______ _______.
Exceptions: Some internal exceptions have ________ names.
Exceptions: An unhandled exception ________ to the enclosing block.
You can put a label before any executable PL/SQL _________.
A FOR-loop counter is defined only within the _____.
GOTO branches to a ________.
You (can/cannot) put a label before an END LOOP statement.
v2 IN NUMBER default 20
Declare an input NUMBER parameter, v2, that defaults to 20.
v1 OUT number default 10
Declare an output NUMBER parameter, v1, that defaults to 10.
v3 IN OUT NUMBER
Declare an input/output NUMBER parameter, v3.
What word is in a function header but not in a procedure header?
mode designation for an input parameter.
mode designation for an output parameter.
mode designation for an input/output parameter.
FORALL and BULK COLLECT
Bulk: The two features that comprise Bulk SQL are:
Assigning values to PL/SQL variables that appear in SQL statements is called _______.
The statement that is used to execute most (native) dynamic SQL.
A subprogram defined within another subprogram is a _______ subprogram.
DynSQL: Native Dynamic SQL
DynSQL: The main command for Native Dynamic SQL.
DynSQL: The clause that supplies bind arguments to EXECUTE IMMEDIATE.
DynSQL: The clause that receives single-row query output from EXECUTE IMMEDIATE.
DynSQL: The standard package that does dynamic SQL.
IN, OUT, IN OUT
DynSQL: The three modes of arguments in the USING clause of EXECUTE IMMEDIATE.
DynSQL: EXECUTE IMMEDIATE does both dynamic SQL and dynamic _______.
DynSQL: In EXECUTE IMMEDIATE, the statement string can use either placeholders, or string ________.
DynSQL: EXECUTE IMMEDIATE can be used for any SQL statement or PL/SQL block, except for ________ queries.
Fundamental: Character used to escape a single quote inside a literal.
DynSQL: Maximum string length to pass to EXECUTE IMMEDIATE.
Fundamental: Maximum string length of a PL/SQL variable.
DynSQL: The colon-prefixed names used in EXECUTE IMMEDIATE are called ________.
Exceptions: The built-in procedure for raising an application-defined error.
Exceptions: Start of the range of error codes for RAISE_APPLICATION_ERROR.
Exceptions: Write the call (2 args) to associate message 'Bad' with error code -20,100.
Exceptions: The package that defines pre-defined exceptions.
A kind of variable used as a placeholder.
Language that PL/SQL is derived from.
Official acronym for PL/SQL's runtime engine.
Kind of code created by the PL/SQL compiler.
SUBTYPE numb IS number;
Declare an unconstrained subtype of NUMBER called NUMB.
SUBTYPE n IS number not null;
Declare a constrained subtype of NUMBER called N that is not null.
Types: The PL/SQL type that is identical to PLS_INTEGER.
Types: Which type of arithmetic does PLS_INTEGER use, library or hardware?
Types: Which type of arithmetic does NUMBER use, library or hardware?
Types: The predefined subtype of PLS_INTEGER that has the NOT NULL constraint.
Types: The only PL/SQL type for which you can specify a range constraint.
Type: A __________ subtype has only a subset of the values of its base type.
Type: An ________ subtype has all the values of its base type.
Types: ________ data types have no internal components.
Cursors: The Oracle predefined REF CURSOR type.
type T is ref cursor
Cursors: Define a ref cursor type called T.
Cursors: The keyword that makes a ref cursor type STRONG.
Cursors: The type definition of a WEAK ref cursor type has no _______ clause.
Cursors: A cursor FOR loop cannot use a _______ variable.
Cursors: A pointer to a cursor is called a ______ _______.
Cursors: A cursor FOR loop can use an explicit or an _______ cursor.
open cv for
Cursors: Give the first 3 words to open a cursor variable called cv.
Cursors: A cursor variable's type must be a ____ ______ type.
Cursors: SYS_REFCURSOR is a predefined type based on _____ ______.
EXIT WHEN cv%NOTFOUND
Cursors: Give the statement for getting out of a FETCH loop for cursor CV.
fetch cv into myvar
Cursors: Give the statement to fetch data from cursor variable CV into MyVar.
fetch C into V
Cursors: Give the statement to fetch data from cursor C into variable V.
Cursors: Give the statement to close cursor C.
Cursors: Give the statement to open cursor C.
Cursors: Give the statement to open cursor C that takes parameter p.
TCL: Create a savepoint called ab
rollback to ab
TCL: Undo changes back to savepoint ab.
dbms_sql: DBMS_SQL package is compiled with _______ rights.
dbms_sql: This DBMS_SQL function opens a new cursor.
dbms_sql: The DBMS_SQL procedure that will execute a DDL or DML statement.
dbms_sql: The return type DBMS_SQL.open_cursor.
dbms_sql: The DBMS_SQL procedure that closes a cursor.
dbms_sql: The integer returned by OPEN_CURSOR is a _____ _____.
dbms_sql: A cursor opened with OPEN_CURSOR (can/cannot) be reused for multiple queries without first closing it.
fundamental: A label can only go directly before an _______ statement.
fundamental: Labels furnish targets for GOTO and _____ statements.
How to reference a variable V declared in a block labelled L: ____.
fundamental: A label can be used to name an ______ block.
triggers: In a trigger, you cannot issue a COMMIT or a ______.
triggers: Programmers (as opposed to DBAs) mostly make use of _____ triggers.
triggers: In a per-row trigger, the ____ record shows you the changed state of an affected row.
triggers: In a per-row trigger, the ____ record shows you the original state of an affected row.
triggers: Unlike a stored procedure, you can enable and _____ a trigger.
triggers: You (can/cannot) explicitly invoke a trigger.
triggers: You create a trigger with the _______ ________ statement.
triggers: By default, a trigger is created in the ______ state.
triggers: To simulate having triggers on SELECT statements, you can try ___ (3-letter acronym).
The meaning of "FGA".
Give the ANSI Date literal for December 25, 2005.
Types: The only Oracle datetime type that doesn't begin with TIMESTAMP is ________.
Triggers: There are __ types of triggers.
triggers: When using the NEW or OLD pseudo-records in the body of the trigger, they must be prefixed with a _______.
for each row
triggers: In a DML trigger, the phrase that makes the trigger run for every affected record.
triggers: If two or more triggers are defined with the same timing point, then you can control the firing order using the ________ clause.
triggers: A compound trigger can fire at more than one ______ point.
triggers: The code in a trigger is actually an ________ block.
after insert on emps
triggers: The clause to make a trigger run after inserting into table EMPS.
for each row
triggers: The clause to make a trigger run once for every affected row.
before insert or update on emps
triggers: The clause to make a trigger run before inserting or updating table EMPS.
triggers: INSTEAD OF triggers only run on ______ views.
triggers: An INSTEAD OF trigger is (always/never) a row-level trigger.
triggers: An INSTEAD OF trigger (can/cannot) be conditional.
An editioning view is like an ____ for a table.
An editioning view can only select a subset of the columns from the base table and provide ________ for them
triggers: On a non-editioning view, you can only define __________ triggers.
The AUTHID property applies to PL/SQL objects that contain _____.
Can a user-defined TYPE have an AUTHID property?
Can a package have an AUTHID property?
Types: One of the 4 PL/SQL things that have a data type (begins with "r").
Types: One of the 4 PL/SQL things that have a data type (begins with "p").
Types: One of the 4 PL/SQL things that have a data type (begins with "v").
The compiler directive governing conditional compilation.
Conditional compilation is useful for compiling a program under different ______ of Oracle.
With conditional compilation you can avoid compiling debug code in the _______ environment.
The PL/SQL ________ evaluates compiler directives.
How many types of compiler directives are there?
A type of compiler directive, begins with "s".
A type of compiler directive, begins with "i".
A type of compiler directive, begins with "e".
The compiler directive used to select which code to compile.
User-defined compiler directives are defined in this system compile parameter.
$IF $THEN $ELSE $END
The 4 keywords in the $IF compiler directive.
The inquiry directive that gives the current line number in the procedure.
The 2 characters that begin all inquiry directives.
The command used to populate PLSQL_CCFLAGS.
$$PLSQL_LINE and $$PLSQL_UNIT are ______-ly defined inquiry directives.
Data dictionary view that shows the compiler settings for each compiled object.
The $IF must evaluate a ______ _______ expression.
Native compilation turns bytecode into ________ code.
Bytecode is _______ _______ code.
In interpreted mode, the PL/SQL VM interprets the _________.
In interpreted mode, PL/SQL code is run in a _______ _______.
The PL/SQL virtual machine turns bytecode into system ______.
In Native Mode, PL/SQL code is compiled into ______ ______.
If you are in a procedure inside a package, the compilation unit is the _______.
Set flag bb true: ALTER SESSION SET PLSQL_CCFLAGS= '____'
PLSQL_CCFLAGS hold a comma-separated list of ______ pairs (hyphenated).
The values defined in PLSQL_CCFLAGS can be referenced as _____ directives.
The allowed data types of user-defined conditional compilation directives are boolean and _______.
To recompile a procedure, use the _____ ______ command.
The $ERROR directive is used to make compilation ____.
An $ERROR directive ends with _____.
Packaged constants can synchronize programs through use in _______ directives.
What clause makes a compilation default to the PLSQL_CCFLAGS settings from the last compilation?
Assignment of PLSQL_CCFLAGS (cannot / can) be included in ALTER PROCEDURE ... COMPILE ...
Package for getting the postprocessed source of a program.
The PL/SQL preprocessor passes ______ code to the compiler.
Conditional ______ allows backward compability with older database versions.
The _____ utility turns PL/SQL source code into unreadable text.
True encryption is done with package __________.
The Wrap utility ________ the source code.
The Wrap utility (does / does not) encrypt source code.
You (can / cannot) wrap trigger source code.
Wrapping can be done with the ____ executable.
The _______ package lets you wrap dynamically generated PL/SQL.
Wrapped code (is / is not) not backward compatible.
wrap ______=xxxx oname=zzzzz
wrap iname=xxxx _____=zzzzz
Default file extension of wrap's output file.
Default file extension of wrap's input file.
DBMS_DDL.WRAP returns a string containing wrapped _____ _____.
DBMS_DDL.CREATE_WRAPPED: wraps and _______ the source code passed to it.
The source for wrapping begins with the word ______.
_________ exceptions can not be trapped with an error handler
a number := 10
Declare a number variable, A, initialized to 10.
One good use of bind variables is to guard against SQL _______.
You have to use _______ SQL to run DDL statements n PL/SQL.
FORALL i IN MyList.FIRST..MyList.LAST
The FORALL statement to loop over a collection MyList using index i.
In a FORALL loop, you (can / cannot) use the index variable for purposes other than indexing into the collection.
exit when cgo%NOTFOUND
Statement to exit a cursor loop on cursor cGo when there are no more records.
system global area
A result-cached function caches its results in the _____ _____ ____ (SGA)
To enable result-caching for a function, use the _____________ clause.
After the RESULT_CACHE clause, you can put a _________ clause to enable dependency-checking.
The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never. (true/false)
The RESULT_CACHE clause has to be in both the declaration and in the ______ of the function.
The RESULT_CACHE clause comes (before / after) the RETURN clause.
With a result-cached function, it uses a cached value when the function is called again with the same arguments. True/False?
If a result-cached function's data source (based on the RELIES_ON clause) is updated, any cached results become _______.
From a block nested inside a procedure P, reference variable V of the outer procedure.
TYPE tN IS TABLE of NUMBER
Define a nested table type, tN, which is a table NUMBERs.
TYPE tV IS VARRAY(100) OF NUMBER
Define a collection type, tV, which is a varray of 100 NUMBERs.
TYPE tA IS TABLE OF NUMBER INDEX BY VARCHAR2(20)
Define a collection type, tA, which is an associative array of NUMBERS, indexed with varchar2(20) values.
To create a collection type, use the TYPE command in PL/SQL and the ______ _____ command in SQL.
A FOR loop can be indexed by numbers or by a ______.
How many PL/SQL collection types are there?
Which collection type is bounded?
nested tables, associative arrays
Which two collection types are unbounded?
VARRAY, Nested table
The _____ and ____ _____ collection types can be used in database table columns.
_____ arrays cannot be used as the type of an Oracle table column.
VARRAYs are (always / never) sparse.
_______ arrays can be sparse or dense, depending on how you fill them.
Nested tables can become sparse via ______.
Nested tables are multisets, meaning that there is no inherent _____ of their elements.
With an sparse, integer-indexed associative array, you (can / cannot) use a regular FOR loop.
In a sparse collection, the indexes of defined elements (do / do not) run in consecutive order.
The FIRST method gets the first (element / index) of a collection.
For an associative array, the FIRST method gets the (lowest / first-created) index.
For a collection, the NEXT method gets the next higher (element / index).
For a collection, the NEXT method returns ____ when there is no next index value.
The method to add one or more elements to a nested table or VARRAY is ______.
To use a TYPE declared in the database, a PL/SQL block has to have _______ privilege on the type.
A nested table has to be initialized using a ________, named the same as the type.
Associative arrays (do / do not) have to be initialized with a constructor.
ex := tbl1 MULTISET EXCEPT tbl2
Into nested table ex, put elements of tbl1 that are not in tbl2.
The EXTEND method is not used with ______ _______.
You can define PL/SQL collections using TYPEs defined in the _______.
Associative array types (can / cannot) be defined in the database.
TYPE rec IS RECORD (num NUMBER, name VARCHAR2(10));
Define a record type REC with number num and varchar2(10) name.
The NOCOPY option on an OUT or IN OUT parameter, causes it to be passed by ______.
When a parameter is passed by reference, changes made to it inside the procedure cannot be undone. True/False.
By default, OUT and IN OUT parameters are passed by _____.
If a procedure fails, an OUT parameter passed to it by value (will / will not) be passed back with changes.
By default, an IN parameter is passed by ______.
A large OUT or IN OUT parameter will waste a lot of space and CPU if it is passed by _____.
RECORD OF ARRAYS
You cannot bulk collect into an ARRAY OF RECORDS. You can into a ________________ (3 words).
You cannot bulk collect into an ARRAY OF ________.
You can use the BULK COLLECT INTO clause in a SELECT or a ______.
To insert or update on a table that has a collection column, you have to have _______ privilege on the collection type.
NESTED TABLE myColl STORE AS myCollTab
Give the clause you add to a CREATE TABLE statement, when there is a collection column myColl, that you want to be stored in a table called myCollTab.
FOR-Looping over a collection from FIRST to LAST only works if the collection is _______.
The collection method that tells the number of elements in the collection.
If an initialized collection has no elements, FIRST and LAST return _____.
The LIMIT method is only meaningful for _______.
The names of Java objects in Oracle can be up to __ characters in length.
PL/SQL is a ________ typed programming language.
Static typing is also called _____ typing.
With static typing, type checking is done at ______ time.
With dynamic typing, type checking is done at ______time.
Internally, the NUMBER type is stored as ______. Therefore, decimal numbers (such as prices) are stored exactly, with no rounding.
PLS_INTEGER has its arithmetic implemented in ________, so it is very fast.
SIMPLE_INTEGER is like PLS_INTEGER, but it does not allow ____ values, and it does not raise overflow exceptions.
The three sorts of date/time types are DATE, INTERVAL and _________.
TRUE, FALSE and NULL
What are the 3 values of the BOOLEAN data type?
In short-circuit evaluation, IF-conditions are only evaluated up to where the outcome is certain. True?
In the special IF syntax, is it ELSIF, or ELSEIF?
The BETWEEN operator is (inclusive / exclusive).
In the IF syntax, is if ENDIF or END IF?
What is the exception that is implicitly raised in a CASE statement with no ELSE clause?
The two kinds of CASE statements are simple and ________.
A (simple / searched) CASE statement begins with CASE <expression>.
A (simple / searched) CASE statement begins with CASE WHEN <boolean expression>.
A CASE statement (can / cannot) execute more than one group of statements.
A CASE statement ends with ____ ____.
A CASE expression ends with _____.
This is a CASE (expression / statement): myvar := CASE num WHEN 1 THEN 'good' WHEN 2 THEN 'bad' END;
This is a CASE (expression / statement): CASE num WHEN 1 THEN goto LABEL1 WHEN 2 THEN var := 2 END CASE;
If none of a CASE expression's conditions are TRUE, it returns _____.
True/False: Unlike the CASE statement, the CASE expression does not have the implicit CASE_NOT_FOUND exception.
True/False: A GOTO cannot branch into an IF, CASE or LOOP.
True/False: A GOTO cannot branch into a lower sub-block.
True/False: A GOTO statement cannot branch from an exception handler back into the current BEGIN-END block.
True/False: A GOTO statement can branch from an exception handler into an enclosing block.
The lowest index of a VARRAY is ___.
The lowest index of a nested table is ___.
LOOP, END LOOP
What are the starting and ending keywords of a simple loop?
What is the simplest statement for getting out of simple loop?
What is the simplest statement for conditionally getting out of simple loop?
FOR i IN REVERSE 1..10 LOOP
Give the first line of the FOR loop with index i going from 1 to 10 in reverse.
for update of
Clause added to a cursor to use it with WHERE CURRENT OF.
The FOR UPDATE OF clause references a (table / column).
WHERE CURRENT OF
In a cursor loop, the clause added to an INSERT or UPDATE to point at the current record.
function f (n number) return number result_cache relies_on (emps);
(from inside a package) Declare a function f, that takes a number n, returns a number, and caches results, depending on table EMPS.
cursors: If you use an EXIT statement to exit a cursor FOR loop prematurely, the cursor (is / is not) closed automatically.
cursors: If an exception is raised inside a cursor loop, the cursor (is / is not) also closed automatically.
With Edition-based Redefinition enabled, the unique specification of an object requires OWNER, OBJECT_NAME and ___________.
INSTEAD OF triggers are alternatives to ___ triggers
An INSTEAD OF trigger specifies what to do instead of a ___ statement.
An INSTEAD OF trigger is used, e.g, when you want to insert into a complex _____.
5 types of events that can have triggers: DML, DDL, INSTEAD OFs, database events, and ________ statements.
Simple CASE statement checks for different values of a single _________
Searchable CASE statement: Each WHEN has its own _______ condition.
______ SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
True/False: In 11g you can directly reference sequence pseudo-columns in PL/SQL.
currval and nextval
What are the two sequence pseudo-columns?
Which two of these built-in SQL functions can be used in procedural (PL/SQL) statements: DECODE, CORR, UPPER, SUM, COALESCE, DEREF.
What are two categories of SQL functions that cannot be used in procedural statements?
Conversion: Just about everything will implicitly convert to _____ or _________.
Conversion: It is best to use (implicit / explicit) conversion.
Conversion: Implicit conversion is not always predictable; it is _______-sensitive. And its rules might change.
Conversion: If the specific value is suitable, ____ or _______ values can convert to almost any datatype
Conversion: Implicit conversion can be (faster / slower) than explicit conversion.
PL/SQL lets you define two kinds of composite data types: _______ and _______
In a collection, the internal components always have the _____ data type.
In a collection, the internal components are called _______.
In a record, the internal components are called ______.
%ROWTYPE gets the type of a table or _______.
Records are one of the two kinds of PL/SQL _______ data types.
Collections are one of the two kinds of PL/SQL _______ data types.
True or false: A record is a group of related data items stored in fields, each with its own name and datatype.
A collection is an ordered group of ________, all of the same ____
TYPE t IS TABLE OF rPerson INDEX BY PLS_INTEGER;
Declare an associative array type, t, whose elements are of type rPerson, and whose index is PLS_INTEGER.
r tRec := tRec(9, 'Jones');
define a record variable r, of type tRec, initialized with values 9 and 'Jones' in its two fields.
Declare a variable r whose type is that of the records in table t.
The DEFINITION of a cursor is the part after the keyword __.
cursor c RETURN rC;
Declare a cursor c with no parameters, and return type rC.
Declare a cursor c with no parameters or return type.
The DEFINITION of a cursor is the _____ that returns the records.
A ___________ behaves like a table column, but it is not stored in the table.
COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
List the transaction control statements.
True/false: The SET TRANSACTION statement affects only the current transaction.
Give the statement to make a program autonomous.
An autonomous program runs in its own __________.
true/false: PRAGMA AUTONOMOUS_TRANSACTION has to be the first statement in the declarations section.
To exit an active autonomous transaction without causing an exception, you must issue _____ or ______.
The first SQL statement in an autonomous routine begins a _______.
When an autonomous routine begins execution, the main transaction is ________.
true/false: You can have many autonomous transactions in an autonomous routine.
true/false: You can run a PIPE ROW statement in your autonomous routine while your autonomous transaction is open.
true/false: To run a PIPE ROW statement in your autonomous routine, you must close any autonomous transaction.
To run TCL or DDL statements, a trigger (must / must not) be autonomous .
To run DDL statements, a trigger must be autonomous and must use ______ ______ SQL.
true/false: autonomous triggers are good for logging table DML operations.
procedure P AUTHID current_user;
Declare a package procedure P with no arguments, and give it invoker's rights.
procedure P AUTHID definer;
Declare a package procedure P with no arguments, and give it definer's rights.
The two main parts of a package are the ____ and the _____.
CREATE TYPE cannot be used for _______ array types.
An incomplete type is a type created by a ______ type definition.
An incomplete type only has a ____.
SEVERE, PERFORMANCE, INFORMATIONAL
The three types of compiler warnings.
Give the value list in the PLSQL_WARNINGS parameter to turn on SEVERE compiler warnings and make compiler warning 06002 be an error.
FOR i IN REVERSE 1..10
Make a FOR loop that executes NULL 10 times counting in reverse, using index i.
How many times are a FOR loop's index bounds evaluated?
Can a numeric FOR loop increment by other than 1?
numeric and cursor
Name the two types of FOR loop.
THIS SET IS OFTEN IN FOLDERS WITH...
TSTC ITSE 1345 - Oracle SQL Final Exam Review
Table Objects: Order of Import
Lesson 2: Chapter 4
YOU MIGHT ALSO LIKE...
SQL Server 💻 Terms
MTA 98-361 Lesson 1
Java Interview - 3
Database Interview Questions
OTHER SETS BY THIS CREATOR
SUBNETTING SUBNETMASK / HOSTS
SUBNETMASK DEC / BINARY
SUBNETMASK - DEC / CIDR
OTHER QUIZLET SETS
GIS Test 2