1. const oracledb = require("oracledb");
2. oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
3. oracledb.fetchAsString = [oracledb.CLOB, oracledb.DATE];
4. 
5. class OracleRepository {
6. 	 _pool;
7. 
8. 	 async getLocationDetails(bcccode){
9. 		let query = "BEGIN GET_BR_FC_CBC_NAME(:bcccode,:cursor); END;";
10. 		let bindParameters = {
11. 			bcccode: bcccode,
12. 			cursor: { dir: oracledb.BIND_OUT, type: oracledb.DB_TYPE_CURSOR },
13. 		};
14. 		let result;
15. 		console.log("stats",this._pool.getStatistics())
16. 		let connection = await this._getConnection();
17. 
18. 		try {
19. 			try {
20. 				console.log("excuting sp");
21. 				result = await connection.execute(query, bindParameters);
22. 				console.log("excuting sp done");
23. 
24. 			} catch (err) {
25. 				console.log("error executing",err);
26. 				throw `unable to execute oracle query [Method: OracleRepository.getLocationDetails] [query: ${query}] [parameters: ${JSON.stringify(bindParameters)}]`;
27. 			}
28. 
29. 			let resultSet = result.outBinds.cursor;
30. 
31. 			let queryData = await this.fetchRefCursorFromStream(resultSet);
32. 			console.log("##################",queryData)
33. 			try{
34. 				return queryData[0];
35. 			}
36. 			catch(err){
37. 				console.log("#@@@@@@@@@@@@@",err);
38. 
39. 				throw `unable to json parse the result [Method: OracleRepository.getLocationDetails] [query: ${query}] [parameters: ${JSON.stringify(bindParameters)}] [err : ${err}]`
40. 			}
41. 		} catch (err) {
42. 			console.log("catch",err);
43. 
44. 			throw err;
45. 		} finally {
46. 			// release connection !IMPORTANT
47. 			await this._closeConnection(connection);
48. 		}
49. 	}
50. 	
51. 	 async closePoolConnection() {
52. 		await this._pool.close();
53. 	}
54. 	 fetchRefCursorFromStream(resultSet) {
55. 		return new Promise((resolve, reject) => {
56. 			let result = [];
57. 			let stream = resultSet.toQueryStream();
58. 			stream.on("error", (err) => {
59. 				return reject(`Error While Opening a Query Stream [Method: OracleRepository.fetchRefCursorFromStream]`
60. 				);
61. 			});
62. 			stream.on("data", (data) => {
63. 				result.push(data);
64. 			});
65. 			stream.on("end", function () {
66. 				stream.destroy();
67. 			});
68. 			stream.on("close", function () {
69. 				return resolve(result);
70. 			});
71. 		});
72. 	}
73. 	 async getPool(option) {
74. 		this._pool = await oracledb.createPool(option);
75. 	}
76. 	 async _closeConnection(connection) {
77. 		try {
78. 			if (connection !== undefined) {
79. 				await connection.close();
80. 			}
81. 		} catch (err) {
82. 			console.log("unknown error at OracleRepository.closeConnection: ", err);
83. 		}
84. 	}
85. 	 async _getConnection() {
86. 		try {
87. 			//console.log("pool",this._pool, "status", this._pool.status);
88. 			let connection = await this._pool?.getConnection();
89. 			console.log("pool connection found");
90. 
91. 			connection.callTimeout = 20000;
92. 			return connection;
93. 		} catch (err) {
94. 			console.log("_getConnection error", err);
95. 			throw  `unable to get database connection [Method: OracleRepository.getConnection]`;
96. 		}
97. 	}
98. }
99. 
100. function connect(database) {
101. 	if (database.ISSID == 1) {
102. 		let connectString = `(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${database.HOST})(PORT=${database.PORT}))(CONNECT_DATA=(SID=${database.DATABASE})))`;
103. 		return connectString;
104. 	} else {
105. 		let connectString = `(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${database.HOST})(PORT=${database.PORT}))(CONNECT_DATA=(SERVICE_NAME=${database.DATABASE})))`;
106. 		return connectString;
107. 	}
108. }
109. 
110. let orarepo = new OracleRepository();
111. async function init(){
112. 
113. 	let option ={
114. 	user: 'base_fi_6',
115. 	password: 'base_fi_6' ,
116. 	connectString: connect({HOST: '10.10.30.130', PORT: 1521, DATABASE: 'orcl' }),
117. 	poolMax: 1000, //The maximum number of connections to which a connection pool can grow.
118. 	poolMin: 10, //The minimum number of connections which will be open.
119. 	poolTimeout: 15, //The number of seconds after which idle connections (unused in the pool) are terminated.
120. 	queueTimeout: 3000, //Number of milliseconds after which connection requests waiting in the connection request queue are terminated.
121. 	enableStatistics: true,
122. 	queueMax: 1000,
123. 	poolAlias: "WORKFLOW",
124. 	poolPingInterval : 10
125. };
126. 
127. await orarepo.getPool(option);
128. console.log("pool created");
129. await callSp();
130. }
131. 
132. init().catch(err =>{
133. 	console.log("init error", err);
134. })
135. 
136. 
137. function pause(t){
138. 	return new Promise(resolve =>{
139. 		setTimeout(()=>{
140. 			return resolve();
141. 		},t)
142. 	});	
143. }
144. 
145. async function callSp(){
146. 	try{
147. 		console.log("calling sp")
148. 		let result = await orarepo.getLocationDetails('123456');
149. 		console.log("result", result);
150. 		await pause(8000);
151. 		return await callSp();
152. 		
153. 		} catch(e){
154. 			console.log("sp error",e);
155. 			throw e;
156. 		}
157. }