30 SQL Developer Tips in 30 Days, Day 13: Executing Very Large Scripts

What’s a very large script?

That’s a subjective question, and the answer will depend somewhat on the hardware specs of your machine.

I got this question yesterday:

@thatjeffsmith is there a limit to the number of lines I can paste into the code editor in SQL developer?

— louise mcscootash (@metalouise) September 12, 2014

I enjoy these questions. They’re 99% of the time, not theoretical. She had obviously ran into some sort of wall.

And the wall looked like this:

@thatjeffsmith I've got an insert script generated by the Import Data tool with aforementioned 600k records but it won't open the file.

— louise mcscootash (@metalouise) September 12, 2014

There’s no real answer – like I said before, it ‘depends.’ But 600,000+ statements – our editor wasn’t designed to handle files of that size.

If you were to look at what Notepad does to a file on open compared to what we do on files on open and even as you type, click, and scroll – you would understand why.

So, what’s one to do when you want to run those INSERTs?

One More Tip

It will run even faster if you minimize the script output panel – no need for the tool to write 600k+ update messages to the display…

For very large files like this, you’ll want to make sure you log the output to a secondary file, so consider adding a SPOOL to your script.