Maximum length of command line argument that can be passed to SQL*Plus?

Question

I am calling SQL*Plus from Linux C Shell:

sqlplus username/password @file.sql var1 var2 var3

If I pass a string as var1, how long can this string be?

Is it governed by the OS? In this case:

Linux version 2.6.9-100.ELsmp ([email protected]) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-11)) #1 SMP Tue Feb 1 12:17:32 EST 2011

Update: Empirical testing yielded the following results:

• A command line argument of 5200 characters gave the error, "Word too long."
• 1300 characters then produced the SQL*Plus error, "string beginning "(000796384..." is too long. maximum size is 239 characters."
• As soon as I got under 239 chars all was well.

I think I'll use sqlldr to overcome this.

Solution 1

Try with: xargs --show-limits </dev/null

Your environment variables take up 2446 bytes
POSIX upper limit on argument length (this system): 2092658
POSIX smallest allowable upper limit on argument length (all systems): 4096
Maximum length of command we could actually use: 2090212
Size of command buffer we are actually using: 131072

There is no limit per argument, but a total for the whole command line length. In my system (Fedora 15/zsh) its closer to 2Mb. (line 4).

Solution 2

I came across "How long an argument list your kernel can take on the command line before it chokes?":

getconf ARG_MAX

which gives the following on my system:

131072

Comments

[Joan Cardona]

Thanks for this, but unfortunately xargs in this RedHat distribution doesn't have this flag. :(

[Pablo Castellazzi]

The closest thing to redhat i have is a vps with CentOS 5.4 also with linux 2.6.9. xargs report Maximum length of command we could actually use: 126682.

[rogerdpack]

2621440 here on CentOS 6.2

[Asclepius]

FYI: A simple way to prevent xargs from hanging after showing the limits is to use echo | xargs --show-limits. It'll cleanly exit.

[cprcrack]

2097152 in Ubuntu Server 14.04 LTS

[Behrooz]

@A-B-B FYI: that's not a hang, just press Ctrl-D to give it the EOF it asks for.

[nealmcb]

What system is that on, @umber?

[surj]

262144 on OSX 10.11.2

[Signal15]

1,048,320 on Solaris 10 1/13 (Update 11) (Kernel 150400-35), but 131,072 on RHEL 5.11 (Kernel 2.6.18-409.el5)

[LinusR]

Does anyone know whether quotation marks count toward the limit on argument length, or is it just the content of the argument itself? For example, if the limit on argument length was 10, would /bin/echo "0123456789" be at the limit, or two bytes over the limit due to the quotation marks?

[haxpor]

2097152 for CentOS 7

[Znik]

you can simply test it on your system. on my debian, xargs show limits displays the same, 2092658 bytes as command arguments. But it accepts properly 13k bytes as argument. I didn't test more because I don't need this. Unfortunately, many commands and apps get parameters only by argument call, not by its stdin or prepared argument file.

[pietrop]

2097152 on Ubuntu Server 16.04 LTS

[Pedro Vagner]

2097152 or 2 MiB or ~2MB on Raspbian GNU/Linux 8 (jessie)

[Vincas Dargis]

2Mb -> 2MB* . It's bytes, not bits ;-)

[smac89]

How does someone actually reach this limitation? I was under the impression that this also included passing arrays to functions i.e. I thought this will fail in zsh print -lf %u {1..3000000}, given that getconf ARG_MAX gives me 2097152

[smac89]

I asked this on another comment but let me ask here as well. How does someone actually reach this limitation? I was under the impression that this also included passing arrays to functions i.e. I thought this will fail in zsh print -lf %u {1..3000000}, given that getconf ARG_MAX gives me 2097152, and using xargs --show-limits shows: Maximum length of command we could actually use: 2070294

[Jordan]

2097152 on Windows Subsystem for Linux running Ubuntu 20.04.