Can WITH clause have hardcoded values with out using Select statement?

Question

I have a situation where i need to join two different databases (both are oracle and i don't have DB link) to get my result. I am wondering if i can create a subquery using WITH clause with all the hardcoded values to join to the other table in other database. I tried something like below

WITH TEMP AS (SELECT '8108428','8110729' FROM DUAL) 

but it is giving the values as columns, but i need them under one column as rows, so i can join to other table. Can someone please help me a solution? I have used WITH clause previously but that had a query with in it, but this will have to take the hardcoded values only

Appreciate your responses!

Answer 1

Every SELECT returns a table, and every column in a table must have a name; thus, what you need is column names in your Common Table Expression:

WITH cteTEMP AS (SELECT '8108428' AS SOME_VAL FROM DUAL UNION ALL
                 SELECT '8110729' AS SOME_VAL FROM DUAL) 
SELECT *
  FROM cteTemp

Later in a comment you mention that you have thousands of values. Perhaps you can use a regular expression to parse your values out of a string:

WITH cteCsv AS (SELECT '8108428,8110729' AS CSV FROM DUAL),
     cteVals AS (SELECT REGEXP_SUBSTR(CSV, '[^,]+', 1, LEVEL) AS SOME_VAL
                   FROM CSV
                   CONNECT BY REGEXP_SUBSTR(CSV, '[^,]+', 1, LEVEL) IS NOT NULL)
SELECT *
  FROM cteVals

db<>fiddle here

Answer 2

You can use IN operator, passing tuples to it, which allow more than 1000 values inside. This way you will not need to worry about how to pass long string literal to database.

select *
from dual
where (1, dummy) in (
  (1, 'X'),
  (1, 'X')
)

db<>fiddle is here

What about generation of this list of tuples:

• You can export your result with IDs from the first database in Excel as you do now and generate the list by concatenation.
• You can generate it with the same select statement from SQL*Plus/SQL Developer using spool:

set heading off
set feedback off
set pages 0
set linesize 1000
set colsep ' '
set serveroutput on
spool c:\temp\myresult.txt

select
  '(1,' ,
  id_col,
  ')' as tuple
from my_source_table;

spool off

Answer 3

Three step process:

1. Create a JSON string (a JSON array encoding your values).
2. By whatever means (likely outside the databases, since you said you can't make them talk to each other), "copy" this string from the source db to the target db.
3. In the target db, call json_table on the string. You can use that directly in any query that requires the "source" table - in my example below, I extract the data in a view in the with clause, so you can use it as if the "source" table existed in the target db.

I am not showing Step 2 below, since that is not really on the database side. You can copy the string from the first query's output, and email it to yourself, or save it in a file and email the file, and then import from the file on the target side.

For Step 1, suppose I need to copy the "names" from SCOTT.EMP. That is as easy as

select json_arrayagg(ename) as json_str from scott.emp;

The result looks like this (note that I added a linebreak for readability, it is not actually in the output):

JSON_STR
------------------------------------------------------------------------------
["SMITH","ALLEN","WARD","JONES","MARTIN","BLAKE","CLARK","SCOTT",
 "KING","TURNER","ADAMS","JAMES","FORD","MILLER"]

For Step 3, suppose I copied that long string and pasted it in the query below. (You may need something different to move the string from one place to the other - left to you to think about.) The view created in the with clause replaces the view you were trying to create in your original question.

with
  local_emp_copy (emp) as (
    select ename
    from   json_table(
             '["SMITH","ALLEN","WARD","JONES","MARTIN","BLAKE","CLARK","SCOTT",
               "KING","TURNER","ADAMS","JAMES","FORD","MILLER"]'
             , '$[*]' columns ename varchar2 path '$')
  )
select emp from local_emp_copy  --  or do whatever you need with the view
;
  
EMP      
---------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

In your original question, you were creating a view with a column of strings, even though those strings were actually numbers. If the values are numbers, not strings, you shouldn't enclose them in quotes. In the JSON approach, if you have a column of numbers, the first step is the same. In step 3, in the columns clause of json_table, change the data type of the column to number. It's that simple.

Answer 4

You can create a collection type:

CREATE TYPE varchar2_10_table AS TABLE OF VARCHAR2(10);

Then if you were expecting to do something, for example, like:

WITH TEMP ( value ) AS (
  SELECT '8108428' FROM DUAL UNION ALL
  SELECT '8110729' FROM DUAL
)
SELECT *
FROM   your_table
WHERE  id IN ( SELECT value FROM temp )

Then you can just replace the WITH clause with the collection:

SELECT *
FROM   your_table
WHERE  id IN ( SELECT value FROM TABLE( varchar2_10_table( '8108428','8110729' ) ) )

or using the MEMBER OF operator:

SELECT *
FROM   your_table
WHERE  id MEMBER OF varchar2_10_table( '8108428','8110729' )

Or, if you want to keep the WITH clause and unpack the collection's values into it then:

WITH TEMP ( value ) AS (
  SELECT COLUMN_VALUE FROM TABLE( varchar2_10_table( '8108428','8110729' ) )
)
SELECT *
FROM   your_table
WHERE  id IN ( SELECT value FROM temp )

or, keeping the collection in the WITH clause:

WITH TEMP ( collection_value ) AS (
  SELECT varchar2_10_table( '8108428','8110729' ) FROM DUAL
)
SELECT y.*
FROM   your_table y
       INNER JOIN temp t
       ON ( y.id MEMBER OF t.collection_value )

Answer 5

The easiest way is to get your data as XMLTYPE or JSON, for example:

Get your data as XMLTYPE on the first database:

select xmltype(cursor(select table_name,num_rows from user_tables)) xmldata from dual;

XMLDATA
----------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TABLE_NAME>MY_DUAL</TABLE_NAME>
  <NUM_ROWS>1</NUM_ROWS>
 </ROW>
 <ROW>
  <TABLE_NAME>T</TABLE_NAME>
  <NUM_ROWS>10000</NUM_ROWS>
 </ROW>
 <ROW>
  <TABLE_NAME>T1</TABLE_NAME>
  <NUM_ROWS>1000</NUM_ROWS>
 </ROW>
 <ROW>
  <TABLE_NAME>T_DEL</TABLE_NAME>
  <NUM_ROWS>2678541</NUM_ROWS>
 </ROW>
</ROWSET>

And use it on another database:

SQL> select *
  2  from xmltable('/ROWSET/ROW'
  3  passing xmltype(q'[<?xml version="1.0"?>
  4  <ROWSET>
  5   <ROW>
  6    <TABLE_NAME>MY_DUAL</TABLE_NAME>
  7    <NUM_ROWS>1</NUM_ROWS>
  8   </ROW>
  9   <ROW>
 10    <TABLE_NAME>T</TABLE_NAME>
 11    <NUM_ROWS>10000</NUM_ROWS>
 12   </ROW>
 13   <ROW>
 14    <TABLE_NAME>T1</TABLE_NAME>
 15    <NUM_ROWS>1000</NUM_ROWS>
 16   </ROW>
 17   <ROW>
 18    <TABLE_NAME>T_DEL</TABLE_NAME>
 19    <NUM_ROWS>2678541</NUM_ROWS>
 20   </ROW>
 21  </ROWSET>
 22  ]')
 23  columns
 24    TABLE_NAME,
 25    NUM_ROWS number
 26  );

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
MY_DUAL                                 1
T                                   10000
T1                                   1000
T_DEL                             2678541

Answer 6

Please try this.

WITH TEMP AS (
              SELECT '8108428'FROM DUAL
              union all
              SELECT '8110729' FROM DUAL 
             )