rem
rem $Header: adloci.sql 11-aug-99.10:58:55 hyeh Exp $
rem
rem adloci.sql
rem
rem Copyright (c) 1999, Oracle Corporation.  All rights reserved.
rem
rem    NAME
rem      adloci.sql - LOB sampels for App Dev guide
rem
rem    DESCRIPTION
rem      This demo file provides DML examples of inserting, selecting,
rem      updating, and deleting tables with CLOB, BLOB, and BFILE datatypes.
rem
rem    NOTES
rem      The expected results are commented out after each statement.
rem
rem    MODIFIED   (MM/DD/YY)
rem    hyeh        08/11/99 - set session format
rem    mjaeger     07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem    ytsai       02/01/99 - Created
rem

REMARK >>>> Set System Variables For Current SQLPlus Session <<<<
SET FEEDBACK 1
SET NUMWIDTH 10
SET PAGESIZE 24
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET DEFINE '^'

SET ECHO ON;
/* chapter 2 examples follow */
CONNECT adldemo/adldemo;

/* You can initialize the LOBs in Multimedia_tab by using the following SQL
   INSERT statement: */
INSERT INTO Multimedia_tab VALUES (1001, EMPTY_CLOB(), EMPTY_CLOB(), NULL,
    HEXTORAW('B1C2F3'), EMPTY_BLOB(), NULL, NULL, NULL, NULL);
/* 1 row created. */

/*  */

/* Setting an Internal LOB to Empty */
INSERT INTO a_table VALUES (EMPTY_BLOB());
/* 1 row created. */

/*  */

/* In the new row of table Multimedia_tab,
   the columns STORY and FLSUB are initialized using EMPTY_CLOB(),
   the columns FRAME and SOUND are initialized using EMPTY_BLOB(),
   the column TRANSSCRIPT in the nested table is initialized
   using EMPTY_CLOB(),
   the column DRAWING in the column object is initialized using EMPTY_BLOB():
   */
INSERT INTO Multimedia_tab
  VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), NULL,
             HEXTORAW('A1B2F8'), EMPTY_BLOB(), NULL,
          InSeg_tab(InSeg_typ(1, NULL, 'Ted Koppell', 'Jimmy Carter', NULL,
                    EMPTY_CLOB())), NULL,
          Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(), NULL)
         );
/* 1 row created. */

/*  */

/* In the new row of table Voiceover_tab, the column SCRIPT is
   initialized using EMPTY_CLOB(): */
INSERT INTO Voiceover_tab
    VALUES ('Abraham Lincoln', EMPTY_CLOB(), 'James Earl Jones', 1, NULL);
/* 1 row created. */

/*  */

/* Store records in the archive table VoiceoverLib_tab: */
INSERT INTO VoiceoverLib_tab
    VALUES ('George Washington', EMPTY_CLOB(), 'Robert Redford', 1, NULL);
/* 1 row created. */

/*  */

/* Insert values into Voiceover_tab by selecting from VoiceoverLib_tab: */
INSERT INTO Voiceover_tab
   (SELECT * from VoiceoverLib_tab
       WHERE Take = 1);
/* 1 row created. */

/*  */

/* Insert value into ArchiveSounds_tab */
INSERT INTO ArchiveSounds_tab VALUES (1, NULL);
/* 1 row created. */

/* Insert value into long_tab */
INSERT INTO long_tab VALUES (1, NULL);
/* 1 row created. */

/* Example: Copy Long to LOB Using SQL */
INSERT INTO Multimedia_tab (clip_id,sound) SELECT id, TO_LOB(SoundEffects)
   FROM ArchiveSounds_tab WHERE id =1;
/* 1 rows created. */

INSERT INTO clob_tab SELECT id, TO_LOB(long_col)
   FROM long_tab;
/* 1 row created. */

/*  */

/* In the following SQL statement, 255 is the amount to read
   and 1 is the starting offset from which to read: */
SELECT DBMS_LOB.SUBSTR(Sound, 255, 1) FROM Multimedia_tab where Clip_ID = 1;

/*  */

