input string to table

Question

I am doing some debugging in SQL for oracle 10g. I have a big input string which is used in "IN Clause" i.e.

select * from table where col in ('str2','str3','str4','str5',...)

i want to convert the in clause to rows or table? Is there a way to do this i.e.

select 'str2','str3','str4','str5', .. from dual

but this outputs multiple columns and i want multiple rows?

Edit:

Here is what i am trying to do. suppose i have an excel data in tmp_table1 (cant create in reality) and tmp_table1 is same as the IN clause, then the below statement will give the missing keys.

SELECT *
  FROM tmp_table1
 WHERE unique_id NOT IN (
               SELECT unique_id
                 FROM table1
                WHERE unique_id IN
                                 ('str1', 'str2', 'str3', 'str4'))

now @andriy-m solution works if the in string is less than 4000. but what if its greater?

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

You are probably looking for this solution.

edited May 23 '17 at 12:29

Community

1
1

answered May 05 '11 at 21:46

Andriy M

76,112
17
94
154

Thanks this is almost what i need. how ever looks like my string is long since i am getting "ORA-01704: string literal too long" error – surya May 06 '11 at 19:35
@spiderdevil - Oracle has an absolute limit for strings of 4000 characters. If that isn't enough for you you need a different approach, one which uses PL/SQL. Please edit your question to give us more details about what you're trying to achieve. – APC May 07 '11 at 11:45

score 0 · Answer 2 · answered May 05 '11 at 20:16

0

You can UNION the values into multiple rows:

SELECT 'str2' AS col FROM dual
UNION
SELECT 'str3' FROM dual
UNION
SELECT 'str4' FROM dual
UNION
SELECT 'str5' FROM dual

answered May 05 '11 at 20:16

Dan J

16,319
7
50
82

thanks, but as i said, i have a big input i.e more or equal to 300 input srings 'str2','str3','str4','str5',...'str300'... and doing union looks like a lot of work :( – surya May 05 '11 at 20:42
I don't know where the input is coming from (an external program, a text file, your keyboard...) But you can insert the values into a temporary table and join on it to filter your query. – Dan J May 05 '11 at 21:26
input is coming from excel file. i copy and pasted them to form the in clause. (by replacing \n to ','). I don't have permission to create temp table. is there no other way? – surya May 05 '11 at 21:52
If you want to convert the in-clause values into rows, and can't create a temp table, I know of no other way than a UNION. But let me ask: why do you need to convert the values from an IN clause to rows? – Dan J May 05 '11 at 21:55
So here is what i am trying to do..My table1 contains primary keys str1,str2,str3...str500.the excel also contains same table1 keys but couple of them are invalid... now i want to compare both keys to find which one is invalid – surya May 06 '11 at 03:13

input string to table

2 Answers2