Deck 10: Oracle-Supplied Packages, Dynamic SQL, and Hiding Source Code

Question 1

The goal of SQL is to empower users with little or no query skills to be able to perform a variety of data queries.

Answer 1

False

Question 2

The DBMS_DDL package provides the only Oracle method available to hide source code.

Answer 2

False

Question 3

If a table contains any LOB columns, client tools such as SQL Developer cannot display the images, however, the GETLENGTH function can be used to confirm a file was loaded into the LOB column.

Answer 3

True

Question 4

The array-like processing available in DBMS_SQL makes working with many rows much more cumbersome.

Question 5

The BLOB, CLOB, and NCLOB objects can all be stored internally and are typically stored in a separate tablespace from database tables to enable optimization of the attributes of each tablespace for the type of data being stored.

Question 6

An EXECUTE step is not needed with DDL statements because the PARSE step automatically executes the statement upon successful parsing.

Question 7

The ability to send e-mails from within PL/SQL code has been simplified with the introduction of the DBMS_ALERT package.

Question 8

One advantage of LOBs is that a table can have multiple LOB columns but only one LONG column.

Question 9

Dynamic SQL allows us to employ a more object-oriented approach to modeling our database and applications.

Question 10

The util.ora file contains environment settings, such as the UTL_FILE_DIR parameter, that determine if and how certain Oracle features are accessible from within PL/SQL code.

Question 11

DBMS_SQL is simpler to code and processes more efficiently than native dynamic SQL.

Question 12

You must enable DBMS output in order to see the display from DBMS_OUTPUT commands.

Question 13

Obfuscating or wrapping code refers to hiding source code.

Question 14

The UTL_FILE package contains a PUT_LINE data type that is used to declare a file variable.

Question 15

The REQUEST_PIECES function is typically used to avoid possibly truncating information contained on the Web page.

Question 16

Support for DBMS_SQL statements is built into the PL/SQL interpreter.

Question 17

The init.ora file contains all the settings for the database and is read when the database is started up.

Question 18

Dynamic SQL allows the criteria of a query such as the column checked in the WHERE clause to be identified at compile time.

Question 19

Attempting to use a parameter to provide an identifier, such as a column name or table name, in an SQL statement within PL/SQL raises an error.

Question 20

The basic mechanics of the alert process include registering an alert name, setting up when an alert should signal, and enabling the appropriate users to receive the alert.

Question 21

A(n) mutator is similar to a partition or area of the physical disk. _________________________

Question 22

The code fragment ____ would raise an error.

A) DBMS_ALERT.REGISTER( 'new_bid' );
B) DBMS_ALERT.WAITONE( 'new_bid', v_msg, v_status, 600);
C) DBMS_ALERT( 'new_bid', v_msg, v_status, 600);
D) DBMS_ALERT.SIGNAL( 'new_bid', TO_CHAR(:new.bid));

Question 23

The OPEN FOR method cannot reuse parsed statements. _________________________

Question 24

In the UTL_FILE package, the ____ program unit is used to read a line from a file.

A) FOPEN
B) PUT_LINE
C) GET_LINE
D) FCLOSE

Question 25

The UTL_HTTP package provides the capability to make Hypertext Transfer Protocol (HTTP) calls from within PL/SQL programs. _________________________

Question 26

When the SQL statement is built in the PL/SQL block, placeholders with colon prefixes are used to mark where values are supplied at runtime. _________________________

Question 27

The DBMS_ALERT package allows two or more sessions, in the same instance, to communicate. _________________________

Question 28

When executing a stored program, any DBMS_OUTPUT lines are placed in an output buffer, which dumps its contents to the screen when the program has completed execution. _________________________

Question 29

All of the following procedures are included in the DBMS_ALERT package, except ____.

A) REGISTER
B) SIGNAL
C) WAIT
D) WAITONE

Question 30

In the UTL_HTTP package, the ____ function is used to retrieve the HTML source from a specified URL.

A) REQUEST_PIECES
B) NOOP
C) RCPT
D) RSET

Question 31

A large object (LOB) can hold ____.

A) 1 GB
B) 2 GB
C) 3 GB
D) 4 GB

Question 32

BEGIN
DBMS_OUTPUT.PUT('This');
DBMS_OUTPUT.PUT(' is a');
DBMS_OUTPUT.PUT(' test');
DBMS_OUTPUT.PUT_LINE('This is test2');
DBMS_OUTPUT.PUT('Working?');
END;
The code fragment above is an example of a(n) ____.

A) cursor
B) alert
C) anonymous block
D) trigger

Question 33

Alerts are transaction based and are sent only if the transaction is committed. _________________________

Question 34

