🔮 Module 4: Advanced Data Types

-- Create table with JSON column
CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  employee_data JSON  -- JSON data type
);

-- Insert JSON document
INSERT INTO employees 
VALUES (
  101,
  'John Smith',
  '{"email":"john@company.com","department":"IT","salary":85000,"skills":["SQL","Java","Python"]}'
);

-- Query JSON data
SELECT 
  employee_id,
  name,
  employee_data.email,
  employee_data.salary,
  employee_data.skills[0]
FROM employees;

-- Access nested JSON with path notation
SELECT 
  JSON_VALUE(employee_data, '$.email') AS email,
  JSON_VALUE(employee_data, '$.department') AS dept,
  JSON_QUERY(employee_data, '$.skills') AS all_skills
FROM employees;

-- Unnest JSON arrays with JSON_TABLE
SELECT 
  e.employee_id,
  e.name,
  jt.skill
FROM employees e
CROSS JOIN JSON_TABLE(
  e.employee_data,
  '$.skills[*]' COLUMNS (skill VARCHAR2(50) PATH '$')
) jt;

-- Create table with XMLType column
CREATE TABLE documents (
  doc_id NUMBER PRIMARY KEY,
  doc_content XMLType
);

-- Insert XML document
INSERT INTO documents 
VALUES (
  1,
  XMLType(
    '
     
       101
       John Smith
       john@company.com
       
         SQL
         Java
       
     '
  )
);

-- Query XML using XPath
SELECT 
  doc_id,
  extractValue(doc_content, '/employee/name/text()') AS name,
  extractValue(doc_content, '/employee/email/text()') AS email
FROM documents;

-- Create table with LOB columns
CREATE TABLE documents (
  doc_id NUMBER PRIMARY KEY,
  doc_name VARCHAR2(100),
  content CLOB,        -- Large text
  attachment BLOB      -- Binary file
);

-- Insert LOB data (small chunks)
INSERT INTO documents (doc_id, doc_name, content)
VALUES (1, 'Report', 'This is a large text document...');

-- Check LOB size
SELECT 
  doc_id,
  DBMS_LOB.getlength(content) AS content_size,
  DBMS_LOB.getlength(attachment) AS attachment_size
FROM documents;

-- Read LOB data
DECLARE
  v_content CLOB;
  v_chunk VARCHAR2(32767);
BEGIN
  SELECT content INTO v_content 
  FROM documents WHERE doc_id = 1;
  
  DBMS_LOB.read(v_content, 1000, 1, v_chunk);
  DBMS_OUTPUT.put_line(v_chunk);
END;

-- Define VARRAY type
CREATE TYPE skills_array AS VARRAY(10) OF VARCHAR2(50);
/

-- Use VARRAY in table
CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  skills skills_array
);

-- Insert VARRAY data
INSERT INTO employees 
VALUES (
  101,
  'John Smith',
  skills_array('SQL', 'Java', 'Python')
);

-- Access VARRAY elements
SELECT 
  employee_id,
  name,
  skills(1) AS first_skill,
  TABLE(skills) AS all_skills
FROM employees;

-- Define Nested Table type
CREATE TYPE projects_table AS TABLE OF VARCHAR2(100);
/

-- Create table storage for nested table
CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  projects projects_table
)
NESTED TABLE projects STORE AS projects_nt;

-- Insert nested table data
INSERT INTO employees
VALUES (
  101,
  'John Smith',
  projects_table('Project A', 'Project B', 'Project C')
);

-- Query nested table
SELECT 
  e.employee_id,
  e.name,
  p.column_value AS project
FROM employees e,
TABLE(e.projects) p;

-- Define object type (like a class)
CREATE TYPE address_type AS OBJECT (
  street VARCHAR2(100),
  city VARCHAR2(50),
  state VARCHAR2(2),
  zip_code VARCHAR2(10)
);
/

-- Use object type in table
CREATE TABLE customers (
  customer_id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  address address_type,
  phone VARCHAR2(20)
);

-- Insert object data
INSERT INTO customers
VALUES (
  1,
  'John Smith',
  address_type('123 Main St', 'New York', 'NY', '10001'),
  '555-1234'
);

-- Access object attributes
SELECT 
  customer_id,
  name,
  address.street,
  address.city,
  address.zip_code
FROM customers;

-- Define object type with methods
CREATE TYPE employee_type AS OBJECT (
  emp_id NUMBER,
  name VARCHAR2(100),
  salary NUMBER,
  MEMBER FUNCTION get_annual_salary RETURN NUMBER,
  MEMBER FUNCTION is_high_earner RETURN BOOLEAN
);
/

-- Implement methods
CREATE TYPE BODY employee_type AS
  MEMBER FUNCTION get_annual_salary RETURN NUMBER IS
  BEGIN
    RETURN salary * 12;
  END;
  
  MEMBER FUNCTION is_high_earner RETURN BOOLEAN IS
  BEGIN
    RETURN salary > 10000;
  END;
END;
/

-- Create functional index on JSON field for performance
CREATE INDEX emp_email_idx ON employees(
  JSON_VALUE(employee_data, '$.email')
);

-- Use JSON_TABLE for better performance with large result sets
SELECT *
FROM JSON_TABLE(
  SELECT employee_data FROM employees,
  '$[*]' COLUMNS (
    email VARCHAR2(100) PATH '$.email',
    name VARCHAR2(100) PATH '$.name',
    salary NUMBER PATH '$.salary'
  )
);