Generate a Properties File using Shell Script and Results from a SQL Query

Question

I am trying to create a properties file like this...

firstname=Jon
lastname=Snow
occupation=Nights_Watch
family=Stark

...from a query like this...

SELECT 
  a.fname as firstname, 
  a.lname as lastname, 
  b.occ as occupation... 
FROM 
  names a, 
  occupation b, 
  family c... 
WHERE... 

How can I do this? As I am aware of only using spool to a CSV file which won't work here?

These property files will be picked up by shell scripts to run automated tasks. I am using Oracle DB

Answer 1

Perhaps something like this?

psql -c 'select id, name from test where id = 1' -x -t -A -F = dbname -U dbuser

Output would be like:

id=1
name=test1

(For the full list of options: man psql.)

Answer 2

Since you mentionned spool I will assume you are running on Oracle. This should produce a result in the desired format, that you can spool straight away.

SELECT
    'firstname=' || firstname || CHR(10) ||
    'lastname=' || lastname || CHR(10) -- and so on for all fields
FROM your_tables;

The same approach should be possible with all database engines, if you know the correct incantation for a litteral new line and the syntax for string concatenation.