How to Select a List of 10,000 Unique Ids from Dual in Oracle SQL

how to select a list of 10,000 unique ids from dual in oracle SQL

Use a collection (they are not limited to 1000 items like an IN clause is):

SELECT COLUMN_VALUE AS id
FROM   TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       )

SYS.ODCIVARCHAR2LIST and SYS.ODCINUMBERLIST are collection types that are supplied in the SYS schema.

You can join this directly to whichever table you are SELECTing from without needing to use the DUAL table:

SELECT y.*
FROM   your_table y
       INNER JOIN TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       ) i
       ON (y.id = i.COLUMN_VALUE);

If you can get a collection type created then you do not even need the TABLE expression and can use it directly in the WHERE clause using the MEMBER OF operator:

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(200);
/

SELECT *
FROM   yourtable
WHERE  id MEMBER OF stringlist(
                      'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
                    );

You can even pass the values as a bind parameter - see my answer here

How to put more than 1000 values into an Oracle IN clause

Put the values in a temporary table and then do a select where id in (select id from temptable)

How to get records randomly from the oracle database?

SELECT *
FROM   (
    SELECT *
    FROM   table
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 21;

Binded variable to subquery

One option is to create a table, store those values into it and rewrite your query to

and a.name in (select b.name from a_new_table b)

If more than a single user uses that new table, consider using a global (or private, depending on your Oracle version) temporary table so that everyone sees only their own data, or include another identifier to serve the same purpose.

---

Another option is to split that loooong list of elements into rows; you'd do that in a subquery which would act just like the previous example, e..g

and a.name in (select regexp_substr(:variable, '[^,]+', 1, level) as var_name
               from dual
               connect by regexp_count(:variable, ',') + 1
              )

---

Related Topics