In the DBMS_LOB package, which of the following represents the programs that add, change, or remove the large objects (LOBs)?

A) BFILE
B) mutator
C) observer
D) tablespace

Question 35

The programs within the ____ package can be grouped into two sets: mutators and observers.

A) BLOB
B) DBMS_LOB
C) CLOB
D) BFILE

Question 36

The DBMS_OUTPUT procedure ____ places an end-of-line marker in the buffer.

A) ENABLE
B) PUT_LINE
C) NEW_LINE
D) GET_LINE

Question 37

The UTL_LINE package allows access to files that reside on an operating system and are accessible from the database server. _________________________

Question 38

All of the following programs are available in the UTL_FILE package, except ____.

A) FOPEN
B) RSET
C) PUT_LINE
D) GET_LINE

Question 39

Native dynamic SQL is simpler to code and processes statements more efficiently than the DBMS_SQL package. _________________________

Question 40

The ____ package can be used to analyze the HTML source of Web sites in order to accomplish tasks such as tracking competitor pricing on products, gathering pricing for raw materials, and collecting relevant business news for a particular industry.

A) UTL_HTTP
B) UTL-SMTP
C) DBMS_ALERT
D) DBMS_PIPE

Question 41

The ____ package allows parameters to be directly used as identifiers, such as column names and table names.

A) DBMS_SQL
B) DBMS_ALERT
C) UTL_SMTP
D) DBMS_PIPE

Question 42

____ and PL/SQL allow the construction and parsing of statements at runtime rather than compile time.

A) Dynamic SQL
B) Native SQL
C) DBMS_SQL
D) AD_HOC SQL

Question 43

The process flow of the DBMS_SQL procedures involves the establishment of a(n) ____ to contain and manipulate the SQL statement.

A) BIND_VARIABLE
B) object type
C) cursor
D) DML statement

Question 44

____ are used to distinguish parameter values that are used as criteria in the DBMS_SQL package, versus parameters that provide identifiers such as column names.

A) DDLs
B) Placeholders
C) DMLs
D) Cursors

Question 45

The ____ parameter of the ANALYZE_OBJECT procedure dictates that the value provided must be either TABLE, INDEX, or CLUSTER.

A) TYPE
B) SCHEMA
C) NAME
D) PARTNAME

Question 46

The ____ built-in package contains a miscellaneous group of programs ranging from capabilities to assist in procedure management to reporting error information.

A) DBMS_SESSION
B) DBMS_UTILITY
C) DBMS_XMLGEN
D) UTL_INADDR

Question 47

In the DBMS_LOB package, the term ____ represents programs that read LOBs or information about the LOBs.

A) Mutator
B) Tablespace
C) BFILE
D) Observer

Question 48

In the DBMS_DDL package, the ____ procedure creates statistics for database objects to be utilized to enhance performance.

A) ANALYZE_OBJECT
B) ALTER_COMPILE
C) ALTER_DDL
D) ANALYZE_DDL

Question 49

The ____ built-in package retrieves Internet site host names or IP addresses.

A) DBMS_JAVA
B) UTL_HTTP
C) UTL_INADDR
D) DBMS_UTILITY

Question 50

To successfully compile a program, you must either own the object or you must be granted the ____ privilege to compile an object in another schema.

A) ANALYZE_OBJECT
B) ALTER
C) MODIFY PROCEDURE
D) ALTER ANY PROCEDURE

Question 51

____ allows us to make the SQL statements in our PL/SQL code much more flexible in regards to providing values, such as column names, at runtime.

A) SQL
B) Object technology
C) OBJECT id
D) Dynamic SQL

Question 52

The ____ package allows access to two DDL statements that cannot be used directly within a PL/SQL block: ALTER_COMPILE and ANALYZE_OBJECT.

A) DBMS_PIPE
B) DBMS_SQL
C) DBMS_DDL
D) DBMS_JOB

Question 53

DBMS_DDL.ALTER_COMPILE (
Type VARCHAR2,
Schema VARCHAR2,
Name VARCHAR2);
In the header for the ALTER_COMPILE procedure above, the ____ statement refers to the object's owner.

A) type
B) schema
C) name
D) ALTER

Question 54

The ____ built-in package retrieves information about database objects.

A) DBMS_JAVA
B) DBMS_METADATA
C) DBMS_RANDOM
D) DBMS_SESSION

Question 55

____ will cause an error if used directly within PL/SQL.

A) Query statements
B) DDL statements
C) DML statements
D) SELECT statements

Question 56

In the DBMS_DDL package, ____ is a valid ANALYZE_OBJECT exception.

A) ORA-20000
B) ORA-20002
C) ORA-20003
D) ORA-20004

Question 57

The ____ built-in package is used to access Web pages.

