MacLochlainns Weblog

EXECUTE IMMEDIATE sql_stmt
  USING { IN | IN OUT | OUT } local_variable [, ...]
  RETURNING INTO { IN OUT | OUT } local_variable [, ...];

-- Enable SERVEROUTPUT.
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Declare an anonymous testing block.
DECLARE
 
  -- Declare two local variables.
  lv_super_hero     VARCHAR2(20) := 'Thor';
  lv_super_villain  VARCHAR2(20) := 'Loki';
 
  -- Declare a null statement variable.
  lv_stmt  VARCHAR2(32767);
 
  -- Declare a local procedure to parse the NDS block.
  PROCEDURE print_code_block (pv_block VARCHAR2) IS
    -- Declare local parsing variables.
    lv_length   INTEGER := 1;
    lv_start    INTEGER := 1;
    lv_end      INTEGER := 1;
  BEGIN
    -- Read line by line on a line return character.
    WHILE NOT (lv_end = 0) LOOP
      -- Check for line returns.
      lv_end := INSTR(lv_stmt,CHR(10),lv_start);
      -- Check whether line return has been read.
      IF NOT lv_end = 0 THEN     
        -- Reset the ending substring value and print substring.
        lv_end := INSTR(lv_stmt,CHR(10),lv_start);
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,lv_end - lv_start));
      ELSE
        -- Print the last substring with a semicolon and exit the loop.      
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,LENGTH(lv_stmt) - lv_start)||';');
      END IF;
      -- Reset the beginning of the string.
      lv_start := lv_end + 1;      
    END LOOP;    
  END print_code_block;
 
BEGIN
 
  -- Demonstrate good triumps over evil.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
  -- Assign the anonymous block to the local statement variable.
  lv_stmt := 'DECLARE'||CHR(10)
          || '  lv_super_hero     VARCHAR2(20);'||CHR(10)
          || '  lv_super_villain  VARCHAR2(20);'||CHR(10)
          || 'BEGIN'||CHR(10)
          || '  lv_super_hero '||CHR(58)||'= :pv_super_hero;'||CHR(10)
          || '  lv_super_villain '||CHR(58)||'= :pv_super_villain;'||CHR(10)
          || '  :pv_super_hero '||CHR(58)||'= lv_super_villain;'||CHR(10)
          || '  :pv_super_villain '||CHR(58)||'= lv_super_hero;'||CHR(10)
          || 'END;';
 
  -- Run the NDS program.
  EXECUTE IMMEDIATE lv_stmt USING IN OUT lv_super_hero
                                , IN OUT lv_super_villain;
 
  -- Print the diagnostic code block, that's why it used line returns afterall.  
  dbms_output.put_line('--------------------------------------------------');
  print_code_block(lv_stmt);
  dbms_output.put_line('--------------------------------------------------');
 
  -- Demonstrate the world is upside down without Johnny Depp playing Capt'n Jack.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
END;
/

The good Thor beats up the bad Loki!
--------------------------------------------------
| DECLARE
|   lv_super_hero     VARCHAR2(20);
|   lv_super_villain  VARCHAR2(20);
| BEGIN
|   lv_super_hero := :pv_super_hero;
|   lv_super_villain := :pv_super_villain;
|   :pv_super_hero := lv_super_villain;
|   :pv_super_villain := lv_super_hero;
| END;
--------------------------------------------------
The good Loki beats up the bad Thor!

PLS-00999: Implementation Restriction (may be temporary)

CREATE OR REPLACE FUNCTION full_name
( pv_first_name   VARCHAR2
, pv_last_name    VARCHAR2 )
RETURN VARCHAR2 RESULT_CACHE IS
BEGIN  
  RETURN pv_first_name || ' ' || pv_last_name;
END full_name;
/

SELECT   full_name(c.first_name, c.last_name)
FROM     contact c;

CREATE OR REPLACE PACKAGE cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE;
END cached_function;
/

CREATE OR REPLACE PACKAGE BODY cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE IS
  BEGIN  
    RETURN pv_first_name || ' ' || pv_last_name;
  END full_name; 
END cached_function;
/

SELECT   cached_function.full_name(c.first_name, c.last_name)
FROM     contact c;

CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = @sv_login_name;

ERROR 1351 (HY000): VIEW's SELECT contains a variable or parameter

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS set_login_name$$
 
-- Create the function.
CREATE FUNCTION set_login_name(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a local variable to verify completion of the task. */
  DECLARE  lv_success_flag  INT UNSIGNED  DEFAULT FALSE;
 
  /* Check whether the input value is something other than a null value. */
  IF pv_login_name IS NOT NULL THEN
 
    /* Set the session variable and enable the success flag. */
    SET @sv_login_name := pv_login_name;
    SET lv_success_flag := TRUE;
 
  END IF;
 
  /* Return the success flag. */
  RETURN lv_success_flag;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

SELECT IF(set_login_name('Frodo')=TRUE,'Login Name Set','Login Name Not Set') AS "Login Name Status";

SELECT IF(set_login_name('Frodo')=1,'Login Name Set','Login Name Not Set') AS "Login Name Status";

SELECT set_login_name('Frodo') AS "Login Name Status";

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS get_login_name$$
 
-- Create the function.
CREATE FUNCTION get_login_name() RETURNS VARCHAR(20)
BEGIN
  /* Return the success flag. */
  RETURN @sv_login_name;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

-- Conditionally drop the table.
DROP TABLE IF EXISTS hobbit;
 
-- Create the table.
CREATE TABLE hobbit
( hobbit_id    INT UNSIGNED
, hobbit_name  VARCHAR(20));
 
-- Seed two rows.
INSERT INTO hobbit VALUES ( 1,'Bilbo'),( 1,'Frodo');
 
-- Conditionally drop the view.
DROP VIEW IF EXISTS hobbit_v;
 
-- Create the function-enabled view.
CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = get_login_name();

UPDATE hobbit_v SET hobbit_id = 2;

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

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;

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;

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'

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;

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;

CREATE SYNONYM get_directory_path FOR system.get_directory_path;

SELECT get_directory_path('UPLOAD_FILES') FROM dual;

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

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

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';
/

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\ImportLogs'
                            ,'read,write,delete');
END;
/

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

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;

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 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

GRANT SELECT ON item_import TO application;

CREATE SYNONYM item_import FOR item_import;

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_table IS
  TABLE OF item_import_object;
/

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'));

                                                                  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

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 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;
/

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

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;
/

SET DEFINE OFF

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 PROCEDURE delete_file (dfile VARCHAR2) IS
LANGUAGE JAVA
NAME 'DeleteFile.deleteFile(java.lang.String)';
/

CREATE OR REPLACE TYPE sample_object IS OBJECT
(id       NUMBER
,name     VARCHAR2(30));
/
 
CREATE OR REPLACE TYPE sample_table IS TABLE OF sample_object;
/

DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object();
BEGIN
  NULL;
END;
/

  lv_object_struct SAMPLE_OBJECT := sample_object();
                                    *
ERROR at line 2:
ORA-06550: line 2, column 37:
PLS-00306: wrong number or types of arguments in call to 'SAMPLE_OBJECT'
ORA-06550: line 2, column 20:
PL/SQL: Item ignored

DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object(NULL, NULL);
BEGIN
  NULL;
END;
/

CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT);
/

CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(NULL,NULL);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT);
/

CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,NULL);
  BEGIN
    SELF := sample_obj;
  END sample_object;
  CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,pv_name);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED
 
DECLARE
  lv_object_struct1 SAMPLE_OBJECT := sample_object();
  lv_object_struct2 SAMPLE_OBJECT := sample_object('User Name');
  lv_object_struct3 SAMPLE_OBJECT := sample_object(1001,'User Name');
BEGIN
  dbms_output.put_line('lv_object_struct1.id   ['||lv_object_struct1.id||']');
  dbms_output.put_line('lv_object_struct1.name ['||lv_object_struct1.name||']');
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  lv_object_struct2.name := 'Changed Name';
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  dbms_output.put_line('lv_object_struct3.id   ['||lv_object_struct3.id||']');
  dbms_output.put_line('lv_object_struct3.name ['||lv_object_struct3.name||']');
END;
/

lv_object_struct1.id   [1]
lv_object_struct1.name []
lv_object_struct2.id   [2]
lv_object_struct2.name [User Name]
lv_object_struct2.id   [2]
lv_object_struct2.name [Changed Name]
lv_object_struct3.id   [1001]
lv_object_struct3.name [User Name]

CREATE OR REPLACE FUNCTION find_root_function
( pv_number   BINARY_DOUBLE
, pv_power    BINARY_DOUBLE
, pv_inverse  BINARY_INTEGER DEFAULT 0 ) RETURN BINARY_DOUBLE IS
 
  -- Declare local variable for return value.
  lv_result   BINARY_DOUBLE;
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    lv_result := POWER(pv_number,pv_power);
  ELSE
    lv_result := POWER(pv_number,(1 / pv_power));
  END IF;
 
  RETURN lv_result;
END find_root_function;
/

SELECT TO_CHAR(find_root_function(4,3),'99,999.90') FROM dual;
SELECT TO_CHAR(find_root_function(125,3,1),'99,999.90') FROM dual;

CREATE OR REPLACE PROCEDURE find_root_procedure
( pv_number   IN     BINARY_DOUBLE
, pv_power    IN     BINARY_DOUBLE
, pv_inverse  IN     BINARY_INTEGER DEFAULT 0
, pv_return      OUT BINARY_DOUBLE ) IS
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    pv_return := POWER(pv_number,pv_power);
  ELSE
    dbms_output.put_line('here');
    pv_return := POWER(pv_number,(1 / pv_power));
  END IF;
 
END find_root_procedure;
/

DECLARE
 
  -- Declare input variables.
  lv_input   BINARY_DOUBLE;
  lv_power   BINARY_DOUBLE;
  lv_inverse BINARY_INTEGER;
  lv_output  BINARY_DOUBLE;
 
BEGIN
 
  -- Assign input values to variables.
  lv_input := '&1';
  lv_power := '&2';
  lv_inverse := '&3';
 
  -- Test raising to a power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
  -- Test raising to an inverse power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
END;
/

-- SQL*Plus Test.
VARIABLE sv_input BINARY_DOUBLE
VARIABLE sv_power BINARY_DOUBLE
VARIABLE sv_inverse BINARY_DOUBLE
VARIABLE sv_output  BINARY_DOUBLE
 
-- Verify the null value of the session variable.
SELECT :sv_output AS ":sv_output" FROM dual;
 
BEGIN
 
  -- Prompt for local assignments and initialize output variable.
  :sv_input   := '&1';
  :sv_power   := '&2';
  :sv_inverse := '&3';
  :sv_output  := 0;
 
END;
/
 
-- Run the procedure in the SQL*Plus scope.
EXECUTE find_root_procedure(:sv_input, :sv_power, :sv_inverse, :sv_output);
 
-- Query the new value of the session variable.
SELECT TO_CHAR(:sv_output,'99,999.90') AS ":output" FROM dual;