ITSE 1345 PL/SQL EXAM 2

Which of the following are correct about caching invoker rights functions? Please select from the following - (Multiple answers possible)

A and D are correct. Oracle Database 1 2c lets you create cached invoker rights functions. They implicitly use the current user to distinguish between cached result sets, and that means different results are kept for each invoker.

Which of the following is a capability of PL/SQL? Please explain/ elaborate on your answer and please note multiple selections combinations are possible Please select and support your response from the following - (Multiple answers possible)

E All of the above --E is correct. PL/SQL can call SQL, implement object types, wrap C-callable programs, and wrap Java programs.

Which of the following isn't a keyword in PL/SQL? Please explain/elaborate on your answer and please note multiple selections combinations are possible.

E---STRUCTURE Oracle doesn't support a STRUCTURE keyword. It does support the RECORD, REVERSE, CURSOR, and LIMIT keywords.

__SQL is an imperative language that lets you work in the Oracle database. - This is a modified True/False question. Please provide a True or False position and support you position

False - SQL is a set-based declarative language that lets you work in the Oracle database.

A declaration block can't contain an anonymous block.

False. A declaration block can contain an anonymous block if the anonymous block is embedded in a named block.

The Oracle database relies on an external Java Virtual Machine to run stored Java libraries.

False. The Oracle internal JVM runs all stored Java libraries.

_The relational database model evolved from the object-relational database model. - This is a modfied True/False question. Please provide a True or False postion and support you position

False. The object-relational database model evolved from the relational database model because object-oriented database models required too much memory to marshal objects.

PL/SQL is an imperative language that is both event-driven and object-oriented

True - PL/SQL is an imperative language that is both event-driven and object-oriented.

Relational databases store data. - This is a modified True/False question. Please provide a True or False position and support you position

True, relational databases store data in tables.

SQL*Plus provides which of the following? Please explain/ elaborate on your answer and please note multiple selections combinations are possible Please select and support your response from the following - (Multiple answers possible)

A and B and C A. An interactive mode - B. A call mode - C.A server modeB. --A, B, and C are correct. SQL*Plus supports an interactive mode, which is also known as the client software. Calls made by external programs run through a portion of SQL*Plus known as the call mode.

Which of the following describes the roles of the Oracle listener? Please explain/ elaborate on your answer and please note multiple selections combinations are possible Please select and support your responce from the following - (Multiple answers possible)

A and D are correct. The Oracle listener listens for incoming client requests and forwards requests to a SQL* Plus session. The actual SQL*Plus session is a different executable from the interactive one that you launch when working with Oracle Database 12c.

Which of the following are valid symbol sets in PL/SQL? -- Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---A colon and equal sign set (:=) assignment operator = := performs an assignment. B---A guillemets or double angle bracket set (<< >>) as delimiters for labels = Is a label target for a GOTO statement C---A less than symbol and greater than symbol set (<>) as a comparison operator = Is a not equal comparison operator. D---An exclamation mark and equal sign set (1=) as a comparison operator = Is a not equal comparison operator. The colon and equal sign set (:=) performs an assignment; the guillemets or double angle bracket set (<< >>) is a label target for a GOTO statement; the less than symbol and greater than symbol set (<>) is a not equal comparison operator; and an exclamation mark and equal sign set (!=) is a not-equal comparison operator.

Which of the following are valid symbol sets in PL/SQL? -- Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---A colon and equal sign set (:=) assignment operator = := performs an assignment. B---A guillemets or double angle bracket set (<< >>) as delimiters for labels = Is a label target for a GOTO statement C---A less than symbol and greater than symbol set (<>) as a comparison operator = Is a not equal comparison operator. D---An exclamation mark and equal sign set (1=) as a comparison operator = Is a not equal comparison operator. The colon and equal sign set (:=) performs an assignment; the guillemets or double angle bracket set (<< >>) is a label target for a GOTO statement; the less than symbol and greater than symbol set (<>) is a not equal comparison operator; and an exclamation mark and equal sign set (!=) is a not-equal comparison operator.

Lexical units are the basic building blocks in programming languages, and they can perform which of the following? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---A delimiter B---An identifier C---A literal D---A comment Delimiters, identifiers, literals, and comments are lexical units.

