Skip to content

Instantly share code, notes, and snippets.

@FranckPachot

FranckPachot/oracleconnectiontocloud.ipynb Secret

Last active January 25, 2023 14:00
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save FranckPachot/7fd9dec0d38a2326e0ab41151b88687f to your computer and use it in GitHub Desktop.
Save FranckPachot/7fd9dec0d38a2326e0ab41151b88687f to your computer and use it in GitHub Desktop.
Download ZIP
OracleConnectionToCloud.ipynb
Display the source blob
Display the rendered blob
Raw
oracleconnectiontocloud.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "OracleConnectionToCloud.ipynb",
"provenance": [],
"collapsed_sections": [],
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/FranckPachot/7fd9dec0d38a2326e0ab41151b88687f/oracleconnectiontocloud.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "uYeoaOOhCpoJ",
"colab_type": "text"
},
"source": [
"# Connection to Oracle Cloud from Google Colab Notebook\n",
"By Franck Pachot"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "VnCxm61CJGyx",
"colab_type": "text"
},
"source": [
"In a [previous post](https://medium.com/@FranckPachot/postgresql-and-jupyter-notebook-e7b68cb6427d) I explained how to connect to PostgreSQL from a Jupyter notebook.\n",
"\n",
"Here, I'll do the same but connecting to an Oracle Database in the Oracle Cloud (using the credential wallet) and from [Google Colab](https://colab.research.google.com) to share the notebook"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "AC9M1c8xnE5M",
"colab_type": "text"
},
"source": [
"**Update 18-JAN-2020** *Following [Chris Jones comment](https://medium.com/@christopher.jones.oracle/with-instant-client-you-shouldnt-set-oracle-home-75943c456c09), I added --script to the .rpm install (in order to get ldconfig to be runned) and unzipped the wallet into /client64/lib/network/admin so that I don't have to set ORACLE_HOME*\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "9LWsawH-CzrF",
"colab_type": "text"
},
"source": [
"## Download the Oracle Instant Client (basic and sqlplus)"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "h_ukKVNMJ0ZG",
"colab_type": "text"
},
"source": [
"The Oracle Client software has been made available recently on the Oracle YUM repository so that it can be downloaded without any login or manual click-through acceptance. The information about them is available [here](https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html)"
]
},
{
"cell_type": "code",
"metadata": {
"id": "x0-g0H68idkw",
"colab_type": "code",
"outputId": "18577385-8290-47a7-8e09-9bee114f9c04",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 445
}
},
"source": [
"! ls -l oracle-instantclient*-basiclite-*.rpm || wget https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm\n",
"! ls -l oracle-instantclient*-sqlplus-*.rpm || wget https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm\n"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"ls: cannot access 'oracle-instantclient*-basiclite-*.rpm': No such file or directory\n",
"--2020-01-18 16:30:44-- https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm\n",
"Resolving yum.oracle.com (yum.oracle.com)... 23.211.8.198\n",
"Connecting to yum.oracle.com (yum.oracle.com)|23.211.8.198|:443... connected.\n",
"HTTP request sent, awaiting response... 200 OK\n",
"Length: 28553496 (27M) [application/x-rpm]\n",
"Saving to: ‘oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm’\n",
"\n",
"oracle-instantclien 100%[===================>] 27.23M 841KB/s in 34s \n",
"\n",
"2020-01-18 16:31:21 (816 KB/s) - ‘oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm’ saved [28553496/28553496]\n",
"\n",
"ls: cannot access 'oracle-instantclient*-sqlplus-*.rpm': No such file or directory\n",
"--2020-01-18 16:31:23-- https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm\n",
"Resolving yum.oracle.com (yum.oracle.com)... 23.211.8.198\n",
"Connecting to yum.oracle.com (yum.oracle.com)|23.211.8.198|:443... connected.\n",
"HTTP request sent, awaiting response... 200 OK\n",
"Length: 702792 (686K) [application/x-rpm]\n",
"Saving to: ‘oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm’\n",
"\n",
"oracle-instantclien 100%[===================>] 686.32K --.-KB/s in 0.05s \n",
"\n",
"2020-01-18 16:31:23 (14.6 MB/s) - ‘oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm’ saved [702792/702792]\n",
"\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "pfzJgi0ItLtG",
"colab_type": "text"
},
"source": [
"## Install necessary packages\n",
"As those are RPMs I need **alien** to install from the .rpm package, and I will need **libaio** to run the Oracle client"
]
},
{
"cell_type": "code",
"metadata": {
"id": "oVUG4hXhjSMh",
"colab_type": "code",
"outputId": "ab0dcea1-c7f7-494a-ebaf-07dc2e088b55",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 1000
}
},
"source": [
"! sudo apt-get install alien libaio1"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Reading package lists... Done\n",
"Building dependency tree \n",
"Reading state information... Done\n",
"libaio1 is already the newest version (0.3.110-5ubuntu0.1).\n",
"libaio1 set to manually installed.\n",
"The following package was automatically installed and is no longer required:\n",
" libnvidia-common-430\n",
"Use 'sudo apt autoremove' to remove it.\n",
"The following additional packages will be installed:\n",
" autoconf automake autopoint autotools-dev cpio debhelper debugedit\n",
" dh-autoreconf dh-strip-nondeterminism file gettext gettext-base\n",
" intltool-debian libarchive-cpio-perl libarchive-zip-perl libdw1\n",
" libfile-stripnondeterminism-perl liblua5.2-0 libmagic-mgc libmagic1\n",
" libmail-sendmail-perl librpm8 librpmbuild8 librpmio8 librpmsign8 libsigsegv2\n",
" libsys-hostname-long-perl libtimedate-perl libtool m4 po-debconf rpm\n",
" rpm-common rpm2cpio\n",
"Suggested packages:\n",
" lintian autoconf-archive gnu-standards autoconf-doc libarchive1 dh-make dwz\n",
" rpm-i18n gettext-doc libasprintf-dev libgettextpo-dev libtool-doc gcj-jdk\n",
" m4-doc libmail-box-perl elfutils rpmlint rpm2html\n",
"The following NEW packages will be installed:\n",
" alien autoconf automake autopoint autotools-dev cpio debhelper debugedit\n",
" dh-autoreconf dh-strip-nondeterminism file gettext gettext-base\n",
" intltool-debian libarchive-cpio-perl libarchive-zip-perl libdw1\n",
" libfile-stripnondeterminism-perl liblua5.2-0 libmagic-mgc libmagic1\n",
" libmail-sendmail-perl librpm8 librpmbuild8 librpmio8 librpmsign8 libsigsegv2\n",
" libsys-hostname-long-perl libtimedate-perl libtool m4 po-debconf rpm\n",
" rpm-common rpm2cpio\n",
"0 upgraded, 35 newly installed, 0 to remove and 7 not upgraded.\n",
"Need to get 5,697 kB of archives.\n",
"After this operation, 25.0 MB of additional disk space will be used.\n",
"Get:1 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 cpio amd64 2.12+dfsg-6ubuntu0.18.04.1 [86.2 kB]\n",
"Get:2 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libmagic-mgc amd64 1:5.32-2ubuntu0.3 [184 kB]\n",
"Get:3 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libmagic1 amd64 1:5.32-2ubuntu0.3 [68.7 kB]\n",
"Get:4 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 file amd64 1:5.32-2ubuntu0.3 [22.1 kB]\n",
"Get:5 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 gettext-base amd64 0.19.8.1-6ubuntu0.3 [113 kB]\n",
"Get:6 http://archive.ubuntu.com/ubuntu bionic/main amd64 autotools-dev all 20180224.1 [39.6 kB]\n",
"Get:7 http://archive.ubuntu.com/ubuntu bionic/main amd64 libsigsegv2 amd64 2.12-1 [14.7 kB]\n",
"Get:8 http://archive.ubuntu.com/ubuntu bionic/main amd64 m4 amd64 1.4.18-1 [197 kB]\n",
"Get:9 http://archive.ubuntu.com/ubuntu bionic/main amd64 autoconf all 2.69-11 [322 kB]\n",
"Get:10 http://archive.ubuntu.com/ubuntu bionic/main amd64 automake all 1:1.15.1-3ubuntu2 [509 kB]\n",
"Get:11 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 autopoint all 0.19.8.1-6ubuntu0.3 [426 kB]\n",
"Get:12 http://archive.ubuntu.com/ubuntu bionic/main amd64 libtool all 2.4.6-2 [194 kB]\n",
"Get:13 http://archive.ubuntu.com/ubuntu bionic/main amd64 dh-autoreconf all 17 [15.8 kB]\n",
"Get:14 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libarchive-zip-perl all 1.60-1ubuntu0.1 [84.6 kB]\n",
"Get:15 http://archive.ubuntu.com/ubuntu bionic/main amd64 libfile-stripnondeterminism-perl all 0.040-1.1~build1 [13.8 kB]\n",
"Get:16 http://archive.ubuntu.com/ubuntu bionic/main amd64 libtimedate-perl all 2.3000-2 [37.5 kB]\n",
"Get:17 http://archive.ubuntu.com/ubuntu bionic/main amd64 dh-strip-nondeterminism all 0.040-1.1~build1 [5,208 B]\n",
"Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 gettext amd64 0.19.8.1-6ubuntu0.3 [1,293 kB]\n",
"Get:19 http://archive.ubuntu.com/ubuntu bionic/main amd64 intltool-debian all 0.35.0+20060710.4 [24.9 kB]\n",
"Get:20 http://archive.ubuntu.com/ubuntu bionic/main amd64 po-debconf all 1.0.20 [232 kB]\n",
"Get:21 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 debhelper all 11.1.6ubuntu2 [902 kB]\n",
"Get:22 http://archive.ubuntu.com/ubuntu bionic/main amd64 liblua5.2-0 amd64 5.2.4-1.1build1 [108 kB]\n",
"Get:23 http://archive.ubuntu.com/ubuntu bionic/universe amd64 librpmio8 amd64 4.14.1+dfsg1-2 [74.6 kB]\n",
"Get:24 http://archive.ubuntu.com/ubuntu bionic/universe amd64 librpm8 amd64 4.14.1+dfsg1-2 [173 kB]\n",
"Get:25 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libdw1 amd64 0.170-0.4ubuntu0.1 [203 kB]\n",
"Get:26 http://archive.ubuntu.com/ubuntu bionic/universe amd64 librpmbuild8 amd64 4.14.1+dfsg1-2 [70.5 kB]\n",
"Get:27 http://archive.ubuntu.com/ubuntu bionic/universe amd64 librpmsign8 amd64 4.14.1+dfsg1-2 [8,184 B]\n",
"Get:28 http://archive.ubuntu.com/ubuntu bionic/universe amd64 rpm-common amd64 4.14.1+dfsg1-2 [28.7 kB]\n",
"Get:29 http://archive.ubuntu.com/ubuntu bionic/universe amd64 rpm2cpio amd64 4.14.1+dfsg1-2 [7,988 B]\n",
"Get:30 http://archive.ubuntu.com/ubuntu bionic/universe amd64 debugedit amd64 4.14.1+dfsg1-2 [19.1 kB]\n",
"Get:31 http://archive.ubuntu.com/ubuntu bionic/universe amd64 rpm amd64 4.14.1+dfsg1-2 [119 kB]\n",
"Get:32 http://archive.ubuntu.com/ubuntu bionic/universe amd64 alien all 8.95 [54.5 kB]\n",
"Get:33 http://archive.ubuntu.com/ubuntu bionic/main amd64 libarchive-cpio-perl all 0.10-1 [9,644 B]\n",
"Get:34 http://archive.ubuntu.com/ubuntu bionic/main amd64 libsys-hostname-long-perl all 1.5-1 [11.7 kB]\n",
"Get:35 http://archive.ubuntu.com/ubuntu bionic/main amd64 libmail-sendmail-perl all 0.80-1 [22.6 kB]\n",
"Fetched 5,697 kB in 0s (18.8 MB/s)\n",
"debconf: unable to initialize frontend: Dialog\n",
"debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76, <> line 35.)\n",
"debconf: falling back to frontend: Readline\n",
"debconf: unable to initialize frontend: Readline\n",
"debconf: (This frontend requires a controlling tty.)\n",
"debconf: falling back to frontend: Teletype\n",
"dpkg-preconfigure: unable to re-open stdin: \n",
"Selecting previously unselected package cpio.\n",
"(Reading database ... 135004 files and directories currently installed.)\n",
"Preparing to unpack .../00-cpio_2.12+dfsg-6ubuntu0.18.04.1_amd64.deb ...\n",
"Unpacking cpio (2.12+dfsg-6ubuntu0.18.04.1) ...\n",
"Selecting previously unselected package libmagic-mgc.\n",
"Preparing to unpack .../01-libmagic-mgc_1%3a5.32-2ubuntu0.3_amd64.deb ...\n",
"Unpacking libmagic-mgc (1:5.32-2ubuntu0.3) ...\n",
"Selecting previously unselected package libmagic1:amd64.\n",
"Preparing to unpack .../02-libmagic1_1%3a5.32-2ubuntu0.3_amd64.deb ...\n",
"Unpacking libmagic1:amd64 (1:5.32-2ubuntu0.3) ...\n",
"Selecting previously unselected package file.\n",
"Preparing to unpack .../03-file_1%3a5.32-2ubuntu0.3_amd64.deb ...\n",
"Unpacking file (1:5.32-2ubuntu0.3) ...\n",
"Selecting previously unselected package gettext-base.\n",
"Preparing to unpack .../04-gettext-base_0.19.8.1-6ubuntu0.3_amd64.deb ...\n",
"Unpacking gettext-base (0.19.8.1-6ubuntu0.3) ...\n",
"Selecting previously unselected package autotools-dev.\n",
"Preparing to unpack .../05-autotools-dev_20180224.1_all.deb ...\n",
"Unpacking autotools-dev (20180224.1) ...\n",
"Selecting previously unselected package libsigsegv2:amd64.\n",
"Preparing to unpack .../06-libsigsegv2_2.12-1_amd64.deb ...\n",
"Unpacking libsigsegv2:amd64 (2.12-1) ...\n",
"Selecting previously unselected package m4.\n",
"Preparing to unpack .../07-m4_1.4.18-1_amd64.deb ...\n",
"Unpacking m4 (1.4.18-1) ...\n",
"Selecting previously unselected package autoconf.\n",
"Preparing to unpack .../08-autoconf_2.69-11_all.deb ...\n",
"Unpacking autoconf (2.69-11) ...\n",
"Selecting previously unselected package automake.\n",
"Preparing to unpack .../09-automake_1%3a1.15.1-3ubuntu2_all.deb ...\n",
"Unpacking automake (1:1.15.1-3ubuntu2) ...\n",
"Selecting previously unselected package autopoint.\n",
"Preparing to unpack .../10-autopoint_0.19.8.1-6ubuntu0.3_all.deb ...\n",
"Unpacking autopoint (0.19.8.1-6ubuntu0.3) ...\n",
"Selecting previously unselected package libtool.\n",
"Preparing to unpack .../11-libtool_2.4.6-2_all.deb ...\n",
"Unpacking libtool (2.4.6-2) ...\n",
"Selecting previously unselected package dh-autoreconf.\n",
"Preparing to unpack .../12-dh-autoreconf_17_all.deb ...\n",
"Unpacking dh-autoreconf (17) ...\n",
"Selecting previously unselected package libarchive-zip-perl.\n",
"Preparing to unpack .../13-libarchive-zip-perl_1.60-1ubuntu0.1_all.deb ...\n",
"Unpacking libarchive-zip-perl (1.60-1ubuntu0.1) ...\n",
"Selecting previously unselected package libfile-stripnondeterminism-perl.\n",
"Preparing to unpack .../14-libfile-stripnondeterminism-perl_0.040-1.1~build1_all.deb ...\n",
"Unpacking libfile-stripnondeterminism-perl (0.040-1.1~build1) ...\n",
"Selecting previously unselected package libtimedate-perl.\n",
"Preparing to unpack .../15-libtimedate-perl_2.3000-2_all.deb ...\n",
"Unpacking libtimedate-perl (2.3000-2) ...\n",
"Selecting previously unselected package dh-strip-nondeterminism.\n",
"Preparing to unpack .../16-dh-strip-nondeterminism_0.040-1.1~build1_all.deb ...\n",
"Unpacking dh-strip-nondeterminism (0.040-1.1~build1) ...\n",
"Selecting previously unselected package gettext.\n",
"Preparing to unpack .../17-gettext_0.19.8.1-6ubuntu0.3_amd64.deb ...\n",
"Unpacking gettext (0.19.8.1-6ubuntu0.3) ...\n",
"Selecting previously unselected package intltool-debian.\n",
"Preparing to unpack .../18-intltool-debian_0.35.0+20060710.4_all.deb ...\n",
"Unpacking intltool-debian (0.35.0+20060710.4) ...\n",
"Selecting previously unselected package po-debconf.\n",
"Preparing to unpack .../19-po-debconf_1.0.20_all.deb ...\n",
"Unpacking po-debconf (1.0.20) ...\n",
"Selecting previously unselected package debhelper.\n",
"Preparing to unpack .../20-debhelper_11.1.6ubuntu2_all.deb ...\n",
"Unpacking debhelper (11.1.6ubuntu2) ...\n",
"Selecting previously unselected package liblua5.2-0:amd64.\n",
"Preparing to unpack .../21-liblua5.2-0_5.2.4-1.1build1_amd64.deb ...\n",
"Unpacking liblua5.2-0:amd64 (5.2.4-1.1build1) ...\n",
"Selecting previously unselected package librpmio8.\n",
"Preparing to unpack .../22-librpmio8_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking librpmio8 (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package librpm8.\n",
"Preparing to unpack .../23-librpm8_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking librpm8 (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package libdw1:amd64.\n",
"Preparing to unpack .../24-libdw1_0.170-0.4ubuntu0.1_amd64.deb ...\n",
"Unpacking libdw1:amd64 (0.170-0.4ubuntu0.1) ...\n",
"Selecting previously unselected package librpmbuild8.\n",
"Preparing to unpack .../25-librpmbuild8_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking librpmbuild8 (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package librpmsign8.\n",
"Preparing to unpack .../26-librpmsign8_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking librpmsign8 (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package rpm-common.\n",
"Preparing to unpack .../27-rpm-common_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking rpm-common (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package rpm2cpio.\n",
"Preparing to unpack .../28-rpm2cpio_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking rpm2cpio (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package debugedit.\n",
"Preparing to unpack .../29-debugedit_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking debugedit (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package rpm.\n",
"Preparing to unpack .../30-rpm_4.14.1+dfsg1-2_amd64.deb ...\n",
"Unpacking rpm (4.14.1+dfsg1-2) ...\n",
"Selecting previously unselected package alien.\n",
"Preparing to unpack .../31-alien_8.95_all.deb ...\n",
"Unpacking alien (8.95) ...\n",
"Selecting previously unselected package libarchive-cpio-perl.\n",
"Preparing to unpack .../32-libarchive-cpio-perl_0.10-1_all.deb ...\n",
"Unpacking libarchive-cpio-perl (0.10-1) ...\n",
"Selecting previously unselected package libsys-hostname-long-perl.\n",
"Preparing to unpack .../33-libsys-hostname-long-perl_1.5-1_all.deb ...\n",
"Unpacking libsys-hostname-long-perl (1.5-1) ...\n",
"Selecting previously unselected package libmail-sendmail-perl.\n",
"Preparing to unpack .../34-libmail-sendmail-perl_0.80-1_all.deb ...\n",
"Unpacking libmail-sendmail-perl (0.80-1) ...\n",
"Setting up cpio (2.12+dfsg-6ubuntu0.18.04.1) ...\n",
"update-alternatives: using /bin/mt-gnu to provide /bin/mt (mt) in auto mode\n",
"Setting up libarchive-zip-perl (1.60-1ubuntu0.1) ...\n",
"Setting up libdw1:amd64 (0.170-0.4ubuntu0.1) ...\n",
"Setting up libtimedate-perl (2.3000-2) ...\n",
"Setting up libsigsegv2:amd64 (2.12-1) ...\n",
"Setting up libarchive-cpio-perl (0.10-1) ...\n",
"Setting up gettext-base (0.19.8.1-6ubuntu0.3) ...\n",
"Setting up m4 (1.4.18-1) ...\n",
"Setting up libmagic-mgc (1:5.32-2ubuntu0.3) ...\n",
"Setting up libmagic1:amd64 (1:5.32-2ubuntu0.3) ...\n",
"Setting up libsys-hostname-long-perl (1.5-1) ...\n",
"Setting up libmail-sendmail-perl (0.80-1) ...\n",
"Setting up autotools-dev (20180224.1) ...\n",
"Setting up liblua5.2-0:amd64 (5.2.4-1.1build1) ...\n",
"Setting up autopoint (0.19.8.1-6ubuntu0.3) ...\n",
"Setting up libfile-stripnondeterminism-perl (0.040-1.1~build1) ...\n",
"Setting up librpmio8 (4.14.1+dfsg1-2) ...\n",
"Setting up gettext (0.19.8.1-6ubuntu0.3) ...\n",
"Setting up debugedit (4.14.1+dfsg1-2) ...\n",
"Setting up autoconf (2.69-11) ...\n",
"Setting up file (1:5.32-2ubuntu0.3) ...\n",
"Setting up intltool-debian (0.35.0+20060710.4) ...\n",
"Setting up automake (1:1.15.1-3ubuntu2) ...\n",
"update-alternatives: using /usr/bin/automake-1.15 to provide /usr/bin/automake (automake) in auto mode\n",
"Setting up libtool (2.4.6-2) ...\n",
"Setting up librpm8 (4.14.1+dfsg1-2) ...\n",
"Setting up po-debconf (1.0.20) ...\n",
"Setting up rpm-common (4.14.1+dfsg1-2) ...\n",
"Setting up librpmsign8 (4.14.1+dfsg1-2) ...\n",
"Setting up librpmbuild8 (4.14.1+dfsg1-2) ...\n",
"Setting up rpm2cpio (4.14.1+dfsg1-2) ...\n",
"Setting up rpm (4.14.1+dfsg1-2) ...\n",
"Setting up dh-autoreconf (17) ...\n",
"Setting up dh-strip-nondeterminism (0.040-1.1~build1) ...\n",
"Setting up debhelper (11.1.6ubuntu2) ...\n",
"Setting up alien (8.95) ...\n",
"Processing triggers for libc-bin (2.27-3ubuntu1) ...\n",
"Processing triggers for man-db (2.8.3-2ubuntu0.1) ...\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "5S4KiN9sDcqc",
"colab_type": "text"
},
"source": [
"Now installing the Oracle Instand Client:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "vwOICFO4oF_o",
"colab_type": "code",
"outputId": "3855a2d6-89c8-4d8c-8ead-63fc79e81beb",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 459
}
},
"source": [
"! sudo alien -i oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"Warning: Skipping conversion of scripts in package oracle-instantclient19.3-basiclite: postinst postrm\n",
"Warning: Use the --scripts parameter to include the scripts.\n",
"warning: oracle-instantclient19.3-basiclite-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"\tdpkg --no-force-overwrite -i oracle-instantclient19.3-basiclite_19.3.0.0.0-2_amd64.deb\n",
"Selecting previously unselected package oracle-instantclient19.3-basiclite.\n",
"(Reading database ... 136721 files and directories currently installed.)\n",
"Preparing to unpack oracle-instantclient19.3-basiclite_19.3.0.0.0-2_amd64.deb ...\n",
"Unpacking oracle-instantclient19.3-basiclite (19.3.0.0.0-2) ...\n",
"Setting up oracle-instantclient19.3-basiclite (19.3.0.0.0-2) ...\n",
"Processing triggers for libc-bin (2.27-3ubuntu1) ...\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "code",
"metadata": {
"id": "qW8sbuWOjg7B",
"colab_type": "code",
"outputId": "46c34778-1396-48ef-b14f-051c9c0d6eaf",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 408
}
},
"source": [
"! sudo alien -i --scripts oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"warning: oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY\n",
"\tdpkg --no-force-overwrite -i oracle-instantclient19.3-sqlplus_19.3.0.0.0-2_amd64.deb\n",
"(Reading database ... 136769 files and directories currently installed.)\n",
"Preparing to unpack oracle-instantclient19.3-sqlplus_19.3.0.0.0-2_amd64.deb ...\n",
"Unpacking oracle-instantclient19.3-sqlplus (19.3.0.0.0-2) over (19.3.0.0.0-2) ...\n",
"Setting up oracle-instantclient19.3-sqlplus (19.3.0.0.0-2) ...\n",
"Processing triggers for libc-bin (2.27-3ubuntu1) ...\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "NzfD7yvlEMZ_",
"colab_type": "text"
},
"source": [
"## Test Oracle environment"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "HbnPzLpcD6sh",
"colab_type": "text"
},
"source": [
"Here is where the Oracle Client is installed:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "lVEavCzxj3uz",
"colab_type": "code",
"outputId": "1f287ec4-3003-438b-ba20-d0ed2bc247b1",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 51
}
},
"source": [
"! type sqlplus\n",
"! ls -l /usr/bin/sqlplus"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"sqlplus is /usr/bin/sqlplus\n",
"lrwxrwxrwx 1 root root 41 Jan 18 16:39 /usr/bin/sqlplus -> /usr/lib/oracle/19.3/client64/bin/sqlplus\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "4rkxOqbntlkm",
"colab_type": "text"
},
"source": [
"I'm just testing sqlplus can be started"
]
},
{
"cell_type": "code",
"metadata": {
"id": "MlAFm4fMk4V7",
"colab_type": "code",
"outputId": "be472daa-a144-4459-cdd6-47ab45da0645",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 136
}
},
"source": [
"!sqlplus /nolog <<<'exit'"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"\n",
"SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 18 16:40:14 2020\n",
"Version 19.3.0.0.0\n",
"\n",
"Copyright (c) 1982, 2019, Oracle. All rights reserved.\n",
"\n",
"SQL> "
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "DdcwSRJzEZlf",
"colab_type": "text"
},
"source": [
"## Connection to Oracle with Cloud Credentials wallet\n",
"\n",
"In order to connect to the Oracle Cloud ATP/ADW services, opened to the internet (which we can also restrict with ACL) I need the Client Credentials wallet\n",
"\n",
"![Service Console - Administration - Download Client Credentials (wallet)](https://cdn-images-1.medium.com/max/1200/1*UIWXKGqlsaC4n-Zgc095EQ.png)"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "LeDSE1uXFNXr",
"colab_type": "text"
},
"source": [
"Put any password here, you don't need it to connect.\n",
"\n",
"The wallet must be uploaded:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "UkgnC4XwfDw9",
"colab_type": "code",
"outputId": "365ba89d-e53a-4d3c-98ed-021a377ef4bd",
"colab": {
"resources": {
"http://localhost:8080/nbextensions/google.colab/files.js": {
"data": "Ly8gQ29weXJpZ2h0IDIwMTcgR29vZ2xlIExMQwovLwovLyBMaWNlbnNlZCB1bmRlciB0aGUgQXBhY2hlIExpY2Vuc2UsIFZlcnNpb24gMi4wICh0aGUgIkxpY2Vuc2UiKTsKLy8geW91IG1heSBub3QgdXNlIHRoaXMgZmlsZSBleGNlcHQgaW4gY29tcGxpYW5jZSB3aXRoIHRoZSBMaWNlbnNlLgovLyBZb3UgbWF5IG9idGFpbiBhIGNvcHkgb2YgdGhlIExpY2Vuc2UgYXQKLy8KLy8gICAgICBodHRwOi8vd3d3LmFwYWNoZS5vcmcvbGljZW5zZXMvTElDRU5TRS0yLjAKLy8KLy8gVW5sZXNzIHJlcXVpcmVkIGJ5IGFwcGxpY2FibGUgbGF3IG9yIGFncmVlZCB0byBpbiB3cml0aW5nLCBzb2Z0d2FyZQovLyBkaXN0cmlidXRlZCB1bmRlciB0aGUgTGljZW5zZSBpcyBkaXN0cmlidXRlZCBvbiBhbiAiQVMgSVMiIEJBU0lTLAovLyBXSVRIT1VUIFdBUlJBTlRJRVMgT1IgQ09ORElUSU9OUyBPRiBBTlkgS0lORCwgZWl0aGVyIGV4cHJlc3Mgb3IgaW1wbGllZC4KLy8gU2VlIHRoZSBMaWNlbnNlIGZvciB0aGUgc3BlY2lmaWMgbGFuZ3VhZ2UgZ292ZXJuaW5nIHBlcm1pc3Npb25zIGFuZAovLyBsaW1pdGF0aW9ucyB1bmRlciB0aGUgTGljZW5zZS4KCi8qKgogKiBAZmlsZW92ZXJ2aWV3IEhlbHBlcnMgZm9yIGdvb2dsZS5jb2xhYiBQeXRob24gbW9kdWxlLgogKi8KKGZ1bmN0aW9uKHNjb3BlKSB7CmZ1bmN0aW9uIHNwYW4odGV4dCwgc3R5bGVBdHRyaWJ1dGVzID0ge30pIHsKICBjb25zdCBlbGVtZW50ID0gZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgnc3BhbicpOwogIGVsZW1lbnQudGV4dENvbnRlbnQgPSB0ZXh0OwogIGZvciAoY29uc3Qga2V5IG9mIE9iamVjdC5rZXlzKHN0eWxlQXR0cmlidXRlcykpIHsKICAgIGVsZW1lbnQuc3R5bGVba2V5XSA9IHN0eWxlQXR0cmlidXRlc1trZXldOwogIH0KICByZXR1cm4gZWxlbWVudDsKfQoKLy8gTWF4IG51bWJlciBvZiBieXRlcyB3aGljaCB3aWxsIGJlIHVwbG9hZGVkIGF0IGEgdGltZS4KY29uc3QgTUFYX1BBWUxPQURfU0laRSA9IDEwMCAqIDEwMjQ7Ci8vIE1heCBhbW91bnQgb2YgdGltZSB0byBibG9jayB3YWl0aW5nIGZvciB0aGUgdXNlci4KY29uc3QgRklMRV9DSEFOR0VfVElNRU9VVF9NUyA9IDMwICogMTAwMDsKCmZ1bmN0aW9uIF91cGxvYWRGaWxlcyhpbnB1dElkLCBvdXRwdXRJZCkgewogIGNvbnN0IHN0ZXBzID0gdXBsb2FkRmlsZXNTdGVwKGlucHV0SWQsIG91dHB1dElkKTsKICBjb25zdCBvdXRwdXRFbGVtZW50ID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQob3V0cHV0SWQpOwogIC8vIENhY2hlIHN0ZXBzIG9uIHRoZSBvdXRwdXRFbGVtZW50IHRvIG1ha2UgaXQgYXZhaWxhYmxlIGZvciB0aGUgbmV4dCBjYWxsCiAgLy8gdG8gdXBsb2FkRmlsZXNDb250aW51ZSBmcm9tIFB5dGhvbi4KICBvdXRwdXRFbGVtZW50LnN0ZXBzID0gc3RlcHM7CgogIHJldHVybiBfdXBsb2FkRmlsZXNDb250aW51ZShvdXRwdXRJZCk7Cn0KCi8vIFRoaXMgaXMgcm91Z2hseSBhbiBhc3luYyBnZW5lcmF0b3IgKG5vdCBzdXBwb3J0ZWQgaW4gdGhlIGJyb3dzZXIgeWV0KSwKLy8gd2hlcmUgdGhlcmUgYXJlIG11bHRpcGxlIGFzeW5jaHJvbm91cyBzdGVwcyBhbmQgdGhlIFB5dGhvbiBzaWRlIGlzIGdvaW5nCi8vIHRvIHBvbGwgZm9yIGNvbXBsZXRpb24gb2YgZWFjaCBzdGVwLgovLyBUaGlzIHVzZXMgYSBQcm9taXNlIHRvIGJsb2NrIHRoZSBweXRob24gc2lkZSBvbiBjb21wbGV0aW9uIG9mIGVhY2ggc3RlcCwKLy8gdGhlbiBwYXNzZXMgdGhlIHJlc3VsdCBvZiB0aGUgcHJldmlvdXMgc3RlcCBhcyB0aGUgaW5wdXQgdG8gdGhlIG5leHQgc3RlcC4KZnVuY3Rpb24gX3VwbG9hZEZpbGVzQ29udGludWUob3V0cHV0SWQpIHsKICBjb25zdCBvdXRwdXRFbGVtZW50ID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQob3V0cHV0SWQpOwogIGNvbnN0IHN0ZXBzID0gb3V0cHV0RWxlbWVudC5zdGVwczsKCiAgY29uc3QgbmV4dCA9IHN0ZXBzLm5leHQob3V0cHV0RWxlbWVudC5sYXN0UHJvbWlzZVZhbHVlKTsKICByZXR1cm4gUHJvbWlzZS5yZXNvbHZlKG5leHQudmFsdWUucHJvbWlzZSkudGhlbigodmFsdWUpID0+IHsKICAgIC8vIENhY2hlIHRoZSBsYXN0IHByb21pc2UgdmFsdWUgdG8gbWFrZSBpdCBhdmFpbGFibGUgdG8gdGhlIG5leHQKICAgIC8vIHN0ZXAgb2YgdGhlIGdlbmVyYXRvci4KICAgIG91dHB1dEVsZW1lbnQubGFzdFByb21pc2VWYWx1ZSA9IHZhbHVlOwogICAgcmV0dXJuIG5leHQudmFsdWUucmVzcG9uc2U7CiAgfSk7Cn0KCi8qKgogKiBHZW5lcmF0b3IgZnVuY3Rpb24gd2hpY2ggaXMgY2FsbGVkIGJldHdlZW4gZWFjaCBhc3luYyBzdGVwIG9mIHRoZSB1cGxvYWQKICogcHJvY2Vzcy4KICogQHBhcmFtIHtzdHJpbmd9IGlucHV0SWQgRWxlbWVudCBJRCBvZiB0aGUgaW5wdXQgZmlsZSBwaWNrZXIgZWxlbWVudC4KICogQHBhcmFtIHtzdHJpbmd9IG91dHB1dElkIEVsZW1lbnQgSUQgb2YgdGhlIG91dHB1dCBkaXNwbGF5LgogKiBAcmV0dXJuIHshSXRlcmFibGU8IU9iamVjdD59IEl0ZXJhYmxlIG9mIG5leHQgc3RlcHMuCiAqLwpmdW5jdGlvbiogdXBsb2FkRmlsZXNTdGVwKGlucHV0SWQsIG91dHB1dElkKSB7CiAgY29uc3QgaW5wdXRFbGVtZW50ID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQoaW5wdXRJZCk7CiAgaW5wdXRFbGVtZW50LmRpc2FibGVkID0gZmFsc2U7CgogIGNvbnN0IG91dHB1dEVsZW1lbnQgPSBkb2N1bWVudC5nZXRFbGVtZW50QnlJZChvdXRwdXRJZCk7CiAgb3V0cHV0RWxlbWVudC5pbm5lckhUTUwgPSAnJzsKCiAgY29uc3QgcGlja2VkUHJvbWlzZSA9IG5ldyBQcm9taXNlKChyZXNvbHZlKSA9PiB7CiAgICBpbnB1dEVsZW1lbnQuYWRkRXZlbnRMaXN0ZW5lcignY2hhbmdlJywgKGUpID0+IHsKICAgICAgcmVzb2x2ZShlLnRhcmdldC5maWxlcyk7CiAgICB9KTsKICB9KTsKCiAgY29uc3QgY2FuY2VsID0gZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgnYnV0dG9uJyk7CiAgaW5wdXRFbGVtZW50LnBhcmVudEVsZW1lbnQuYXBwZW5kQ2hpbGQoY2FuY2VsKTsKICBjYW5jZWwudGV4dENvbnRlbnQgPSAnQ2FuY2VsIHVwbG9hZCc7CiAgY29uc3QgY2FuY2VsUHJvbWlzZSA9IG5ldyBQcm9taXNlKChyZXNvbHZlKSA9PiB7CiAgICBjYW5jZWwub25jbGljayA9ICgpID0+IHsKICAgICAgcmVzb2x2ZShudWxsKTsKICAgIH07CiAgfSk7CgogIC8vIENhbmNlbCB1cGxvYWQgaWYgdXNlciBoYXNuJ3QgcGlja2VkIGFueXRoaW5nIGluIHRpbWVvdXQuCiAgY29uc3QgdGltZW91dFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgc2V0VGltZW91dCgoKSA9PiB7CiAgICAgIHJlc29sdmUobnVsbCk7CiAgICB9LCBGSUxFX0NIQU5HRV9USU1FT1VUX01TKTsKICB9KTsKCiAgLy8gV2FpdCBmb3IgdGhlIHVzZXIgdG8gcGljayB0aGUgZmlsZXMuCiAgY29uc3QgZmlsZXMgPSB5aWVsZCB7CiAgICBwcm9taXNlOiBQcm9taXNlLnJhY2UoW3BpY2tlZFByb21pc2UsIHRpbWVvdXRQcm9taXNlLCBjYW5jZWxQcm9taXNlXSksCiAgICByZXNwb25zZTogewogICAgICBhY3Rpb246ICdzdGFydGluZycsCiAgICB9CiAgfTsKCiAgaWYgKCFmaWxlcykgewogICAgcmV0dXJuIHsKICAgICAgcmVzcG9uc2U6IHsKICAgICAgICBhY3Rpb246ICdjb21wbGV0ZScsCiAgICAgIH0KICAgIH07CiAgfQoKICBjYW5jZWwucmVtb3ZlKCk7CgogIC8vIERpc2FibGUgdGhlIGlucHV0IGVsZW1lbnQgc2luY2UgZnVydGhlciBwaWNrcyBhcmUgbm90IGFsbG93ZWQuCiAgaW5wdXRFbGVtZW50LmRpc2FibGVkID0gdHJ1ZTsKCiAgZm9yIChjb25zdCBmaWxlIG9mIGZpbGVzKSB7CiAgICBjb25zdCBsaSA9IGRvY3VtZW50LmNyZWF0ZUVsZW1lbnQoJ2xpJyk7CiAgICBsaS5hcHBlbmQoc3BhbihmaWxlLm5hbWUsIHtmb250V2VpZ2h0OiAnYm9sZCd9KSk7CiAgICBsaS5hcHBlbmQoc3BhbigKICAgICAgICBgKCR7ZmlsZS50eXBlIHx8ICduL2EnfSkgLSAke2ZpbGUuc2l6ZX0gYnl0ZXMsIGAgKwogICAgICAgIGBsYXN0IG1vZGlmaWVkOiAkewogICAgICAgICAgICBmaWxlLmxhc3RNb2RpZmllZERhdGUgPyBmaWxlLmxhc3RNb2RpZmllZERhdGUudG9Mb2NhbGVEYXRlU3RyaW5nKCkgOgogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAnbi9hJ30gLSBgKSk7CiAgICBjb25zdCBwZXJjZW50ID0gc3BhbignMCUgZG9uZScpOwogICAgbGkuYXBwZW5kQ2hpbGQocGVyY2VudCk7CgogICAgb3V0cHV0RWxlbWVudC5hcHBlbmRDaGlsZChsaSk7CgogICAgY29uc3QgZmlsZURhdGFQcm9taXNlID0gbmV3IFByb21pc2UoKHJlc29sdmUpID0+IHsKICAgICAgY29uc3QgcmVhZGVyID0gbmV3IEZpbGVSZWFkZXIoKTsKICAgICAgcmVhZGVyLm9ubG9hZCA9IChlKSA9PiB7CiAgICAgICAgcmVzb2x2ZShlLnRhcmdldC5yZXN1bHQpOwogICAgICB9OwogICAgICByZWFkZXIucmVhZEFzQXJyYXlCdWZmZXIoZmlsZSk7CiAgICB9KTsKICAgIC8vIFdhaXQgZm9yIHRoZSBkYXRhIHRvIGJlIHJlYWR5LgogICAgbGV0IGZpbGVEYXRhID0geWllbGQgewogICAgICBwcm9taXNlOiBmaWxlRGF0YVByb21pc2UsCiAgICAgIHJlc3BvbnNlOiB7CiAgICAgICAgYWN0aW9uOiAnY29udGludWUnLAogICAgICB9CiAgICB9OwoKICAgIC8vIFVzZSBhIGNodW5rZWQgc2VuZGluZyB0byBhdm9pZCBtZXNzYWdlIHNpemUgbGltaXRzLiBTZWUgYi82MjExNTY2MC4KICAgIGxldCBwb3NpdGlvbiA9IDA7CiAgICB3aGlsZSAocG9zaXRpb24gPCBmaWxlRGF0YS5ieXRlTGVuZ3RoKSB7CiAgICAgIGNvbnN0IGxlbmd0aCA9IE1hdGgubWluKGZpbGVEYXRhLmJ5dGVMZW5ndGggLSBwb3NpdGlvbiwgTUFYX1BBWUxPQURfU0laRSk7CiAgICAgIGNvbnN0IGNodW5rID0gbmV3IFVpbnQ4QXJyYXkoZmlsZURhdGEsIHBvc2l0aW9uLCBsZW5ndGgpOwogICAgICBwb3NpdGlvbiArPSBsZW5ndGg7CgogICAgICBjb25zdCBiYXNlNjQgPSBidG9hKFN0cmluZy5mcm9tQ2hhckNvZGUuYXBwbHkobnVsbCwgY2h1bmspKTsKICAgICAgeWllbGQgewogICAgICAgIHJlc3BvbnNlOiB7CiAgICAgICAgICBhY3Rpb246ICdhcHBlbmQnLAogICAgICAgICAgZmlsZTogZmlsZS5uYW1lLAogICAgICAgICAgZGF0YTogYmFzZTY0LAogICAgICAgIH0sCiAgICAgIH07CiAgICAgIHBlcmNlbnQudGV4dENvbnRlbnQgPQogICAgICAgICAgYCR7TWF0aC5yb3VuZCgocG9zaXRpb24gLyBmaWxlRGF0YS5ieXRlTGVuZ3RoKSAqIDEwMCl9JSBkb25lYDsKICAgIH0KICB9CgogIC8vIEFsbCBkb25lLgogIHlpZWxkIHsKICAgIHJlc3BvbnNlOiB7CiAgICAgIGFjdGlvbjogJ2NvbXBsZXRlJywKICAgIH0KICB9Owp9CgpzY29wZS5nb29nbGUgPSBzY29wZS5nb29nbGUgfHwge307CnNjb3BlLmdvb2dsZS5jb2xhYiA9IHNjb3BlLmdvb2dsZS5jb2xhYiB8fCB7fTsKc2NvcGUuZ29vZ2xlLmNvbGFiLl9maWxlcyA9IHsKICBfdXBsb2FkRmlsZXMsCiAgX3VwbG9hZEZpbGVzQ29udGludWUsCn07Cn0pKHNlbGYpOwo=",
"ok": true,
"headers": [
[
"content-type",
"application/javascript"
]
],
"status": 200,
"status_text": ""
}
},
"base_uri": "https://localhost:8080/",
"height": 74
}
},
"source": [
"from google.colab import files\n",
"uploaded = files.upload()"
],
"execution_count": 0,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/html": [
"\n",
" <input type=\"file\" id=\"files-46d7baee-cb6e-495c-bc02-bb76cb85dbcc\" name=\"files[]\" multiple disabled />\n",
" <output id=\"result-46d7baee-cb6e-495c-bc02-bb76cb85dbcc\">\n",
" Upload widget is only available when the cell has been executed in the\n",
" current browser session. Please rerun this cell to enable.\n",
" </output>\n",
" <script src=\"/nbextensions/google.colab/files.js\"></script> "
],
"text/plain": [
"<IPython.core.display.HTML object>"
]
},
"metadata": {
"tags": []
}
},
{
"output_type": "stream",
"text": [
"Saving Wallet_DEMO.zip to Wallet_DEMO.zip\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "ZAy-KNzhFYOl",
"colab_type": "text"
},
"source": [
"I unzip it in the default directory where it is searched for ($ORACLE_HOME/rdbms/admin)\n",
"\n",
"*Note: I tried to use TNS_ADMIN to mention another location but never get consistent result with iPython SQL.*"
]
},
{
"cell_type": "code",
"metadata": {
"id": "7Yn8KHz6gVGv",
"colab_type": "code",
"outputId": "8cfa0f47-4934-4ec0-8e81-eed87f9d82b3",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 153
}
},
"source": [
"!unzip -o -d /usr/lib/oracle/19.3/client64/lib/network/admin Wallet_*.zip"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Archive: Wallet_DEMO.zip\n",
" inflating: /usr/lib/oracle/19.3/client64/lib/network/admin/cwallet.sso \n",
" inflating: /usr/lib/oracle/19.3/client64/lib/network/admin/tnsnames.ora \n",
" inflating: /usr/lib/oracle/19.3/client64/lib/network/admin/truststore.jks \n",
" inflating: /usr/lib/oracle/19.3/client64/lib/network/admin/ojdbc.properties \n",
" inflating: /usr/lib/oracle/19.3/client64/lib/network/admin/sqlnet.ora \n",
" inflating: /usr/lib/oracle/19.3/client64/lib/network/admin/ewallet.p12 \n",
" inflating: /usr/lib/oracle/19.3/client64/lib/network/admin/keystore.jks \n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "e6yJegLoFrDN",
"colab_type": "text"
},
"source": [
"I check that the tnsnames.ora contains my DEMO service:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "rrWdFcUAhN0c",
"colab_type": "code",
"outputId": "70f56382-ac8a-43df-ea4a-c74f0e6b9f29",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 88
}
},
"source": [
"!grep -A2 demo /usr/lib/oracle/19.3/client64/lib/network/admin/tnsnames.ora"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"demo_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=suulflfcsyx91z0_atp1_high.atp.oraclecloud.com))(security=(ssl_server_cert_dn=\"CN=adb.eu-zurich-1.oraclecloud.com,OU=Oracle ADB ZURICH,O=Oracle Corporation,L=Redwood City,ST=California,C=US\")))\r\n",
"\r\n",
"atp1_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=suulflfcsyx91z0_atp1_low.atp.oraclecloud.com))(security=(ssl_server_cert_dn=\"CN=adb.eu-zurich-1.oraclecloud.com,OU=Oracle ADB ZURICH,O=Oracle Corporation,L=Redwood City,ST=California,C=US\")))\r\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "jhjqcRyIF4kJ",
"colab_type": "text"
},
"source": [
"and that the sqlnet.ora contains the location of the wallet - another advantage to use the default location is that I don't need to change it:\n",
"\n",
"---\n",
"\n"
]
},
{
"cell_type": "code",
"metadata": {
"id": "VAqjsTynvDiA",
"colab_type": "code",
"outputId": "fef7f737-f719-442c-8ce0-1725511b08fc",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 51
}
},
"source": [
"!cat /usr/lib/oracle/19.3/client64/network/admin/sqlnet.ora"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=\"?/network/admin\")))\n",
"SSL_SERVER_DN_MATCH=yes"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "N7XFOAQqF_FZ",
"colab_type": "text"
},
"source": [
"I enter my password without revealing it to you:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "d92dMV9JwUrC",
"colab_type": "code",
"outputId": "b301a29a-5824-43cd-a8f3-76dc74ea6a7d",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"source": [
"from getpass import getpass\n",
"password = getpass('Enter the user password:')"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Enter the user password:··········\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "5laV-akPGNOx",
"colab_type": "text"
},
"source": [
"I'm now ready to test the connection from SQL*Plus"
]
},
{
"cell_type": "code",
"metadata": {
"id": "sYPF_mV5rVHo",
"colab_type": "code",
"outputId": "6bdf0838-98b2-45bf-e766-4125f774853e",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 272
}
},
"source": [
"! sqlplus -L demo/$password@demo_high <<<'show user' "
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"\n",
"SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 18 17:00:36 2020\n",
"Version 19.3.0.0.0\n",
"\n",
"Copyright (c) 1982, 2019, Oracle. All rights reserved.\n",
"\n",
"Last Successful login time: Sat Jan 18 2020 00:00:02 +00:00\n",
"\n",
"Connected to:\n",
"Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\n",
"Version 18.4.0.0.0\n",
"\n",
"SQL> USER is \"DEMO\"\n",
"SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\n",
"Version 18.4.0.0.0\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "Kx7uBfCDL1LD",
"colab_type": "text"
},
"source": [
"With sqlplus, there's no limitation about what we need to run. The best way is to create a script and run it, like:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "yHSeunE8MAhX",
"colab_type": "code",
"outputId": "1b350ae0-9155-4487-bbac-4497109bea39",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"source": [
"%%writefile script01.sql\n",
"set echo on feedback on\n",
"select * from dual;\n",
"exit"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Writing script01.sql\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "code",
"metadata": {
"id": "6aoKOBOMM3j0",
"colab_type": "code",
"outputId": "4da76626-633f-4d59-fa05-98a4a307cf39",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 136
}
},
"source": [
"! sqlplus -s -L demo/$password@demo_high @ script01.sql </dev/null"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"\n",
"D\n",
"-\n",
"X\n",
"\n",
"1 row selected.\n",
"\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "uVuAbQokHkoF",
"colab_type": "text"
},
"source": [
"## Connection from Python"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "kS1DsGYpHqk4",
"colab_type": "text"
},
"source": [
"A script run from SQLPlus is good to set the environment, but the idea of the notebook is to run some Python code. I need cx_Oracle to connection from Python to Oracle through the OCI (Oracle Call Interface)\n"
]
},
{
"cell_type": "code",
"metadata": {
"id": "mtWQG05Pcwcf",
"colab_type": "code",
"outputId": "d929171b-5ee6-4343-91f0-69de04ad9636",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 122
}
},
"source": [
"pip install cx_Oracle"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Collecting cx_Oracle\n",
"\u001b[?25l Downloading https://files.pythonhosted.org/packages/d5/15/d38862a4bd0e18d8ef2a3c98f39e743b8951ec5efd8bc63e75db04b9bc31/cx_Oracle-7.3.0-cp36-cp36m-manylinux1_x86_64.whl (737kB)\n",
"\r\u001b[K |▌ | 10kB 17.5MB/s eta 0:00:01\r\u001b[K |█ | 20kB 7.0MB/s eta 0:00:01\r\u001b[K |█▍ | 30kB 8.2MB/s eta 0:00:01\r\u001b[K |█▉ | 40kB 5.9MB/s eta 0:00:01\r\u001b[K |██▎ | 51kB 6.3MB/s eta 0:00:01\r\u001b[K |██▊ | 61kB 7.3MB/s eta 0:00:01\r\u001b[K |███▏ | 71kB 7.7MB/s eta 0:00:01\r\u001b[K |███▋ | 81kB 7.6MB/s eta 0:00:01\r\u001b[K |████ | 92kB 8.3MB/s eta 0:00:01\r\u001b[K |████▌ | 102kB 9.0MB/s eta 0:00:01\r\u001b[K |█████ | 112kB 9.0MB/s eta 0:00:01\r\u001b[K |█████▍ | 122kB 9.0MB/s eta 0:00:01\r\u001b[K |█████▉ | 133kB 9.0MB/s eta 0:00:01\r\u001b[K |██████▎ | 143kB 9.0MB/s eta 0:00:01\r\u001b[K |██████▊ | 153kB 9.0MB/s eta 0:00:01\r\u001b[K |███████▏ | 163kB 9.0MB/s eta 0:00:01\r\u001b[K |███████▋ | 174kB 9.0MB/s eta 0:00:01\r\u001b[K |████████ | 184kB 9.0MB/s eta 0:00:01\r\u001b[K |████████▌ | 194kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████ | 204kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████▍ | 215kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████▉ | 225kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████▎ | 235kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████▊ | 245kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████▏ | 256kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████▋ | 266kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████ | 276kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████▌ | 286kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████ | 296kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████▍ | 307kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████▉ | 317kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████▎ | 327kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████▊ | 337kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████▏ | 348kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████▋ | 358kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████ | 368kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████▌ | 378kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████ | 389kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████▍ | 399kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████▉ | 409kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████▎ | 419kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████▊ | 430kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████▏ | 440kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████▋ | 450kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████████ | 460kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████████▌ | 471kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████ | 481kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████▍ | 491kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████▉ | 501kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████████▎ | 512kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████████▊ | 522kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████████▏ | 532kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████████▋ | 542kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████████████ | 552kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████████████▌ | 563kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████████ | 573kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████████▍ | 583kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████████▉ | 593kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████████████▎ | 604kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████████████▊ | 614kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████████████▏ | 624kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████████████▋ | 634kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████████████████ | 645kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████████████████▌ | 655kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████████████ | 665kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████████████▍ | 675kB 9.0MB/s eta 0:00:01\r\u001b[K |█████████████████████████████▉ | 686kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████████████████▎ | 696kB 9.0MB/s eta 0:00:01\r\u001b[K |██████████████████████████████▊ | 706kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████████████████▏| 716kB 9.0MB/s eta 0:00:01\r\u001b[K |███████████████████████████████▋| 727kB 9.0MB/s eta 0:00:01\r\u001b[K |████████████████████████████████| 737kB 9.0MB/s \n",
"\u001b[?25hInstalling collected packages: cx-Oracle\n",
"Successfully installed cx-Oracle-7.3.0\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "j6EgpXcwIdxu",
"colab_type": "text"
},
"source": [
"Here is a connection from Python:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "2HAjBoiI-BCq",
"colab_type": "code",
"outputId": "c855b85c-d651-47b7-966f-5e845f63ddbb",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"source": [
"import cx_Oracle\n",
"con = cx_Oracle.connect(\"demo\", password, \"demo_high\")\n",
"cur = con.cursor()\n",
"cur.execute(\"select current_timestamp,sys_context('userenv','instance_name') from dual\")\n",
"row = cur.fetchone()\n",
"print(row)"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"(datetime.datetime(2020, 1, 18, 17, 2, 18, 379203), 'feio1pod1')\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "UaeuR9z2IQJC",
"colab_type": "text"
},
"source": [
"## Connection from iPython SQL"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "wdUohu6jIkdH",
"colab_type": "text"
},
"source": [
"Some simple queries can be run from the %sql magic. It is very limited through as it cannot run a PL/SQL block for example. But for queries, it can be simpler.\n",
"\n",
"See https://github.com/catherinedevlin/ipython-sql](https://github.com/catherinedevlin/ipython-sql)\n",
"\n",
"I just need to install iPython SQL"
]
},
{
"cell_type": "code",
"metadata": {
"id": "pCu7kRlad9iV",
"colab_type": "code",
"outputId": "750236cf-aa23-45cd-b293-ec288d8216d0",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 326
}
},
"source": [
"pip install ipython-sql"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Requirement already satisfied: ipython-sql in /usr/local/lib/python3.6/dist-packages (0.3.9)\n",
"Requirement already satisfied: sqlparse in /usr/local/lib/python3.6/dist-packages (from ipython-sql) (0.3.0)\n",
"Requirement already satisfied: six in /usr/local/lib/python3.6/dist-packages (from ipython-sql) (1.12.0)\n",
"Requirement already satisfied: ipython>=1.0 in /usr/local/lib/python3.6/dist-packages (from ipython-sql) (5.5.0)\n",
"Requirement already satisfied: prettytable in /usr/local/lib/python3.6/dist-packages (from ipython-sql) (0.7.2)\n",
"Requirement already satisfied: ipython-genutils>=0.1.0 in /usr/local/lib/python3.6/dist-packages (from ipython-sql) (0.2.0)\n",
"Requirement already satisfied: sqlalchemy>=0.6.7 in /usr/local/lib/python3.6/dist-packages (from ipython-sql) (1.3.12)\n",
"Requirement already satisfied: pickleshare in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
"Requirement already satisfied: pygments in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (2.1.3)\n",
"Requirement already satisfied: decorator in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (4.4.1)\n",
"Requirement already satisfied: pexpect; sys_platform != \"win32\" in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (4.7.0)\n",
"Requirement already satisfied: prompt-toolkit<2.0.0,>=1.0.4 in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (1.0.18)\n",
"Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (4.3.3)\n",
"Requirement already satisfied: simplegeneric>0.8 in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (0.8.1)\n",
"Requirement already satisfied: setuptools>=18.5 in /usr/local/lib/python3.6/dist-packages (from ipython>=1.0->ipython-sql) (42.0.2)\n",
"Requirement already satisfied: ptyprocess>=0.5 in /usr/local/lib/python3.6/dist-packages (from pexpect; sys_platform != \"win32\"->ipython>=1.0->ipython-sql) (0.6.0)\n",
"Requirement already satisfied: wcwidth in /usr/local/lib/python3.6/dist-packages (from prompt-toolkit<2.0.0,>=1.0.4->ipython>=1.0->ipython-sql) (0.1.8)\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "syqCu4LmH2f3",
"colab_type": "text"
},
"source": [
"and load it:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "vrq1U977eAVn",
"colab_type": "code",
"outputId": "e207efc1-db15-40ac-b250-f144cad56d6c",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 51
}
},
"source": [
"%load_ext sql"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"The sql extension is already loaded. To reload it, use:\n",
" %reload_ext sql\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "9jWnyA9LH-ZY",
"colab_type": "text"
},
"source": [
"Now I can connect with the %sql magic"
]
},
{
"cell_type": "code",
"metadata": {
"id": "TZYzNVQ2eVr9",
"colab_type": "code",
"outputId": "384ab501-644b-4d77-f92c-9395474f77a7",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 88
}
},
"source": [
"%sql oracle://demo:$password@demo_high"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"/usr/local/lib/python3.6/dist-packages/sqlalchemy/dialects/oracle/base.py:1339: SAWarning: Oracle version (18, 4, 0, 0, 0) is known to have a maximum identifier length of 128, rather than the historical default of 30. SQLAlchemy 1.4 will use 128 for this database; please set max_identifier_length=128 in create_engine() in order to test the application with this new length, or set to 30 in order to assure that 30 continues to be used. In particular, pay close attention to the behavior of database migrations as dynamically generated names may change. See the section 'Max Identifier Lengths' in the SQLAlchemy Oracle dialect documentation for background.\n",
" % ((self.server_version_info,))\n"
],
"name": "stderr"
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"'Connected: demo@None'"
]
},
"metadata": {
"tags": []
},
"execution_count": 53
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "32TXRduUJAe_",
"colab_type": "text"
},
"source": [
"And run some simple statements:"
]
},
{
"cell_type": "code",
"metadata": {
"id": "ctKhVPRqiB1h",
"colab_type": "code",
"outputId": "84507a98-e0fc-4ef8-907e-151d4400fbec",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 95
}
},
"source": [
"%%sql \n",
"select current_timestamp,sys_context('userenv','instance_name') from dual"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
" * oracle://demo:***@demo_high\n",
"0 rows affected.\n"
],
"name": "stdout"
},
{
"output_type": "execute_result",
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>current_timestamp</th>\n",
" <th>SYS_CONTEXT(&#x27;USERENV&#x27;,&#x27;INSTANCE_NAME&#x27;)</th>\n",
" </tr>\n",
" <tr>\n",
" <td>2020-01-18 17:03:13.137073</td>\n",
" <td>feio1pod1</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(datetime.datetime(2020, 1, 18, 17, 3, 13, 137073), 'feio1pod1')]"
]
},
"metadata": {
"tags": []
},
"execution_count": 54
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "BgQzVuRlOAO-",
"colab_type": "text"
},
"source": [
"\n",
"---\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "-T2GjA4QN9yU",
"colab_type": "text"
},
"source": [
"Any comment welcome on Twitter: [@FranckPachot](https://twitter.com/FranckPachot)"
]
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment