CREATE OR REPLACE DIRECTORY YOUR_DIRECTORY AS '/path/to/your/directory';

DECLARE
  v_output_file UTL_FILE.FILE_TYPE;
BEGIN
  -- Open the file for writing (change 'YOUR_DIRECTORY' and 'output_file.sql' accordingly)
  v_output_file := UTL_FILE.FOPEN('YOUR_DIRECTORY', 'output_file.sql', 'W');

  FOR rec IN (SELECT * FROM source_table) LOOP
    -- Generate the INSERT statement and write it to the file
    UTL_FILE.PUT_LINE(v_output_file, 
      'INSERT INTO target_table (col1, col2, col3, col4, col5, col6, col7, col8) ' ||
      'VALUES (''' || rec.col1 || ''', ''' || rec.col2 || ''', ''' || rec.col3 || ''', ' ||
      '''' || rec.col4 || ''', ''' || rec.col5 || ''', ''' || rec.col6 || ''', ''' || rec.col7 || ''', ' ||
      '''' || rec.col8 || ''');');
  END LOOP;

  -- Close the file
  UTL_FILE.FCLOSE(v_output_file);
EXCEPTION
  WHEN OTHERS THEN
    -- Handle exceptions as needed
    IF UTL_FILE.IS_OPEN(v_output_file) THEN
      UTL_FILE.FCLOSE(v_output_file);
    END IF;
    RAISE;
END;
/

[root@br8dba01 u01]# mkdir -p APP_DIR
[root@br8dba01 u01]# chown -R oracle:oinstall APP_DIR
[root@br8dba01 u01]# chmod -R 755 APP_DIR/
[root@br8dba01 u01]# cd APP_DIR/
[root@br8dba01 APP_DIR]# pwd
/u01/APP_DIR
[root@br8dba01 APP_DIR]#

SQL> CREATE OR REPLACE DIRECTORY APP_DIR AS '/u01/APP_DIR';

Directory created.

SQL>

SQL> GRANT READ,WRITE ON DIRECTORY APP_DIR TO SCOTT;

Grant succeeded.

SQL>

SQL> SELECT * FROM EMP WHERE JOB='CLERK';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL>

SQL> DECLARE
  v_output_file UTL_FILE.FILE_TYPE;
BEGIN
  -- Open the file for writing (change 'YOUR_DIRECTORY' and 'output_file_plsql.sql' accordingly)
  v_output_file := UTL_FILE.FOPEN('APP_DIR', 'output_file_plsql.sql', 'W');

  FOR rec IN (SELECT * FROM EMP WHERE JOB='CLERK') LOOP
    -- Generate the INSERT statement and write it to the file
    UTL_FILE.PUT_LINE(v_output_file,
      'INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) ' ||
      'VALUES (''' || rec.EMPNO || ''', ''' || rec.ENAME || ''', ''' || rec.JOB || ''', ' ||
      '''' || rec.MGR || ''', ''' || rec.HIREDATE || ''', ''' || rec.SAL || ''', ''' || rec.COMM || ''', ' ||
      '''' || rec.DEPTNO || ''');');
  END LOOP;

  -- Close the file
  UTL_FILE.FCLOSE(v_output_file);
EXCEPTION
  WHEN OTHERS THEN
    -- Handle exceptions as needed
    IF UTL_FILE.IS_OPEN(v_output_file) THEN
      UTL_FILE.FCLOSE(v_output_file);
    END IF;
    RAISE;
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26

PL/SQL procedure successfully completed.

SQL>

[oracle@br8dba01 ~]$ ls -ltr /u01/APP_DIR/
total 4
-rw-r--r--. 1 oracle oinstall 571 Oct 26 10:31 output_file_plsql.sql
[oracle@br8dba01 ~]$
[oracle@br8dba01 ~]$ cat /u01/APP_DIR/output_file_plsql.sql
INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('7369', 'SMITH', 'CLERK', '7902', '17-DEC-80', '800', '', '20');
INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('7876', 'ADAMS', 'CLERK', '7788', '23-MAY-87', '1100', '', '20');
INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('7900', 'JAMES', 'CLERK', '7698', '03-DEC-81', '950', '', '30');
INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('7934', 'MILLER', 'CLERK', '7782', '23-JAN-82', '1300', '', '10');
[oracle@br8dba01 ~]$