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 (mockbuild@x86-010.build.bos.redhat.com) (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.

SQLPLUS parameters are not intended to pass massive amounts of data. It's nice to know what the limit is, but your question should be : how do I pass lots of data to SQLPLUS ? Using the parameters of it, is the wrong answer. But I see you have found one solution, be it SQLLDR. — tvCa, Dec 07 '14 at 17:40
http://stackoverflow.com/questions/6846263/maximum-length-of-command-line-argument-that-can-be-passed-to-sqlplus-from-lin || http://askubuntu.com/questions/14081/what-is-the-maximum-length-of-command-line-arguments-in-gnome-terminal || http://serverfault.com/questions/163371/linux-command-line-character-limit || http://unix.stackexchange.com/questions/120642/what-defines-the-maximum-size-for-a-command-single-argument — Ciro Santilli OurBigBook.com, Jun 22 '15 at 09:44

score 89 · Accepted Answer · edited Dec 22 '20 at 06:03

89

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

edited Dec 22 '20 at 06:03

GKFX

1,386
1
11
30

answered Jul 27 '11 at 18:29

Pablo Castellazzi

4,164
23
20

1

Thanks for this, but unfortunately xargs in this RedHat distribution doesn't have this flag. :( – Umber Ferrule Jul 29 '11 at 09:51
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`. – Pablo Castellazzi Jul 29 '11 at 14:30
9

**FYI**: A simple way to prevent `xargs` from hanging after showing the limits is to use `echo | xargs --show-limits`. It'll cleanly exit. – Asclepius Jan 23 '14 at 17:58
6

@A-B-B FYI: that's not a hang, just press Ctrl-D to give it the EOF it asks for. – Behrooz Oct 11 '14 at 13:33
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? – LinusR Jun 16 '17 at 18:38
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. – Znik Mar 05 '18 at 22:03
2Mb -> 2MB* . It's bytes, not bits ;-) – Vincas Dargis Jul 20 '20 at 11:35
I asked this on another [comment](https://stackoverflow.com/questions/6846263/maximum-length-of-command-line-argument-that-can-be-passed-to-sqlplus/6871471#comment115416562_6871471) 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_ – smac89 Dec 14 '20 at 07:25

score 58 · Answer 2 · edited Jul 13 '16 at 03:10

58

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:

edited Jul 13 '16 at 03:10

Gokhan Tank

3,786
1
22
19

answered Jul 29 '11 at 10:02

Umber Ferrule

3,358
6
35
38

4

2621440 here on CentOS 6.2 – rogerdpack Aug 29 '13 at 17:23
7

2097152 in Ubuntu Server 14.04 LTS – cprcrack Aug 05 '14 at 14:42
What system is that on, @umber? – nealmcb Dec 31 '14 at 00:05
2

262144 on OSX 10.11.2 – surj Feb 18 '16 at 18:44
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) – Signal15 Dec 06 '16 at 18:14
1

2097152 for CentOS 7 – haxpor Oct 29 '17 at 14:29
1

`2097152` on Ubuntu Server 16.04 LTS – pietrop Mar 26 '18 at 15:59
`2097152` or `2 MiB` or `~2MB` on Raspbian GNU/Linux 8 (jessie) – Apr 03 '20 at 14:00
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 Dec 14 '20 at 07:14
`2097152` on Windows Subsystem for Linux running Ubuntu 20.04. – Jordan Mar 06 '22 at 19:27

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

2 Answers2

Linked