Oracledbex

Adapter to Oracle Database. Using DBConnection and ODBC.

Based on the project : Mssqlex

Documentation in development

Test in development

Getting Started

Oracledbex requires the Erlang ODBC application to be installed.

To install what is necessary for the odbc connection, refer to the following manual: ODBC.md

If available in Hex, the package can be installed by adding oracledbex to your list of dependencies in mix.exs:

def deps do
  [
    {:oracledbex, "~> 0.1.0"}
  ]
end

Quickfast introduction

Conection

iex(1)> {:ok, pid} = Oracledbex.start_link([{"DSN","ORACLEODBC"}])
{:ok, #PID<0.174.0>}

Examples

Select

iex(4)> {:ok,_,data} = Oracledbex.query(pid,"SELECT SYSDATE FROM DUAL",[])
{:ok,
 %Oracle.Query{columns: nil, name: "", statement: "SELECT SYSDATE FROM DUAL"},
 %Oracle.Result{columns: ["SYSDATE"], num_rows: 1,
  rows: [[{{2018, 1, 31}, {23, 18, 59, 0}}]]}}

{:ok,_,data} = Oracledbex.query(pid,"SELECT * FROM HR.EMPLOYEES",[])
{:ok,
 %Oracle.Query{columns: nil, name: "", statement: "SELECT * FROM HR.EMPLOYEES"},
 %Oracle.Result{columns: ["EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "EMAIL",
   "PHONE_NUMBER", "HIRE_DATE", "JOB_ID", "SALARY", "COMMISSION_PCT",
   "MANAGER_ID", "DEPARTMENT_ID"], num_rows: 107,
  rows: [[100, "Steven", "King", "SKING", "515.123.4567",
    {{2003, 6, 17}, {0, 0, 0, 0}}, "AD_PRES", #Decimal<2.4E+4>, nil, nil, 90],
   [101, "Neena", "Kochhar", "NKOCHHAR", "515.123.4568",
    {{2005, 9, 21}, {0, 0, 0, 0}}, "AD_VP", #Decimal<1.7E+4>, nil, 100, 90],

iex(4)> {:ok,_,data} = Oracledbex.query(pid,"SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = ? AND FIRST_NAME = ?",[115,"Alexander"])
{:ok,
 %Oracle.Query{columns: nil, name: "",
  statement: "SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = ? AND FIRST_NAME = ?"},
 %Oracle.Result{columns: ["EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "EMAIL",
   "PHONE_NUMBER", "HIRE_DATE", "JOB_ID", "SALARY", "COMMISSION_PCT",
   "MANAGER_ID", "DEPARTMENT_ID"], num_rows: 1,
  rows: [[115, "Alexander", "Khoo", "AKHOO", "515.127.4562",
    {{2003, 5, 18}, {0, 0, 0, 0}}, "PU_CLERK", #Decimal<3.1E+3>, nil, 114,
    30]]}}

Update

iex(5)> {:ok,_,data} = Oracledbex.query(pid,"UPDATE HR.EMPLOYEES SET SALARY = ? WHERE EMPLOYEE_ID = ?",[20000,115])
{:ok,
 %Oracle.Query{columns: nil, name: "",
  statement: "UPDATE HR.EMPLOYEES SET SALARY = ? WHERE EMPLOYEE_ID = ?"},
 %Oracle.Result{columns: nil, num_rows: 1, rows: nil}}

Delete

iex(7)> {:ok,_,data} = Oracledbex.query(pid,"DELETE  FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = ?",[115])
{:ok,
 %Oracle.Query{columns: nil, name: "",
  statement: "DELETE  FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = ?"},
 %Oracle.Result{columns: nil, num_rows: 1, rows: nil}}

Insert

iex(3)> {:ok,_,data} = Oracledbex.query(pid,"INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID,HIRE_DATE) VALUES (?,?,?,?,?,?,?,?,?,TO_DATE(?,'DD/MM/RR'))",[4000,"ERIC","Flores","ericpaulfloresegmail.com","44444.44","PU_CLERK",300000,114,30,"31/01/18"])
{:ok,
 %Oracle.Query{columns: nil, name: "",
  statement: "INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID,HIRE_DATE) VALUES (?,?,?,?,?,?,?,?,?,TO_DATE(?,'DD/MM/RR'))"},
 %Oracle.Result{columns: nil, num_rows: 1, rows: nil}}

Pool connection

Example

Conection

iex(1)> {:ok,pool} = DBConnection.start_link(Oracle.Protocol,[{"DSN","ORACLEODBC"},{:pool_size,5},{:pool,DBConnection.Poolboy}])
{:ok, #PID<0.175.0>}

Examples of querys whith pool connection

{:ok,_,data} = Oracledbex.query(pool,"SELECT SYSDATE FROM DUAL",[],pool: DBConnection.Poolboy)
{:ok,
 %Oracle.Query{columns: nil, name: "", statement: "SELECT SYSDATE FROM DUAL"},
 %Oracle.Result{columns: ["SYSDATE"], num_rows: 1,
  rows: [[{{2018, 2, 1}, {0, 55, 57, 0}}]]}}

iex(4)> {:ok,_,data} = Oracledbex.query(pool,"SELECT FIRST_NAME  FROM HR.EMPLOYEES",[],pool: DBConnection.Poolboy)
{:ok,
 %Oracle.Query{columns: nil, name: "",
  statement: "SELECT FIRST_NAME FROM HR.EMPLOYEES"},
 %Oracle.Result{columns: ["FIRST_NAME"], num_rows: 107,
  rows: [["Ellen"], ["Sundar"], ["Mozhe"], ["David"], ["Hermann"], ["Shelli"],
   ["Amit"], ["Elizabeth"], ["Sarah"], ["David"], ["Laura"], ["Harrison"],
   ["Alexis"], ["Anthony"], ["Gerald"], ["Nanette"], ["John"], ["Kelly"],
   ["Karen"], ["Curtis"], ["Lex"], ["Julia"], ["Jennifer"], ["Louise"],
   ["Bruce"], ["Alberto"], ["Britney"], ["Daniel"], ["Pat"], ["Kevin"],
   ["Jean"], ["ERIC"], ["Tayler"], ["Adam"], ["Timothy"], ["Ki"], ["Girard"],
   ["William"], ["Douglas"], ["Kimberely"], ["Nancy"], ["Danielle"], ["Peter"],
   ["Michael"], ["Shelley"], [...], ...]}}

iex(5)> {:ok,_,data} = Oracledbex.query(pool,"SELECT *  FROM HR.COUNTRIES WHERE  REGION_ID = ?",[1],pool: DBConnection.Poolboy)
{:ok,
 %Oracle.Query{columns: nil, name: "",
  statement: "SELECT *  FROM HR.COUNTRIES WHERE  REGION_ID = ?"},
 %Oracle.Result{columns: ["COUNTRY_ID", "COUNTRY_NAME", "REGION_ID"],
  num_rows: 8,
  rows: [["BE", "Belgium", #Decimal<1.0>], ["CH", "Switzerland", #Decimal<1.0>],
   ["DE", "Germany", #Decimal<1.0>], ["DK", "Denmark", #Decimal<1.0>],
   ["FR", "France", #Decimal<1.0>], ["IT", "Italy", #Decimal<1.0>],
   ["NL", "Netherlands", #Decimal<1.0>],
   ["UK", "United Kingdom", #Decimal<1.0>]]}}

Documentation can be generated with ExDoc and published on HexDocs. Once published, the docs can be found at https://hexdocs.pm/oracledbex.