2

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 (cat my_dump.sql | grep '.\{5000\}' | 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"
2
  • Are you sure that , can't also appear in literal strings - in which case appending newlines after them would alter the values inserted in tables? Also, are there lines with more that 2500 consecutive characters with no commas? – fra-san Oct 20 '20 at 11:13
  • @fra-san interesting questions, for the commas in literal string they might exist but I do not want to handle this case. As for the "2500 consecutive characters with no commas" i'm pretty sure there is not, if there is and it discard the line I am okay with this. – homer Oct 20 '20 at 14:30
3

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 ","...

1
  • Oh thank you, this works like a charm. – homer Oct 20 '20 at 13:30
3

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

3
  • I might miss something but this does not seem to work echo "this is a test, test, test, test, test" | awk 'length > 20 {gsub(/.{0,19},/, "&\n")} 1' – homer Oct 20 '20 at 13:39
  • @homer outputs this is a test, test, test, test, test on separate lines for me – muru Oct 20 '20 at 13:59
  • @muru, It also works for me but (I can't say why ) if you replace 19 by 1900 it seems to block, – JJoao Oct 20 '20 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.