0

I am using sqlplus to connect to an Oracle DB via Linux command line. I would like to be able to read through a flatfile that contains a list of macro names I am searching for in the db. I would like for it to read each one, output the results to a .csv file, read the next one and append the csv file and so on until it has read through the whole list. I have yet to find a method that works, advice is greatly appreciated!

Filename contains a list of variables such as: drawio macroname1 macroname2 etc.

SQL Query:

set trimout on
set tab off
set linesize 10000
set pagesize 20000
set colsep |
spool  results.csv
SELECT c.contentid, c.title, s.spacekey, s.spacename
FROM SPACES s
 JOIN CONTENT c ON s.spaceid = c.spaceid
JOIN CONTENT_PERM_SET cps ON c.contentid = cps.content_id
 JOIN CONTENT_PERM cp ON cps.id = cp.cps_id
JOIN BODYCONTENT bc ON bc.contentid = c.contentid
WHERE s.spacekey NOT LIKE '~%' AND
(cps.CONTENT_ID IN (SELECT c.contentid FROM CONTENT_PERM_SET group by 1 having count(*)>1)
OR (cps.CONTENT_ID IN (SELECT c.contentid FROM CONTENT_PERM_SET group by 1 having count(*)=1) AND cps.CONT_PERM_TYPE = 'Edit'))
AND bc.body LIKE '%ac:name="file_name%'
GROUP BY c.contentid, c.title, s.spacekey, s.spacename ; ```

AND bc.body LIKE '%ac:name="file_name%' <-- This is the variable I would like for it to replace with the macro name from the flatfile.

Thank you,
Joel
Joel
  • 1
  • The `sqlplus` has an ability called "substitution variables". There is a whole chapter in official text book on them. If you do not want them - use `sed` to replace a pattern with a value, and run the updated script through the `sqlplus`. Doable, but substitution variables are designed for staff like yours. – White Owl Oct 26 '22 at 02:03

0 Answers0