CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperoracle12c
Marci-man
Marci-man

Reputation: 2233

Oracle SQL: Running insert statements from a large text file

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.

Upvotes: 0

Views: 2003

Answers (3)

Jon Heller
Jon Heller

Reputation: 36807

Call the file with @ instead of trying to open the file. You may also want to disable feedback to avoid many thousands of "1 row inserted" messages.

set feedback off;
@c:\users\jon\Desktop\test.sql

The above commands are SQL*Plus syntax, but Oracle SQL Developer worksheets understand basic SQL*Plus commands. If you need to frequently run large scripts then you might want to learn the command line SQL*Plus, but if this is just a one-time task then stick with SQL Developer.

Upvotes: 1

Sergey Afinogenov
Sergey Afinogenov

Reputation: 2212

Use sqlplus and if where are too much text use options to log only in the file not on screen

 SET TERMOUT OFF; 

 spool M:\Documents\test.log;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

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');
...

Upvotes: 2

Related Questions