tailieunhanh - Oracle Built−in Packages- P87

Oracle Built−in Packages- P87: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk FUNCTION lob_loc IN BLOB BFILE amount IN INTEGER 32767 offset IN INTEGER 1 RETURN RAW FUNCTION lob_loc IN CLOB CHARACTER SET ANY_CS amount IN INTEGER 32767 offset IN INTEGER 1 RETURN VARCHAR2 CHARACTER SET lob_loc CHARSET The overloaded specification allows SUBSTR to be used with all types of LOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input. The SUBSTR function and procedure provide similar functionality. READ is a procedure while SUBSTR is a function. However READ will raise NO_DATA_FOUND and INVALID_ARGVAL exceptions while SUBSTR will ignore these exceptions when they occur and will return NULL. SUBSTR can also be called from within a SQL statement but cannot since it is a procedure. Parameters are summarized in the following table. Parameter Description lob_loc Locator for the LOB to be read amount Number of bytes BFILE BLOB or characters CLOB NCLOB to read offset Location of the byte BFILE BLOB or character CLOB NCLOB in the LOB at which the read begins The SUBSTR function returns a NULL value for any of the following conditions One of the parameters is NULL or invalid amount 1 or amount 32767 offset 1 or offset LOBMAXSIZE Exceptions The SUBSTR function may raise one of the following exceptions UNOPENEDFILE For BFILEs. Files must be open before the SUBSTR operation. NOEXISTDIRECTORY For BFILEs. NOPRIVDIRECTORY For BFILEs. INVALIDDIRECTORY For BFILEs. Reading and Examining LOBs 421 Appendix A What s on the Companion Disk INVALIDOPERATION For BFILEs. Restrictions The program asserts a purity level with the RESTRICT_REFERENCES pragma. PRAGMA RESTRICT_REFERENCES substr WNDS RNDS WNPS RNPS Examples The following example reads the first 60 characters of the CLOB chapter_text column of the my_book_texttable using the Chapter 1 row DECLARE v_text_loc CLOB v_text_amt BINARY_INTEGER 60 .