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
Possible duplicate of Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error? — Thomas G
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
Just show us please the actual SQL, an have a look at the linkeThomas G provided — wolφi

MT0 MT0 · Accepted Answer · 2018-05-29T10:17:36

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 Answers