IT 314 FINAL
The ____ markers are used in PL/SQL to identify a label.
<< >>
Which of the following statements about collections is NOT true?
A collection may hold many rows of data and many fields.
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books;
A database object named INVENTORY may already exist.
____ refer to the arguments that are used when calling or invoking the program unit.
Actual parameters
Which of the following statements is true?
After OUT parameters are included in functions, the function can no longer be used in SQL statements.
All of the following would cause a trigger to fire, except ____.
BEFORE
The only required sections of a PL/SQL block are the ____ sections.
BEGIN & END
____ refers to a condition where there is no WHEN clause in the CASE statement.
CASE_NOT_FOUND
The ____ action used with an explicit cursor clears the active set of rows and frees the memory area used for the cursor.
CLOSE
Which of the following code fragments would not raise an error?
CREATE OR REPLACE FUNCTION ship_calc(p_qty IN NUMBER) RETURN NUMBER IS lv_ship_num NUMBER(5,2); BEGIN IF p_qty > 10 THEN lv_ship_num := 11.00; ELSE lv_ship_num := 5.00; END IF; RETURN lv_ship_num; END;
The ____________________ option can be added to the variable declaration to require the variable to always contain a particular value within the block.
Constant
DDL events include all of the following, except ____.
DECLARE
Which of the following PL/SQL blocks requires the variable to always contain a particular value within the block?
DECLARE order CONSTANT NUMBER(2,2) := .02; departure DATE; BEGIN ---- executable statements --- END;
Which of the following statements about performing DML operations on complex views is correct?
DML operations cannot be performed on a non key-preserved table.
____________________ is the process of identifying and removing errors from within program code.
Debugging
Which of the following allows a table of records value to persist for a user session?
Declaring the table of records variable in a package specification.
The ____ section of a PL/SQL block contains handlers that allow you to control what the application will do if an error occurs when the executable statements are processed.
Exception
____ is required in a PRAGMA EXCEPTION_INIT statement.
Exception name
A view can be modified using the MODIFY clause of the ALTER VIEW command.
False
A(n) index-by table is an ordered group of elements that allows the handling of multiple values of the same data type as a single unit.
False
A(n) procedure cannot be invoked in a standalone fashion.
False
A(n) procedure is part of an expression and cannot serve an entire statement.
False
Explicit cursors are declared automatically for all DML and SELECT statements issued within a PL/SQL block.
False
Functions that have not been declared in a package specification are considered public.
False
Implicit cursors are declared and manipulated in the PL/SQL block code for handling a set of rows returned by a SELECT statement.
False
In a(n) function, the RETURN statement includes no arguments and is followed with a semicolon.
False
LOB and OBJECT columns can be referenced in a trigger and can also be modified.
False
Package code is not cached, nor is package data such as variables and cursors.
False
Packages can be created in local libraries, as can procedures and functions.
False
Rows cannot be added to a table through a complex view that was created with the ORDER BY clause.
False
The DEL command can be used to remove the package specification and body.
False
The SQLERRM function returns the Oracle error number.
False
The TYPE attribute tells the system to look up the data type of a database column and use it for the declared variable.
False
The pragma level defines what type of data structures the function reads or modifies.
False
The pseudo column ROW can be used to perform a "TOP-N" analysis.
False
The term "actual parameters" refers to the parameters that are listed in the program unit.
False
The term passed by reference implies that the value is copied from the actual parameter to the formal parameter.
False
The term passed by value implies that a pointer to the value in the actual parameter is created instead of copying the value from the actual parameter to the formal parameter.
False
The term server-side refers to code that resides on the client machine.
False
Three arguments are required in a PRAGMA EXCEPTION_INIT statement.
False
To remove a trigger from the system, the DEL TRIGGER statement is used.
False
The NOFORCE keyword can be used to create a view based upon a table that has not yet been created.
False, it is the Force keyword
PL/SQL blocks use ____________________ variables to move values from an application environment into the PL/SQL block for processing.
Host/bind
The ____________________ combined mode can achieve the passing of a value both into and out of a procedure with a single parameter.
IN OUT
Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99);
The ____________________ clause follows the SELECT clause and indicates which variables are to hold the values that are retrieved from the database.
INTO
____ aren't allowed in the CREATE VIEW command.
ORDER BY clauses
____ can be created only as stored program units.
Packages
Which of the following attributes is available to enable movement through the index-by table data?
Prior
____________________ cannot be used in SQL statements.
Procedures
In the syntax of the CREATE TRIGGER statement, the ____________________ clause can be used to change the OLD and NEW qualifier names to something of your choice.
REFERENCING
All of the following represent packaged function purity levels, except ____.
RIPS
A(n) ____ of data typically includes a number of different fields.
ROW
DML operations are not allowed on a view that includes the pseudo column ____.
ROWNUM
____ has the possibility of raising the NO_DATA_FOUND or TOO_MANY_ROWS exception.
SELECT
____ triggers refer to database triggers that are fired by Data Definition Language (DDL) statements or database system events rather than DML actions.
System
A(n) ____________________ statement could be used to create a record data type.
TYPE
A database trigger is tied to a database table or view and is implicitly fired by the database system when that table or view is affected with the associated DML action.
True
A(n) CASE expression evaluates conditions and returns a value in an assignment statement.
True
A(n) procedure is used to accomplish one or more tasks, return none or many values, and can be used only in PL/SQL statements.
True
An ORDER BY clause can be used to perform "TOP-N" analysis.
True
An infinite loop causes a program to loop indefinitely, disrupting the ability of the code to continue with any processing beyond the loop.
True
Correlation identifiers allow us to refer to and use the row data values of a DML action.
True
Developers can use the PRAGMA RESTRICT_REFERENCES compiler instruction to indicate the purity level of the function in the package specification.
True
Functions written in other languages such as JAVA can also be called from Oracle programs.
True
One major advantage of using collections as part of the physical database is being able to retrieve multiple values with a query of a single column.
True
Program units are made flexible by using parameters, which are mechanisms to send values into and out of the program unit.
True
The EXIT WHEN clause ensures that a basic loop runs at least once.
True
The INTO clause follows the SELECT clause and indicates which variables are to hold the values that are retrieved from the database.
True
The following loop terminates when the lv_cnt_num variable holds a value of 6. BEGIN WHILE lv_cnt_num <= 5 LOOP DBMS_OUTPUT.PUT_LINE( lv_cnt_num ); lv_cnt_num := lv_cnt_num + 1; END LOOP; END;
True
When an IF statement checks only one condition and performs actions only if the condition is TRUE, it is referred to as a(n) simple IF condition.
True
____ are named memory areas that hold values to allow the retrieval and manipulation of values in a program.
Variables
The ____ handler should always be the last handler listed in the EXCEPTION section of a block.
WHEN OTHERS
The ____________________ keywords are used to make certain any DML operations do not prevent a row from being accessible to the view after the DML operation is executed.
WITH CHECK OPTION
The ____________________ keywords can be used to ensure data cannot be changed through the view.
WITH READ ONLY
An attempted division by zero causes the ____________________ exception.
ZERO_DIVIDE
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books;
a database object named INVENTORY may already exist.
Which statement is true about the view created with the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
all of the above
One major advantage of using ____ as part of the physical database is being able to retrieve multiple values with a query of a single column.
collections
To reference a host variable in PL/SQL, a preceding ____ must be used with the host variable name.
colon
The statements that are used to control the flow of logic processing in your programs are commonly referred to as ____.
control structures
The RAISE_APPLICATION_ERROR built-in function is provided by Oracle to ____.
create error messages
To declare a variable, you must supply a variable name and ____________________.
data type
A(n) ____________________ contains a RETURN clause and is used to manipulate data and return a single resulting value.
function
If the EXIT WHEN clause is not included in the basic loop, the result is the programmer's nightmare of a(n) ____________________.
infinite loop
The subquery used to create a(n) ____ view can contain an ORDER BY clause.
inline
A(n) ____ table is one that is involved in a join and the keys of the original table are included in the keys of the resultant join.
key-preserved
To rebuild a database, if you are using the iSQL*Plus tool (Internet interface), first click the ____________________ button.
load script
A(n) ____________________ view is used to replicate data which can be called a snapshot.
materialized
A(n) ____ is a table that is being modified by a DML action when a trigger is fired.
mutating table
The main differences between a ____ and a VARRAY are that a VARRAY has a set size upon creation, the order of elements is preserved, and the data is stored inline with the table data.
nested table
A(n) ____ table is a table that does not contain the primary key that a view uses to uniquely identify each record being displayed by the view.
non key-preserved
Using the same name on multiple program units within the same package is referred to as ____.
overloading
A(n) ____ must be assigned one of three available modes: IN, OUT, IN OUT.
parameter
A(n) _________________ data type is quite similar to the structure of a row in a database table.
record
A(n) ____________________ view cannot be created using a GROUP BY clause.
simple
The default timing of a trigger is ___________________________ level and no code is included to achieve this timing.
statement
An inline view is created by a SELECT statement that contains a(n) ____________________ in the FROM clause.
subquery, exists while the SELECT command is being executed.
____ are functions that can be used in conjunction with table variables and allow greater ability to manipulate table values.
table attributes