/* Example: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB() Using SQL */
UPDATE Multimedia_tab SET Story = EMPTY_CLOB() WHERE Clip_ID = 1;
/* 2 row updated. */

/*  */

UPDATE Multimedia_tab SET FLSub = EMPTY_CLOB() WHERE Clip_ID = 1;
/* 2 row updated. */

/*  */

UPDATE multimedia_tab SET Sound = EMPTY_BLOB() WHERE Clip_ID = 1;
/* 2 row updated. */

/*  */

/* Example: Update as Select Using SQL DML */
INSERT INTO VoiceoverLib_tab
    VALUES ('Abraham Lincoln', EMPTY_CLOB(), 'Robert Redford', 101, NULL);
/* 1 row created. */

/*  */

UPDATE Voiceover_tab SET (Originator, Script, Actor, Take, Recording) =
    (SELECT * FROM VoiceoverLib_tab T2 WHERE T2.Take = 101);
/* 2 rows updated. */

/* above needs a SET = clause; try */
UPDATE Voiceover_tab SET (Originator,script,actor,take,recording) = (
SELECT * FROM VoiceoverLib_tab T2 WHERE T2.Take = 101);
/* 2 rows updated. (Note the number of rows updated depends on the
 number of rows in voiceover_tab */

/*  */

UPDATE Multimedia_tab Mtab
  SET Voiced_ref =
    (SELECT REF(Vref) FROM Voiceover_tab Vref
       WHERE Vref.Actor = 'James Earl Jones' AND Vref.Take = 1)
  WHERE Mtab.Clip_ID = 1;
/* 2 rows updated. note the number of rows updated seems determined
 by number of rows in voiceover_tab */

/*  */

/* Example: Delete a LOB Using SQL DML */
DELETE FROM Multimedia_tab
     WHERE Clip_ID = 10;
/* 0 rows deleted; need actual data. */

/*  */

/*Chapter 3 examples follow */

/* Copy All or Part of One (Temporary) LOB to Another */
INSERT INTO Voiceover_tab
    (SELECT * FROM VoiceoverLib_tab Vtab1
     WHERE Vtab1.Take = 101);
/* 1 row created. */

/*  */

/*  chapter 4 examples follow */

/* Example: Insert a Row Containing a BFILE as Select Using SQL */
 INSERT INTO Voiceover_tab
   (SELECT * from VoiceoverLib_tab
       WHERE Take = 101);
/* 1 rows created. */

/*  */

/* Example: Insert a Row by means of BFILENAME() Using SQL */
/* Note that this is the same INSERT statement as applied
   to internal persistent LOBs but with the BFILENAME() function
   added to initialize the BFILE columns.
   */

INSERT INTO voiceover_tab VALUES ('Abraham Lincoln', EMPTY_CLOB(), 'James Earl
Jones',1,NULL);
/* 1 row created. */

INSERT INTO Multimedia_tab VALUES (3, EMPTY_CLOB(), EMPTY_CLOB(),
               BFILENAME('PHOTO_DIR', 'LINCOLN_PHOTO'),
               EMPTY_BLOB(), EMPTY_BLOB(),
(SELECT REF(Vref) FROM Voiceover_tab Vref WHERE Actor = 'James Earl Jones'),
               NULL, BFILENAME('AUDIO_DIR', 'LINCOLN_AUDIO'),
               MAP_TYP('Gettysburg', 23, 34, 45, 56, EMPTY_BLOB(), NULL));
/* 1 row created. */

/* Example: Update a BFILE by means of BILENAME() Using SQL */
UPDATE Multimedia_tab
    SET Photo = BFILENAME('PHOTO_DIR', 'Nixon_photo') where Clip_ID = 3;
/* 1 row updated. */

/*  */

/* Example: Update a BFILE as Select Using SQL */

UPDATE Voiceover_tab
    SET (originator,script,actor,take,recording) =
    (SELECT * FROM VoiceoverLib_tab VLtab WHERE VLtab.Take = 101);
/* 5 rows updated */
/*  */


/* Example: Delete a Row from a Table Using SQL */
DELETE FROM Multimedia_tab
     WHERE Clip_ID = 3;
/* 1 row deleted. */

