Split long SQL expression at delimiter

Question

Context

I'm trying to import a dump that have some long lines (8k+ character) with SQL*Plus, so I face the error SP2-0027: Input is too long (> 2499 characters). This is a hard-coded limit and cannot be overcome.

Expected solution

I would like to stream my input in bash and to split lines longer than the expected width on the last , (comma) character.

So I should have something like

cat my_dump.sql | *magic_command* | sqlplus system/oracle@xe

Details

1. I know that newer version can accept lines up to 4999 characters but I still have lines longer (grep '.\{5000\}' my_dump.sql | wc -l)
2. It is not really feasible to update the dump by hand
3. I did try to use tr but this split every line wich I do not want
4. I did try to use fmt and fold but it does not seems to be possible to use a custom delimiter
5. I am currently looking on sed but I cannot seem to figure out a regexp that would "find the last match of , in the first 2500 characters if there is more than 2500 characters"

Answer 1

Try something like:

sed -re '/.{2500}/ s/.{,2500},/&\n/g'

Explanation:

• /.{2500}/ if line contains 2500 characteres (or more) ...
• s/.{,2500},/&\n/g substitute up to 2500 char followed by a , adding a newline

I would not be surprised if this replaces some unreplaceble ","...

Answer 2

I'd imagine something like this awk command would work:

awk 'length > 2499 {gsub(/.{0,2498},/, "&\n")} 1'

The regex allows for up to 2498 characters before a comma, (so 2499 including the comma), and inserts a newline after it. (I used 2499 since the error messages mentions that number.)