Oracle Built−in Packages- P82: 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 CREATE TABLE my_book_files file_descr VARCHAR2 40 book_file BFILE or an attribute in a TYPE CREATE OR REPLACE PACKAGE blobby IS TYPE adpage_rectype IS RECORD customer_id NUMBER persuasive_picture BFILE or a parameter RETURN type in a PL SQL procedure or function CREATE OR REPLACE FUNCTION blended_image old_image IN BFILE new_image IN BFILE RETURN BFILE IS BEGIN END In each case the BFILE value points to an operating-system file residing on the server and outside of the database. The BLOB datatype The BLOB datatype is used to store large binary objects within the database the objects can be up to four gigabytes and may consist of unstructured raw data. A BLOB could be a PL SQL variable DECLARE corporate_logo BLOB or a column in a table CREATE TABLE my_book_diagrams chapter_descr VARCHAR2 40 diagram_no INTEGER diagram BLOB or an attribute in a TYPE CREATE OR REPLACE PACKAGE chapter IS TYPE diagram_rectype IS RECORD chapter_num NUMBER diagram BLOB or a parameter RETURN type in a PL SQL procedure or function CREATE OR REPLACE PROCEDURE show_blob blob_in IN BLOB IS BEGIN END A BLOB is an internal LOB and therefore can participate in database transactions. In other words changes made to a BLOB can be rolled back or committed along with other changes during a transaction. BLOBs cannot span transactions. LOB Datatypes 396 Appendix A What s on the Companion Disk The CLOB datatype The CLOB datatype is used to store large blocks within the database. The blocks can be up to four gigabytes of single-byte character data. A CLOB could be a PL SQL variable DECLARE gettysburg_address_text CLOB or a column in a table CREATE TABLE my_book_text chapter_descr VARCHAR2 40 chapter_text CLOB or an attribute in a TYPE CREATE OR REPLACE PACKAGE speechifying IS TYPE poll_results_rectype IS RECORD speech_num NUMBER speech_txt CLOB or a parameter RETURN type in a PL SQL procedure or function CREATE OR REPLACE PROCEDURE edit_speech
