Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Does Oracle have any plans on creating a standalone python driver

4203522Mar 3 2020 — edited Mar 4 2020

I've waisted many days trying to connect to oracle database on our company network in python via cx_Oracle.  I've read through many stack-exchange posts, looked at blog questions here etc.

I'll put all the details below, but before looking at that my fundamental question is why would Oracle not provide a simple standalone python driver package to connect to Oracle DBs that DOES NOT REQUIRE installing ALL OF THIS: Oracle Instant Client or Oracle database, and then having to tweak environment variables?

The motivation here is that data science very hot now and data science work uses Python as the preferred language; so why would you force everyone who wants to connect to Oracle in Python have to jump through all these gigantic ridiculous hoops just to connect to the DB?

I had to abandon connecting in Python and use Java instead for which there IS a simple standalone JDBC driver.

Here are the details of my environment:

Mac Mojave desktop computer.

Python 3.7

Oracle on network is version 11.2

I've tried following this installation: https://oracle.github.io/odpi/doc/installation.html#macos

I've tried and retried all the steps described there many times and end up with below exception at the python line: conn = cx_Oracle.connect(connection_string)

Traceback (most recent call last):

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 2060, in <module>

    main()

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 2054, in main

    globals = debugger.run(setup['file'], None, None, is_module)

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 1405, in run

    return self._exec(is_module, entry_point_fn, module_name, file, globals, locals)

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 1412, in _exec

    pydev_imports.execfile(file, globals, locals)  # execute the script

  File "/Applications/PyCharm.app/Contents/helpers/pydev/_pydev_imps/_pydev_execfile.py", line 18, in execfile

    exec(compile(contents+"\n", file, 'exec'), glob, loc)

  File "/Users/me/projdir/pymain.py", line 44, in <module>

    conn = cx_Oracle.connect(connection_string)

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): no suitable image found.  Did find:

  /Users/llnesbitt/lib/libclntsh.dylib: stat() failed with errno=20". See https://oracle.github.io/odpi/doc/installation.html#macos for help

This post has been answered by Christopher Jones-Oracle on Mar 3 2020
Jump to Answer

Comments

Solomon Yakobson
Answer

Well, since CLOB in general is multi-line text each clob needs to be stored as a separate file. So we prepare CTL file:

LOAD DATA
INFILE 'C:\TEMP\TMP_LOAD_CLOB_DATA.TXT'
  INTO TABLE TMP_LOAD_CLOB_DATA
  FIELDS TERMINATED BY X'09'
  (
   CUSTOMER_ID,
   FIRSTNAME,
   LASTNAME,
   CUSTOMER_DETAILS_CLOB_FILE FILLER,
   CUSTOMER_DETAILS LOBFILE(CUSTOMER_DETAILS_CLOB_FILE) TERMINATED BY EOF
  )

Data file C:\TEMP\TMP_LOAD_CLOB_DATA.TXT:

123	ABC	XYZ	C:\TEMP\TMP_LOAD_CLOB1.TXT
456	DEF	ZZZ	C:\TEMP\TMP_LOAD_CLOB2.TXT

Clob file C:\TEMP\TMP_LOAD_CLOB1.TXT:

Lot Of Information 
about customer ABC XYZ
which should be loaded
into a clob

Clob file C:\TEMP\TMP_LOAD_CLOB2.TXT:

Lot Of Information 
about customer DEF ZZZ
which should be loaded
into a clob

Now:

SQL> SELECT  *
  2    FROM  TMP_LOAD_CLOB_DATA
  3  /

no rows selected

SQL> host
Microsoft Windows [Version 10.0.17763.1637]
(c) 2018 Microsoft Corporation. All rights reserved.

I:\>sqlldr scott@pdb1sol122/tiger control=C:\TEMP\TMP_LOAD_CLOB_DATA.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Feb 5 07:25:12 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table TMP_LOAD_CLOB_DATA:
  2 Rows successfully loaded.

Check the log file:
  TMP_LOAD_CLOB_DATA.log
for more information about the load.

I:\>exit

SQL> SELECT  *
  2    FROM  TMP_LOAD_CLOB_DATA
  3  /


CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS
----------- --------- -------- ------------------------------
123         ABC       XYZ      Lot Of Information
                               about customer ABC XYZ
                               which should be loaded
                               into a clob

456         DEF       ZZZ      Lot Of Information
                               about customer DEF ZZZ
                               which should be loaded
                               into a clob

SQL>

SY.

Marked as Answer by Nirvan · Feb 22 2021
Nirvan

Hi Solomon, Thank you for your response..

User_ZZAIV

Hi,
For above is there any way to load CLOB data without having separate files ??

User_H3J7U

To load lobs, use the insert, update, or merge statement.

User_48D4R

What to do when a large text is part of a single file and needs to be inserted with an SQLLDR.
eg: it is 498,780 characters long.
ID | VALUE | DATA
1 | Dr4wQQ | it is 498,780 characters long......
2 | WQWQ3 | it is 925,342 characters long......
3 | DERRQQ | it is 478,998 characters long......

Solomon Yakobson

I'll assume CLOB doesn't start with new line. I will also assume, based on your data sample, vertical pipe can't appear in CLOB - otherwise you simply can't use it as field separator. If so, control file TMP_LOAD_CLOB_DATA.CTL:

LOAD DATA
  INFILE 'C:\MY_TEMP\TMP_LOAD_CLOB_DATA.TXT'
  "str X'7C0D0A'"
  INTO TABLE TMP_LOAD_CLOB_DATA
  FIELDS TERMINATED BY "|"
  (
   ID,
   VALUE,
   DATA
  )

7C is hex ascii code for vertical pipe. 0D0A is new line (CRLF - carriage return, line feed). Now we need to add vertical pipe at the end of each line in data file TMP_LOAD_CLOB_DATA.TXT:

1 | Dr4wQQ | it
 is 498,780
 characters
 long......|
2 | WQWQ3 | it 
is 
925,342
 characters long......|
3 | DERRQQ | it 
is 
478,998
 characters
 long
 line1
 line2
 many
 more
 lines|

Now we load:

I:\>sqlldr scott@xxx/yyy control=c:\my_temp\tmp_load_clob_data.ctl log=c:\my_temp\tmp_load_clob_data.log

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Aug 12 06:55:18 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 3

Table TMP_LOAD_CLOB_DATA:
  3 Rows successfully loaded.

Check the log file:
  c:\my_temp\tmp_load_clob_data.log
for more information about the load.

I:\>

Now we check what was loaded:

SQL> select * from tmp_load_clob_data;

        ID VALUE      DATA
---------- ---------- ----------------------------------------
         1  Dr4wQQ     it
                       is 498,780
                       characters
                       long......

         2  WQWQ3      it
                      is
                      925,342
                       characters long......

         3  DERRQQ     it
                      is
                      478,998
                       characters
                       long
                       line1
                       line2
                       many
                       more
                       lines

SQL>

SY.

Gor_Mahia


Gor_Mahia

Hi
Did you get a solid solution to this issue? It seems splitting CLOB field isn't that helpful since you still need a unique key to merger them back?

1 - 8

Post Details

Added on Mar 3 2020
5 comments
1,157 views