CREATE OR REPLACE DIRECTORY my_dir AS '/path/to/your/directory';
grant read,write on directory my_dir to sys;
CREATE TABLE file_table (
file_id NUMBER PRIMARY KEY,
file_name VARCHAR2(255),
file_data BFILE
);
INSERT INTO file_table (file_id, file_name, file_data)
VALUES (1, 'example.txt', BFILENAME('MY_DIR', 'example.txt'));
DECLARE
file_content BLOB;
BEGIN
SELECT file_data INTO file_content FROM file_table WHERE file_id = 1;
-- Process the file_content (BLOB) as needed.
END;
CREATE OR REPLACE PROCEDURE write_to_file(p_file_name VARCHAR2, p_file_content CLOB) AS
file_handle UTL_FILE.FILE_TYPE;
BEGIN
file_handle := UTL_FILE.FOPEN('MY_DIR', p_file_name, 'W');
UTL_FILE.PUT_LINE(file_handle, p_file_content);
UTL_FILE.FCLOSE(file_handle);
END;
BEGIN
write_to_file('new_file.txt', 'This is the content of the new file.');
END;
drop directory my_dir;
--------------------------------# How To Access External Tables #-------------------------------------
create or replace directory my_dir as '/path/to/dir';
grant read,write on directory my_dir to sys;
CREATE TABLE external_data (
column1 NUMBER,
column2 VARCHAR2(255),
column3 DATE
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_dir -- Specify the directory object created earlier.
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ',' -- Set the appropriate delimiter for your file.
MISSING FIELD VALUES ARE NULL
)
LOCATION ('your_file.csv') -- Specify the name of your external file.
)
REJECT LIMIT UNLIMITED; -- Allows unlimited rejected records.
SELECT * FROM external_data;
drop table external_data;
