10 min read
Billy Syrett
In my previous post, I explained how to automatically generate Word documents using Node.js, pulling data from a JSON array.
This post takes that concept one step further and uses data from an Oracle database to generate Word documents. This example will create documents for each IAL in IFS but you can use this method to create documents for anything in your database - you just need to change the SQL query and the substitution tags.
The Word document template has been adjusted to include the tags I need: view_name, view_desc, view_code, currently_assigned_permissions, currently_assigned_users and last_compile_date and has been formatted properly.
We'll use the source code from the previous post as a starting point but remove the JSON array and for loop.
const PizZip = require('pizzip');
const Docxtemplater = require('docxtemplater');
const fs = require('fs');
const path = require('path');
function replaceErrors(key, value) {
if (value instanceof Error) {
return Object.getOwnPropertyNames(value).reduce(function (error, key) {
error[key] = value[key];
return error;
}, {});
}
return value;
}
function errorHandler(error) {
console.log(JSON.stringify({ error: error }, replaceErrxors));
if (error.properties && error.properties.errors instanceof Array) {
const errorMessages = error.properties.errors.map(function (error) {
return error.properties.explanation;
}).join("\n");
console.log('errorMessages', errorMessages);
}
throw error;
}
To connect to an Oracle database from Node.js, we need one additional dependency: oracledb. This package does require some additional steps regarding the Oracle client libraries. I already have the correct Oracle client setup but if you don't, you can find all steps required to do this in the oracledb package documentation.
To install the oracledb dependency, execute the following command in your terminal:
npm install oracledb
And include the package at the top of the code of the index.js file:
const oracledb = require('oracledb');
I like to keep database connection details in a separate file so it's quicker to jump in and change them. To do this, create a new file named dbconfig.js and export an object with user, password and connectString keys.
module.exports = {
user: "IFSAPP",
password: "hunter2",
connectString: "IFSL"
};
For the connectString in the example above, I'm using the net service name from my tnsnames.ora file but there are many different types of connection strings you can use. For a full list of valid connection strings, please see the oracledb package documentation.
Include the connection information by requiring it after the other dependencies in index.js
const dbConfig = require('./dbconfig.js');
At this stage, it's a good time to prepare the SQL statement that we want to execute. For this example, my query gets the IAL name, code, description, last compile time and a list of users and roles that have permission to view it.
SELECT SUBSTR(V.VIEW_NAME, 1, LENGTH(V.VIEW_NAME) - 4) AS VIEW_NAME,
V.TEXT AS VIEW_CODE,
I.DESCRIPTION,
(SELECT LISTAGG(P.GRANTEE, CHR(10)) WITHIN GROUP (ORDER BY P.GRANTEE)
FROM DBA_TAB_PRIVS P
WHERE P.OWNER = 'IFSINFO'
AND P.GRANTEE NOT IN ('IFSAPP', 'IFSINFO', 'IFSSYS')
AND EXISTS(SELECT * FROM IFSAPP.FND_ROLE R
WHERE R.ROLE = P.GRANTEE)
AND P.TABLE_NAME = I.NAME) AS PERMISSIONS_ASSIGNED,
(SELECT LISTAGG(P.GRANTEE, CHR(10)) WITHIN GROUP (ORDER BY P.GRANTEE)
FROM DBA_TAB_PRIVS P
WHERE P.OWNER = 'IFSINFO'
AND P.GRANTEE NOT IN ('IFSAPP', 'IFSINFO', 'IFSSYS')
AND EXISTS(SELECT * FROM ALL_USERS U
WHERE U.USERNAME = P.GRANTEE)
AND P.TABLE_NAME = I.NAME) AS USERS_ASSIGNED,
(SELECT D.LAST_DDL_TIME
FROM DBA_OBJECTS D
WHERE D.OBJECT_NAME = I.NAME
AND D.OWNER = 'IFSINFO') AS LAST_COMPILE_TIME
FROM ALL_VIEWS V,
IFSAPP.IAL_OBJECT I
WHERE V.OWNER = 'IFSINFO'
AND SUBSTR(V.VIEW_NAME, LENGTH(V.VIEW_NAME) - 3) = '_IAL'
AND SUBSTR(V.VIEW_NAME, 1, LENGTH(V.VIEW_NAME) - 4) = I.NAME
After the error handling functions, create an asynchronous function named run to create the connection to the Oracle DB and pass in an option to return the results as an object.
async function run() {
let connection;
try {
let sql, binds, options, result;
connection = await oracledb.getConnection(dbConfig);
binds = {};
options = {
outFormat: oracledb.OUT_FORMAT_OBJECT,
};
Next, define the SQL statement that needs to be executed, execute the command and store the results in the result variable.
sql = `SELECT SUBSTR(V.VIEW_NAME, 1, LENGTH(V.VIEW_NAME) - 4) AS VIEW_NAME,
V.TEXT AS VIEW_CODE,
I.DESCRIPTION,
(SELECT LISTAGG(P.GRANTEE, CHR(10)) WITHIN GROUP (ORDER BY P.GRANTEE)
FROM DBA_TAB_PRIVS P
WHERE P.OWNER = 'IFSINFO'
AND P.GRANTEE NOT IN ('IFSAPP', 'IFSINFO', 'IFSSYS')
AND EXISTS(SELECT * FROM IFSAPP.FND_ROLE R
WHERE R.ROLE = P.GRANTEE)
AND P.TABLE_NAME = I.NAME) AS PERMISSIONS_ASSIGNED,
(SELECT LISTAGG(P.GRANTEE, CHR(10)) WITHIN GROUP (ORDER BY P.GRANTEE)
FROM DBA_TAB_PRIVS P
WHERE P.OWNER = 'IFSINFO'
AND P.GRANTEE NOT IN ('IFSAPP', 'IFSINFO', 'IFSSYS')
AND EXISTS(SELECT * FROM ALL_USERS U
WHERE U.USERNAME = P.GRANTEE)
AND P.TABLE_NAME = I.NAME) AS USERS_ASSIGNED,
(SELECT D.LAST_DDL_TIME
FROM DBA_OBJECTS D
WHERE D.OBJECT_NAME = I.NAME
AND D.OWNER = 'IFSINFO') AS LAST_COMPILE_TIME
FROM ALL_VIEWS V,
IFSAPP.IAL_OBJECT I
WHERE V.OWNER = 'IFSINFO'
AND SUBSTR(V.VIEW_NAME, LENGTH(V.VIEW_NAME) - 3) = '_IAL'
AND SUBSTR(V.VIEW_NAME, 1, LENGTH(V.VIEW_NAME) - 4) = I.NAME`;
result = await connection.execute(sql, binds, options);
From here, generating the actual Word documents using this data is essentially the same as in the previous post: loop through the rows array in the result variable, set the substitution data and output the file.
The only differences to the previous post are this data has line breaks in it (when displaying the assigned users/roles), so an extra parameter is passed into the Docxtemplater function:
doc = new Docxtemplater(zip, { linebreaks: true });
And the keys in the result array objects don't match exactly with the document so they need to be manually assigned:
doc.setData({
view_name: row['VIEW_NAME'],
view_desc: row['DESCRIPTION'] ? row['DESCRIPTION'] : 'None',
view_code: row['VIEW_CODE'],
currently_assigned_permissions: row['PERMISSIONS_ASSIGNED'] ? row['PERMISSIONS_ASSIGNED'] : 'None',
currently_assigned_users: row['USERS_ASSIGNED'] ? row['USERS_ASSIGNED'] : 'None',
last_compile_date: row['LAST_COMPILE_TIME']
});
Apart from those 2 parts, the rest of the code is the same:
for (let i = 0; i < result.rows.length; i++) {
const row = result.rows[i];
const content = fs.readFileSync(path.resolve(__dirname, 'template.docx'), 'binary');
var zip = new PizZip(content);
var doc;
try {
doc = new Docxtemplater(zip, { linebreaks: true });
} catch (error) {
errorHandler(error);
}
doc.setData({
view_name: row['VIEW_NAME'],
view_desc: row['DESCRIPTION'] ? row['DESCRIPTION'] : 'None',
view_code: row['VIEW_CODE'],
currently_assigned_permissions: row['PERMISSIONS_ASSIGNED'] ? row['PERMISSIONS_ASSIGNED'] : 'None',
currently_assigned_users: row['USERS_ASSIGNED'] ? row['USERS_ASSIGNED'] : 'None',
last_compile_date: row['LAST_COMPILE_TIME']
});
try {
doc.render()
}
catch (error) {
errorHandler(error);
}
var buf = doc.getZip().generate({ type: 'nodebuffer' });
fs.writeFileSync(path.resolve(__dirname, 'output', `${row['VIEW_NAME']}.docx`), buf);
console.log(`"${row['VIEW_NAME']}.docx" written to disk`)
}
Finish off by catching any connection errors and if everything goes well, close the connection to the database.
} catch (err) {
console.error(err);
} finally {
if (connection) {
try {
await connection.close();
} catch (err) {
console.error(err);
}
}
}
}
Lastly, call the run function.
run();
To execute the code, run the same command from the previous post in your terminal window.
node .
One document per IAL will now be created. An example of this can be found below:
Full Source Code
index.js
const PizZip = require('pizzip');
const Docxtemplater = require('docxtemplater');
const fs = require('fs');
const path = require('path');
const oracledb = require('oracledb');
const dbConfig = require('./dbconfig.js');
function replaceErrors(key, value) {
if (value instanceof Error) {
return Object.getOwnPropertyNames(value).reduce(function (error, key) {
error[key] = value[key];
return error;
}, {});
}
return value;
}
function errorHandler(error) {
console.log(JSON.stringify({ error: error }, replaceErrxors));
if (error.properties && error.properties.errors instanceof Array) {
const errorMessages = error.properties.errors.map(function (error) {
return error.properties.explanation;
}).join("\n");
console.log('errorMessages', errorMessages);
}
throw error;
}
async function run() {
let connection;
try {
let sql, binds, options, result;
connection = await oracledb.getConnection(dbConfig);
binds = {};
options = {
outFormat: oracledb.OUT_FORMAT_OBJECT,
};
sql = `SELECT SUBSTR(V.VIEW_NAME, 1, LENGTH(V.VIEW_NAME) - 4) AS VIEW_NAME,
V.TEXT AS VIEW_CODE,
I.DESCRIPTION,
(SELECT LISTAGG(P.GRANTEE, CHR(10)) WITHIN GROUP (ORDER BY P.GRANTEE)
FROM DBA_TAB_PRIVS P
WHERE P.OWNER = 'IFSINFO'
AND P.GRANTEE NOT IN ('IFSAPP', 'IFSINFO', 'IFSSYS')
AND EXISTS(SELECT * FROM IFSAPP.FND_ROLE R
WHERE R.ROLE = P.GRANTEE)
AND P.TABLE_NAME = I.NAME) AS PERMISSIONS_ASSIGNED,
(SELECT LISTAGG(P.GRANTEE, CHR(10)) WITHIN GROUP (ORDER BY P.GRANTEE)
FROM DBA_TAB_PRIVS P
WHERE P.OWNER = 'IFSINFO'
AND P.GRANTEE NOT IN ('IFSAPP', 'IFSINFO', 'IFSSYS')
AND EXISTS(SELECT * FROM ALL_USERS U
WHERE U.USERNAME = P.GRANTEE)
AND P.TABLE_NAME = I.NAME) AS USERS_ASSIGNED,
(SELECT D.LAST_DDL_TIME
FROM DBA_OBJECTS D
WHERE D.OBJECT_NAME = I.NAME
AND D.OWNER = 'IFSINFO') AS LAST_COMPILE_TIME
FROM ALL_VIEWS V,
IFSAPP.IAL_OBJECT I
WHERE V.OWNER = 'IFSINFO'
AND SUBSTR(V.VIEW_NAME, LENGTH(V.VIEW_NAME) - 3) = '_IAL'
AND SUBSTR(V.VIEW_NAME, 1, LENGTH(V.VIEW_NAME) - 4) = I.NAME`;
result = await connection.execute(sql, binds, options);
for (let i = 0; i < result.rows.length; i++) {
const row = result.rows[i];
const content = fs.readFileSync(path.resolve(__dirname, 'template.docx'), 'binary');
var zip = new PizZip(content);
var doc;
try {
doc = new Docxtemplater(zip, { linebreaks: true });
} catch (error) {
errorHandler(error);
}
doc.setData({
view_name: row['VIEW_NAME'],
view_desc: row['DESCRIPTION'] ? row['DESCRIPTION'] : 'None',
view_code: row['VIEW_CODE'],
currently_assigned_permissions: row['PERMISSIONS_ASSIGNED'] ? row['PERMISSIONS_ASSIGNED'] : 'None',
currently_assigned_users: row['USERS_ASSIGNED'] ? row['USERS_ASSIGNED'] : 'None',
last_compile_date: row['LAST_COMPILE_TIME']
});
try {
doc.render()
}
catch (error) {
errorHandler(error);
}
var buf = doc.getZip().generate({ type: 'nodebuffer' });
fs.writeFileSync(path.resolve(__dirname, 'output', `${row['VIEW_NAME']}.docx`), buf);
console.log(`"${row['VIEW_NAME']}.docx" written to disk`)
}
} catch (err) {
console.error(err);
} finally {
if (connection) {
try {
await connection.close();
} catch (err) {
console.error(err);
}
}
}
}
run();
dbconfig.js
module.exports = {
user: "IFSAPP",
password: "hunter2",
connectString: "IFSL"
};