Task Detail

Oracle2csv and /opt/oracle/client symbolic link Activity on Mon Jun 18 2018 19:56:01 GMT+0000 (UTC) 1. oracle2csv: is one of Charles node applications. https://gitlab.clayton.edu/adminsystems/oracle2csv 2. 'npm install -g oracle2csv' kelp giving me error messages because oracle2csv was using node oracledb 1.13.1 and not the latest 2.3.0. So the oracledb 1.13.1 was looking for the right location through the directory instead of going through the $PATH to find the location. 3. "couldn't find the '/opt/oracle/instantclient/libclntsh.dylib" -- I fixed this by "mkdir -p /opt/oracle/instantclient" and create symbolic link to where I placed my Oracle client files (I want my oracle client to be organized) -- "sudo ln -s /Applications/oracle/product/instantclient_64/12.2/lib/libclntsh.dylib /opt/oracle/instantclient" 4. "Couldn't find header file" -- I looked further into the error, and notice that the oracledb 1.13.1 was looking for "/opt/oracle/instantclient/sdk/include/oci.h" -- I download the SDK 12.2 from Oracle, and placed it under "/opt/oracle/instanclient" 5. 'npm install -g oracle2csv' was successfully installed the fixes above, but when I ran oracle2csv sqlfile.sql I got "couldn't load from libclntsh.dylib.12.1" -- I fixed the problem by: create symbolic "sudo ln -s /Applications/oracle/product/instantclient_64/12.2/lib/libclntsh.dylib.12.1 /opt/oracle/instantclient"

Oracle Client work with Node Oracle module: Activity on Wed Apr 25 2018 15:00:55 GMT+0000 (UTC) https://oracle.github.io/node-oracledb/doc/api.html#getstarted Test the communication between node oracledb to Oracle Client and to Oracle Database: var oracledb = require('oracledb'); // Get a non-pooled connection oracledb.getConnection( { user : 'go to the project to get this', password : 'go to the project to get this', connectString : 'go to the project to get this' }, function(err, connection) { if (err) { console.error(err.message); return; } connection.execute( // The statement to execute 'SELECT A.SPRIDEN_LAST_NAME FROM SPRIDEN A WHERE A.SPRIDEN_PIDM = 65403 ', //WHERE A.SPRIDEN_PIDM = , // The "bind value" 180 for the bind variable ":id" [], // execute() options argument. Since the query only returns one // row, we can optimize memory usage by reducing the default // maxRows value. For the complete list of other options see // the documentation. { maxRows: 1 //, outFormat: oracledb.OBJECT // query result format //, extendedMetaData: true // get extra metadata //, fetchArraySize: 100 // internal buffer allocation size for tuning }, // The callback function handles the SQL execution results function(err, result) { if (err) { console.error(err.message); doRelease(connection); return; } console.log(result.metaData); // [ { name: 'DEPARTMENT_ID' }, { name: 'DEPARTMENT_NAME' } ] console.log(result.rows); // [ [ 180, 'Construction' ] ] doRelease(connection); }); }); // Note: connections should always be released when not needed function doRelease(connection) { connection.close( function(err) { if (err) { console.error(err.message); } }); } This was working fine. Activity on Wed Apr 25 2018 14:48:54 GMT+0000 (UTC) After installing 'npm install oracledb' module inside of my project, I should receive the message below: oracledb ******************************************************************************** oracledb ** Node-oracledb 2.2.0 installation complete for Node.js 8.8.1 (darwin, x64) oracledb ** oracledb ** To use the installed node-oracledb: oracledb ** - You need to have the Oracle Instant Client Basic or Basic Light package in ~/lib or /usr/local/lib oracledb ** Download from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html oracledb ** oracledb ** Node-oracledb installation instructions: https://oracle.github.io/node-oracledb/INSTALL.html oracledb ******************************************************************************** Even it is says to have Oracle Instant Client Basic or Basic Light in ~/lib or /usr/local/lib, I don't need to worry about this because I already set all these up from 'Add Oracle Client in my OSX' (task 2) and changed the path in ~/.zshr file.