Lexical units are the basic building blocks in programming languages, and they can perform which of the following? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---A delimiter B---An identifier C---A literal D---A comment Delimiters, identifiers, literals, and comments are lexical units.

Which of the following guards entry and exit to the loop in PL/SQL? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---A range FOR loop B---A cursor FOR loop The FOR loop guards entry and exit and manages them implicitly.

Which of the following are valid loop structures in PL/SQL? Please explain/elaborate on your answer and please note multiple selections combinations are possible.

A---A simple loop B---A FOR loop C---AWHILE loop Oracle supports a range and cursor FOR loop, a WHILE loop, and a simple loop.

Which of the following are only guard-on-entry loops? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---A simple range loop B---A range FOR loop C---AWHILE loop A simple loop can be coded to guard on entry or exit. A range FOR loop guards on entry and exit, and a WHILE loop guards entry only. DO-UNTIL and DO-WHILE aren't loop structures in PL/SQL.

Which parameter modes are supported in Oracle PL/SQL? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---IN C---OUT D---IN OUT The IN mode works for pass-by-value parameters, and IN OUT and OUT-only mode work for pass-by-reference parameters. There isn't an INOUT mode.

Which of the following is true about which FL/SQL data types you can access in an embedded SQL statement? Please select from the following - (Multiple answers possible)

A---The PL/SQL data type must be declared in a package. B---The SQL statement needs to be embedded in the PL/SQL block where the type is defined. C---The PL/SQL data type must be locally defined. Oracle Database 12c lets you consume a FL/SQL associative array in a SQL statement when you meet three conditions. First, the data type must be defined in a FL/SQL package. Second, there must be a local vari'ole that uses that data type. Third, the SQL statement must be embedded within the PL/SQL block.

A conditional statement applied against two operands can evaluate which of the following? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

A---The truth of a comparison involving only not-null values Comparison operations are three-valued logic comparisons unless you're checking for a null value. Checking for a null value is an evaluation of what a reference points to, not the value that it holds. Comparison operations between two operands only work when both operands hold not-null values.

Which of the following are only guard-on-exit loops? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

Answer: A---A simple cursor loop B---A simple range loop Only the simple lop allows unfettered access and allows you to guard on exit. This allows yo to run the logic once and exit regardless of any conditions before entering the loop.

Which of the following lets you access a surrogate primary key from an identity column for use in a subsequent INSERT statement as a foreign key value?Please select from the following Please select from the following - (Multiple answers possible)

B is correct. The RETURNING INTO clause lets you capture the value from an identity column.

A simple case statement works with which of the following data types? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

B---A VARCHAR2 data type C---A NCHAR data type D---A CHAR data type B, C, and D are correct. Oracle supports a VARCHAR2, NCHAR, or CHAR data type in a simple case statement.

Which of the following are valid scalar data types in PL/SQL? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

B---A VARCHAR2 data type C---A NCHAR data type D---A CHAR data type E---A DATE data type The TEXT data type isn't a valid data type in PL/SQL, but it is a valid data type in MySQL

Which of the following are valid scalar data types in PL/SQL? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

B---A VARCHAR2 data type C---A NCHAR data type D---A CHAR data type E---A DATE data type The TEXT data type isn't a valid data type in PL/SQL, but it is a valid data type in MySQL.

Which of the following collections work best with a bulk delete operation on a well-defined (or normalized) table with a surrogate key for its single-column primary key? Please explain/ elaborate on your answer and please note multiple selections combinations are possible,

B---A single scalar collection C---A single record collection Bulk operations only work when there is one collection to traverse, and that means a single scalar or record collection.

Which of the following are reasons for using a system reference cursor? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

B---An alternative when you want to query data in one program and use it in another C---A PL/SQL-only solution with the results of composite data type You use a system reference cursor when you want to query data in one program and use it in another. System reference cursors are also PL/SQL-only solutions with one exception that was covered in Chapter 2, and that's using an Implicit Record Set (IRS).

Which of the following are reasons for using a system reference cursor? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

B---An alternative when you want to query data in one program and use it in another C---A PL/SQL-only solution with the results of composite data type You use a system reference cursor when you want to query data in one program and use it in another. System reference cursors are also PL/SQL-only solutions with one exception that was covered in Chapter 2, and that's using an Implicit Record Set (IRS).

