《Oracle PL/SQL开发指南》学习笔记24——大对象（Large Objects）（章节回顾、测试）

什么？大对象你用不到？

错，只是一般情况下用不到。二般情况下，不得不用啊！所以，你还是得好好学！

使用内部存储的LOB类型（Working with Internally Stored LOB Types）

Review Section
This section has described the following points about working with LOBs:
1. Character Large Objects (CLOBs), National Character Large Objects (NCLOBs), and Binary Large Objects (BLOBs) support character strings of 8 to 128 terabytes. Oracle Database 12c lets you set db_block_size to a value of 2KB to 32KB.
2. The CLOB, NCLOB, and BLOB data types are object types, and Oracle doesn't provide a convenient SQL built-in function to construct a LOB.
3. The dbms_lob package contains functions and procedures that let you read, write, and append data to CLOB variables and columns.
4. The terms descriptor and locator are sometimes used interchangeably, but Oracle prefers locator to describe the pointer to the location of CLOBs stored in the database.
5. You have NULL, empty, or populated CLOB variables and columns.
6. Oracle supports direct writes of strings in a PL/SQL context but not in a SQL context.
This means you need to write a function that takes a string and returns a CLOB to pass a string to the VALUES clause of an INSERT statement.
7. When inserting or updating data larger than 32,767 bytes, you need to initialize a CLOB with a call to the empty_clob function. You pass the segments of data through the locator in chunks no larger than 32,768 bytes.

 

将文件读入内部存储的列（Reading Files into Internally Stored Columns）

Review Section
This section has described the following points about how you read and write from local files into CLOB, NCLOB, and BLOB columns:
1. Virtual directories support the use of the bfilename function, which lets you find external files.
2. The dbms_lob package's loadclobfromfile procedure lets you read a character file in its entirety or in chunks.
3. The dbms_lob package's loadblobfromfile procedure lets you read a binary file in its entirety or in chunks.
4. A pass-by-reference procedure enables a web-based program to open a locator value, which lets the program upload large objects through the HTTP protocol. It does so by providing access to a column's locator value.
5. External programming languages open, access, and load large objects through pass-byreference stored procedures.

 

使用二进制文件（Working with Binary Files (BFILEs)）

Review Section
This section has described the following points about working with BFILE types:
1. BFILEs are physically stored externally while they hold a reference descriptor or locator that is stored in the column.
2. A BFILE relies on virtual directories.
3. Virtual directories are stored in the database catalog, and they hold the path resolution to find external BFILEs.
4. You can architect a solution where you can discover the canonical path without hardcoding it, but it requires special grants and synonyms to make it work.

 

了解DBMS_LOG包（Understanding the DBMS_LOB Package）

Review Section
This section has qualified the following points about working with the dbms_lob package.
1. To understand how to use the dbms_lob package, you need to understand the key package constants and exceptions.
2. Many of the procedures in the dbms_lob package are pass-by-reference, which means you need to carefully wrap their behaviors in other program units.

 

Mastery Check

The mastery check is a series of true-or-false and multiple-choice questions that let you confirm
how well you understand the material in the chapter. You may check Appendix I for answers to
these questions.

True or False:
1. CLOB and NCLOB data types are object types and require explicit construction in a SQL context.
True. CLOB and NCLOB data types are object types and require explicit construction in a SQL context.
2. CLOB and NCLOB data types are subclasses to a generic LOB class.
False. CLOB and NCLOB data types are not subclasses to a generic LOB class.
3. The BLOB data type holds binary streams.
True. The BLOB data type may hold binary streams but may also hold character streams.
As a rule, BLOB data types hold only binary streams.
4. You can assign a string literal to a CLOB inside a VALUES clause of an INSERT statement.
False. You can’t assign a string literal to a CLOB or NCLOB inside a VALUES clause of an INSERT statement. You must push the string through a stored function that creates a temporary CLOB and returns a CLOB data type.
5. A stored function can convert a LONG data type to a CLOB data type.
True. A stored function can convert a LONG data type to a CLOB data type, an example of which is provided in the sidebar “Converting a LONG to a CLOB.”
6. The empty_clob function supports the CLOB, NCLOB, and BLOB data types.
False. The empty_clob function supports the CLOB and NCLOB data types but not the BLOB data type. You must use the empty_blob function when you work with BLOB data types.
7. You can assign strings of hexadecimal values to BLOB variables in a PL/SQL context.
True. You can assign hexadecimal values to a BLOB variable in either a SQL or PL/SQL context.

8. A BFILE depends on a virtual directory to find the external file.
True. A locator for a BFILE is a combination of a virtual directory and a filename.
9. A SELECT-INTO statement can assign a string to a CLOB variable.
True. A SELECT-INTO statement runs only inside a PL/SQL block, and it assigns a result from the SELECT-list into a local CLOB variable. This type of assignment is limited to the maximum size of a VARCHAR2, which is 32,767 bytes when the max_string_size parameter is set to EXTENDED.
10. A SELECT-INTO statement can assign a LONG column value to a CLOB variable.
False. A SELECT-INTO statement can’t assign a LONG column because it doesn’t support
the LONG data type. The TO_CHAR function doesn’t accept a LONG call parameter either.
That leaves you with the dbms_sql package as your only means to convert a LONG to a CLOB or NCLOB data type.

Multiple Choice:
11. Which of the following are pass-by-reference procedures in the dbms_lob package?
(Multiple answers possible)
A. lob_readonly
B. write
C. lob_readwrite
D. writeappend
E. isopen
B and D are correct. The write and writeappend procedures of the dbms_lob
package are pass-by-reference procedures. lob_readonly and lob_readwrite are
package constants, and isopen is a function.
12. Which of the following are functions in the dbms_lob package? (Multiple answers possible)
A. open
B. isopen
C. converttoblob
D. unopened_file
E. issecurefile
B and E are correct. The isopen and issecurefile functions of the dbms_lob
package are pass-by-reference functions. open and converttoblob are procedures of
the dbms_lob package, and isopen is a function.
13. Which of the following are exceptions in the dbms_lob package? (Multiple answers possible)
A. OPEN_TOOMANY
B. NOPRIV_DIR
C. NOEXIST_DIRECTORY

D. UNINTIALIZED_BLOB
E. GETOPTIONS
A and C are correct. open_toomany and noexist_directory are exceptions declared
in the dbms_lob package. nopriv_dir and uninitialized_blob are not any part
of the dbms_lob package. getoptions is a function in the dbms_lob package.
14. Which of the following are LOBs in an Oracle Database 12c database? (Multiple answers
possible)
A. A BLOB
B. A CLOB
C. A NCLOB
D. A BFILE
E. All of the above
E is correct. The BLOB, CLOB, NCLOB, and BFILE are all valid large objects in the Oracle
Database 12c database.
15. Which of the following are internally stored LOBs in Oracle Database 12c? (Multiple
answers possible)
A. A BLOB
B. A CLOB
C. A NCLOB
D. A BFILE
E. All of the above
A, B, and C are correct. The BLOB, CLOB, and NCLOB are all valid large objects that are
stored inside the Oracle Database 12c database. The BFILE only stores a locator and
filename in the database; the physical file is stored outside the database as a file.