Load data from oracle db to csv or excel sheet

 declare
csv_fh  utl_file.file_type;
begin
    csv_fh := utl_file.fopen('/tmp', 'data_export.csv', 'W');
    for r in   ( select id,name from mydb) loop
    utl_file.put_line(csv_fh, r.id||'|'||r.name);
    end loop;
    utl_file.fclose(csv_fh);
end;  

 

CREATE DIRECTORY test_dir AS '/var/opt/oracle/lstest';

-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('/tmp', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/

utl_file_dir                         string      /tmp

SQL> DECLARE
    fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('/tmp', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/  

PL/SQL procedure successfully completed.

SQL> ls -lrt /tmp

SP2-0734: unknown command beginning "ls -lrt /t..." - rest of line ignored.

SQL> ! cat /tmp/test_file.txt

Writing TO a file

SQL> 

declare
   csv_fh  utl_file.file_type;
begin
    csv_fh := utl_file.fopen('/tmp', 'data_export.csv', 'W');
    for r in   ( select id,name from mydb) loop
    utl_file.put_line(csv_fh, r.id||'|'||r.name);
    end loop;
    utl_file.fclose(csv_fh);
end;  

/

PL/SQL procedure successfully completed.