Home » SQL & PL/SQL » SQL & PL/SQL » SQL Where Limitation (Linux RHAT 6.5 on using oracle 11.2.0.4)
SQL Where Limitation [message #639019] Mon, 29 June 2015 15:09 Go to next message
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 #639020 is a reply to message #639019] Mon, 29 June 2015 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>.....etc up to 140k records ! Is there away to reads the accnts from a file into this sql?
The values can & should reside in a table
WHERE ACCNT IN SELECT ACCOUNT_NAME FROM SOURCE_TBL;

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Re: SQL Where Limitation [message #639022 is a reply to message #639020] Mon, 29 June 2015 19:04 Go to previous messageGo to next message
ma251436
Messages: 5
Registered: June 2015
Location: USA
Junior Member
So I will need to upload the file to temp table for ex. temp_acctsand code as follows:

select acct, new_spid
      from sv
      where acct in (select acct 
                     from temp_accts)
      ;
Re: SQL Where Limitation [message #639023 is a reply to message #639022] Mon, 29 June 2015 19:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://docs.oracle.com/apps/search/search.jsp?category=database&product=e50529-01&q=EXTERNAL+TABLE
Re: SQL Where Limitation [message #639024 is a reply to message #639019] Tue, 30 June 2015 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
sqlplus and sql developer have a limit of 1000 records.


This is neither SQL nor SQL Developer, this is IN operator (from your example as SQL as NO limit in number of returned rows).
Note that "X IN (1,2,3,4)" is equivalent to "X=1 or X=2 or X=3 or X=4" or "X IN (1,2) or X IN (3,4)".

Quote:
Is there away to reads the accnts from a file into this sql?


As BlackSwan mentioned external table is the best way but it requires the file to be in the database server.

Re: SQL Where Limitation [message #639066 is a reply to message #639019] Tue, 30 June 2015 14:20 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Data selection on basis of columns from two consecutive rows (merged)
Next Topic: need select query based on pl sql table
Goto Forum:
  


Current Time: Thu Mar 28 05:04:08 CDT 2024