create directory and files in oracle database

 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;