A) UTL_INADDR
B) UTL_HTTP
C) DBMS_SESSION
D) DBMS_METADATA

Question 58

The ____ package provides access to DDL statements that cannot be included directly within PL/SQL code.

A) LOB
B) DBMS_DDL
C) DBMS_SQL
D) DBMS_JOB

Question 59

____ establishes a work area for a statement to be handled.

A) OPEN_CURSOR
B) PARSE
C) CLOSE_CURSOR
D) CLOSE_PARSER

Question 60

The ____ procedure of the DBMS_DDL package allows developers to programmatically compile database objects.

A) ALTER_RECOMPILE
B) ANALYZE_OBJECT
C) ALTER_COMPILE
D) DDL_ALTER

Question 61

____ frees the resources allocated to the cursor.

A) EXIT_CURSOR
B) FETCH_ROWS
C) EXECUTE
D) CLOSE_CURSOR

Question 62

Which of the following is a procedure in the DBMS_DDL package that will assist to hide source code?

A) WRAP.
B) CODE_WRAPPED.
C) CREATE_WRAPPED.
D) WRAP_CODE.

Question 63

Which of the following is the correct syntax for an EXECUTE IMMEDIATE statement?

A) EXECUTE IMMEDIATE 'SQL statement'
[ INTO ( var1, var2, ... | record ]
[ USING [ IN | OUT | IN OUT ] bindvar1, bindvar2, ... ]
[ RETURNING | RETURN INTO outvar1, outvar2, ... ];
B) EXECUTE IMMEDIATE 'SQL statement'
[ INTO ( var1, var2, ... | record ]
[ RETURNING | RETURN INTO outvar1, outvar2, ... ]
C) EXECUTE IMMEDIATE 'SQL statement'
[ INTO ( var1, var2, ... | record ]
[ USING [ IN | OUT | IN OUT ] bindvar1, bindvar2, ... ]
D) EXECUTE IMMEDIATE 'SQL statement'
[ USING [ IN | OUT | IN OUT ] bindvar1, bindvar2, ... ]
[ RETURNING | RETURN INTO outvar1, outvar2, ... ];

Question 64

All of the following, except ____, represent steps required to perform DDL with the DBMS_SQL package.

A) OPEN_CURSOR
B) PARSE
C) BIND_VARIABLE
D) CLOSE_CURSOR

Question 65

The ____ program associates placeholders in the Cursor with PL/SQL variables.

A) PARSE
B) BIND_VARIABLE
C) CLOSE_CURSOR
D) EXECUTE

Question 66

Two methods can be used to implement _________________________: EXECUTE IMMEDIATE and OPEN FOR.

Question 67

All of the following except ____ require parameters.

A) EXECUTE
B) BIND_VARIABLE
C) OPEN_CURSOR
D) PARSE

Question 68

In which of the following situations is the use of native dynamic SQL considered inappropriate?

A) The number and types of columns to be used is not known.
B) The number and type of bind variables is known.
C) You want to perform DDL.
D) You are executing the statement only once or twice.

Question 69

____ retrieves the data returned by a query.

A) BIND_VARIABLE
B) FETCH_ROWS
C) COLUMN_VALUE
D) DEFINE_COLUMN

Question 70

Which of the following is a command line utility that assists in hiding source code?

A) wrap utility.
B) obfuscate utility.
C) DBMS_DDL utility.
D) wrapcode utility.

Question 71

The _________________________ package was introduced with Oracle version 7 to allow more dynamic SQL code within PL/SQL coding.

Question 72

Attempting to use a(n) _________________________ to provide an identifier, such as a column name or table name, in an SQL statement within PL/SQL raises an error.

Question 73

The _________________________ package is used to display messages to the screen from a stored program unit or an anonymous block.

Question 74

The _________________________ package provides the capabilities of a job scheduler that sets up programs to run at specified times.

Question 75

A(n) _________________________ is a pointer to a cursor or SQL work area.

Question 76

____ is responsible for checking syntax.

A) PARSE
B) OPEN_CURSOR
C) CLOSE_CURSOR
D) OPEN_PARSER

Question 77

The _________________________ action of the DBMS_DDL package computes statistics on a database object, typically tables and indexes, such as the range of values.

Question 78

The ____ method can be used to implement native dynamic SQL.

A) EXECUTE
B) EXECUTE FOR
C) OPEN FOR
D) OPEN IMMEDIATE

Question 79

The _________________________ method of the ANALYZE_OBJECT procedure in the DBMS_DDL package erases the statistics that already exist in the data dictionary for an object.

Question 80

The use of a(n) _________________________ statement, such as ALTER TABLE or CREATE TABLE, directly within PL/SQL produces an error.