Database Reference
In-Depth Information
ate a copy of the SONG table called SONG2. This way we can re-create
the RECORDING column as a BFILE pointer. Figure 16.3 shows the
structure of the new SONG2 table, where the BFILE datatype is shown as
a BINARY FILE LOB.
CREATE TABLE SONG2 AS SELECT SONG_ID, ARTIST_ID, TITLE
, RECORDING_DATE, PLAYING_TIME
FROM SONG;
ALTER TABLE SONG2 ADD (RECORDING BFILE);
A BFILE datatype uses the BFILENAME function for instantiation.
The syntax is as follows:
BFILENAME ('directory', 'filename')
. The directory is an Oracle
DIRECTORY object, essentially being an alias type full path name pointer
to a directory on a server.
CREATE DIRECTORY MULTIMEDIA
AS 'c:\oracle\ora10\oltp\multimedia';
Note:
The CREATE ANY DIRECTORY system privilege may be required.
UPDATE SONG2 SET RECORDING =
BFILENAME('MULTIMEDIA', 'recording.wav')
WHERE SONG_ID = 1;
The next section examines user-defined datatypes.
16.2.3
User-Defined Datatypes
Creating an object type with the CREATE TYPE command, such as a
VARRAY or nested table type, creates a user-defined datatype. Oracle some-
times refers to user-defined datatypes as user types. All these user-defined
datatypes are available for use in column definitions, just like the standard
Oracle Database 10
g
datatypes. You will see how this works when you reach
Chapter 18 and begin creating your own tables. There will be much more on
VARRAY and nested table collection datatypes later in this chapter.
The following commands describe the ARTIST table's definition and a
collection object contained therein. The first DESC command shows the
ARTIST table, and the second DESC command displays the structure of
the INSTRUMENTSCOLLECTION type. The INSTRUMENTSCOL-