Which of the following keywords work when you define a view? Please select from the following - (Multiple answers possible)

B---The BEQUEATH INVOKER keywords D---The BEQUEATH DEFINER keywords The BEQUEATH keyword may precede either the INVOKER keyword or DEFINER keyword. The AUTHID keyword may precede either DEFINER or CURRENT_ USER lout only for functions, procedures, packages, and object types.

Which of the following converts a relational model to an object-relational model? Please explain/ elaborate on your answer and please note multiple selections combinations are possible Please select and support your response from the following - (Multiple answers possible)

C and D and E --C.An object data type -D. An imperative language that lets you build native object types - E. A JVM inside the database-------C, D, and E are correct. You require an object data type and an imperative language to create the objects. PL/SQL is that language, and the JVM runs the objects.

Which of the following isn't a cursor attribute? Please explain/elaborate on your answer and please note multiple selections combinations are possible.

C---%TYPE %TYPE is a column anchoring attribute, not a cursor attribute.

Which of the following data types are best suited for scientific calculations in PL/SQL? Please explain/ elaborate on your answer and please note multiple selections combinations are possible.

C---A BI NARY _DOUBLE data type D---A BINARY-FLOAT data type Only the IEEE-754 variables BINARY-DOUBLE and BINARY-FLOAT are considered scientific computing data types.

Which of the following support expanding the SQL text of LONG columns into CLOB columns when working with the CDB_, DBA_, ALL_, and USER VIEWS in the Oracle Database 12c database? Please select from the following - (Multiple answers possible)

C---You can use the dbms_sql package to convert LONG data types to VARCHAR2 data types. D---You can use the length built-in function to discover the size of a LONG data type. E---You can use the dbms_lob package to create a temporary CLOB data type. While not an Oracle Database 12c feature, you need to convert views from their native LONG data type to a CLUB before you can use the new expand sql text procedure in the dbms utility package. You convert a LONG to a CLUB through a three-step process unless you like to read all the characters one-by-one in a loop. If you read character by character in your LONG to CLUB procedure, you don't need to fetch the LONG data type into a local variable to size it before calling the conversion procedure. The first step gets the size of the LONG data value with the LENGTH built-in, which means you query the administrative view twice. The second step requires you to convert LONG to a VARCHAR2 by using the result of the LENGTH built-in and the def me_column long procedure from the dbms_sql package. The third step uses the VARCHAR2 as a call parameter to the dbms_ lob. write procedure from the dbms_lob package to create a CLUB from the VARCHAR2.

Which of the following are not types of SQL statements? Please explain/ elaborate on your answer and please note multiple selections combinations are possible Please select and support your response from the following - (Multiple answers possible)

D. Create, replace, update, and delete (CRUD) statements --A, B, C, and E are correct. DDL, DML, DCL, and TCL are valid types of SQL statements. CRUD is a description of functionality, not of the DML type of statements

Databases always rely on two-valued logic. This is a modified True/False question. Please provide a True or False position and support your position

False. Databases rely on three-valued logic because they must be capable of comparing a null value. That's why Oracle Database and other databases support the IS [NOT] NULL reference comparison operator.

The colon and equal sign set (:=) is the only assignment operator in PL/SQL.

False. The : = operator is the only right-to-left assignment operator in PL/SQL, but you can use the SELECT-INTO statement or BULK COLLECT INTO statement to perform left-to¬right assignments.

A SELECT- INTO statement is an example of an explicit cursor.

False. A SELECT-INTO statement is an implicit cursor.

A declaration block can't contain an anonymous block.

False. A declaration block can contain an anonymous block if the anonymous block is embedded in a named block.

A record data type is a SQL data type. This is a modified True/False question. Please provide a True or False position and support you position

False. A record data type is a PL/SQL data type.

A two-tier model works between a browser and a database server.

False. A two-tier model works between a SQL*Plus command-line interface (CLI) and the Oracle Database 12c database server.

An execution block can contain a local named block.

False. An execution block can't contain a local named block; local named blocks must be defined in the declaration block.

The .currval pseudocolumn no longer has a dependency on a preceding .nextval pseudocolumn call in a session.This is a modified True/False question. Please provide a True or False position and support you position

