61 lines
2.7 KiB
MySQL
61 lines
2.7 KiB
MySQL
|
-- +----------------------------------------------------------------------------+
|
||
|
|
-- | Jeffrey M. Hunter |
|
||
|
|
-- | jhunter@idevelopment.info |
|
||
|
|
-- | www.idevelopment.info |
|
||
|
|
-- |----------------------------------------------------------------------------|
|
||
|
|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
|
||
|
|
-- |----------------------------------------------------------------------------|
|
||
|
|
-- | DATABASE : Oracle |
|
||
|
|
-- | FILE : example_create_clob.sql |
|
||
|
|
-- | CLASS : Examples |
|
||
|
|
-- | PURPOSE : Example SQL script that demonstrates how to create tables |
|
||
|
|
-- | containing a CLOB datatype in Oracle8i and higher. One of the |
|
||
|
|
-- | biggest differences between creating a CLOB in Oracle8 and |
|
||
|
|
-- | Oracle8i or higher is the use of the INDEX clause within the LOB|
|
||
|
|
-- | clause declaration. In Oracle8 it is possible to name the LOB |
|
||
|
|
-- | INDEX and declare a tablespace and storage clause for it. With |
|
||
|
|
-- | versions Oracle8i and higher, it is still possible to name the |
|
||
|
|
-- | INDEX LOB SEGMENT using the INDEX clause but these versions of |
|
||
|
|
-- | Oracle (8i and higher) will simply ignore anything else within |
|
||
|
|
-- | the INDEX clause (like tablespaces and storage clause.) From |
|
||
|
|
-- | what I have read Oracle is deprecating the tablespace and |
|
||
|
|
-- | storage clauses from being used within the INDEX clause. |
|
||
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
||
|
|
-- | environment before attempting to run it in production. |
|
||
|
|
-- +----------------------------------------------------------------------------+
|
||
|
|
|
||
|
|
DROP TABLE xml_documents
|
||
|
|
/
|
||
|
|
|
||
|
|
CREATE TABLE xml_documents (
|
||
|
|
docname VARCHAR2(200)
|
||
|
|
, xmldoc CLOB
|
||
|
|
, log CLOB
|
||
|
|
, timestamp DATE
|
||
|
|
)
|
||
|
|
LOB (xmldoc)
|
||
|
|
STORE AS xml_documents_lob (
|
||
|
|
TABLESPACE lob_data
|
||
|
|
STORAGE (
|
||
|
|
INITIAL 1m NEXT 1m PCTINCREASE 0 MAXEXTENTS unlimited
|
||
|
|
)
|
||
|
|
INDEX xml_documents_lob_idx
|
||
|
|
)
|
||
|
|
LOB (log)
|
||
|
|
STORE AS xml_log_lob (
|
||
|
|
TABLESPACE lob_data
|
||
|
|
STORAGE (
|
||
|
|
INITIAL 1m NEXT 1m PCTINCREASE 0 MAXEXTENTS unlimited
|
||
|
|
)
|
||
|
|
INDEX xml_log_lob_idx
|
||
|
|
)
|
||
|
|
TABLESPACE users
|
||
|
|
STORAGE (
|
||
|
|
INITIAL 256k
|
||
|
|
NEXT 256k
|
||
|
|
MINEXTENTS 1
|
||
|
|
MAXEXTENTS 121
|
||
|
|
PCTINCREASE 0
|
||
|
|
)
|
||
|
|
/
|