lob_user_schema.sql 1.68 KB
Newer Older
O'Reilly Media, Inc. committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
-- The SQL*Plus script lob_user_schema.sql performs the following:
--   1. Creates the lob_user.
--   2. Creates the database objects.
--   3. Populates the database tables with example data.

-- This script should be run by the system user (or the DBA).
CONNECT system/manager;

-- drop lob_user
DROP USER lob_user CASCADE;

-- create lob_user
CREATE USER lob_user IDENTIFIED BY lob_password;

-- allow the user to connect, create database objects and
-- create directory objects (for the BFILEs)
GRANT connect, resource, create any directory TO lob_user;

-- connect as lob_user
CONNECT lob_user/lob_password;

-- create the tables
CREATE TABLE clob_content (
  file_name   VARCHAR2(40) NOT NULL,
  clob_column CLOB NOT NULL
);

CREATE TABLE blob_content (
  file_name   VARCHAR2(40) NOT NULL,
  blob_column BLOB NOT NULL
);

CREATE TABLE bfile_content (
  file_name    VARCHAR2(40) NOT NULL,
  bfile_column BFILE NOT NULL
);

CREATE TABLE long_content (
  file_name   VARCHAR2(40) NOT NULL,
  long_column LONG NOT NULL
);

CREATE TABLE long_raw_content (
  file_name       VARCHAR2(40) NOT NULL,
  long_raw_column LONG RAW NOT NULL
);

-- create the BFILE directory
CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS 'C:\sample_files';

-- create the function retrieve_LONG_func()
CREATE OR REPLACE FUNCTION retrieve_LONG_func(
  p_file_name IN VARCHAR2
) RETURN LONG IS
  long_column_var LONG;
BEGIN

  -- retrieve the LONG column into a LONG variable
  SELECT
    long_column
  INTO
    long_column_var
  FROM
    long_content
  WHERE
    file_name = p_file_name;

  -- return the LONG variable
  RETURN long_column_var;

END retrieve_LONG_func;
/