False. Oracle Database 12c does not remove the session dependency that the . currval pseudocolumn has on the .nextval pseudocolumn.

Oracle Database 12c doesn't provide a means to prevent the entry of an explicit null in an INSERT statement, which means you can still override a DEFAULT column value. This is a modified True/False question. Please provide a True or False position and support you position

False. Oracle Database 12c does provide an ON NULL clause that prevents the insertion or update of an explicit null when a default value has been specified.

VARCHAR2, NVARCHAR2, and RAW data types are now always 32,767 bytes in the Oracle Database 12c database.

False. Oracle Database 12c provides a max string size parameter, which lets you set it to EXTENDED when you want VARCHAR2, NVARCHAR2, and RAW columns to hold 32,767 bytes. Data types remain capped at the prior maximum size when the max_ string size parameter is set to STANDARD.

The colon and equal sign set (:=) is the only assignment operator in PL!SQL. Please provide a True or False postion and support you position.

False. The : = operator is the only right-to-left assignment operator in PL/SQL, but you can use the SELECT- INTO statement or SULK COLLECT INTO statement to perform left-to¬right assignments.

The colon and equal sign set (:=) is the only assignment operator in PL/SQL.

False. The : = operator is the only right-to-left assignment operator in PL/SQL, but you can use the SELECT-INTO statement or BULK COLLECT INTO statement to perform left-to¬right assignments.

An EXCEPTION block is where you put handling for errors raised in the declaration block of the same anonymous or named program unit. Please provide a True or False position and support you position.

False. The EXCEPTION block is where you handle exceptions from the execution section, but it can't handle exceptions raised in the declaration block.

Valid-time (VT) indicates the point at which transactions commit. This is a modified True/False question. Please provide a True or False position and support you position

False. Valid-time (VT) indicates the point at which a business event occurs, and is unrelated to the transaction-time (U).

A declaration block begins with the function or procedure header, specification, or signature in a named block.

True. A declaration block immediately follows the function or procedure header.

A PL/SQL function can return a PL/SQL associative array directly into a SQL statement with the changes introduced in Oracle 12c.

True. Oracle Database 12c lets you consume a PL/SQL associative array in a SQL statement when you meet three conditions. First, the data type must be defined in a PL/SQL package.Second, there must be a local variable that uses that data type. Third, the SQL statement must be embedded within the PL/SQL block.

A simple CASE statement can use a numeric selector.

True. A simple CASE statement evaluates a numeric value, and when it finds a match in one of the WHEN clauses, it exits the CASE statement.

Conjunctive logic involves determining when two or more things are true at the same time.This is a modified True/False question. Please provide a True or False position and support you position

True. Conjunctive logic involves two or more comparisons joined by an AND keyword. Two or all of the comparisons must be true for a conjunctive statement to be true.

Inclusion logic involves determining when one or another thing is true at any time. This is a modified True/False question. Please provide a True or False position and support you position

True. Inclusion logic involves two or more comparisons joined by an OR keyword. At least one item must be found true, and when one is found true, short-circuit logic stops making any remaining comparisons.

A declaration block begins with the function or procedure header, specification, or signature in a named block.

True. A declaration block immediately follows the function or procedure header.

A searched CASE statement may use a string or numeric selector. This is a modified True/False question. Please provide a True or False position and support your position

True. A searched CASE statement evaluates Boolean logic, or the result of comparison operations.

A system reference cursor is a PL/SQL-only data type. This is a modified True/False question. Please provide a True or False position and support you position

True. A system reference cursor is a PL/SQL-only data type.

A three-tier model is a specialized form of an n-tier model.

True. A three-tier model is a specialized form of an n-tier model, where you have a browser, an Apache server, and a database server.

An identifier is a lexical unit.

True. An identifier is a lexical unit.

A basic block in PL/SQL must have at least a null statement to compile. Please provide a True or False position and support you position

True. Any PL/SQL block must include at least one statement, like a NULL; statement. The NULL; statement is advantageous for testing whether the blocks of code are organized correctly before embedding logic.

Conditional compilation supports conditional compilation flags.

True. Conditional compilation supports any number of compilation flags.

