0

I have a requirement to construct an SQL that has a where clause which is expected to look into a file entries to be used in that clause.

SELECT DISTINCT '/',t_05.puid, ',', t_01.puid,'/', t_01.poriginal_file_name
FROM PWORKSPACEOBJECT t_02, PREF_LIST_0 t_03, PPOM_APPLICATION_OBJECT t_04, PDATASET t_05, PIMANFILE t_01
WHERE t_03.pvalu_0 = t_01.puid AND t_02.puid = t_03.puid AND t_03.puid = t_04.puid AND t_04.puid = t_05.puid AND t_02.puid IN ( 'izeVNXjf44e$yB',
'gWYRvN9044e$yB' );

The above is the SQL query. As you can see the IN clause has two different strings ( puids ) that are to be considered. But in my case, this list is like 50k entries long and would come from splunk and will be in a text file.

Sample output of the text file looks as belows:

'gWYRvN9044e$yB',
'DOZVpdOQ44e$yB',
'TlfVpdOQ44e$yB',
'wOWRehUc44e$yB',
'wyeRehUc44e$yB',
'w6URehUc44e$yB',
'wScRehUc44e$yB',
'yzXVNXjf44e$yB',
'guWRvN9044e$yB',
'QiYRehUc44e$yB',
'gycRvN9044e$yB'

I am not an SQL guru, but a quick google on this gave me a reference to OPENROWSET construct, which is not available on Oracle.

Can you please suggest some pointers on what can be done to circumvent the problem.

Thanks, Pavan.

Pavan Dittakavi
  • 3,013
  • 5
  • 27
  • 47

2 Answers2

1

Consider using an external table, SQL Loader or perhaps loading the file into a table in the application layer and querying it normally.

Robert Bain
  • 9,113
  • 8
  • 44
  • 63
0

I would recommend creating a Global Temporary table, adding the rows to that table, and then joining to your temp table.
How to create a temporary table in Oracle

Other options: You could also use pipelined functions: https://oracle-base.com/articles/misc/pipelined-table-functions

Or use the with as... construct to fold the data into the SQL. But that would create a long SQL statement.

Egret
  • 739
  • 3
  • 8