plsql - Oracle SQL: Running insert statements from a large text file

Question

I have a large text file (around 50mb). This text file has thousands of insert statements. I tried to open the text file in Oracle SQL Developer, but it is too large. How do I insert the data into my tables without opening the file in SQL Developer?

I tried to loop through the insert statements one by one and insert them into my table like this:

DECLARE 
V1 VARCHAR2(32767);
  fileVariable UTL_FILE.FILE_TYPE; 
BEGIN
  fileVariable := UTL_FILE.FOPEN('h:/Documents',
                                         'clob_export.sql',
                                         'R',
                                         32760);
 UTL_FILE.GET_LINE(fileVariable,V1,32767); 
 UTL_FILE.FCLOSE(fileVariable); 
END;

But this doesn't seem to work. I can't create directories on the machine, and anyways, the text file is on the computer where I am running SQL Developer and SQL Developer is connected remotely to the database.

question from:https://stackoverflow.com/questions/65908216/oracle-sql-running-insert-statements-from-a-large-text-file

Answer 1

The simplest way - from my point of view - is to run it from SQL*Plus, such as:

c:Temp>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Uto Sij 26 22:20:18 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @insert_data.sql

1 row created.


1 row created.

<snip>

presuming that insert_data.sql contains something like

insert into dept values (1, 'Dept 1', 'NY');
insert into dept values (2, 'Dept 2', 'London');
...