Tag Archives: How to Generate INSERT statements to a flat file using a PL/SQL query

Generate INSERT statements to a flat file using a PL/SQL query

How to Generate INSERT statements to a flat file using a PL/SQL query

Sample PL/SQL Statement:

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;
/

Test Output:

[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 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/