Oracle JDBC

(Last Updated On: )

This post is how to connect to oracle from JDBC.

There are four main connection examples:

TCP to Single Host
  1. jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOST>)(PORT=<PORT>))(CONNECT_DATA=(SERVICE_NAME=<SERVICE_NAME>)))
TCP to Multiple Hosts
  1. jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOST1>)(PORT=<PORT1>))(ADDRESS=(PROTOCOL=TCP)(HOST=<HOST2>)(PORT=<PORT2>))(CONNECT_DATA=(SERVICE_NAME=<SERVICE_NAME>)))
SSL to Single Host
  1. jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<HOST>)(PORT=<PORT>))(CONNECT_DATA=(SERVICE_NAME=<SERVICE_NAME>)))
SSL to Multiple Hosts
  1. jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<HOST1>)(PORT=<PORT1>))(ADDRESS=(PROTOCOL=TCPS)(HOST=<HOST2>)(PORT=<PORT2>))(CONNECT_DATA=(SERVICE_NAME=<SERVICE_NAME>)))
Connect Using PySpark With Databricks

You will need to get the secret from a keyvault. Refer to Databricks: Get Secret

  1. secret = <Get_Secret>
  2.  
  3. df = spark.read \
  4. .format("jdbc") \
  5. .option("url", "<URL>") \
  6. .option("dbtable", "<SCHEMA>.<TABLE>") \
  7. .option("user", "<ACCOUNT>") \
  8. .option("password", secret) \
  9. .option("driver", "oracle.jdbc.driver.OracleDriver") \
  10. .laod()
Connect Using SQL With Databricks

It should be noted that this isn’t a really good way and I don’t believe is completely secure.

Add cluster configuration

  1. spark.databricks.secureVariableSubstitute.enabled false
  2. spark.<SOME_VARIABLE_NAME> {{secrets/<SCOPE>/<SECRET>}}

Code

  1. %sql
  2.  
  3. CREATE SCHEMA IF NOT EXISTS <SCHEMA_NAME>;
  4.  
  5. DROP VIEW IF EXISTS <VIEW_NAME>;
  6.  
  7. CREATE TEMPORARY VIEW <SCHEMA_NAME>.<VIEW_NAME>
  8. USING org.apache.spark.sql.jdbc
  9. OPTIONS (
  10. url "<URL>",
  11. dbtable "<SCHEMA_NAME>.<TABLE_NAME>",
  12. user "<ACCOUNT>",
  13. password "${spark.<SOME_VARIABLE_NAME>}",
  14. driver "oracle.jdbc.driver.OracleDriver"
  15. )