Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Joachim.

Asked: May 29, 2005 - 3:23 pm UTC

Answered by: Tom Kyte - Last updated: June 25, 2020 - 12:20 pm UTC

Category: Database - Version: 10

Viewed 50K+ times! This question is

You Asked

what is the maximum length of an sql statement you have seen so far ?
(just for curiosity)

as far as I know, in Oracle8 there was a limit of 64k allowed,
in Oracle10 there is no limit at all ? (didn't find something
in reference)

and we said...

Even in Oracle8, 64k was not the limit.

The limit is and was "by operating environment". SQLPlus might have a limit, Pro*C might have a limit. VB might impose it's own limit, JDBC might.

The API's are that which would impose a limit, well, besides "practicality" of course.


In plsql for example, execute immediate is limited to 32k because PLSQL itself is limited to 32k in a varchar variable. However, you can use dbms_sql to parse an ARRAY that can be huge -- megabytes in size.

Obviously *not recommended* (think about shared pool utilization!)

But I'm sure you have some PLSQL packages that exceeded 32k or even 64k in 8i -- and you create them using SQL...

and you rated our response

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Maybe the documentation doesn't help

May 30, 2005 - 10:55 am UTC

Reviewer: Doug from Edinburgh, UK

I was asked this question the other week and had a look in the documentation for 8.1.7 (the database is still stuck on this version at the app vendor's insistence)

This is what it says there :-

"SQL Statement Length
Maximum length of statements
64K maximum; particular tools may impose lower limits."


Tom Kyte

Followup  

May 30, 2005 - 11:22 am UTC

was documentation bug

tkyte@ORA8IW> declare
2 l_stmt dbms_sql.varchar2s;
3 l_cursor integer default dbms_sql.open_cursor;
4 l_rows number default 0;
5 l_length number := 0;
6 begin
7 l_stmt(1) := 'select 1 c1';
8 for i in 2 .. 15000
9 loop
10 l_stmt(i) := ', 1 c' || i;
11 end loop;
12 l_stmt(15000) := 'from dual';
13
14 for i in l_stmt.first .. l_stmt.last
15 loop
16 l_length := l_length + length(l_stmt(i));
17 end loop;
18 dbms_output.put_line( 'length = ' || l_length );
19
20 dbms_sql.parse( c => l_cursor,
21 statement => l_stmt,
22 lb => l_stmt.first,
23 ub => l_stmt.last,
24 lfflg => TRUE,
25 language_flag => dbms_sql.native );
26
27 l_rows := dbms_sql.execute(l_cursor);
28
29 dbms_sql.close_cursor( l_cursor );
30 end;
31 /
length = 138898

PL/SQL procedure successfully completed.

tkyte@ORA8IW>

the bug was fixed by removing the statement from later doc sets.

Cool ...

May 30, 2005 - 12:10 pm UTC

Reviewer: Doug

... I was just pointing out how easy it would be for someone to pick this up wrong, given that this particular manual is still online at docs.oracle.com.

Sadly, some of us are still stuck with 8i and that's normally when we need the docs most of all - to try to remember any old limits that might have been lifted.

Thanks for the clarification

32k limit on PL/SQL

May 31, 2005 - 9:22 am UTC

Reviewer: Joachim Seekopp

<you wrote>
In plsql for example, execute immediate is limited to 32k because PLSQL itself
is limited to 32k in a varchar variable.
</you wrote>

doesn't seem to be true:

set serverout on size 999999

declare
l_stmt1 long;
l_stmt2 long;
l_length number := 0;
p varchar2(32);
begin
p := 'explain plan for select 1 c1'; l_length := l_length + length(p);
l_stmt1 := p;
for i in 2 .. 3700 loop
p := ', 1 c' || i; l_length := l_length + length(p);
l_stmt1 := l_stmt1 || p;
end loop;
for i in 2 .. 3700 loop
p := ', 1 c' || i; l_length := l_length + length(p);
l_stmt2 := l_stmt2 || p;
end loop;
l_stmt2 := l_stmt2 || ' from dual';
dbms_output.put_line(concat('length=', l_length));
execute immediate l_stmt1 || l_stmt2;
end;
/

length=64402

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Tom Kyte

Followup  

May 31, 2005 - 10:05 am UTC

careful with that -- you'll find it "flaky" at best. for large SQL's dbms_sql with the array is going to be the way to do it.

Object name length restriction

July 06, 2005 - 2:44 pm UTC

Reviewer: A reader

I dont know about 10G but until 9i Database object names cannot be greator than 30 characters.

Why this painful restriction.
SQL SERVER which always copies from ORACLE has surpassed ORACLE on this one.

Any plans by ORACLE to remove this restriction or release a patch to do in earlier releases like 817,9205.

Tom Kyte

Followup  

July 07, 2005 - 8:21 am UTC

Not that I am aware of - it would have an incredible ripple effect on thousands of tools, 3rd party things, everything.

but - curious, can you give me a real world couple of examples where 30 characters for the name of an object is insufficient? I mean, wow - I would not like so much to actually have things even approaching 30, let alone exceed (I've never come close - well, only exception might be a constraint name)

no, we can't

July 07, 2005 - 9:29 am UTC

Reviewer: Joachim Seekopp

no, we can't give a real world couple of examples where 30
characters for the name of an object is insufficient.
we always can build artificial names -
for example
mycon<seq>
where <seq> is the value of a sequence
and maintain a translationtable
...
artifical_name varchar2(30),
name_i_would_like_i_could_give varchar2(1024)
...

(please not:
length('name_i_would_like_i_could_give') = 30
:-)
)

but - this restriction is one of the most annoying thing when
working with oracle

did you ever inherit a scheme with table names length up to 30 characters, where you had to add a family of triggers to them ?

in order to use "speaking names", you had to build the name of the trigger out of the name of the table - hitting the 30-char-border freqeunetly



Tom Kyte

Followup  

July 07, 2005 - 9:59 am UTC

why cannot you give one real world example? they are helpful if you ever try to, oh I don't know, put in an enhancement request.

"convince me", win me over, provide to me a compelling reason. tell me why it is worth breaking perhaps every 3rd party and most internal tools ever developed.


There is no rule saying triggers should be named after their tables. A trigger has an attribute that is the table it is on.

Do you regularly put the foreign key globbed into the primary key as well as in the foreign key in your master/detail??

create table dept( deptno primary key );
create table emp (empno_DEPTNO primary key, deptno references dept )

insert into dept values ( 10 );
insert into emp( 1234_10, 10 );

?

I see these really long names (anything over 15/20 characters to me personally is "a bit overboard") as being a problem, not a solution personally (I'm allowed to have an opinion right?)



Object name length restriction : example

July 07, 2005 - 10:28 am UTC

Reviewer: A reader

Basically its an attempt to Standardize naming conventions for DB object names.

I dont know How Oracle got this BLACK MAGIC number of 30 to start with.

Because of the 30 character limitation then we have to use modified crytic names.

Long names are self documenting and self explanatory from a Business perspective as well.

If we use code generation to generate thousands of triggers
This 30 character limitation kills u if we define a standard to name triggers as TRG_[ACTION TYPE/TRIGGERING SEQUENCE]_[TABLE NAME].

Same is the case when defining PKs FKS.
Some of the Business attribute names are really long in Financial,pharmacuetical world.


I pray to LARRY, please do HURRY to change this. :)

Just my .2 cents



Tom Kyte

Followup  

July 07, 2005 - 1:00 pm UTC

beats the ansi sql standard of 18.

How did SS get their black magic number?

heck, why do strings have sizes at all right? I mean, you don't code any limits into your programs do you?

I don't see a business case here at all. If you use code generation to generate thousands of triggers, well, thats an entirely different discussion.

don't see it happening, the implications downstream are horrendous.

There is real value to long names

July 07, 2005 - 10:36 am UTC

Reviewer: Mike Friedman from Shanghai

Our developers are all native Chinese speakers. That means that abbreviations that are totally obvious to you and me are cryptic and unusable for them - they need to memorize them. A full name is much easier - even if they don't know what a word means they just put their mouse over the word and get an automatic translation into Chinese using any one of several Windows translation productions.

So, for example, consider Sales_Orders, Sales_Order_Items, and Production_Order_Item_Details. Ooops... when over 30. Adding to the pain, what about the PK constraint, Production_Order_Item_Details_PK.

We can (and do) use abbreviations, but it adds to our time and cost for training new staff.

The alternative argument is that having to type long names is a problem as well, but with modern IDEs it isn't an issue.

This isn't just an issue for us. Look at Oracle EBS. They also try to avoid abbreviations but for longer names they have no choice.

This certainly isn't the most important enhancement I can think of to Oracle but if someone had time to up the limit to 200 or just get rid of it I'd be pretty happy.

Tom Kyte

Followup  

July 07, 2005 - 1:05 pm UTC

... but with modern IDEs it isn't an issue .....

we'll have to agree to disagree on that point.




Hmm...

July 07, 2005 - 11:33 am UTC

Reviewer: Vladimir Andreev from Germany

SQL> set lines 41
SQL> desc dba_objects
 Name              Null?    Type
 ----------------- -------- -------------
 OWNER                      VARCHAR2(30)
 <b>OBJECT_NAME                VARCHAR2(128)</b>
 SUBOBJECT_NAME             VARCHAR2(30)
 OBJECT_ID                  NUMBER
 DATA_OBJECT_ID             NUMBER
 OBJECT_TYPE                VARCHAR2(18)
 CREATED                    DATE
 LAST_DDL_TIME              DATE
 TIMESTAMP                  VARCHAR2(19)
 STATUS                     VARCHAR2(7)
 TEMPORARY                  VARCHAR2(1)
 GENERATED                  VARCHAR2(1)
 SECONDARY                  VARCHAR2(1)

 

Tom Kyte

Followup  

July 07, 2005 - 1:50 pm UTC

for database links. which may consist of series of dotted together names each of which is....

30 characters max.


not sure what was the question, but ...

July 07, 2005 - 1:27 pm UTC

Reviewer: Gabe

Vladimir:

If you check the definition of dba_objects you'll see it is a union all of [roughly] sys.obj$ and sys.link$ ... the names in obj$ are varchar2(30) and in link$ are varchar2(128) ... hence the varchar2(128) in dba_objects.

Tom Kyte

Followup  

July 07, 2005 - 1:57 pm UTC

I think he was saying "internally you must be good to go to 128 already"

But I'm thinking about exp/imp - no go.
seg fault on virtually every tool.
no existing 3rd party products working.
every modern day IDE crashing.

In response to such a fundemental, big change.

July 20, 2005 - 3:39 pm UTC

Reviewer: David Aldridge from Colorado Springs

create table emp_sal_pay
(
emp_sal_pay# number primary key,
emp_id ferefences emp,
date_of_payment date not null,
payment_amt number(38,2) not null,
payment_method references pay_method
)
/

Comment on Table emp_sal_pay is
'This is a table in which we store the records of payments
of employees salaries, with a synthetic key generated by
the sequence EMP_SAL_PAY_SEQ blah blah blah and so on for
up to 4000 characters.
Thanks and have a nice day.'
/



Max length of statements in Oracle 11g

June 25, 2020 - 11:37 am UTC

Reviewer: Anton from Russia

Hi, Tom!

I am facing the issue with very long statements in Oracle 11g (~220k-symbol selects with a lot of autogenerated IN (...) clauses). Selects are fired by JDBC causing SQLException 17410 No more data from socket.

On the other hand, selects with the same structure but with shorter lengths (~100k symbols) are executed fine.

The problem is that there is no reference in the docs on how to manage maximum statement length. There is just a note: The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory which is not informative at all.

Can you share your experience on how to estimate this maximum length and what database tweaks (if any) can help to increase it?
Chris Saxon

Followup  

June 25, 2020 - 12:20 pm UTC

220k symbols?! Yikes!

I've seen SQL statements several small print pages long; but I don't have any definitive guide on the upper limit. As the docs say, it's environment specific.

Remember the longer the statement is, the longer it's going to take to parse it. For big, complex queries this can add a lot of overhead

A couple of things which may help reduce the size of your query:

- Put sections of the query in a view, then change the query to use the view
- Instead of autogenerating IN lists, save the values to a temporary table and query this instead

If you can combine these, it can bring down the size of your statement drastically.

More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here