Maybe the documentation doesn't help
Doug, May 30, 2005 - 10:55 am UTC
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."
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 ...
Doug, May 30, 2005 - 12:10 pm UTC
... 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
Joachim Seekopp, May 31, 2005 - 9:22 am UTC
<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.
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
A reader, July 06, 2005 - 2:44 pm UTC
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.
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
Joachim Seekopp, July 07, 2005 - 9:29 am UTC
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
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
A reader, July 07, 2005 - 10:28 am UTC
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
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
Mike Friedman, July 07, 2005 - 10:36 am UTC
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.
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...
Vladimir Andreev, July 07, 2005 - 11:33 am UTC
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)
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 ...
Gabe, July 07, 2005 - 1:27 pm UTC
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.
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.
David Aldridge, July 20, 2005 - 3:39 pm UTC
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
Anton, June 25, 2020 - 11:37 am UTC
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?
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.