rem
rem $Header: adlsetup.sql 11-aug-99.10:58:44 hyeh Exp $
rem
rem adlsetup.sql
rem
rem Copyright (c) 1999, Oracle Corporation.  All rights reserved.
rem
rem    NAME
rem      adlsetup.sql - LOB samples for App Dev guide
rem
rem    DESCRIPTION
rem      This demo file provides DDL exmaples of creating object types and
rem      tables with CLOB, BLOB, and BFILE datatypes.
rem
rem    NOTES
rem      Directories are created in the driver file tkoodemo.tsc
rem      for protability in the directory path.
rem      If you don't use tkoodemo.tsc to run adlsetup.sql/adlplsql.sql
rem      you will need to uncomment the CREATE DIRECTORY statements below.
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/22/99 - move create directory to tkoodemo.tsc
rem    ytsai       02/01/99 - Created
rem
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 '^'

/* chapter 2 DDL follows */
SET ECHO ON;
CONNECT SYS/knl_test7 AS SYSDBA;
DROP USER adldemo CASCADE;

CREATE USER adldemo identified by adldemo;
GRANT CONNECT, RESOURCE to adldemo;
rem Uncomment the follow statements if you are not running from tkoodemo.tsc
rem On Unix:
rem CREATE DIRECTORY AUDIO_DIR AS '/vobs/rdbms/demo/';
rem CREATE DIRECTORY FRAME_DIR AS '/vobs/rdbms/demo/';
rem CREATE DIRECTORY PHOTO_DIR AS '/vobs/rdbms/demo/';
rem On NT:
rem CREATE DIRECTORY AUDIO_DIR AS '\oracle\rdbms\demo\';
rem CREATE DIRECTORY FRAME_DIR AS '\oracle\rdbms\demo\';
rem CREATE DIRECTORY PHOTO_DIR AS '\oracle\rdbms\demo\';
GRANT READ ON DIRECTORY audio_dir to adldemo;
GRANT READ ON DIRECTORY frame_dir to adldemo;
GRANT READ ON DIRECTORY photo_dir to adldemo;

CONNECT adldemo/adldemo

CREATE TABLE a_table (blob_col BLOB);

/* Table created.*/

CREATE TYPE Voiced_typ AS OBJECT (
   Originator      VARCHAR2(30),
   Script          CLOB,
   Actor           VARCHAR2(30),
   Take            NUMBER,
   Recording       BFILE
)
/

/* Create a type InSeg_typ as the base type for the nested table containing
   a LOB: */

CREATE TYPE InSeg_typ AS OBJECT (
   Segment         NUMBER,
   Interview_Date  DATE,
   Interviewer     VARCHAR2(30),
   Interviewee     VARCHAR2(30),
   Recording       BFILE,
   Transcript      CLOB
)
/

/* Type created, but need a nested table of that type to embed in
 multi_media_tab; so...  */

CREATE TYPE InSeg_tab AS TABLE of InSeg_typ
/

/* Create Type Map_typ using SQL DDL as a basis for the table that will
   contain the column object: */

CREATE TYPE Map_typ AS OBJECT (
   Region          VARCHAR2(30),
   NW              NUMBER,
   NE              NUMBER,
   SW              NUMBER,
   SE              NUMBER,
   Drawing         BLOB,
   Aerial          BFILE
)
/

/* Create support table MapLib_tab as an archive of maps using SQL DDL: */

CREATE TABLE Map_Libtab of Map_typ;

CREATE TABLE Voiceover_tab of Voiced_typ (
Script DEFAULT EMPTY_CLOB(),
   CONSTRAINT Take CHECK (Take IS NOT NULL),
   Recording DEFAULT NULL
);

CREATE TABLE VoiceoverLib_tab of Voiced_typ (
Script DEFAULT EMPTY_CLOB(),
   CONSTRAINT TakeLib CHECK (Take IS NOT NULL),
   Recording DEFAULT NULL
);

CREATE TABLE Multimedia_tab (
   Clip_ID         NUMBER NOT NULL,
   Story           CLOB default EMPTY_CLOB(),
   FLSub           NCLOB default EMPTY_CLOB(),
   Photo           BFILE default NULL,
   Frame           BLOB default EMPTY_BLOB(),
   Sound           BLOB default EMPTY_BLOB(),
   Voiced_ref      REF Voiced_typ,
   InSeg_ntab      InSeg_tab,
   Music           BFILE default NULL,
   Map_obj         Map_typ
) NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab;

CREATE TABLE long_tab (
id NUMBER,
long_col LONG);

CREATE TABLE lob_tab (
id NUMBER,
blob_col BLOB);

CREATE TABLE clob_tab (
id NUMBER,
clob_col CLOB);

/* chapter 3 DDL follows */

CREATE TABLE long_raw_tab (id number, long_raw_col long raw);

CREATE TABLE ArchiveSounds_tab (
id NUMBER,
SoundEffects LONG RAW);

/* chapter 4 DDL follows */

/* none  */