Database triggers run between the first phase of a DML statement and the COMMIT statement.

True. Database triggers run between the first phase of a DML statement and the COMMIT statement. The COMMIT statement ends the transaction and is the second phase of a two-phase commit (2 PC).

Short-circuit logic occurs with inclusion logic.This is a modified True/False question. Please provide a True or False position and support your position

True. Inclusion logic uses short-circuit logic, which stops making comparisons once one item is found true.

Oracle Database 12c now supports top-n query results without an offset value.

True. Oracle Database 12c lets you create top-n query with or without OFFSET values.

It is possible to define a default column that uses the . currval pseudocolumn for a sequence.This is a modified True/False question. Please provide a True or False position and support you position

True. Oracle Database 12c lets you define a default column that uses the . currval pseudocolumn.

It is possible to define a default column that uses the .nextval pseudocolumn for a sequence.This is a modified True/False question. Please provide a True or False position and support you position

True. Oracle Database 12c lets you define a default column that uses the .nextval pseudocolumn.

Identity columns let you automatically number the values of a surrogate key column.

True. Oracle Database 12c lets you define an identity column in any table that uses an implicitly generated sequence.

You can embed a PUSQL function inside a query's WITH clause and call it from external programs.

True. Oracle Database 12c lets you implement a PUSQL function inside a WITH clause. Unfortunately, the semicolon required to terminate statements and blocks causes a conflict with the SQLTERMINATOR and limits your ability to reading the statement within a preconfigured SQL*Plus session. You must embed the query inside a view to call it from other program units.

Packages let you define overloaded functions and procedures.

True. Oracle supports overloaded functions and procedures in packages, which is an object-oriented feature.

PL/SQL is the procedural extension of SQL.

True. PL/SQL is the Procedure Language/Structured Query Language.

The PL/SQL programming language supports arrays and lists as composite data types. This is a modified True/False question. Please provide a True or False position and support you position

True. PL/SQL supports array (varray) and list (table) collections, which are composite type variables.

Relational databases store information about how data is stored. - This is a modified True/False question. Please provide a True or False position and support you position

True. Relational databases store information about how data is stored, and this type of information is called the data catalog or data dictionary.

__Relational Software, Inc. became Oracle Corporation. - This is a modified True/False question. Please provide a True or False position and support you position

True. Software Development Laboratories (SDL) led to the formation of Relational Software, Inc. (RSI), which in turn led to the formation of Oracle Corporation.

A CONTINUE statement lets you skip the balance of an iteration through a loop.

True. The CONTINUE statement instructs the program to skip the balance of an iteration cycle and return to the top of the loop.

The DECLARE block is where you put all variable, cursor, and local function and procedure implementations. Please provide a True or False position and support you position.

True. The DECLARE block is where you put all variable, cursor, and local function and procedure implementations.

The FOR-ALL statement lets you perform bulk INSERT statements.

True. The FOR-ALL statement is the key structure for performing bulk INSERT and UPDATE statements.

The elsif statement lets you branch execution in an if statement. Please provide a True or False position and support you position

True. The elsif statement keyword lets you branch execution.

The equal sign and greater than symbol set (=>) is an association operator. This is a modfied True/False question. Please provide a True or False position and support you position.

True. The equal sign and greater than symbol set (=>) is an association operator, and it's used for named notation.

The equal sign and greater than symbol set (=>) is an association operator. This is a modified True/False question. Please provide a True or False position and support you position

True. The equal sign and greater than symbol set (=>) is an association operator, and it's used for named notation.

PL/SQL lets you create subtypes of standard scalar variables. This is a modified True/False question. Please provide a True or False position and support you position

True. You can create subtypes of standard scalar variables with PL/SQL.

Oracle supports SQL and PL/SQL collections as parameter and return value data types.

True. You can have a function that returns a SQL or PL/SQL collection. You must call functions that return a PL/SQL collection inside another PIJSQL program unit. Functions that return a SQL collection work in either SQL or PL/SQL.

Oracle supports both simple and searched case statements.

True. You can implement a simple or searched case statement.

You need to provide forward-referencing stubs for local functions or procedures to avoid a procedure or function "not declared in this scope" error.

True. You need to provide forward-referencing stubs to avoid a forward reference.