insert 1000+ long numbers into oracle temp table

Question

I was doing a select operation on my table in oracle but was getting ORA-01795 so, then I try inserting my values in the list of order 1000+ (890623250,915941020,915941021,....1000+ times) into temp table and I can't figure it out how to do it so that later I can do a select from a temp table

So basically my objective is to insert those 1000 id into the temp table of schema TEMP_L{ID INTEGER} like INSERT INTO TEMP_LINK SELECT(890623254,915941020,1000+ values )

Can you share your actual query? The question is a bit unclear, and seeing something concrete may help clearing it up. — Mureinik, May 29 '18 at 06:46
Possible duplicate of [Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?](https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is) — Thomas G, May 29 '18 at 06:47
Actually I'm just trying to insert my list of ids(890623250,915941020,915941021,....1000+ times) into a temp table table of schema TEMP_L{INT ID} — Deepak Mann, May 29 '18 at 06:54
Just show us please the actual SQL, an have a look at the linkeThomas G provided — wolφi, May 29 '18 at 07:28

score 0 · Answer 1 · answered May 29 '18 at 07:13

0

Preferably use SQL* Loader for bulk inserts. One other option would be to construct a query using Excel or notepad++ for all the ids.

INSERT INTO mytable(id) 
select 890623250 FROM DUAL UNION ALL
select 915941020 FROM DUAL UNION ALL
...
..

answered May 29 '18 at 07:13

Kaushik Nayak

30,772
5
32
45

score 0 · Accepted Answer · answered May 29 '18 at 10:17

Use a collection. SYS.ODCINUMERLIST is a built-in VARRAY:

INSERT INTO TEMP_LINK ( value )
SELECT COLUMN_VALUE
FROM   TABLE( SYS.ODCINUMBERLIST( 890623254,915941020,1000 /* + values */ ) );

Or you can define your own collection:

CREATE TYPE NumberList IS TABLE OF NUMBER;

INSERT INTO TEMP_LINK ( value )
SELECT COLUMN_VALUE
FROM   TABLE( NumberList( 890623254,915941020,1000 /* + values */ ) );

However, if you are going to use collections then you don't need to load them into a temp table:

SELECT *
FROM   your_table
WHERE  your_id MEMBER OF NumberList( 890623254,915941020,1000 /* + values */ )

or

SELECT *
FROM   your_table
WHERE  your_id IN (
  SELECT COLUMN_VALUE
  FROM   TABLE( 890623254,915941020,1000 /* + values */ )
);

insert 1000+ long numbers into oracle temp table

2 Answers2