Q
Problem solve Get help with specific problems with your technologies, process and projects.

Removing blank lines from a text file

I want to upload a .txt file containing records in an Oracle table with the UTL_FILE package. There are blank lines between two records in many places, and with the script I'm running, whenever the procedure encounters a blank line it comes out of the file considering it as the end of the file. Can you suggest how to remove blank lines from a text file with the help of the script only?

I want to upload a .txt file containing records in an Oracle table with the UTL_FILE package. There are blank lines (wide spaces) between two records in many places, and with the script I'm running, whenever the procedure encounters a blank line between records it comes out of the file considering it as the end of the file. Since the file is a very large size, I cannot manually remove the blank lines between the records. Can you suggest how to remove blank lines from a text file for uploading in an Oracle database with the help of the script only?
I feel that the proper tool for this job is to use SQL*Loader, not the UTL_FILE package.

However, if you want or need to use UTL_FILE, then you have complete programmatic control over reading the contents of the file you opened.

In your PL/SQL routine, you can use the GET_LINE command to read one line of text from the file. Once you have that line of text, how you handle it is up to you. A blank line should appear as just a carraige return in the text file. When you read a line with only a carraige return, the GET_LINE routine will only return NULL.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close