Category Archives: Scripts

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/

Generate INSERT statements from the SELECT query

How to Generate INSERT statements from the SELECT query


Generate INSERT statements from the SELECT query

-- Set the output file path and name
SPOOL /path/to/output_file.sql

-- Generate INSERT statements from the SELECT query
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF

-- You can customize the column list and WHERE condition as needed
SELECT 'INSERT INTO target_table (col1, col2, col3) VALUES (' ||
       CHR(39) || col1 || CHR(39) || ', ' ||
       CHR(39) || col2 || CHR(39) || ', ' ||
       CHR(39) || col3 || CHR(39) || ');'
FROM source_table
-- WHERE some_condition;

-- Turn off SPOOL
SPOOL OFF


Test Output

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>

[oracle@br8dba01 ~]$ cat 1.sql
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SPOOL output_file.sql
SELECT 'INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (' ||
       CHR(39) || EMPNO || CHR(39) || ', ' ||
       CHR(39) || ENAME || CHR(39) || ', ' ||
       CHR(39) || JOB || CHR(39) || ', ' ||
           CHR(39) || MGR || CHR(39) || ', ' ||
           CHR(39) || HIREDATE || CHR(39) || ', ' ||
           CHR(39) || SAL || CHR(39) || ', ' ||
           CHR(39) || COMM || CHR(39) || ', ' ||
           CHR(39) || DEPTNO || CHR(39) || ');'
FROM EMP WHERE JOB='CLERK';
SPOOL OFF
[oracle@br8dba01 ~]$

SQL> @1.sql
INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('73
69', 'SMITH', 'CLERK', '7902', '17-DEC-80', '800', '', '20');

INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('78
76', 'ADAMS', 'CLERK', '7788', '23-MAY-87', '1100', '', '20');

INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('79
00', 'JAMES', 'CLERK', '7698', '03-DEC-81', '950', '', '30');

INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('79
34', 'MILLER', 'CLERK', '7782', '23-JAN-82', '1300', '', '10');

SQL>

[oracle@br8dba01 ~]$ cat output_file.sql
INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('73
69', 'SMITH', 'CLERK', '7902', '17-DEC-80', '800', '', '20');

INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('78
76', 'ADAMS', 'CLERK', '7788', '23-MAY-87', '1100', '', '20');

INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('79
00', 'JAMES', 'CLERK', '7698', '03-DEC-81', '950', '', '30');

INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('79
34', '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/

DBA Sheet v6.1

We have prepared DBA Sheet v6.1 which used to use on daily basis and we want to share with you all.

Special thanks to my friend "Sugivardhan Sundarrajan" who contributed lot of time to prepare this sheet

Please click here to download DBA Sheet v6.1

Please leave feedback in comment section below in case of any issues in this regard.

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
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

DBA Sheet

We have prepared DBA Sheet which used to use on daily basis and we want to share with you all.

Special thanks to my friend "Sugivardhan Sundarrajan" who contributed lot of time to prepare this sheet

Please click here to download DBA-Sheet - v7.0

Please leave feedback in comment section below in case of any issues in this regard.

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
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/