Creating a table from two given lists

Question

I am required to create a temporary table which will have two columns. The two columns will each come in a java array format e.g [1,2,3,4,5,6,....,n],[1.1,1.2,1.3,....,1.n] I am at a loss at how to do this, every example I seem to find online they use hardcoded in values which I can not use as it will change every time the application is run. There is a restriction that it all must be contained in one single sql file.

The expected output is simple a table of two columns as such:

col1 | col2
-----------
1    | 1.1
2    | 2.2
3    | 3.3

and so on.

So where are the lists and their values coming from? How is the application getting or generating them, and how will it pass them to Oracle? And how will the temporary table (do you mean a real global/local temporary table, or a CTE, or a collection) be used? — Alex Poole, Sep 10 '19 at 15:30
It will be two java arrays (just got an update, will edit the question shortly) which will be retrieved from neo4j. Similarly they are being passed through java to oracle — C Martin, Sep 10 '19 at 15:32
You can pass a Java array to Oracle as a collection - [here's one simple example](https://stackoverflow.com/a/35218816/266304), so passing two arrays is just as simple really. The examples you found might then work with those? — Alex Poole, Sep 10 '19 at 15:39
I feel this probably deserves a new question but how do I create a table using the collections? — C Martin, Sep 10 '19 at 16:21
You still haven't said what kind of table or what you'll do with it. As there are no answers it's probably still better to edit this question to fully explain what you are doing. And any restrictions - whether you can create schema-level objects to support this, for instance. — Alex Poole, Sep 10 '19 at 16:24
I just need a simple table, 2 columns, ints in one, strings in the other and certain lines of it will be unioned onto a larger statement. As for restrictions, so long as it is contained in oracle there shouldn't be any — C Martin, Sep 10 '19 at 16:28

score 1 · Accepted Answer · answered Sep 10 '19 at 16:40

Given a collection of numbers and a collection of strings, with the same number of elements, you could do something nasty like:

select a.column_value as num, b.column_value as str
from (
  select rownum as rn, column_value
  from table(sys.odcinumberlist(1,2,3,4,5,6))
) a
join (
  select rownum as rn, column_value
  from table(sys.odcivarchar2list('1.1','1.2','1.3','1.4','1.5','x.y'))
) b
on a.rn = b.rn;

       NUM STR                           
---------- ------------------------------
         1 1.1                           
         2 1.2                           
         3 1.3                           
         4 1.4                           
         5 1.5                           
         6 x.y

This relies on the collection unnesting preserving the order of the elements in both table collecton expressions, so the rn values stay in sync; as far as I'm aware that isn't guaranteed but I haven't seen it not do that, for whatever that is worth. A more involved approach would be to use PL/SQL to process to two varrays into a single combine collection, but that rather complicates using the result in a query, unless you can create new schema-level objects and collection types. Or to do that processing on the Java side and populate a two-dimensional schema-level collection type directly.

Anyway, you can supply the array values from Java as shown here and elsewhere, via bind variables, making that query:

select a.column_value as num, b.column_value as str
from (
  select rownum as rn, column_value
  from table(?)
) a
join (
  select rownum as rn, column_value
  from table(?)
) b
on a.rn = b.rn;

And you can use that as an inline view, or a CTE, as part of a larger query.

Creating a table from two given lists

1 Answers1