Add Oracle client to my OS Activity on Thu Apr 26 2018 18:19:54 GMT+0000 (UTC) About export PATH on the task activity below: It doesn't matter if I put in .bash_profile or .zshrc. 1) .bash_profile is a interactive login shell file, so the path will be exported to the environment when I login to my Mac. 2) .zshrc is a non-interactive non-login shell file, so the path will be exported to the environment when I open/restart a zsh shell. Open a new zsh shell session will kick off .zshrc file to run. 3) .bashrc is a non-interactive non-login shell file for bash. 4) I don't know, yet what would be interactive login shell file for zsh, but I would keep .bash_profile as default for now. Activity on Wed Apr 25 2018 14:29:30 GMT+0000 (UTC) SUCCESSFUL CONNECTED TO BANNER DB through sqlplus using the instruction from the link below: https://tomeuwork.wordpress.com/2014/05/12/how-to-install-oracle-sqlplus-and-oracle-client-in-mac-os/ Since this is working, I know the Oracle Client library is working. Step: 1. Downloaded Instant Client Package - Basic and Client Package - SQL*Plus from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html 2. Extracted them the packages and put all the files in both extracted folders into one folder (instantclient_12_2) 3. Made the directories below in root Applications folder instead of putting them in my user ~/lib: $ mkdir -p /Applications/oracle/product/instantclient_64/12.2/bin $ mkdir -p /Applications/oracle/product/instantclient_64/12.2/lib $ mkdir -p /Applications/oracle/product/instantclient_64/12.2/jdbc/lib $ mkdir -p /Applications/oracle/product/instantclient_64/12.2/rdbms/jlib $ mkdir -p /Applications/oracle/product/instantclient_64/12.2/sqlplus/admin $ mkdir -p /Applications/oracle/product/instantclient_64/12.2/network/admin 4. Next, moved the files from the extracted united folder (instantclient_12_2) in desktop over the directories above: $ mv ojdbc* /Applications/oracle/product/instantclient_64/12.2/jdbc/lib/ $ mv x*.jar /Applications/oracle/product/instantclient_64/12.2/rdbms/jlib/ $ mv glogin.sql /Applications/oracle/product/instantclient_64/12.2/sqlplus/admin/ $ mv *dylib* /Applications/oracle/product/instantclient_64/12.2/lib/ $ mv *README /Applications/oracle/product/instantclient_64/12.2/ $ mv * /Applications/oracle/product/instantclient_64/12.2/bin/ 5. Copied the tnsnames.ora file to /Applications/oracle/product/instantclient_64/12.2/network/admin/ 6. IMPORTANT: Setup environment variables: in the instruction above, it shows me to add path into ~/.bash_profile, but I was using ZSH shell and not BASH. I "nano ~/.zshrc" and added the below to it: export ORACLE_HOME=/Applications/oracle/product/instantclient_64/12.2 export PATH=$ORACLE_HOME/bin:$PATH export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib Restarted the shell to complete adding the path. echo $PATH to check to see if the path was included, yet. 7. I "sqlplus gphan@dev.clayton" in the terminal, and it was working fine. ==> the Oracle library was working fine, so now I can move on and to get node-oracledb to work with oracle client. Activity on Fri Mar 30 2018 20:43:23 GMT+0000 (UTC) 1. For this project I will not need to use tnsnames.ora. 2. tnsnames.ora is needed when I have many applications use the same connection. 3. The oracle connection of this project will be configured inside of the config.js file. Activity on Fri Mar 30 2018 20:39:19 GMT+0000 (UTC) in number 3 of note on Fri Mar 30 2018 15:25:04, I shouldn't move the extracted folder into "~/usr/local/lib", but move all the content in that folder to "~/lib", so when the application run and kick off the config.js file, the extracted oracle files in the "~/lib" folder will start building the connection. Also, the files should be place under "~/lib" because this is my lib folder, and the ~/usr/local/lib is belong to the OS or related to the OS more. Activity on Fri Mar 30 2018 19:25:03 GMT+0000 (UTC) 1. Go to http://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html and choose Client for Mac OS X (Intel x86) (32-bit and 64-bit). 2. Since my node architecture is 64bit, then I need to download the 64bit client. 3. Extract the .zip folder then move to "~/usr/local/lib" Activity on Fri Mar 30 2018 18:33:40 GMT+0000 (UTC) Adding Oracle client to my Mac Operating System. This Oracle client will be the connection bridge between the node-oracledb module to the database (Banner database).

Node-Oracledb installation: Activity on Wed Apr 25 2018 14:44:05 GMT+0000 (UTC) After installing 'npm install oracledb' module inside of my project, I should receive the message below: oracledb ******************************************************************************** oracledb ** Node-oracledb 2.2.0 installation complete for Node.js 8.8.1 (darwin, x64) oracledb ** oracledb ** To use the installed node-oracledb: oracledb ** - You need to have the Oracle Instant Client Basic or Basic Light package in ~/lib or /usr/local/lib oracledb ** Download from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html oracledb ** oracledb ** Node-oracledb installation instructions: https://oracle.github.io/node-oracledb/INSTALL.html oracledb ******************************************************************************** Even it is says to have Oracle Instant Client Basic or Basic Light in ~/lib or /usr/local/lib, I don't need to worry about this because I already set all these up from 'Add Oracle Client in my OSX' (last task) and changed the path in ~/.zshr file. Activity on Fri Mar 30 2018 18:26:20 GMT+0000 (UTC) IMPORTANT - Node-oracle module should be installed as a module in BanAssist application and not on global/Operating System. Activity on Fri Mar 30 2018 18:24:31 GMT+0000 (UTC) I used https://github.com/oracle/node-oracledb/blob/master/INSTALL.md#overview for installation guide. 1. I tried 'npm install oracledb' but got error, and Charles advised me to install from the source code. 2. I was successfully installing the module using the source code. 2.1 The OS need to have Python 2.7 and my macOS High Sierra comes with it. 2.2 Xcode also was needed to install and was installed through Apple Store - make sure to open Xcode and agree with the licensing - I ran "sudo xcodebuild -license" in my terminal to agree with the license. 2.3 Run "npm install oracle/node-oracledb.git#v2.1.2" to install node-oracle module after all. Activity on Fri Mar 30 2018 18:13:00 GMT+0000 (UTC) Node-oracledb is a node module which is used for the application (the BanAssist-api) to communicate with Oracle client.

Summary: Activity on Fri Mar 30 2018 14:07:44 GMT+0000 (UTC) The layers of Oracle configuration for a node project from inside out (from the project to the database): 1. Node-Oracledb module - will be installed within BannerAssist project - this is the communication bridge between the application and Oracle Client. 2. Oracle client - will be installed on Mac Operating System - this is the communication bridge between the Node-Oracledb module with Oracle database (Banner).