Understanding the LONG and LONG RAW Types

I mentioned at the start of this chapter that LOBs are now the preferred storage type for large blocks of data, but you may encounter older databases that still use the following types:

• LONG    Used to store up to 2 gigabytes of character data

• LONG RAW    Used to store up to 2 gigabytes of binary data

• RAW    Used to store up to 4 kilobytes of binary data

In this section, you ll learn how to use LONG and LONG RAW types in SQL and PL/SQL. RAW may be used in the same manner as a LONG RAW , so I ve omitted coverage of RAW .

The Example Tables

In this section, I ll use two new tables in the examples (created by the lob_schema.sql script):

• long_content    Contains a LONG column named long_column

• long_raw_content    Contains a LONG RAW column named long_raw_column

In addition, both of these tables also contain a column to store the name of the file from which the content was originally read. These two tables are defined as follows :

 CREATE TABLE long_content (id    INTEGER PRIMARY KEY,  long_column LONG NOT NULL); CREATE TABLE long_raw_content (id    INTEGER PRIMARY KEY,  long_raw_column LONG RAW NOT NULL); 

Using LONG and LONG RAW Columns

In this section, you ll learn how to use LONG and LONG RAW columns from within SQL. The following INSERT statements add rows to the long_content table:

 INSERT INTO long_content (id,  long_column) VALUES (1,  'Creeps in this petty pace'); INSERT INTO long_content (id,  long_column) VALUES (2,  ' from day to day'); 

The next INSERT adds a row to the long_raw_content table:

 INSERT INTO long_raw_content (id,  long_raw_column) VALUES (1,  '100111010101011111'); 

The following query retrieves the row from long_content :

  SELECT *   FROM long_content;  ID ---------- LONG_COLUMN -------------------------  1 Creeps in this petty pace  2  from day to day