MacLochlainns Weblog

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

ERROR 1616 (HY000): DELAYED option not supported for table 'message'

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

package vulcantech.vth.server.commands;
 
import java.io.IOException;
import java.net.Socket;
 
import vulcantech.vth.server.combeans.MessageBean;
 
public class MessageHandler implements Handler {
 
  @Override
  public void handleIt(Object... args) {
 
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
 
    DatabaseConnection dbconnection = new DatabaseConnection();
 
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
 
    synchronized (this) {
      dbconnection.executeUpdate(update);
    }
 
    try {
      sock.getOutputStream().write(1);
    } catch (IOException e) {
      e.printStackTrace();
    }
    dbconnection.close();
  }
}

package vulcantech.vth.server.commands; import java.io.IOException; import java.net.Socket; import vulcantech.vth.server.combeans.MessageBean; public class MessageHandler implements Handler { @Override public void handleIt(Object... args) { MessageBean message = (MessageBean) args[0]; Socket sock = (Socket) args[1]; DatabaseConnection dbconnection = new DatabaseConnection(); String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')"); synchronized (this) { dbconnection.executeUpdate(update); } try { sock.getOutputStream().write(1); } catch (IOException e) { e.printStackTrace(); } dbconnection.close(); } }

File Name: C:\Program Files (x86)\Zend\ZendServer\bin\JavaServer.exe
Infection: Win32/DH.FF860061{00000000-00080000-00000000}

File Name: C:\Program Files (x86)\Zend\ZendServer\bin\JavaServer.exe Infection: Win32/DH.FF860061{00000000-00080000-00000000}

C:\Imports\ImportFiles
C:\Imports\ImportLogs

C:\Imports\ImportFiles C:\Imports\ImportLogs

CREATE USER import IDENTIFIED BY import
DEFAULT TABLESPACE users QUOTA 1000M ON users
TEMPORARY TABLESPACE temp;

CREATE USER import IDENTIFIED BY import DEFAULT TABLESPACE users QUOTA 1000M ON users TEMPORARY TABLESPACE temp;

GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
,     CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER
,     CREATE TYPE,  CREATE VIEW TO import;

GRANT create CLUSTER, create INDEXTYPE, create OPERATOR , create procedure, create sequence, create session , create SYNONYM, create table, create trigger , create type, create view TO import;

CREATE DIRECTORY upload_files AS 'C:\Imports\ImportFiles';
CREATE DIRECTORY upload_logs AS 'C:\Imports\ImportLogs';

CREATE DIRECTORY upload_files AS 'C:\Imports\ImportFiles'; CREATE DIRECTORY upload_logs AS 'C:\Imports\ImportLogs';

GRANT READ ON DIRECTORY upload_files TO import;
GRANT READ, WRITE ON DIRECTORY upload_logs TO import;

GRANT READ ON DIRECTORY upload_files TO import; GRANT READ, WRITE ON DIRECTORY upload_logs TO import;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE TABLE item_import_ext_table
( asin_number           VARCHAR2(10)
, item_type             VARCHAR2(15)
, item_title            VARCHAR2(60)
, item_subtitle          VARCHAR2(60)
, item_rating            VARCHAR2(8)
, item_rating_agency     VARCHAR2(4)
, item_release_date      DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload_files
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD_LOGS':'item_import_ext_table.bad'
      DISCARDFILE 'UPLOAD_LOGS':'item_import_ext_table.dis'
      LOGFILE     'UPLOAD_LOGS':'item_import_ext_table.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('item_import.csv'))
REJECT LIMIT UNLIMITED;

CREATE TABLE item_import_ext_table ( asin_number VARCHAR2(10) , item_type VARCHAR2(15) , item_title VARCHAR2(60) , item_subtitle VARCHAR2(60) , item_rating VARCHAR2(8) , item_rating_agency VARCHAR2(4) , item_release_date DATE) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY upload_files ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'UPLOAD_LOGS':'item_import_ext_table.bad' DISCARDFILE 'UPLOAD_LOGS':'item_import_ext_table.dis' LOGFILE 'UPLOAD_LOGS':'item_import_ext_table.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('item_import.csv')) REJECT LIMIT UNLIMITED;

'B000W74EQC','DVD_WIDE_SCREEN','Harry Potter and the Sorcerer''s Stone',,'PG','MPAA','11-DEC-2007'
'B000W746GK','DVD_WIDE_SCREEN','Harry Potter and the Chamber of Secrets',,'PG','MPAA','11-DEC-2007'
'B000W796OM','DVD_WIDE_SCREEN','Harry Potter and the Prisoner of Azkaban',,'PG','MPAA','11-DEC-2007'
'B000E6EK2Y','DVD_WIDE_SCREEN','Harry Potter and the Goblet of Fire',,'PG-13','MPAA','07-MAR-2006'
'B000W7F5SS','DVD_WIDE_SCREEN','Harry Potter and the Order of the Phoenix',,'PG-13','MPAA','11-DEC-2007'
'B002PMV9FG','DVD_WIDE_SCREEN','Harry Potter and the Half-Blood Prince',,'PG','MPAA','08-DEC-2009'
'B001UV4XHY','DVD_WIDE_SCREEN','Harry Potter and the Deathly Hallows, Part 1',,'PG-13','MPAA','15-APR-2011'
'B001UV4XIS','DVD_WIDE_SCREEN','Harry Potter and the Deathly Hallows, Part 2',,'PG-13','MPAA','11-NOV-2011'

'B000W74EQC','DVD_WIDE_SCREEN','Harry Potter and the Sorcerer''s Stone',,'PG','MPAA','11-DEC-2007' 'B000W746GK','DVD_WIDE_SCREEN','Harry Potter and the Chamber of Secrets',,'PG','MPAA','11-DEC-2007' 'B000W796OM','DVD_WIDE_SCREEN','Harry Potter and the Prisoner of Azkaban',,'PG','MPAA','11-DEC-2007' 'B000E6EK2Y','DVD_WIDE_SCREEN','Harry Potter and the Goblet of Fire',,'PG-13','MPAA','07-MAR-2006' 'B000W7F5SS','DVD_WIDE_SCREEN','Harry Potter and the Order of the Phoenix',,'PG-13','MPAA','11-DEC-2007' 'B002PMV9FG','DVD_WIDE_SCREEN','Harry Potter and the Half-Blood Prince',,'PG','MPAA','08-DEC-2009' 'B001UV4XHY','DVD_WIDE_SCREEN','Harry Potter and the Deathly Hallows, Part 1',,'PG-13','MPAA','15-APR-2011' 'B001UV4XIS','DVD_WIDE_SCREEN','Harry Potter and the Deathly Hallows, Part 2',,'PG-13','MPAA','11-NOV-2011'

SET PAGESIZE 99
 
COLUMN asin_number        FORMAT A11 HEADING "ASIN #"
COLUMN item_title         FORMAT A46 HEADING "ITEM TITLE"
COLUMN item_rating        FORMAT A6  HEADING "RATING"
COLUMN item_release_date  FORMAT A11 HEADING "RELEASE|DATE"
 
SELECT   asin_number
,        item_title
,        item_rating
,        TO_CHAR(item_release_date,'DD-MON-YYYY') AS item_release_date
FROM     item_import_ext_table;

SET PAGESIZE 99 COLUMN asin_number FORMAT A11 HEADING "ASIN #" COLUMN item_title FORMAT A46 HEADING "ITEM TITLE" COLUMN item_rating FORMAT A6 HEADING "RATING" COLUMN item_release_date FORMAT A11 HEADING "RELEASE|DATE" SELECT asin_number , item_title , item_rating , TO_CHAR(item_release_date,'DD-MON-YYYY') AS item_release_date FROM item_import_ext_table;

GRANT SELECT ON sys.dba_directories TO system;

GRANT SELECT ON sys.dba_directories TO system;

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

CREATE OR REPLACE FUNCTION get_directory_path
( virtual_directory IN VARCHAR2 )
RETURN VARCHAR2 IS
  -- Define RETURN variable.
  directory_path VARCHAR2(256) := '';
  --Define dynamic cursor.
  CURSOR get_directory (virtual_directory VARCHAR2) IS
    SELECT   directory_path
    FROM     sys.dba_directories
    WHERE    directory_name = virtual_directory;
  -- Define a LOCAL exception FOR name violation.
  directory_name EXCEPTION;
  PRAGMA EXCEPTION_INIT(directory_name,-22284);
BEGIN
  OPEN  get_directory (virtual_directory);
  FETCH get_directory
  INTO  directory_path;
  CLOSE get_directory;
  -- RETURN file name.
  RETURN directory_path;
EXCEPTION
  WHEN directory_name THEN
    RETURN NULL;
END get_directory_path;
/

CREATE OR REPLACE FUNCTION get_directory_path ( virtual_directory IN VARCHAR2 ) RETURN VARCHAR2 IS -- Define RETURN variable. directory_path VARCHAR2(256) := ''; --Define dynamic cursor. CURSOR get_directory (virtual_directory VARCHAR2) IS SELECT directory_path FROM sys.dba_directories WHERE directory_name = virtual_directory; -- Define a LOCAL exception FOR name violation. directory_name EXCEPTION; PRAGMA EXCEPTION_INIT(directory_name,-22284); BEGIN OPEN get_directory (virtual_directory); FETCH get_directory INTO directory_path; CLOSE get_directory; -- RETURN file name. RETURN directory_path; EXCEPTION WHEN directory_name THEN RETURN NULL; END get_directory_path; /

GRANT EXECUTE ON get_directory_path TO import;

GRANT EXECUTE ON get_directory_path TO import;

CREATE SYNONYM get_directory_path FOR system.get_directory_path;

CREATE SYNONYM get_directory_path FOR system.get_directory_path;

SELECT get_directory_path('UPLOAD_FILES') FROM dual;

SELECT get_directory_path('UPLOAD_FILES') FROM dual;

GET_DIRECTORY_PATH('UPLOAD_FILES')
------------------------------------
C:\Imports\ImportFiles

GET_DIRECTORY_PATH('UPLOAD_FILES') ------------------------------------ C:\Imports\ImportFiles

CREATE OR REPLACE TYPE file_list AS TABLE OF VARCHAR2(255);
/

CREATE OR REPLACE TYPE file_list AS TABLE OF VARCHAR2(255); /

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

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListVirtualDirectory" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define the class.
  public class ListVirtualDirectory {
 
    // Define the method.
    public static ARRAY getList(String path) throws SQLException {
 
    // DECLARE variable AS a NULL, required because OF try-catch block.
    ARRAY listed = NULL;
 
    // Define a connection (this IS FOR Oracle 11g).
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
    // USE a try-catch block TO trap a Java permission error ON the directory.
    try {
      // DECLARE a class WITH the file list.
      File directory = NEW File(path);
 
      // DECLARE a mapping TO the schema-level SQL collection TYPE.
      ArrayDescriptor arrayDescriptor = NEW ArrayDescriptor("FILE_LIST",conn);
 
      // Translate the Java String[] TO the Oracle SQL collection TYPE.
      listed = NEW ARRAY(arrayDescriptor,conn,((Object[]) directory.list())); }
    catch (AccessControlException e) {}
  RETURN listed; }}
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListVirtualDirectory" AS // Import required classes. import java.io.*; import java.security.AccessControlException; import java.sql.*; import java.util.Arrays; import oracle.sql.driver.*; import oracle.sql.ArrayDescriptor; import oracle.sql.ARRAY; // Define the class. public class ListVirtualDirectory { // Define the method. public static ARRAY getList(String path) throws SQLException { // Declare variable as a null, required because of try-catch block. ARRAY listed = null; // Define a connection (this is for Oracle 11g). Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Use a try-catch block to trap a Java permission error on the directory. try { // Declare a class with the file list. File directory = new File(path); // Declare a mapping to the schema-level SQL collection type. ArrayDescriptor arrayDescriptor = new ArrayDescriptor("FILE_LIST",conn); // Translate the Java String[] to the Oracle SQL collection type. listed = new ARRAY(arrayDescriptor,conn,((Object[]) directory.list())); } catch (AccessControlException e) {} return listed; }} /

CREATE OR REPLACE FUNCTION list_files(path VARCHAR2) RETURN FILE_LIST IS
LANGUAGE JAVA
NAME 'ListVirtualDirectory.getList(java.lang.String) return oracle.sql.ARRAY';
/

CREATE OR REPLACE FUNCTION list_files(path VARCHAR2) RETURN FILE_LIST IS LANGUAGE JAVA NAME 'ListVirtualDirectory.getList(java.lang.String) return oracle.sql.ARRAY'; /

1
2
3
4
5
6
7

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('IMPORT'
                             ,'SYS:java.io.FilePermission'
                             ,'C:\Imports\ImportFiles'
                             ,'read,write,delete');
END;
/

BEGIN DBMS_JAVA.GRANT_PERMISSION('IMPORT' ,'SYS:java.io.FilePermission' ,'C:\Imports\ImportFiles' ,'read,write,delete'); END; /

1
2
3
4
5
6
7

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('IMPORT'
                            ,'SYS:java.io.FilePermission'
                            ,'C:\Imports\ImportLogs'
                            ,'read,write,delete');
END;
/

BEGIN DBMS_JAVA.GRANT_PERMISSION('IMPORT' ,'SYS:java.io.FilePermission' ,'C:\Imports\ImportLogs' ,'read,write,delete'); END; /

SELECT   column_value AS "File Names"
FROM     TABLE(list_files(get_directory_path('UPLOAD_FILES')));

SELECT column_value AS "File Names" FROM TABLE(list_files(get_directory_path('UPLOAD_FILES')));

File Names
-----------------
item_import.csv

File Names ----------------- item_import.csv

COLUMN TABLE_NAME FORMAT A30
COLUMN file_name  FORMAT A30
 
SELECT   xt.table_name
,        xt.file_name
FROM    (SELECT   uxt.TABLE_NAME
         ,        ixt.column_value AS file_name
         FROM     user_external_tables uxt CROSS JOIN
         TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt
JOIN     user_external_locations xl
ON       xt.table_name = xl.table_name AND xt.file_name = xl.location;

COLUMN table_name FORMAT A30 COLUMN file_name FORMAT A30 SELECT xt.table_name , xt.file_name FROM (SELECT uxt.TABLE_NAME , ixt.column_value AS file_name FROM user_external_tables uxt CROSS JOIN TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt JOIN user_external_locations xl ON xt.table_name = xl.table_name AND xt.file_name = xl.location;

TABLE_NAME                     FILE_NAME
------------------------------ ------------------------------
ITEM_IMPORT_EXT_TABLE          item_import.csv

TABLE_NAME FILE_NAME ------------------------------ ------------------------------ ITEM_IMPORT_EXT_TABLE item_import.csv

CREATE OR REPLACE FUNCTION external_file_found
( table_in VARCHAR2 ) RETURN NUMBER IS
  -- Define a default return value.
  retval NUMBER := 0;
 
  -- Decalre a cursor to find external tables.
  CURSOR c (cv_table VARCHAR2) IS
    SELECT   xt.table_name
    ,        xt.file_name
    FROM    (SELECT   uxt.TABLE_NAME
             ,        ixt.column_value AS file_name
             FROM     user_external_tables uxt CROSS JOIN
             TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt
    JOIN     user_external_locations xl ON xt.table_name = xl.table_name
    AND      xt.file_name = xl.location AND xt.table_name = UPPER(cv_table);
BEGIN
  FOR i IN c(table_in) LOOP
    retval := 1;
  END LOOP;
  RETURN retval;
END;
/

CREATE OR REPLACE FUNCTION external_file_found ( table_in VARCHAR2 ) RETURN NUMBER IS -- Define a default return value. retval NUMBER := 0; -- Decalre a cursor to find external tables. CURSOR c (cv_table VARCHAR2) IS SELECT xt.table_name , xt.file_name FROM (SELECT uxt.TABLE_NAME , ixt.column_value AS file_name FROM user_external_tables uxt CROSS JOIN TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt JOIN user_external_locations xl ON xt.table_name = xl.table_name AND xt.file_name = xl.location AND xt.table_name = UPPER(cv_table); BEGIN FOR i IN c(table_in) LOOP retval := 1; END LOOP; RETURN retval; END; /

CREATE OR REPLACE VIEW item_import AS
SELECT   *
FROM     item_import_ext_table
WHERE    external_file_found('ITEM_IMPORT_EXT_TABLE') = 1;

CREATE OR REPLACE VIEW item_import AS SELECT * FROM item_import_ext_table WHERE external_file_found('ITEM_IMPORT_EXT_TABLE') = 1;

SELECT * FROM item_import_ext_table
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-04040: file item_import.csv IN UPLOAD_FILES NOT found

SELECT * FROM item_import_ext_table * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file item_import.csv in UPLOAD_FILES not found

GRANT SELECT ON item_import TO application;

GRANT SELECT ON item_import TO application;

CREATE SYNONYM item_import FOR item_import;

CREATE SYNONYM item_import FOR item_import;

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE TYPE item_import_object IS OBJECT
( asin_number         VARCHAR2(10)
, item_type           VARCHAR2(15)
, item_title          VARCHAR2(60)
, item_subtitle       VARCHAR2(60)
, item_rating         VARCHAR2(8)
, item_rating_agency  VARCHAR2(4)
, item_release_date   DATE);
/

CREATE OR REPLACE TYPE item_import_object IS OBJECT ( asin_number VARCHAR2(10) , item_type VARCHAR2(15) , item_title VARCHAR2(60) , item_subtitle VARCHAR2(60) , item_rating VARCHAR2(8) , item_rating_agency VARCHAR2(4) , item_release_date DATE); /

1
2
3

CREATE OR REPLACE TYPE item_import_object_table IS
  TABLE OF item_import_object;
/

CREATE OR REPLACE TYPE item_import_object_table IS TABLE OF item_import_object; /

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

CREATE OR REPLACE FUNCTION external_file_contents
( table_in VARCHAR2 ) RETURN item_import_object_table IS
 
  -- Define a local counter.
  lv_counter NUMBER := 1;
 
  -- Construct an empty collection of ITEM_IMPORT_OBJECT data types.
  lv_item_import_table ITEM_IMPORT_OBJECT_TABLE := item_import_object_table();
 
  -- Decalre a cursor to find external tables.
  CURSOR c (cv_table VARCHAR2) IS
    SELECT   *
    FROM     item_import_ext_table
    WHERE    external_file_found(cv_table) = 1;
 
BEGIN
  FOR i IN c(table_in) LOOP
    lv_item_import_table.EXTEND;
    lv_item_import_table(lv_counter) := item_import_object(i.asin_number
                                                          ,i.item_type
                                                          ,i.item_title
                                                          ,i.item_subtitle
                                                          ,i.item_rating
                                                          ,i.item_rating_agency
                                                          ,i.item_release_date);
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /*
   *  This is where you can place autonomous function calls:
   *  ======================================================
   *   - These can read source and log files, and write them
   *     to CLOB attributes for later inspection or review.
   *   - These can call Java libraries to delete files, but
   *     you should note that Java deletes any file rather
   *     than moving it to the trash bin (where you might
   *     recover it.
   */
 
  RETURN lv_item_import_table;
END;
/

CREATE OR REPLACE FUNCTION external_file_contents ( table_in VARCHAR2 ) RETURN item_import_object_table IS -- Define a local counter. lv_counter NUMBER := 1; -- Construct an empty collection of ITEM_IMPORT_OBJECT data types. lv_item_import_table ITEM_IMPORT_OBJECT_TABLE := item_import_object_table(); -- Decalre a cursor to find external tables. CURSOR c (cv_table VARCHAR2) IS SELECT * FROM item_import_ext_table WHERE external_file_found(cv_table) = 1; BEGIN FOR i IN c(table_in) LOOP lv_item_import_table.EXTEND; lv_item_import_table(lv_counter) := item_import_object(i.asin_number ,i.item_type ,i.item_title ,i.item_subtitle ,i.item_rating ,i.item_rating_agency ,i.item_release_date); lv_counter := lv_counter + 1; END LOOP; /* * This is where you can place autonomous function calls: * ====================================================== * - These can read source and log files, and write them * to CLOB attributes for later inspection or review. * - These can call Java libraries to delete files, but * you should note that Java deletes any file rather * than moving it to the trash bin (where you might * recover it. */ RETURN lv_item_import_table; END; /

/*
 *  SQL*Plus formatting.
 */
SET PAGESIZE 99
 
COLUMN asin_number        FORMAT A11 HEADING "ASIN #"
COLUMN item_title         FORMAT A46 HEADING "ITEM TITLE"
COLUMN item_rating        FORMAT A6  HEADING "RATING"
COLUMN item_release_date  FORMAT A11 HEADING "RELEASE|DATE"
 
/*
 *  Query works only when item_import.csv file is present.
 */
SELECT   asin_number
,        item_title
,        item_rating
,        TO_CHAR(item_release_date,'DD-MON-YYYY') AS item_release_date
FROM     TABLE(external_file_contents('ITEM_IMPORT_EXT_TABLE'));

/* * SQL*Plus formatting. */ SET PAGESIZE 99 COLUMN asin_number FORMAT A11 HEADING "ASIN #" COLUMN item_title FORMAT A46 HEADING "ITEM TITLE" COLUMN item_rating FORMAT A6 HEADING "RATING" COLUMN item_release_date FORMAT A11 HEADING "RELEASE|DATE" /* * Query works only when item_import.csv file is present. */ SELECT asin_number , item_title , item_rating , TO_CHAR(item_release_date,'DD-MON-YYYY') AS item_release_date FROM TABLE(external_file_contents('ITEM_IMPORT_EXT_TABLE'));

                                                                  RELEASE
ASIN #      ITEM TITLE                                     RATING DATE
----------- ---------------------------------------------- ------ -----------
B000W74EQC  Harry Potter and the Sorcerer's Stone          PG     11-DEC-2007
B000W746GK  Harry Potter and the Chamber of Secrets        PG     11-DEC-2007
B000W796OM  Harry Potter and the Prisoner of Azkaban       PG     11-DEC-2007
B000E6EK2Y  Harry Potter and the Goblet of Fire            PG-13  07-MAR-2006
B000W7F5SS  Harry Potter and the Order of the Phoenix      PG-13  11-DEC-2007
B002PMV9FG  Harry Potter and the Half-Blood Prince         PG     08-DEC-2009
B001UV4XHY  Harry Potter and the Deathly Hallows, Part 1   PG-13  15-APR-2011
B001UV4XIS  Harry Potter and the Deathly Hallows, Part 2   PG-13  11-NOV-2011

RELEASE ASIN # ITEM TITLE RATING DATE ----------- ---------------------------------------------- ------ ----------- B000W74EQC Harry Potter and the Sorcerer's Stone PG 11-DEC-2007 B000W746GK Harry Potter and the Chamber of Secrets PG 11-DEC-2007 B000W796OM Harry Potter and the Prisoner of Azkaban PG 11-DEC-2007 B000E6EK2Y Harry Potter and the Goblet of Fire PG-13 07-MAR-2006 B000W7F5SS Harry Potter and the Order of the Phoenix PG-13 11-DEC-2007 B002PMV9FG Harry Potter and the Half-Blood Prince PG 08-DEC-2009 B001UV4XHY Harry Potter and the Deathly Hallows, Part 1 PG-13 15-APR-2011 B001UV4XIS Harry Potter and the Deathly Hallows, Part 2 PG-13 11-NOV-2011

1
2
3

CREATE OR REPLACE VIEW item_import AS
SELECT   *
FROM     TABLE(external_file_contents('ITEM_IMPORT_EXT_TABLE'));

CREATE OR REPLACE VIEW item_import AS SELECT * FROM TABLE(external_file_contents('ITEM_IMPORT_EXT_TABLE'));

CREATE TABLE import_master
( import_master_id  NUMBER CONSTRAINT pk_import_master PRIMARY KEY
, import_table      VARCHAR2(30));
 
-- Create sequence for import master.
CREATE SEQUENCE import_master_s;
 
-- Create import table.
CREATE TABLE import_data
( import_data_id    NUMBER CONSTRAINT pk_import_data PRIMARY KEY
, import_master_id  NUMBER
, import_data       CLOB
, CONSTRAINT fk_import_data FOREIGN KEY (import_data_id)
  REFERENCES import_master (import_master_id))
LOB (import_data) STORE AS BASICFILE item_import_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_data_s;
 
-- Create import table.
CREATE TABLE import_log
( import_log_id     NUMBER CONSTRAINT pk_import_log PRIMARY KEY
, import_master_id  NUMBER
, import_log        CLOB
, CONSTRAINT fk_import_log FOREIGN KEY (import_log_id)
  REFERENCES import_master (import_master_id))
LOB (import_log) STORE AS BASICFILE item_import_log_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_log_s;
 
-- Create import table.
CREATE TABLE import_discard
( import_discard_id  NUMBER CONSTRAINT pk_import_discard PRIMARY KEY
, import_master_id   NUMBER
, import_discard     CLOB
, CONSTRAINT fk_import_discard FOREIGN KEY (import_discard_id)
  REFERENCES import_master (import_master_id))
LOB (import_discard) STORE AS BASICFILE item_import_discard_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_discard_s;
 
-- Create import table.
CREATE TABLE import_bad
( import_bad_id     NUMBER CONSTRAINT pk_import_bad PRIMARY KEY
, import_master_id  NUMBER
, import_bad        CLOB
, CONSTRAINT fk_import_bad FOREIGN KEY (import_bad_id)
  REFERENCES import_master (import_master_id))
LOB (import_bad) STORE AS BASICFILE item_import_bad_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_bad_s;

CREATE TABLE import_master ( import_master_id NUMBER CONSTRAINT pk_import_master PRIMARY KEY , import_table VARCHAR2(30)); -- Create sequence for import master. CREATE SEQUENCE import_master_s; -- Create import table. CREATE TABLE import_data ( import_data_id NUMBER CONSTRAINT pk_import_data PRIMARY KEY , import_master_id NUMBER , import_data CLOB , CONSTRAINT fk_import_data FOREIGN KEY (import_data_id) REFERENCES import_master (import_master_id)) LOB (import_data) STORE AS BASICFILE item_import_clob (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10 NOCACHE LOGGING STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)); -- Create sequence for import master. CREATE SEQUENCE import_data_s; -- Create import table. CREATE TABLE import_log ( import_log_id NUMBER CONSTRAINT pk_import_log PRIMARY KEY , import_master_id NUMBER , import_log CLOB , CONSTRAINT fk_import_log FOREIGN KEY (import_log_id) REFERENCES import_master (import_master_id)) LOB (import_log) STORE AS BASICFILE item_import_log_clob (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10 NOCACHE LOGGING STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)); -- Create sequence for import master. CREATE SEQUENCE import_log_s; -- Create import table. CREATE TABLE import_discard ( import_discard_id NUMBER CONSTRAINT pk_import_discard PRIMARY KEY , import_master_id NUMBER , import_discard CLOB , CONSTRAINT fk_import_discard FOREIGN KEY (import_discard_id) REFERENCES import_master (import_master_id)) LOB (import_discard) STORE AS BASICFILE item_import_discard_clob (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10 NOCACHE LOGGING STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)); -- Create sequence for import master. CREATE SEQUENCE import_discard_s; -- Create import table. CREATE TABLE import_bad ( import_bad_id NUMBER CONSTRAINT pk_import_bad PRIMARY KEY , import_master_id NUMBER , import_bad CLOB , CONSTRAINT fk_import_bad FOREIGN KEY (import_bad_id) REFERENCES import_master (import_master_id)) LOB (import_bad) STORE AS BASICFILE item_import_bad_clob (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10 NOCACHE LOGGING STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)); -- Create sequence for import master. CREATE SEQUENCE import_bad_s;

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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

CREATE OR REPLACE FUNCTION load_clob_from_file
( pv_src_file_name  IN VARCHAR2
, pv_virtual_dir    IN VARCHAR2
, pv_table_name     IN VARCHAR2
, pv_column_name    IN VARCHAR2
, pv_foreign_key    IN NUMBER ) RETURN NUMBER IS
 
  -- Declare placeholder for sequence generated primary key.
  lv_primary_key  NUMBER;
 
  -- Declare default return value.
  lv_retval  NUMBER := 0;
 
  -- Declare local variables for DBMS_LOB.LOADCLOBFROMFILE procedure.
  des_clob    CLOB;
  src_clob    BFILE := BFILENAME(pv_virtual_dir,pv_src_file_name);
  des_offset  NUMBER := 1;
  src_offset  NUMBER := 1;
  ctx_lang    NUMBER := dbms_lob.default_lang_ctx;
  warning     NUMBER;
 
  -- Declare pre-reading size.
  src_clob_size  NUMBER;
 
  -- Declare variables for handling NDS sequence value.
  lv_sequence          VARCHAR2(30);
  lv_sequence_output   NUMBER;
  lv_sequence_tagline  VARCHAR2(10) := '_s.nextval';
 
  -- Define local variable for Native Dynamic SQL (NDS) Statement.
  stmt  VARCHAR2(2000);
 
  -- Declare the function as an autonomous transaction.
  PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
 
  -- Open file only when found.
  IF dbms_lob.fileexists(src_clob) = 1  AND NOT dbms_lob.isopen(src_clob) = 1 THEN
    src_clob_size := dbms_lob.getlength(src_clob);
    dbms_lob.open(src_clob,dbms_lob.lob_readonly);
  END IF;
 
  -- Concatenate the sequence name with the tagline.
  lv_sequence := pv_table_name || lv_sequence_tagline;
 
  -- Assign the sequence through an anonymous block.
  stmt := 'BEGIN '
       || '  :output := '||lv_sequence||';'
       || 'END;';
 
  -- Run the statement to extract a sequence value through NDS.
  EXECUTE IMMEDIATE stmt USING IN OUT lv_sequence_output;
 
  --  Create a dynamic statement that works for all source and log files.
  -- ----------------------------------------------------------------------
  --  NOTE: This statement requires that the row holding the primary key
  --        has been committed because otherwise it raises the following
  --        error because it can't verify the integrity of the foreign
  --        key constraint.
  -- ----------------------------------------------------------------------
  --        DECLARE
  --        *
  --        ERROR at line 1:
  --        ORA-00060: deadlock detected while waiting for resource
  --        ORA-06512: at "IMPORT.LOAD_CLOB_FROM_FILE", line 50
  --        ORA-06512: at line 20
  -- ----------------------------------------------------------------------  
  stmt := 'INSERT INTO '||pv_table_name||' '||CHR(10)||
          'VALUES '||CHR(10)||
          '('||lv_sequence_output||CHR(10)||
          ','||pv_foreign_key||CHR(10)||
          ', empty_clob())'||CHR(10)||
          'RETURNING '||pv_column_name||' INTO :locator';
 
  -- Run dynamic statement.
  EXECUTE IMMEDIATE stmt USING OUT des_clob;
 
  -- Read and write file to CLOB, close source file and commit.
  dbms_lob.loadclobfromfile( dest_lob     => des_clob
                           , src_bfile    => src_clob
                           , amount       => dbms_lob.getlength(src_clob)
                           , dest_offset  => des_offset
                           , src_offset   => src_offset
                           , bfile_csid   => dbms_lob.default_csid
                           , lang_context => ctx_lang
                           , warning      => warning );
 
  -- Close open source file.
  dbms_lob.close(src_clob);
 
  -- Commit write and conditionally acknowledge it.
  IF src_clob_size = dbms_lob.getlength(des_clob) THEN
    COMMIT;
    lv_retval := 1;
  ELSE
    RAISE dbms_lob.operation_failed;
  END IF;
 
  RETURN lv_retval;  
END load_clob_from_file;
/

CREATE OR REPLACE FUNCTION load_clob_from_file ( pv_src_file_name IN VARCHAR2 , pv_virtual_dir IN VARCHAR2 , pv_table_name IN VARCHAR2 , pv_column_name IN VARCHAR2 , pv_foreign_key IN NUMBER ) RETURN NUMBER IS -- Declare placeholder for sequence generated primary key. lv_primary_key NUMBER; -- Declare default return value. lv_retval NUMBER := 0; -- Declare local variables for DBMS_LOB.LOADCLOBFROMFILE procedure. des_clob CLOB; src_clob BFILE := BFILENAME(pv_virtual_dir,pv_src_file_name); des_offset NUMBER := 1; src_offset NUMBEr := 1; ctx_lang NUMBER := dbms_lob.default_lang_ctx; warning NUMBER; -- Declare pre-reading size. src_clob_size NUMBER; -- Declare variables for handling NDS sequence value. lv_sequence VARCHAR2(30); lv_sequence_output NUMBER; lv_sequence_tagline VARCHAR2(10) := '_s.nextval'; -- Define local variable for Native Dynamic SQL (NDS) Statement. stmt VARCHAR2(2000); -- Declare the function as an autonomous transaction. PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- Open file only when found. IF dbms_lob.fileexists(src_clob) = 1 AND NOT dbms_lob.isopen(src_clob) = 1 THEN src_clob_size := dbms_lob.getlength(src_clob); dbms_lob.open(src_clob,dbms_lob.lob_readonly); END IF; -- Concatenate the sequence name with the tagline. lv_sequence := pv_table_name || lv_sequence_tagline; -- Assign the sequence through an anonymous block. stmt := 'BEGIN ' || ' :output := '||lv_sequence||';' || 'END;'; -- Run the statement to extract a sequence value through NDS. EXECUTE IMMEDIATE stmt USING IN OUT lv_sequence_output; -- Create a dynamic statement that works for all source and log files. -- ---------------------------------------------------------------------- -- NOTE: This statement requires that the row holding the primary key -- has been committed because otherwise it raises the following -- error because it can't verify the integrity of the foreign -- key constraint. -- ---------------------------------------------------------------------- -- DECLARE -- * -- ERROR at line 1: -- ORA-00060: deadlock detected while waiting for resource -- ORA-06512: at "IMPORT.LOAD_CLOB_FROM_FILE", line 50 -- ORA-06512: at line 20 -- ---------------------------------------------------------------------- stmt := 'INSERT INTO '||pv_table_name||' '||CHR(10)|| 'VALUES '||CHR(10)|| '('||lv_sequence_output||CHR(10)|| ','||pv_foreign_key||CHR(10)|| ', empty_clob())'||CHR(10)|| 'RETURNING '||pv_column_name||' INTO :locator'; -- Run dynamic statement. EXECUTE IMMEDIATE stmt USING OUT des_clob; -- Read and write file to CLOB, close source file and commit. dbms_lob.loadclobfromfile( dest_lob => des_clob , src_bfile => src_clob , amount => dbms_lob.getlength(src_clob) , dest_offset => des_offset , src_offset => src_offset , bfile_csid => dbms_lob.default_csid , lang_context => ctx_lang , warning => warning ); -- Close open source file. dbms_lob.close(src_clob); -- Commit write and conditionally acknowledge it. IF src_clob_size = dbms_lob.getlength(des_clob) THEN COMMIT; lv_retval := 1; ELSE RAISE dbms_lob.operation_failed; END IF; RETURN lv_retval; END load_clob_from_file; /

-- Insert a sample row in the master table.
INSERT INTO import_master
VALUES (import_master_s.nextval,'ITEM_IMPORT_EXT_TABLE');
 
-- Record the row value to avoid deadlock on uncommitted master record.
COMMIT;
 
-- Test program for loading CLOB files.
DECLARE
 
  -- Declare testing variables.
  lv_file_name     VARCHAR2(255) := 'item_import.csv';
  lv_virtual_dir   VARCHAR2(255) := 'UPLOAD_FILES';
  lv_table_name    VARCHAR2(30)  := 'IMPORT_DATA';
  lv_column_name   VARCHAR2(30)  := 'IMPORT_DATA';
  lv_foreign_key   NUMBER;
 
BEGIN
 
  -- Assign the current value of the sequence to a local variable.
  lv_foreign_key := import_master_s.currval;
 
  -- Check if you can read and insert a CLOB column.
  IF load_clob_from_file(lv_file_name
                        ,lv_virtual_dir
                        ,lv_table_name
                        ,lv_table_name
                        ,lv_foreign_key) = 1 THEN
 
    -- Display a successful subordinate routine.
    dbms_output.put_line('Subordinate routine succeeds.');
  ELSE
    -- Display a failed subordinate routine.
    dbms_output.put_line('Subordinate routine fails.');
  END IF;
 
END load_clob_from_file;
/

-- Insert a sample row in the master table. INSERT INTO import_master VALUES (import_master_s.nextval,'ITEM_IMPORT_EXT_TABLE'); -- Record the row value to avoid deadlock on uncommitted master record. COMMIT; -- Test program for loading CLOB files. DECLARE -- Declare testing variables. lv_file_name VARCHAR2(255) := 'item_import.csv'; lv_virtual_dir VARCHAR2(255) := 'UPLOAD_FILES'; lv_table_name VARCHAR2(30) := 'IMPORT_DATA'; lv_column_name VARCHAR2(30) := 'IMPORT_DATA'; lv_foreign_key NUMBER; BEGIN -- Assign the current value of the sequence to a local variable. lv_foreign_key := import_master_s.currval; -- Check if you can read and insert a CLOB column. IF load_clob_from_file(lv_file_name ,lv_virtual_dir ,lv_table_name ,lv_table_name ,lv_foreign_key) = 1 THEN -- Display a successful subordinate routine. dbms_output.put_line('Subordinate routine succeeds.'); ELSE -- Display a failed subordinate routine. dbms_output.put_line('Subordinate routine fails.'); END IF; END load_clob_from_file; /

DECLARE
 
  -- Declare testing variables.
  lv_file_name     VARCHAR2(255) := 'item_import_ext_table.log';
  lv_virtual_dir   VARCHAR2(255) := 'UPLOAD_LOGS';
  lv_table_name    VARCHAR2(30)  := 'IMPORT_LOG';
  lv_column_name   VARCHAR2(30)  := 'IMPORT_LOG';
  lv_foreign_key   NUMBER;
 
BEGIN
 
  -- Assign the current value of the sequence to a local variable.
  lv_foreign_key := import_master_s.currval;
 
  dbms_output.put_line('Foreign key ['||lv_foreign_key||']');
 
  -- Check if you can read and insert a CLOB column.
  IF load_clob_from_file(lv_file_name
                        ,lv_virtual_dir
                        ,lv_table_name
                        ,lv_table_name
                        ,lv_foreign_key) = 1 THEN
 
    -- Display a successful subordinate routine.
    dbms_output.put_line('Subordinate routine succeeds.');
  ELSE
    -- Display a failed subordinate routine.
    dbms_output.put_line('Subordinate routine fails.');
  END IF;
 
END;
/

DECLARE -- Declare testing variables. lv_file_name VARCHAR2(255) := 'item_import_ext_table.log'; lv_virtual_dir VARCHAR2(255) := 'UPLOAD_LOGS'; lv_table_name VARCHAR2(30) := 'IMPORT_LOG'; lv_column_name VARCHAR2(30) := 'IMPORT_LOG'; lv_foreign_key NUMBER; BEGIN -- Assign the current value of the sequence to a local variable. lv_foreign_key := import_master_s.currval; dbms_output.put_line('Foreign key ['||lv_foreign_key||']'); -- Check if you can read and insert a CLOB column. IF load_clob_from_file(lv_file_name ,lv_virtual_dir ,lv_table_name ,lv_table_name ,lv_foreign_key) = 1 THEN -- Display a successful subordinate routine. dbms_output.put_line('Subordinate routine succeeds.'); ELSE -- Display a failed subordinate routine. dbms_output.put_line('Subordinate routine fails.'); END IF; END; /

SET DEFINE OFF

SET DEFINE OFF

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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

CREATE OR REPLACE FUNCTION cleanup_external_files
( table_in           VARCHAR2
, data_directory_in  VARCHAR2
, log_directory_in   VARCHAR2 ) RETURN NUMBER IS
 
  -- Declare a local Attribute Data Type (ADT).
  TYPE list IS TABLE OF VARCHAR2(3);
 
  -- Declare a collection.
  lv_extension LIST := list('csv','log','bad','dis');
 
  -- Define a default return value.
  retval NUMBER := 0;
 
  -- Declare base target table name.
  lv_target_table  VARCHAR2(30) := 'IMPORT';
  lv_foreign_key   NUMBER;
 
  -- Decalre a cursor to find external tables.
  CURSOR check_source (cv_table_name VARCHAR2) IS
    SELECT   xt.file_name
    FROM    (SELECT   uxt.TABLE_NAME
             ,        ixt.column_value AS file_name
             FROM     user_external_tables uxt CROSS JOIN
             TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt
    JOIN     user_external_locations xl ON xt.TABLE_NAME = xl.TABLE_NAME
    AND      xt.file_name = xl.location AND xt.TABLE_NAME = UPPER(cv_table_name);
 
  -- Declare a cursor to find files and compare for one input file name.
  CURSOR check_logs (cv_file_name VARCHAR2) IS
    SELECT   list.column_value
    FROM     TABLE(list_files(get_directory_path('UPLOAD_LOGS'))) list
    JOIN    (SELECT cv_file_name AS file_name FROM dual) FILTER
    ON       list.column_value = FILTER.file_name;
 
  -- Declare the function as autonomous.
  PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
 
  -- Master loop to check for source and log files.  
  FOR i IN check_source (table_in) LOOP
 
    -- Assign next sequence value to local variable.
    lv_foreign_key := import_master_s.nextval;
 
    -- Write the master record and commit it for the autonomous threads.
    INSERT INTO import_master
    VALUES (lv_foreign_key,'ITEM_IMPORT_EXT_TABLE');
    COMMIT;
 
    -- Process all file extensions.    
    FOR j IN 1..lv_extension.COUNT LOOP
 
      -- The source data file is confirmed by the CHECK_SOURCE cursor.
      IF lv_extension(j) = 'csv' THEN
 
        --  Load the source data file.
        -- ----------------------------------------------------------
        --  The RETVAL holds success or failure, this approach 
        --  suppresses an error when the file can't be loaded.
        --  It should only occur when there's no space available 
        --  in the target table.
        retval := load_clob_from_file(i.file_name
                                     ,data_directory_in
                                     ,lv_target_table||'_DATA'
                                     ,lv_target_table||'_DATA'
                                     ,lv_foreign_key);
                                     lv_foreign_key := lv_foreign_key + 1;
      ELSE
 
        -- Verify that log file exists before attempting to load it.
        FOR k IN check_logs (LOWER(table_in)||'.'||lv_extension(j)) LOOP
 
          --  Load the log, bad, or dis(card) file.
          -- ----------------------------------------------------------
          --  The RETVAL holds success or failure, as mentioned above.
          retval := load_clob_from_file(LOWER(table_in)||'.'||lv_extension(j)
                                       ,log_directory_in
                                       ,lv_target_table||'_'||lv_extension(j)
                                       ,lv_target_table||'_'||lv_extension(j)
                                       ,lv_foreign_key);
        END LOOP;
      END IF;
    END LOOP;
    retval := 1;
  END LOOP;
  RETURN retval;
END;
/

CREATE OR REPLACE FUNCTION cleanup_external_files ( table_in VARCHAR2 , data_directory_in VARCHAR2 , log_directory_in VARCHAR2 ) RETURN NUMBER IS -- Declare a local Attribute Data Type (ADT). TYPE list IS TABLE OF VARCHAR2(3); -- Declare a collection. lv_extension LIST := list('csv','log','bad','dis'); -- Define a default return value. retval NUMBER := 0; -- Declare base target table name. lv_target_table VARCHAR2(30) := 'IMPORT'; lv_foreign_key NUMBER; -- Decalre a cursor to find external tables. CURSOR check_source (cv_table_name VARCHAR2) IS SELECT xt.file_name FROM (SELECT uxt.TABLE_NAME , ixt.column_value AS file_name FROM user_external_tables uxt CROSS JOIN TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt JOIN user_external_locations xl ON xt.TABLE_NAME = xl.TABLE_NAME AND xt.file_name = xl.location AND xt.TABLE_NAME = UPPER(cv_table_name); -- Declare a cursor to find files and compare for one input file name. CURSOR check_logs (cv_file_name VARCHAR2) IS SELECT list.column_value FROM TABLE(list_files(get_directory_path('UPLOAD_LOGS'))) list JOIN (SELECT cv_file_name AS file_name FROM dual) filter ON list.column_value = filter.file_name; -- Declare the function as autonomous. PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- Master loop to check for source and log files. FOR i IN check_source (table_in) LOOP -- Assign next sequence value to local variable. lv_foreign_key := import_master_s.nextval; -- Write the master record and commit it for the autonomous threads. INSERT INTO import_master VALUES (lv_foreign_key,'ITEM_IMPORT_EXT_TABLE'); COMMIT; -- Process all file extensions. FOR j IN 1..lv_extension.COUNT LOOP -- The source data file is confirmed by the CHECK_SOURCE cursor. IF lv_extension(j) = 'csv' THEN -- Load the source data file. -- ---------------------------------------------------------- -- The RETVAL holds success or failure, this approach -- suppresses an error when the file can't be loaded. -- It should only occur when there's no space available -- in the target table. retval := load_clob_from_file(i.file_name ,data_directory_in ,lv_target_table||'_DATA' ,lv_target_table||'_DATA' ,lv_foreign_key); lv_foreign_key := lv_foreign_key + 1; ELSE -- Verify that log file exists before attempting to load it. FOR k IN check_logs (LOWER(table_in)||'.'||lv_extension(j)) LOOP -- Load the log, bad, or dis(card) file. -- ---------------------------------------------------------- -- The RETVAL holds success or failure, as mentioned above. retval := load_clob_from_file(LOWER(table_in)||'.'||lv_extension(j) ,log_directory_in ,lv_target_table||'_'||lv_extension(j) ,lv_target_table||'_'||lv_extension(j) ,lv_foreign_key); END LOOP; END IF; END LOOP; retval := 1; END LOOP; RETURN retval; END; /

1

SET DEFINE OFF

SET DEFINE OFF

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DeleteFile" AS
  // Java import statements
  import java.io.File;
  import java.security.AccessControlException;
 
  // Class definition.
  public class DeleteFile
  {
    // Define variable(s).
    private static File file;
 
    // Define copyTextFile() method.
    public static void deleteFile(String fileName) throws AccessControlException {
 
      // CREATE files FROM canonical file names.
      file = NEW File(fileName);
 
      // DELETE file(s).
      IF (file.isFile() && file.delete()) {}}}
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DeleteFile" AS // Java import statements import java.io.File; import java.security.AccessControlException; // Class definition. public class DeleteFile { // Define variable(s). private static File file; // Define copyTextFile() method. public static void deleteFile(String fileName) throws AccessControlException { // Create files from canonical file names. file = new File(fileName); // Delete file(s). if (file.isFile() && file.delete()) {}}} /

1
2
3
4

CREATE OR REPLACE PROCEDURE delete_file (dfile VARCHAR2) IS
LANGUAGE JAVA
NAME 'DeleteFile.deleteFile(java.lang.String)';
/

CREATE OR REPLACE PROCEDURE delete_file (dfile VARCHAR2) IS LANGUAGE JAVA NAME 'DeleteFile.deleteFile(java.lang.String)'; /

1
2

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(4000); /

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

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortList" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import java.util.Comparator;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define class.
  public class Sorting {
    public static ARRAY sortTitleCaseList(oracle.sql.ARRAY list) throws SQLException, AccessControlException {
 
    // Convert Oracle data type to Java data type.
    String[] unsorted = (String[])list.getArray();
 
    // Sort elements.
    Arrays.sort(unsorted, new Comparator<String>() {
      public int compare(String s1, String s2) {
 
      // Declare a sorting key integer for the return value.
      int sortKey;
 
      // Check if lowercase words match and sort on first letter only.
      if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
         sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
      else
        sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 
      // Return the sorting index.
      return sortKey; }});
 
      // Define a connection (this is for Oracle 11g).
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn);
 
      // Translate the Java String{} to the Oracle SQL collection type.
      ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
 
      // Return the sorted list.
      return sorted; }
  }
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortList" AS // Import required classes. import java.io.*; import java.security.AccessControlException; import java.sql.*; import java.util.Arrays; import java.util.Comparator; import oracle.sql.driver.*; import oracle.sql.ArrayDescriptor; import oracle.sql.ARRAY; // Define class. public class Sorting { public static ARRAY sortTitleCaseList(oracle.sql.ARRAY list) throws SQLException, AccessControlException { // Convert Oracle data type to Java data type. String[] unsorted = (String[])list.getArray(); // Sort elements. Arrays.sort(unsorted, new Comparator<String>() { public int compare(String s1, String s2) { // Declare a sorting key integer for the return value. int sortKey; // Check if lowercase words match and sort on first letter only. if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0) sortKey = s1.substring(0,1).compareTo(s2.substring(0,1)); else sortKey = s1.toLowerCase().compareTo(s2.toLowerCase()); // Return the sorting index. return sortKey; }}); // Define a connection (this is for Oracle 11g). Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Declare a mapping to the schema-level SQL collection type. ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn); // Translate the Java String{} to the Oracle SQL collection type. ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted)); // Return the sorted list. return sorted; } } /

1
2
3
4

CREATE OR REPLACE FUNCTION sortTitleCaseList(list STRINGLIST) RETURN STRINGLIST IS
LANGUAGE JAVA
NAME 'Sorting.sortNaturalCaseList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
/

CREATE OR REPLACE FUNCTION sortTitleCaseList(list STRINGLIST) RETURN STRINGLIST IS LANGUAGE JAVA NAME 'Sorting.sortNaturalCaseList(oracle.sql.ARRAY) return oracle.sql.ARRAY'; /

1
2

SELECT column_value
FROM TABLE(sortTitleCaseList(stringlist('Oranges','apples','Apples','Bananas','Apricots','apricots')));

SELECT column_value FROM TABLE(sortTitleCaseList(stringlist('Oranges','apples','Apples','Bananas','Apricots','apricots')));

COLUMN_VALUE
------------------------
Apples
apples
Apricots
apricots
Bananas
Oranges
 
6 rows selected.

COLUMN_VALUE ------------------------ Apples apples Apricots apricots Bananas Oranges 6 rows selected.

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

// Import required classes.
import java.util.Arrays;
import java.util.Comparator;
 
/**
 * An example of using a Comparator sort.
 */
public class SortStaticString {
 
  /*
   * Sort the instance array.
   */
  public static String[] sortTitleCaseList(String[] list) {
 
    // Sort elements by title case.
    Arrays.sort(list, new Comparator<String>() {
      public int compare(String s1, String s2) {
 
        // Declare a sorting key integer for the return value.
        int sortKey;
 
        // Check if lowercase words match and sort on first letter only.
        if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
          sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
        else
          sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 
        // Return the sorting index.
        return sortKey; }});
 
    // Return the sorted Index.     
    return list;
  }
 
  /*
   * Test case.
   */
  public static void main(String[] args) {
 
    // Construct and instance and apply sort method.     
    args = SortStaticString.sortTitleCaseList(args);
 
    // Print the title case sorted list.
    for (int i = 0; i < args.length; i++) {
      System.out.println(args[i]); }
  }
}

// Import required classes. import java.util.Arrays; import java.util.Comparator; /** * An example of using a Comparator sort. */ public class SortStaticString { /* * Sort the instance array. */ public static String[] sortTitleCaseList(String[] list) { // Sort elements by title case. Arrays.sort(list, new Comparator<String>() { public int compare(String s1, String s2) { // Declare a sorting key integer for the return value. int sortKey; // Check if lowercase words match and sort on first letter only. if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0) sortKey = s1.substring(0,1).compareTo(s2.substring(0,1)); else sortKey = s1.toLowerCase().compareTo(s2.toLowerCase()); // Return the sorting index. return sortKey; }}); // Return the sorted Index. return list; } /* * Test case. */ public static void main(String[] args) { // Construct and instance and apply sort method. args = SortStaticString.sortTitleCaseList(args); // Print the title case sorted list. for (int i = 0; i < args.length; i++) { System.out.println(args[i]); } } }

java SortStaticString apples Oranges Pears Apples orange Grapefruit

java SortStaticString apples Oranges Pears Apples orange Grapefruit

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
73
74
75
76
77
78
79
80
81

/**
 * Enumeration class AppleComputer - write a description of the enum class here
 * 
 * @author  Michael McLaughlin
 * @version 1.0
 */
public enum AppleComputer
{
  /**
   * This is an entry-level desktop computer.
   */
  IMAC("Entry-level Desktop",1199),
  /**
   * This is an manager-level laptop computer.
   */
  MACBOOKAIR("Manager-level Laptop",999),
  /**
   * This is an manager-level laptop computer.
   */
  MACBOOKPRO("Developer-level Laptop",1199),
  /**
   * This is an developer-level laptop computer.
   */
  MACMINI("Mini-Desktop",599),
  /**
   * This is a mini-desktop computer.
   */
  MACPRO("Desktop",2499);
 
  /**
   *  Private variable definitions.
   */
  private double cost;
  private String description;
 
  /**
   *  Constructs an instance with a cost and description. 
   */
  private AppleComputer(String description, double cost) {
    this.description = description;
    this.cost = cost; }
 
  /**
   *  Returns the cost field of an Apple Computer. 
   */
  public double getCost() {
    return this.cost; }
 
  /**
   *  Returns the description field of an Apple Computer. 
   */
  public String getDescription() {
    return description; }
 
  /**
   *  Returns the description field of an Apple Computer. 
   */
  public String getDescription(String name) {
    return this.description; }
 
  /**
   *  Returns the equality of between two AppleComputer Enum types. 
   */
  public boolean equals(AppleComputer ac) {
    // First comparision on primitives and second on String instances.
    if ((this.cost == ac.getCost()) && (this.description.equals(ac.getDescription())))
      return true;
    else
      return false; }
 
  /**
   *  Method to test class integrity.
   */
  public static void main(String[] args) {
    if (args.length == 1) {
      System.out.printf("Apple Computer : %s is %s\n", AppleComputer.valueOf(args[0]).toString(), AppleComputer.IMAC.getDescription(args[0]));
      System.exit(0); } 
    else {
      for (AppleComputer ac : AppleComputer.values())
        System.out.printf("Apple Computer : %s is %s\n", ac, ac.description);  }}    
}

/** * Enumeration class AppleComputer - write a description of the enum class here * * @author Michael McLaughlin * @version 1.0 */ public enum AppleComputer { /** * This is an entry-level desktop computer. */ IMAC("Entry-level Desktop",1199), /** * This is an manager-level laptop computer. */ MACBOOKAIR("Manager-level Laptop",999), /** * This is an manager-level laptop computer. */ MACBOOKPRO("Developer-level Laptop",1199), /** * This is an developer-level laptop computer. */ MACMINI("Mini-Desktop",599), /** * This is a mini-desktop computer. */ MACPRO("Desktop",2499); /** * Private variable definitions. */ private double cost; private String description; /** * Constructs an instance with a cost and description. */ private AppleComputer(String description, double cost) { this.description = description; this.cost = cost; } /** * Returns the cost field of an Apple Computer. */ public double getCost() { return this.cost; } /** * Returns the description field of an Apple Computer. */ public String getDescription() { return description; } /** * Returns the description field of an Apple Computer. */ public String getDescription(String name) { return this.description; } /** * Returns the equality of between two AppleComputer Enum types. */ public boolean equals(AppleComputer ac) { // First comparision on primitives and second on String instances. if ((this.cost == ac.getCost()) && (this.description.equals(ac.getDescription()))) return true; else return false; } /** * Method to test class integrity. */ public static void main(String[] args) { if (args.length == 1) { System.out.printf("Apple Computer : %s is %s\n", AppleComputer.valueOf(args[0]).toString(), AppleComputer.IMAC.getDescription(args[0])); System.exit(0); } else { for (AppleComputer ac : AppleComputer.values()) System.out.printf("Apple Computer : %s is %s\n", ac, ac.description); }} }

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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

/**
 * The EnumTextUse class demonstrates how to identify and use an Enum
 * class that contains a text value.
 * 
 * @author  Michael McLaughlin 
 * @version 1.0
 */
 
/**
 * Import classes.
 */
import java.text.NumberFormat;
import java.text.DecimalFormat;
 
public class EnumTextUse {
 
  /**
   * Declare class level variables.
   */
  private AppleComputer ac;
  private String desc;
 
  /**
   * Constructor for objects of class TestEnum
   */
  public EnumTextUse() {}
 
  /**
   * An example of a method that takes a text string to find the ENUM
   * element value.
   */
  public int setState(String name) {
 
    // Declare local variable for false.
    int returnValue = 0;
 
    // put your code here
    try {
      // Verify that it's a valid instance of AppleComputer.         
      if (AppleComputer.valueOf(name) instanceof AppleComputer) {
        if (AppleComputer.IMAC == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.IMAC; }
        else if (AppleComputer.MACBOOKAIR == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACBOOKAIR; }
        else if (AppleComputer.MACBOOKPRO == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACBOOKPRO; }
        else if (AppleComputer.MACPRO == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACPRO; }
        else if (AppleComputer.MACMINI == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACMINI; }}
 
      // Return -1 as the truth indicator state was set or unnecessary.
      returnValue = -1; }
 
    catch (Exception e) {
      System.out.println(name + " is no longer sold."); }
 
    // Return the int value for true or false.
    return returnValue; 
  }
 
  /**
   * An example of a method that takes a text string to find the ENUM
   * element value.
   */
  public int setState(AppleComputer ac)
  {
    // Assign the AppleComputer fields to a local variable.
    double costSavings;
    double localCost = this.ac.getCost();
    String localDesc = this.ac.toString() + " : " + this.ac.getDescription();
 
    // Define format mask for output.
    NumberFormat f = new DecimalFormat("##,###,##0.00");
 
    // Declare local variable for false.
    int returnValue = 0;
 
    // put your code here
    try {
 
      // Check for an instance of the Enum.
      if (this.ac instanceof AppleComputer) {
 
      // Find different (unequal) instances and update with the new one.
      if (this.ac.equals(ac)) {
        // Print message on match between prior and set value of AppleComputer.
        this.desc = this.ac + " is the authorized platform and no cost difference."; }
 
      else {
 
        // Assign the new Enum value, calculate and display cost savings message.
        this.ac = ac;
        costSavings = this.ac.getCost() - localCost;
 
        // Determine the message based on a reduced or increased cost of replacement.
        if (costSavings > 0) {
          this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(costSavings) + " more than planned."; }
        else {
          this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(Math.abs(costSavings)) + " less than planned."; }}}
 
        // Return -1 as the truth indicator state was set or unnecessary.
        returnValue = -1; }
 
    catch (Exception e) {
      System.out.println(e.getMessage()); }
 
    // Return the int value for true or false.
    return returnValue; 
  }
 
  /**
   * Return the current description value.
   */
  public String getState() {
    return this.desc; }
 
  /**
   * Allows testing the program.
   */
  public static void main(String [] args)
  {
    // Declare a string of possible enumeration types.
    String [] list = {"IMAC","MACBOOK","MACBOOKAIR","MACBOOKPRO","MACMINI","MACPRO"};
 
    // Construct a test instance.
    EnumTextUse etu = new EnumTextUse();
 
    /**
     * Read through the list of enumeration types, printing output from
     * the inherited or overridden toString() method.
     */ 
    for (int i = 0; i < list.length; i++) {
      if (etu.setState(list[i]) != 0) {
 
        // The company standard must apply in all cases.
        if (etu.setState(AppleComputer.IMAC) != 0)
          System.out.println(etu.getState()); }
    }
  }
}

/** * The EnumTextUse class demonstrates how to identify and use an Enum * class that contains a text value. * * @author Michael McLaughlin * @version 1.0 */ /** * Import classes. */ import java.text.NumberFormat; import java.text.DecimalFormat; public class EnumTextUse { /** * Declare class level variables. */ private AppleComputer ac; private String desc; /** * Constructor for objects of class TestEnum */ public EnumTextUse() {} /** * An example of a method that takes a text string to find the ENUM * element value. */ public int setState(String name) { // Declare local variable for false. int returnValue = 0; // put your code here try { // Verify that it's a valid instance of AppleComputer. if (AppleComputer.valueOf(name) instanceof AppleComputer) { if (AppleComputer.IMAC == AppleComputer.valueOf(name)) { this.ac = AppleComputer.IMAC; } else if (AppleComputer.MACBOOKAIR == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACBOOKAIR; } else if (AppleComputer.MACBOOKPRO == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACBOOKPRO; } else if (AppleComputer.MACPRO == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACPRO; } else if (AppleComputer.MACMINI == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACMINI; }} // Return -1 as the truth indicator state was set or unnecessary. returnValue = -1; } catch (Exception e) { System.out.println(name + " is no longer sold."); } // Return the int value for true or false. return returnValue; } /** * An example of a method that takes a text string to find the ENUM * element value. */ public int setState(AppleComputer ac) { // Assign the AppleComputer fields to a local variable. double costSavings; double localCost = this.ac.getCost(); String localDesc = this.ac.toString() + " : " + this.ac.getDescription(); // Define format mask for output. NumberFormat f = new DecimalFormat("##,###,##0.00"); // Declare local variable for false. int returnValue = 0; // put your code here try { // Check for an instance of the Enum. if (this.ac instanceof AppleComputer) { // Find different (unequal) instances and update with the new one. if (this.ac.equals(ac)) { // Print message on match between prior and set value of AppleComputer. this.desc = this.ac + " is the authorized platform and no cost difference."; } else { // Assign the new Enum value, calculate and display cost savings message. this.ac = ac; costSavings = this.ac.getCost() - localCost; // Determine the message based on a reduced or increased cost of replacement. if (costSavings > 0) { this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(costSavings) + " more than planned."; } else { this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(Math.abs(costSavings)) + " less than planned."; }}} // Return -1 as the truth indicator state was set or unnecessary. returnValue = -1; } catch (Exception e) { System.out.println(e.getMessage()); } // Return the int value for true or false. return returnValue; } /** * Return the current description value. */ public String getState() { return this.desc; } /** * Allows testing the program. */ public static void main(String [] args) { // Declare a string of possible enumeration types. String [] list = {"IMAC","MACBOOK","MACBOOKAIR","MACBOOKPRO","MACMINI","MACPRO"}; // Construct a test instance. EnumTextUse etu = new EnumTextUse(); /** * Read through the list of enumeration types, printing output from * the inherited or overridden toString() method. */ for (int i = 0; i < list.length; i++) { if (etu.setState(list[i]) != 0) { // The company standard must apply in all cases. if (etu.setState(AppleComputer.IMAC) != 0) System.out.println(etu.getState()); } } } }

$ java EnumTextUse

$ java EnumTextUse

IMAC is the authorized platform and no cost difference.
MACBOOK is no longer sold.
IMAC is substituted for MACBOOKAIR : Manager-level Laptop at $200.00 more than planned.
IMAC is substituted for MACBOOKPRO : Developer-level Laptop at $0.00 less than planned.
IMAC is substituted for MACMINI : Mini-Desktop at $600.00 more than planned.
IMAC is substituted for MACPRO : Desktop at $1,300.00 less than planned.

IMAC is the authorized platform and no cost difference. MACBOOK is no longer sold. IMAC is substituted for MACBOOKAIR : Manager-level Laptop at $200.00 more than planned. IMAC is substituted for MACBOOKPRO : Developer-level Laptop at $0.00 less than planned. IMAC is substituted for MACMINI : Mini-Desktop at $600.00 more than planned. IMAC is substituted for MACPRO : Desktop at $1,300.00 less than planned.

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

CREATE OR REPLACE FUNCTION update_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2
, new_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
    ELSE
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := new_element_value;
    END IF;
    lv_counter := lv_counter + 1;
  END LOOP;
 
  RETURN lv_element_collection;
END update_collection;
/

CREATE OR REPLACE FUNCTION update_collection ( old_element_collection STREET_LIST , old_element_value VARCHAR2 , new_element_value VARCHAR2 ) RETURN STREET_LIST IS -- Declare and initial a new counter. lv_counter NUMBER := 1; -- Declare local return collection variable. lv_element_collection STREET_LIST := street_list(); BEGIN FOR i IN 1..old_element_collection.COUNT LOOP IF NOT old_element_collection(i) = old_element_value THEN lv_element_collection.EXTEND; lv_element_collection(lv_counter) := old_element_collection(i); ELSE lv_element_collection.EXTEND; lv_element_collection(lv_counter) := new_element_value; END IF; lv_counter := lv_counter + 1; END LOOP; RETURN lv_element_collection; END update_collection; /

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee e
              WHERE   e.employee_id = 1) e
SET   e.street_address = update_collection(e.street_address, 'Suite 525','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

UPDATE TABLE (SELECT e.home_address FROM employee e WHERE e.employee_id = 1) e SET e.street_address = update_collection(e.street_address, 'Suite 525','Suite 522') , e.city = 'Oakland' WHERE e.address_id = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE OR REPLACE FUNCTION delete_from_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
      lv_counter := lv_counter + 1;
    END IF;
  END LOOP;
 
  RETURN lv_element_collection;
END delete_from_collection;
/

CREATE OR REPLACE FUNCTION delete_from_collection ( old_element_collection STREET_LIST , old_element_value VARCHAR2 ) RETURN STREET_LIST IS -- Declare and initial a new counter. lv_counter NUMBER := 1; -- Declare local return collection variable. lv_element_collection STREET_LIST := street_list(); BEGIN FOR i IN 1..old_element_collection.COUNT LOOP IF NOT old_element_collection(i) = old_element_value THEN lv_element_collection.EXTEND; lv_element_collection(lv_counter) := old_element_collection(i); lv_counter := lv_counter + 1; END IF; END LOOP; RETURN lv_element_collection; END delete_from_collection; /

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = delete_from_collection(e.street_address,'Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

UPDATE TABLE (SELECT e.home_address FROM employee1 e WHERE e.employee_id = 1) e SET e.street_address = delete_from_collection(e.street_address,'Suite 522') , e.city = 'Oakland' WHERE e.address_id = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE OR REPLACE FUNCTION sort_collection
( old_element_collection  STREET_LIST) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  -- Sort a collection alphabetically based on case sensitivity.
  SELECT   column_value BULK COLLECT INTO lv_element_collection
  FROM     TABLE(old_element_collection)
  ORDER BY column_value;
 
  RETURN lv_element_collection;
END sort_collection;
/

CREATE OR REPLACE FUNCTION sort_collection ( old_element_collection STREET_LIST) RETURN STREET_LIST IS -- Declare and initial a new counter. lv_counter NUMBER := 1; -- Declare local return collection variable. lv_element_collection STREET_LIST := street_list(); BEGIN -- Sort a collection alphabetically based on case sensitivity. SELECT column_value BULK COLLECT INTO lv_element_collection FROM TABLE(old_element_collection) ORDER BY column_value; RETURN lv_element_collection; END sort_collection; /

1
2

SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'Lewis', 'Clark', 'Fallon')));

SELECT column_value FROM TABLE(sort_collection(street_list('Adams', 'Lewis', 'Clark', 'Fallon')));

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = sort_collection(e.street_address)
,     e.city = 'Oakland'
WHERE e.address_id = 1;

UPDATE TABLE (SELECT e.home_address FROM employee1 e WHERE e.employee_id = 1) e SET e.street_address = sort_collection(e.street_address) , e.city = 'Oakland' WHERE e.address_id = 1;

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

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortOracleList" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define class.
  public class DemoSort {
    public static ARRAY getList(oracle.sql.ARRAY list) throws SQLException, AccessControlException {
 
      // Convert Oracle data type to Java data type.
      String[] unsorted = (String[])list.getArray();
 
      // Sort elements.
      Arrays.sort(unsorted, String.CASE_INSENSITIVE_ORDER);
 
      // Define a connection (this is for Oracle 11g).
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn);
 
      // Translate the Java String{} to the Oracle SQL collection type.
      ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
    return sorted; }}
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortOracleList" AS // Import required classes. import java.io.*; import java.security.AccessControlException; import java.sql.*; import java.util.Arrays; import oracle.sql.driver.*; import oracle.sql.ArrayDescriptor; import oracle.sql.ARRAY; // Define class. public class DemoSort { public static ARRAY getList(oracle.sql.ARRAY list) throws SQLException, AccessControlException { // Convert Oracle data type to Java data type. String[] unsorted = (String[])list.getArray(); // Sort elements. Arrays.sort(unsorted, String.CASE_INSENSITIVE_ORDER); // Define a connection (this is for Oracle 11g). Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Declare a mapping to the schema-level SQL collection type. ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn); // Translate the Java String{} to the Oracle SQL collection type. ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted)); return sorted; }} /

1
2
3
4

CREATE OR REPLACE FUNCTION sortTable(list STRINGLIST) RETURN STRINGLIST IS
LANGUAGE JAVA
NAME 'DemoSort.getList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
/

CREATE OR REPLACE FUNCTION sortTable(list STRINGLIST) RETURN STRINGLIST IS LANGUAGE JAVA NAME 'DemoSort.getList(oracle.sql.ARRAY) return oracle.sql.ARRAY'; /

1
2

SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'adams', 'Lewis', 'Clark', 'Fallon')));

SELECT column_value FROM TABLE(sort_collection(street_list('Adams', 'adams', 'Lewis', 'Clark', 'Fallon')));

12
13
14
15

    -- Sort a collection alphabetically based on case insensitive comparison.
    SELECT   column_value BULK COLLECT INTO lv_element_collection
    FROM     TABLE(old_element_collection)
    ORDER BY UPPER(column_value);

-- Sort a collection alphabetically based on case insensitive comparison. SELECT column_value BULK COLLECT INTO lv_element_collection FROM TABLE(old_element_collection) ORDER BY UPPER(column_value);