Home » SQL & PL/SQL » SQL & PL/SQL » Error in Fetching data thru SQLPLUS
Error in Fetching data thru SQLPLUS [message #406938] Sun, 07 June 2009 20:23 Go to next message
alamtapash@gmail.com
Messages: 8
Registered: June 2009
Location: DHAKA
Junior Member
HI ALL,

I am trying to fetch data from one table using the following query which is written in the spool file:

set numformat 999,999,999,999,999,999,999.999
set trimspool on
set echo off
set line 10000
set pagesize 50000
set head on
set array 1

spool C:\ACCOUNT.spl

select ACCOUNT_NO, BALANCE from TABLE1 where ACCOUNT_NO in (

where the number of account number is more than 11000

)

after running the above spool file the following ERROR is coming:

ERROR at line 2836:
ORA-01795: maximum number of expressions in a list is 1000

could you pls. let us know the process how to extract those bulk record of more than 1000 using the spool file?

NOTE:
without creating new table only for the account, is there any option to resolve the problem? that is; the problem is handled after creating one new table only for the account and then that new table is placed in the subquery. through this process, the problem is handled;
but i want to know is there any other option in sql plus by setting any parameter (before running the query; like set....); such as, by increasing number of rows of record fetching instead of 1000, can it be made to 5000 or more?

Thank you in advance

[Updated on: Sun, 07 June 2009 20:33]

Report message to a moderator

Re: Error in Fetching data thru SQLPLUS [message #406940 is a reply to message #406938] Sun, 07 June 2009 20:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>can it be made to 5000 or more?
NO

You know the solution by using a table (or GTT).
Why do you insist on something which does not exist?
Re: Error in Fetching data thru SQLPLUS [message #406947 is a reply to message #406938] Sun, 07 June 2009 23:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"in (1,2,3,4)" is logically equivalent to "in (1,2) or in (3,4)"

Regards
Michel
Re: Error in Fetching data thru SQLPLUS [message #406976 is a reply to message #406947] Mon, 08 June 2009 01:42 Go to previous message
alamtapash@gmail.com
Messages: 8
Registered: June 2009
Location: DHAKA
Junior Member
Hi Michel,

Your step works fine. Thanks
Previous Topic: ORA-06502: PL/SQL character string buffer too small
Next Topic: grant unlimited tablespace
Goto Forum:
  


Current Time: Thu Dec 08 20:01:38 CST 2016

Total time taken to generate the page: 0.05426 seconds