jsonoracle-databaseutl-file

Oracle UTL_FILE invalid directory

trying to execute this procedure

CREATE OR REPLACE PROCEDURE save_customers_to_json(
  p_file_name in varchar2,
  p_dir_name in varchar2
) AS
    l_file UTL_FILE.file_type;
    l_json CLOB;
BEGIN
    SELECT JSON_ARRAYAGG(
             JSON_OBJECT(
                 'id' VALUE id,
                 'name' VALUE name,
                 'login' VALUE login,
                 'password' VALUE password,
                 'phone_number' VALUE phone_number
             ) RETURNING CLOB
           ) INTO l_json
    FROM customers;

    l_file := UTL_FILE.fopen(p_dir_name, p_file_name, 'w');

    UTL_FILE.put_line(l_file, l_json);

    UTL_FILE.fclose(l_file);

    DBMS_OUTPUT.put_line('JSON file has been created successfully.');
END save_customers_to_json;


begin save_customers_to_json('ser.json', 'serialization'); end;

the output is following

Error starting at line : 2 in command -

begin save_customers_to_json('ser.json', 'serialization'); end;

Error report -

ORA-29280: invalid directory object

ORA-06512: at "SYS.UTL_FILE", line 41

ORA-06512: at "SYS.UTL_FILE", line 478

ORA-06512: at "PROGRAMMER.SAVE_CUSTOMERS_TO_JSON", line 19

ORA-06512: at line 1

29280. 00000 -  "invalid directory object"

*Cause:    A corresponding directory object does not exist.

*Action:   Correct the directory object parameter, or create a corresponding

           directory object with the CREATE DIRECTORY command.

Previously I created directory 'serialization' like this:

create or replace directory serialization as 'C:/app/oradata/ORCL/AUTO/SER';

Also i granted all privileges to programmer .

I tried to execute procedure but still getting error. I granted all the priveleges to programmer.

Solution

  • You've created your directory as:

    create or replace directory serialization

    which means it's an unquoted identifier. The documentation for f_open specifies that the first location argument is:

    Directory location of file. This string is a directory object name and must be specified in upper case.

    I don't think that's quite correct, but it does have to match the name as it appears in the dictionary - i.e. in all_directories - and as it is an unquoted identifier that does mean it has to be supplied in uppercase here. So change

    begin save_customers_to_json('ser.json', 'serialization'); end;

    to

    begin save_customers_to_json('ser.json', 'SERIALIZATION'); end;

    Of course the actual operating system directory still has to be on (or available to) the database server, not a client machine, and be accessible to the user account running the Oracle database processes.