SQL Where Limitation [message #639019] |
Mon, 29 June 2015 15:09 |
|
ma251436
Messages: 5 Registered: June 2015 Location: USA
|
Junior Member |
|
|
sqlplus and sql developer have a limit of 1000 records. I need to query 140k accounts from a single table. How do i do that ? how do i also use a list of all accounts from a file to use in a batch sql job to lookup the values?
select
acct
,NEW_CURRENT_SPID
from sv
where acct IN (
"2012050606",
"2012060223",
......etc up to 140k records ! Is there away to reads the accnts from a file into this sql?
[Updated on: Mon, 29 June 2015 15:10] Report message to a moderator
|
|
|
|
|
|
|
Re: SQL Where Limitation [message #639066 is a reply to message #639019] |
Tue, 30 June 2015 14:20 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Limit of 1000 list elements is, as Michel noted, SQL limitation and has nothing to do with tool you use to connect to the database. However, there is no limit to number of tuples in a list. SO, in general, you could (I am not saying you should) use:
select acct,
NEW_CURRENT_SPID
from sv
where (1,acct) IN (
(1,2012050606),
(1,2012060223),
as-many-tuples-as-you-like
)
/
SY.
|
|
|
Re: SQL Where Limitation [message #639292 is a reply to message #639066] |
Sun, 05 July 2015 00:45 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
how about something stupid like using xml...
not this this webpage is stupid, only that my use for the technique is a bad idea.
DECLARE
input_xml xmltype := xmltype('<?xml version="1.0" encoding="utf-8"?>
<employees>
<employee id="7369">
<name>SMITH</name>
<job>CLERK</job>
</employee>
<employee id="7499">
<name>ALLEN</name>
<job>SALESMAN</job>
</employee>
<employee id="7521">
<name>WARD</name>
<job>SALESMAN</job>
</employee>
</employees>');
BEGIN
INSERT INTO emp (empno, ename, job)
SELECT empno, ename, job
FROM XMLTable(
'/employees/employee'
passing input_xml
columns empno number(4) path '@id'
, ename varchar2(10) path 'name'
, job varchar2(9) path 'job'
)
;
END;
Just stick your 140K values in a big-ass xml doc and burn some CPU. I am sure it will fail, but the idea is kind of cool, using xmltable to turn an arbitrary text document into a table right in the SQL.
I'll bet it is wicked slow even if it did work. But you got to admit, there is something appealing about not having to deal with a DBA for external tables or creating work tables etc.
Oracle XML has some really cool features to get yourself into trouble with. I particularly like generating dynamic SQL and executing it on the fly right inside a SQL statement (woohoo!).
Just having fun. Kevin
|
|
|