Fetching long description gives clob error

fetching long description gives clob error

JPA provides a simple mapping for LOBs. But Hibernate also needs to fetch all data stored in the LOB immediately and map it to a private Long id;. The issue was resolved by fetching the data with a sql override in the source qualifier. Additional Information. Clob interface provides methods for setting and retrieving data in the Clob, giving access to the Clob data; Throws: SQLException - if an error occurs. fetching long description gives clob error

Fetching long description gives clob error - can not

Despite the criticism on best practices and all other stuff this solution worked for me. I had previously used an update in a stored procedure to surpass the 4k limit but that approach didn't work when having more than 32k characters, but this complemented my solution. Thanks!
Most users will find an error while execution of the following line

sprers.eu(newvalue, 0, sprers.eu);

There are 2 steps for inserting data to clob field,

1. Insert the record
2. Open the same row with FOR UPDATE clause and execute the above line of code

The problem is while inserting record don't ignore CLOB field, instead use EMPTY_CLOB() in Insert statement as value for CLOB field.
Hi ,

I need help for oracle datatype-

I am using TEMP_STRING VARCHAR2();

but while stroring in it I am building string more than (so exception) sprers.eu is around +
So I used LONG datatype - but still the same.
Will it be appropriate or is there any other one in Oracle ?

Can you please suggest.

Thanks and regards,
- Ajay K
The head of the article is different from it content. No useful information.
I copied your code into a project and I got a exception at the line

sprers.eu(newvalue, 0, sprers.eu);

THE CONNECTION IS CLOSED!!!

the connection of the datareader is open but the connection of the Clob is Null and I can not set it because it is a read-only property.
If you use Oracle 9, 10 or 11 you can do it much easier. I haven't tested with Oracle 8 because I don't have Oracle 8 on my pc so I can't test with Oracle 8, Oracle 8 is really really old (year ). I show two variants. One with the Oracle provider of Microsoft and one with Oracle's own provider sprers.eu

/* create table testclob (large clob); */

//Microsofts provider for Oracle
using sprers.euClient;

Copy Code

<br /> using (OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=ORA9"))<br /> {<br /> sprers.eu();<br /> <br /> using (OracleCommand cmd = new OracleCommand())<br /> {<br /> sprers.eution = con;<br /> sprers.eudType = sprers.eu;<br /> <br /> sprers.eudText = " insert into testclob values (:parameter) ";<br /> <br /> OracleParameter oracleParameterClob = new OracleParameter();<br /> sprers.euType = sprers.eu; <br /> sprers.eu = "A".PadRight( * , 'B');<br /> sprers.euterName = "PARAMETER"; <br /> <br /> sprers.eu(oracleParameterClob);<br /> <br /> sprers.eueNonQuery();<br /> <br /> }<br /> <br /> sprers.eu();<br /> }<br /> <br />

If you use Oracle's owner provider (sprers.eu):


using sprers.eu;

Copy Code

<br /> <br /> <br /> using (OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=ORA9"))<br /> {<br /> sprers.eu();<br /> <br /> using (OracleCommand cmd = new OracleCommand())<br /> {<br /> sprers.eution = con;<br /> sprers.eudType = sprers.eu;<br /> <br /> sprers.eudText = " insert into testclob values (:parameter) ";<br /> <br /> OracleParameter oracleParameterClob = new OracleParameter();<br /> sprers.euDbType = sprers.eu; <br /> sprers.eu = "A".PadRight( * , 'B');<br /> <br /> <br /> sprers.eu(oracleParameterClob);<br /> <br /> sprers.eueNonQuery();<br /> <br /> }<br /> <br /> sprers.eu();<br /> }<br /> <br />
How to read CLOB???? from datareader

modified on Thursday, October 30, PM


Hi,

I'm getting a null return from GetOracleClobForUpdate at index 0.

My select statement select 1 column which is the clob type column.
Any idea how this could happen?

Thanks in advance.
I am just inserting clobs by adding my clob parameter as sprers.eu Is this not easier?
I don't really get u what you meant..

More details please.. Smile  <div><h2><a>ORA I take CLOB datatype variable still getting this error</a></h2><div><p>Though you are dealing with datatype, the concatenations are producing s (max length 32, bytes), and won't allow overshooting this max during concatenation. </p><p>If has enough rows, the length of in one of the concatenations goes over this limit, and you are given the . You can see this in action, if you add an exception handler like the below at the end of your block:</p><p>When you run the above, you should get a message like the following, with a number just over :</p><p>To allow the full attachment to build, you can take measures to avoid . Here's an example that uses instead. (But note! you also can't a huge either, so this example catches that exception too and lets you know.</p><p>First make test data:</p><p>Now we have rows. </p><p>Now modify the block to only use concatenation on small strings. use instead on big strings:</p><p>You can't print the as-is; if you really want to print it, you'd need to break it up. But if you are really planning to do something like send to another function, you should be good to go.</p><p>When you run the above, you should get something like:</p></div><div><p> answered Oct 1, at </p><div><div><img src=
alexgibbsalexgibbs

2, gold badges silver badges bronze badges

:)">

'ace'


I am facing a problem on sprers.eu(newvalue, 0, sprers.eu). The error I get is "Invalid operation. The connection is closed." At this point, clob's Connection property is null. But I can't assign a connection because it is a readonly property. What do I need to do to get the code to recognize that the Connection in sqlCommandOracle should be set as the Connection for clob?
If the connection is closed, means you have to check your connection first. It's has nothing wrong with the clob

Good luck

'ace'


I am getting the same error on at sprers.eu(newvalue, 0, sprers.eu);

sprers.eu is equal to "Open"

My clob variable has the following properties:
{sprers.euLob}
base {sprers.eu}: {sprers.euLob}
CanRead: true
CanSeek: true
CanWrite: true
ChunkSize: 0
Connection: null
IsBatched: false
IsNull: true
IsTemporary: false
Length: 0
LobType: Blob
Position: 0
Value: {}

Thanks
I had the same problem. You have to create a temp lob. I didn't like this article because this code didn't work for me.

See this: EXAMPLE OK[^]
Your code work correctly with text sql statement.
But I want use stored procedure (like this below), but don't work. Frown   '/', '')  <div><h2>Python and Oracle Database Tutorial: Scripting for the Future</h2><div><h3>Overview</h3><p>This tutorial is an introduction to using Python with Oracle Database. It contains beginner and advanced material. Sections can be done in any order. Choose the content that interests you and your skill level. The tutorial has scripts to run and modify, and has suggested solutions.</p><p>Python is a popular general purpose dynamic scripting language. The cx_Oracle interface provides the Python API to access Oracle Database.</p><p>If you are new to Python review the Appendix: Python Primer to gain an understanding of the language. </p><p>When you have finished this tutorial, we recommend reviewing the cx_Oracle documention. </p><p>The original copy of these instructions that you are reading is here.</p><h3>cx_Oracle Architecture</h3><p>Python programs call cx_Oracle functions. Internally cx_Oracle dynamically loads Oracle Client libraries to access Oracle Database. The database can be on the same machine as Python, or it can be remote. If the database is local, the client libraries from the Oracle Database software installation can be used.</p><img src=

Setup

  • Install software

    To get going, follow either of the quick start instructions:

    For this tutorial, you will need Python (or later), cx_Oracle (or later), and access to Oracle Database.

    The Advanced Queuing section requires Python cx_Oracle to be using Oracle client libraries or later. The SODA section requires Oracle Database 18 or later, and Python cx_Oracle must be using Oracle libraries from , or later.

  • Download the tutorial scripts

    The Python scripts used in this example are in the cx_Oracle GitHub repository.

    Download a zip file of the repository from here and unzip it. Alternatively you can use 'git' to clone the repository with

    The directory has scripts to run and modify. The directory has scripts with suggested code changes.

  • Create a database user

    If you have an existing user, you may be able to use it for most examples (some examples may require extra permissions).

    If you need to create a new user, review the grants created in . Then open a terminal window, change to the directory, and run the script as the SYSTEM user, for example:

    cd samples/tutorial/sql sqlplus -l system/[email protected]/orclpdb1 @create_user

    The example above connects as the SYSTEM user. The connection string is "localhost/orclpdb1", meaning use the database service "orclpdb1" running on localhost (the computer you are running SQL*Plus on). Substitute values for your environment. If you are using Oracle Autonomous Database, use the ADMIN user instead of SYSTEM.

    When the tutorial is finished, the script in the same directory can be used to remove the tutorial user.

  • Install the sample tables

    Once you have a database user, then you can create the tutorial tables by running a command like this, using your values for the tutorial username, password and connection string:

    sqlplus -l pythonhol/[email protected]/orclpdb1 @setup_tables
  • Start the Database Resident Connection Pool (DRCP)

    If you want to try the DRCP examples in section 2, start the DRCP pool. (The pool is already started in Oracle Autonomous Database).

    Run SQL*Plus with SYSDBA privileges, for example:

    sqlplus -l sys/[email protected]/orclcdb as sysdba

    and execute the command:

    execute dbms_connection_sprers.eu_pool()

    Note you may need to do this in the container database, not a pluggable database.

  • Review the connection credentials used by the tutorial scripts

    Review and in the directory. These are included in other Python and SQL files.

    Edit and change the default values to match the connection information for your environment. Alternatively you can set the given envionment variables in your terminal window. For example, the default username is "pythonhol" unless the envionment variable "PYTHON_USER" contains a different username. The default connection string is for the 'orclpdb1' database service on the same machine as Python. (In Python Database API terminology, the connection string parameter is called the "data source name", or "dsn".) Using envionment variables is convenient because you will not be asked to re-enter the password when you run scripts:

    user = sprers.eu("PYTHON_USER", "pythonhol") dsn = sprers.eu("PYTHON_CONNECT_STRING", "localhost/orclpdb1") pw = sprers.eu("PYTHON_PASSWORD") if pw is None: pw = sprers.eus("Enter password for %s: " % user)

    Also change the default username and connection string in the SQL*Plus configuration file :

    -- Default database username def user = "pythonhol" -- Default database connection string def connect_string = "localhost/orclpdb1" -- Prompt for the password accept pw char prompt 'Enter database password for &user: ' hide

    The tutorial instructions may need adjusting, depending on how you have set up your environment.

  • Review the Instant Client library path

    Review the Oracle Client library path settings in . If cx_Oracle cannot locate Oracle Client libraries, then your applications will fail with an error like "DPI Cannot locate a bit Oracle Client library".

    # On Linux this must be None. # Instead, the Oracle environment must be set before Python starts. instant_client_dir = None # On Windows, if your database is on the same machine, comment these lines out # and let instant_client_dir be None. Otherwise, set this to your Instant # Client directory. Note the use of the raw string r"" so backslashes can # be used as directory separators. if sprers.euwith("win"): instant_client_dir = r"c:\oracle\instantclient_19_10" # On macOS (Intel x86) set the directory to your Instant Client directory if sprers.euwith("darwin"): instant_client_dir = sprers.eu("HOME")+"/Downloads/instantclient_19_8" # This can be called at most once per process. if instant_client_dir is not None: cx_sprers.eu_oracle_client(lib_dir=instant_client_dir)

    Set to or to a valid path according to the following notes:

    • If you are on macOS or Windows, and you have installed Oracle Instant Client libraries because your database is on a remote machine, then set to the path of the Instant Client libraries.

    • If you are on Windows and have a local database installed, then comment out the two Windows lines, so that remains .

    • In all other cases (including Linux with Oracle Instant Client), make sure that is set to . In these cases you must make sure that the Oracle libraries from Instant Client or your ORACLE_HOME are in your system library search path before you start Python. On Linux, the path can be configured with ldconfig or with the LD_LIBRARY_PATH environment variables.

1. Connecting to Oracle

You can connect from Python to a local, remote or cloud database. Documentation link for further reading: Connecting to Oracle Database.

  • Creating a basic connection

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) print("Database version:", sprers.eun)

    The cx_Oracle module is imported to provide the API for accessing the Oracle database. Many inbuilt and third party modules can be included in Python scripts this way.

    The method is passed the username, the password and the connection string that you configured in the db_sprers.eu module. In this case, Oracle's Easy Connect connection string syntax is used. It consists of the hostname of your machine, , and the database service name . (In Python Database API terminology, the connection string parameter is called the "data source name", or "dsn".)

    Open a command terminal and change to the directory:

    cd samples/tutorial

    Run the Python script:

    python sprers.eu

    The version number of the database should be displayed. An exception is raised if the connection fails. Adjust the username, password or connection string parameters to invalid values to see the exception.

    cx_Oracle also supports "external authentication", which allows connections without needing usernames and passwords to be embedded in the code. Authentication would then instead be performed by, for example, LDAP or Oracle Wallets.

  • Indentation indicates code structure

    There are no statement terminators or begin/end keywords or braces to indicate blocks of code.

    Open in an editor. Indent the print statement with some spaces:

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) print("Database version:", sprers.eun)

    Save the script and run it again:

    python sprers.eu

    This raises an exception about the indentation. The number of spaces or tabs must be consistent in each block; otherwise, the Python interpreter will either raise an exception or execute code unexpectedly.

    Python may not always be able to identify accidental from deliberate indentation. Check your indentation is correct before running each example. Make sure to indent all statement blocks equally.Note the sample files use spaces, not tabs.

  • Executing a query

    Open in an editor. It looks like:

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu)

    Edit the file and add the code shown in bold below:

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() sprers.eue("select * from dept order by deptno") res = sprers.eull() for row in res: print(row)

    Make sure the line is indented. This lab uses spaces, not tabs.

    The code executes a query and fetches all data.

    Save the file and run it:

    python sprers.eu

    In each loop iteration a new row is stored in as a Python "tuple" and is displayed.

    Fetching data is described further in section 3.

  • Closing connections

    Connections and other resources used by cx_Oracle will automatically be closed at the end of scope. This is a common programming style that takes care of the correct order of resource closure.

    Resources can also be explicitly closed to free up database resources if they are no longer needed. This is strongly recommended in blocks of code that remain active for some time.

    Open in an editor and add calls to close the cursor and connection like:

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() sprers.eue("select * from dept order by deptno") res = sprers.eull() for row in res: print(row) sprers.eu()sprers.eu()

    Running the script completes without error:

    python sprers.eu

    If you swap the order of the two calls you will see an error.

  • Checking versions

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) print(cx_sprers.eun)

    Run the script:

    python sprers.eu

    This gives the version of the cx_Oracle interface.

    Edit the file to print the version of the database, and of the Oracle client libraries used by cx_Oracle:

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) print(cx_sprers.eun) print("Database version:", sprers.eun) print("Client version:", cx_sprers.euversion())

    When the script is run, it will display:

    Database version: Client version: (19, 8, 0, 0, 0)

    Note the client version is a tuple.

    Any cx_Oracle installation can connect to older and newer Oracle Database versions. By checking the Oracle Database and client versions numbers, the application can make use of the best Oracle features available.

2. Connection Pooling

Connection pooling is important for performance in when multi-threaded applications frequently connect and disconnect from the database. Pooling also gives the best support for Oracle high availability features. Documentation link for further reading: Connection Pooling.

  • Connection pooling

    Review the code contained in :

    import cx_Oracle import threading import db_config pool = cx_Oracle.SessionPool(db_sprers.eu, db_sprers.eu, db_sprers.eu, min = 2, max = 5, increment = 1, threaded = True, getmode = cx_sprers.eu_ATTRVAL_WAIT) def Query(): con = pool.acquire() cur = sprers.eu() for i in range(4): sprers.eue("select sprers.eul from dual") seqval, = sprers.eune() print("Thread", sprers.eut_thread().name, "fetched sequence =", seqval) thread1 = sprers.eu(name='#1', target=Query) sprers.eu() thread2 = sprers.eu(name='#2', target=Query) sprers.eu() sprers.eu() sprers.eu() print("All done!")

    The function creates a pool of Oracle connections for the user. Connections in the pool can be used by cx_Oracle by calling . The initial pool size is 2 connections. The maximum size is 5 connections. When the pool needs to grow, then 1 new connection will be created at a time. The pool can shrink back to the minimum size of 2 when connections are no longer in use.

    The line creates a method that is called by each thread.

    In the method, the call gets one connection from the pool (as long as less than 5 are already in use). This connection is used in a loop of 4 iterations to query the sequence . At the end of the method, cx_Oracle will automatically close the cursor and release the connection back to the pool for reuse.

    The line fetches a row and puts the single value contained in the result tuple into the variable . Without the comma, the value in would be a tuple like "".

    Two threads are created, each invoking the method.

    In a command terminal, run:

    python connect_sprers.eu

    The output shows interleaved query results as each thread fetches values independently. The order of interleaving may vary from run to run.

  • Connection pool experiments

    Review , which has a loop for the number of threads, each iteration invoking the method:

    import cx_Oracle import threading import db_config pool = cx_sprers.eunPool(db_sprers.eu, db_sprers.eu, db_sprers.eu, min = 2, max = 5, increment = 1, threaded = True, getmode = cx_sprers.eu_ATTRVAL_WAIT) def Query(): con = sprers.eue() cur = sprers.eu() for i in range(4): sprers.eue("select sprers.eul from dual") seqval, = sprers.eune() print("Thread", sprers.eut_thread().name, "fetched sequence =", seqval) numberOfThreads = 2 threadArray = [] for i in range(numberOfThreads): thread = sprers.eu(name = '#' + str(i), target = Query) sprers.eu(thread) sprers.eu() for t in threadArray: sprers.eu() print("All done!")

    In a command terminal, run:

    python connect_sprers.eu

    Experiment with different values of the pool parameters and . Larger initial pool sizes will make the pool creation slower, but the connections will be available immediately when needed.

    Try changing to . When exceeds the maximum size of the pool, the call will now generate an error such as "ORA OCISessionGet() timed out waiting for pool to create new connections".

    Pool configurations where is the same as (and ) are often recommended as a best practice. This avoids connection storms on the database server.

  • Creating a DRCP Connection

    Database Resident Connection Pooling allows multiple Python processes on multiple machines to share a small pool of database server processes.

    Below left is a diagram without DRCP. Every application standalone connection (or cx_Oracle connection-pool connection) has its own database server process. Standalone application and close calls require the expensive create and destroy of those database server processes. cx_Oracle connection pools reduce these costs by keeping database server processes open, but every cx_Oracle connection pool will requires its own set of database server processes, even if they are not doing database work: these idle server processes consumes database host resources. Below right is a diagram with DRCP. Scripts and Python processes can share database servers from a precreated pool of servers and return them when they are not in use.

    Picture of 3-tier application architecture without DRCP showing connections from multiple application processes each going to a server process in the database tier

    Without DRCP

    Picture of 3-tier application architecture with DRCP showing connections from multiple application processes going to a pool of server processes in the database tier

    With DRCP

    DRCP is useful when the database host machine does not have enough memory to handle the number of database server processes required. If DRCP is enabled, it is best used in conjunction with cx_Oracle's connection pooling. However, if the database host memory is large enough, then the default, 'dedicated' server process model is generally recommended. This can be with or without a cx_Oracle connection pool, depending on the connection rate.

    Batch scripts doing long running jobs should generally use dedicated connections. Both dedicated and DRCP servers can be used together in the same application or database.

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu + ":pooled", cclass="PYTHONHOL", purity=cx_sprers.eu_PURITY_SELF) print("Database version:", sprers.eun)

    This is similar to but "" is appended to the connection string, telling the database to use a pooled server. A Connection Class "PYTHONHOL" is also passed into the method to allow grouping of database servers to applications. Note with Autonomous Database, the connection string has a different form, see the ADB documentation.

    The "purity" of the connection is defined as the constant, meaning the session state (such as the default date format) might be retained between connection calls, giving performance benefits. Session information will be discarded if a pooled server is later reused by an application with a different connection class name.

    Applications that should never share session information should use a different connection class and/or use to force creation of a new session. This reduces overall scalability but prevents applications mis-using session information.

    Run in a terminal window.

    python connect_sprers.eu

    The output is simply the version of the database.

  • Connection pooling and DRCP

    DRCP works well with cx_Oracle's connection pooling.

    Edit , reset any changed pool options, and modify it to use DRCP:

    import cx_Oracle import threading pool = cx_sprers.eunPool(db_sprers.eu, db_sprers.eu, db_sprers.eu + ":pooled", min = 2, max = 5, increment = 1, threaded = True, getmode = cx_sprers.eu_ATTRVAL_WAIT) def Query(): con = sprers.eue(cclass = "PYTHONHOL", purity = cx_sprers.eu_PURITY_SELF) cur = sprers.eu() for i in range(4): sprers.eue("select sprers.eul from dual") seqval, = sprers.eune() print("Thread", sprers.eut_thread().name, "fetched sequence =", seqval) numberOfThreads = 2 threadArray = [] for i in range(numberOfThreads): thread = sprers.eu(name = '#' + str(i), target = Query) sprers.eu(thread) sprers.eu() for t in threadArray: sprers.eu() print("All done!")

    The script logic does not need to be changed to benefit from DRCP connection pooling.

    Run the script:

    python connect_sprers.eu

    Review drcp_sprers.eu and set the connection string to your database. Then open a new a terminal window and invoke SQL*Plus:

    sqlplus /nolog @drcp_sprers.eu

    This will prompt for the SYSTEM password and the database connection string. With Pluggable databases, you will need to connect to the container database. Note that with ADB, this view does not contain rows, so running this script is not useful.

    For other databases, the script shows the number of connection requests made to the pool since the database was started ("NUM_REQUESTS"), how many of those reused a pooled server's session ("NUM_HITS"), and how many had to create new sessions ("NUM_MISSES"). Typically the goal is a low number of misses.

    To see the pool configuration you can query DBA_CPOOL_INFO.

  • More DRCP investigation

    To explore the behaviors of cx_Oracle connection pooling and DRCP pooling futher, you could try changing the purity to to see the effect on the DRCP NUM_MISSES statistic.

    Another experiement is to include the module at the file top:

    import time

    and add calls to in the code, for example in the query loop. Then look at the way the threads execute. Use to monitor the pool's behavior.

3. Fetching Data

Executing SELECT queries is the primary way to get data from Oracle Database. Documentation link for further reading: SQL Queries.

  • A simple query

    There are a number of functions you can use to query an Oracle database, but the basics of querying are always the same:

    1. Execute the statement.
    2. Bind data values (optional).
    3. Fetch the results from the database.

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() sprers.eue("select * from dept order by deptno") for deptno, dname, loc in cur: print("Department number: ", deptno) print("Department name: ", dname) print("Department location:", loc)

    The method opens a cursor for statements to use.

    The method parses and executes the statement.

    The loop fetches each row from the cursor and unpacks the returned tuple into the variables , , , which are then printed.

    Run the script in a terminal window:

    python sprers.eu

    The output is:

    Department number: 10 Department name: ACCOUNTING Department location: NEW YORK Department number: 20 Department name: RESEARCH Department location: DALLAS Department number: 30 Department name: SALES Department location: CHICAGO Department number: 40 Department name: OPERATIONS Department location: BOSTON
  • Using fetchone()

    When the number of rows is large, the call may use too much memory.

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() sprers.eue("select * from dept order by deptno") row = sprers.eune() print(row) row = sprers.eune() print(row)

    This uses the method to return just a single row as a tuple. When called multiple time, consecutive rows are returned:

    Run the script in a terminal window:

    python query_sprers.eu

    The first two rows of the table are printed.

  • Using fetchmany()

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() sprers.eue("select * from dept order by deptno") res = sprers.euany(numRows = 3) print(res)

    The method returns a list of tuples. By default the number of rows returned is specified by the cursor attribute (which defaults to ). Here the parameter specifies that three rows should be returned.

    Run the script in a terminal window:

    python query_sprers.eu

    The first three rows of the table are returned as a list (Python's name for an array) of tuples.

    You can access elements of the lists by position indexes. To see this, edit the file and add:

    print(res[0]) # first row print(res[0][1]) # second element of first row
  • Scrollable cursors

    Scrollable cursors enable the application to move backwards as well as forwards in query results. They can be used to skip rows as well as move to a particular row.

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu(scrollable = True) sprers.eue("select * from dept order by deptno") sprers.eu(2, mode = "absolute") # go to second row print(sprers.eune()) sprers.eu(-1) # go back one row print(sprers.eune())

    Run the script in a terminal window:

    python query_sprers.eu

    Edit and experiment with different scroll options and orders, such as:

    sprers.eu(1) # go to next row print(sprers.eune()) sprers.eu(mode = "first") # go to first row print(sprers.eune())

    Try some scroll options that go beyond the number of rows in the resultset.

  • Tuning with arraysize and prefetchrows

    This section demonstrates a way to improve query performance by increasing the number of rows returned in each batch from Oracle to the Python program.

    Row prefetching and array fetching are both internal buffering techniques to reduce round-trips to the database. The difference is the code layer that is doing the buffering, and when the buffering occurs.

    First, create a table with a large number of rows. Review :

    create table bigtab (mycol varchar2(20)); begin for i in loop insert into bigtab (mycol) values (dbms_sprers.eu('A',20)); end loop; end; / show errors commit;

    In a terminal window run the script as:

    sqlplus /nolog @query_sprers.eu

    Review the code contained in :

    import cx_Oracle import time import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) start = sprers.eu() cur = sprers.eu() sprers.euchrows = sprers.euize = sprers.eue("select * from bigtab") res = sprers.eull() # print(res) # uncomment to display the query results elapsed = (sprers.eu() - start) print(elapsed, "seconds")

    This uses the 'time' module to measure elapsed time of the query. The prefetchrows and arraysize values are This causes batches of records at a time to be returned from the database to a cache in Python. These values can be tuned to reduce the number of "round-trips" made to the database, often reducing network load and reducing the number of context switches on the database server. The , and methods will read from the cache before requesting more data from the database.

    In a terminal window, run:

    python query_sprers.eu

    Rerun a few times to see the average times.

    Experiment with different prefetchrows and arraysize values. For example, edit and change the arraysize to:

    sprers.euize =

    Rerun the script to compare the performance of different arraysize settings.

    In general, larger array sizes improve performance. Depending on how fast your system is, you may need to use different values than those given here to see a meaningful time difference.

    There is a time/space tradeoff for increasing the values. Larger values will require more memory in Python for buffering the records.

    If you know the query returns a fixed number of rows, for example 20 rows, then set arraysize to 20 and prefetchrows to The addition of one for prefetchrows prevents a round-trip to check for end-of-fetch. The statement execution and fetch will take a total of one round-trip. This minimizes load on the database.

    If you know a query only returns a few records, decrease the arraysize from the default to reduce memory usage.

4. Binding Data

Bind variables enable you to re-execute statements with new data values without the overhead of re-parsing the statement. Binding improves code reusability, improves application scalability, and can reduce the risk of SQL injection attacks. Using bind variables is strongly recommended. Documentation link for further reading: Using Bind Variables.

  • Binding in queries

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() sql = "select * from dept where deptno = :id order by deptno" sprers.eue(sql, id = 20) res = sprers.eull() print(res) sprers.eue(sql, id = 10) res = sprers.eull() print(res)

    The statement contains a bind variable "" placeholder. The statement is executed twice with different values for the clause.

    From a terminal window, run:

    python bind_sprers.eu

    The output shows the details for the two departments.

    An arbitrary number of named arguments can be used in an call. Each argument name must match a bind variable name. Alternatively, instead of passing multiple arguments you could pass a second argument to that is a sequence or a dictionary. Later examples show these syntaxes.

    To bind a database NULL, use the Python value

    cx_Oracle uses Oracle Database's Statement Cache. As long as the statement you pass to is in that cache, you can use different bind values and still avoid a full statement parse. The statement cache size is configurable for each connection. To see the default statement cache size, edit and add a line at the end:

    print(sprers.euchesize)

    Re-run the file.

    In your applications you would set the statement cache size to the number of unique statements commonly executed.

  • Binding in inserts

    Review the code in creating a table for inserting data:

    create table mytab (id number, data varchar2(20), constraint my_pk primary key (id));

    Run the script as:

    sqlplus /nolog @bind_sprers.eu

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() rows = [ (1, "First" ), (2, "Second" ), (3, "Third" ), (4, "Fourth" ), (5, "Fifth" ), (6, "Sixth" ), (7, "Seventh" ) ] sprers.euemany("insert into mytab(id, data) values (:1, :2)", rows) # Now query the results back cur2 = sprers.eu() sprers.eue('select * from mytab') res = sprers.eull() print(res)

    The '' array contains the data to be inserted.

    The call inserts all rows. This call uses "array binding", which is an efficient way to insert multiple records.

    The final part of the script queries the results back and displays them as a list of tuples.

    From a terminal window, run:

    python bind_sprers.eu

    The new results are automatically rolled back at the end of the script so re-running it will always show the same number of rows in the table.

  • Batcherrors

    The Batcherrors features allows invalid data to be identified while allowing valid data to be inserted.

    Edit the data values in and create a row with a duplicate key:

    rows = [ (1, "First" ), (2, "Second" ), (3, "Third" ), (4, "Fourth" ), (5, "Fifth" ), (6, "Sixth" ), (6, "Duplicate" ), (7, "Seventh" ) ]

    From a terminal window, run:

    python bind_sprers.eu

    The duplicate generates the error "ORA unique constraint (sprers.eu_PK) violated". The data is rolled back and the query returns no rows.

    Edit the file again and enable like:

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() rows = [ (1, "First" ), (2, "Second" ), (3, "Third" ), (4, "Fourth" ), (5, "Fifth" ), (6, "Sixth" ), (6, "Duplicate" ), (7, "Seventh" ) ] sprers.euemany("insert into mytab(id, data) values (:1, :2)", rows, batcherrors = True) for error in sprers.eucherrors(): print("Error", sprers.eu(), "at row offset", sprers.eu) # Now query the results back cur2 = sprers.eu() sprers.eue('select * from mytab') res = sprers.eull() print(res)

    Run the file:

    python bind_sprers.eu

    The new code shows the offending duplicate row: "ORA unique constraint (sprers.eu_PK) violated at row offset 6". This indicates the 6th data value (counting from 0) had a problem.

    The other data gets inserted and is queried back.

    At the end of the script, cx_Oracle will roll back an uncommitted transaction. If you want to commit results, you can use:

    sprers.eu()

    To force cx_Oracle to roll back, use:

    sprers.euck()
  • Binding named objects

    cx_Oracle can fetch and bind named object types such as Oracle's Spatial Data Objects (SDO).

    In a terminal window, start SQL*Plus using the lab credentials and connection string, such as:

    sqlplus -l pythonhol/[email protected]/orclpdb1

    Use the SQL*Plus DESCRIBE command to look at the SDO definition:

    desc sprers.eu_GEOMETRY

    It contains various attributes and methods. The top level description is:

    Name Null? Type SDO_GTYPE NUMBER SDO_SRID NUMBER SDO_POINT sprers.eu_POINT_TYPE SDO_ELEM_INFO sprers.eu_ELEM_INFO_ARRAY SDO_ORDINATES sprers.eu_ORDINATE_ARRAY

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() # Create table sprers.eue("""begin execute immediate 'drop table testgeometry'; exception when others then if sqlcode <> then raise; end if; end;""") sprers.eue("""create table testgeometry ( id number(9) not null, geometry sprers.eu_GEOMETRY not null)""") # Create and populate Oracle objects typeObj = con.gettype("sprers.eu_GEOMETRY") elementInfoTypeObj = con.gettype("sprers.eu_ELEM_INFO_ARRAY") ordinateTypeObj = con.gettype("sprers.eu_ORDINATE_ARRAY") obj = typeObj.newobject() sprers.eu_GTYPE = sprers.eu_ELEM_INFO = elementInfoTypeObj.newobject() sprers.eu_ELEM_INFO.extend([1, , 3]) sprers.eu_ORDINATES = ordinateTypeObj.newobject() sprers.eu_ORDINATES.extend([1, 1, 5, 7]) print("Created object", obj) # Add a new row print("Adding row to table") sprers.eue("insert into testgeometry values (1, :objbv)", objbv = obj) print("Row added!") # Query the row print("Querying row just inserted") sprers.eue("select id, geometry from testgeometry"); for row in cur: print(row)

    This uses to get the database types of the SDO and its object attributes. The calls create Python representations of those objects. The python object atributes are then set. Oracle VARRAY types such as SDO_ELEM_INFO_ARRAY are set with .

    Run the file:

    python bind_sprers.eu

    The new SDO is shown as an object, similar to:

    (1, <cx_sprers.eu sprers.eu_GEOMETRY at 0xa>)

    To show the attribute values, edit the the query code section at the end of the file. Add a new method that traverses the object. The file below the existing comment "") should look like:

    # (Change below here) # Define a function to dump the contents of an Oracle object def dumpobject(obj, prefix = " "): if sprers.euection: print(prefix, "[") for value in sprers.eu(): if isinstance(value, cx_sprers.eu): dumpobject(value, prefix + " ") else: print(prefix + " ", repr(value)) print(prefix, "]") else: print(prefix, "{") for attr in sprers.euutes: value = getattr(obj, sprers.eu) if isinstance(value, cx_sprers.eu): print(prefix + " " + sprers.eu + " :") dumpobject(value, prefix + " ") else: print(prefix + " " + sprers.eu + " :", repr(value)) print(prefix, "}") # Query the row print("Querying row just inserted") sprers.eue("select id, geometry from testgeometry") for id, obj in cur: print("Id: ", id) dumpobject(obj)

    Run the file again:

    python bind_sprers.eu

    This shows

    Querying row just inserted Id: 1 { SDO_GTYPE : SDO_SRID : None SDO_POINT : None SDO_ELEM_INFO : [ 1 3 ] SDO_ORDINATES : [ 1 1 5 7 ] }

    To explore further, try setting the SDO attribute SDO_POINT, which is of type SDO_POINT_TYPE.

    The and methods can also be used to bind PL/SQL Records and Collections.

    Before deciding to use objects, review your performance goals because working with scalar values can be faster.

5. PL/SQL

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL procedures and functions are stored and run in the database. Using PL/SQL lets all database applications reuse logic, no matter how the application accesses the database. Many data-related operations can be performed in PL/SQL faster than extracting the data into a program (for example, Python) and then processing it. Documentation link for further reading: PL/SQL Execution.

  • PL/SQL functions

    Review which creates a PL/SQL stored function to insert a row into a new table named ptab and return double the inserted value:

    create table ptab (mydata varchar(20), myid number); create or replace function myfunc(d_p in varchar2, i_p in number) return number as begin insert into ptab (mydata, myid) values (d_p, i_p); return (i_p * 2); end; /

    Run the script using:

    sqlplus /nolog @plsql_sprers.eu

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() res = sprers.eunc('myfunc', int, ('abc', 2)) print(res)

    This uses to execute the function. The second parameter is the type of the returned value. It should be one of the types supported by cx_Oracle or one of the type constants defined by cx_Oracle (such as cx_sprers.eu). The two PL/SQL function parameters are passed as a tuple, binding them to the function parameter arguments.

    From a terminal window, run:

    python plsql_sprers.eu

    The output is a result of the PL/SQL function calculation.

  • PL/SQL procedures

    Review which creates a PL/SQL procedure to accept two parameters. The second parameter contains an OUT return value.

    create or replace procedure myproc(v1_p in number, v2_p out number) as begin v2_p := v1_p * 2; end; /

    Run the script with:

    sqlplus /nolog @plsql_sprers.eu

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() myvar = sprers.eu(int) sprers.euoc('myproc', (, myvar)) print(sprers.euue())

    This creates an integer variable to hold the value returned by the PL/SQL OUT parameter. The input number and the output variable name are bound to the procedure call parameters using a tuple.

    To call the PL/SQL procedure, the method is used.

    In a terminal window, run:

    python plsql_sprers.eu

    The method displays the returned value.

6. Type Handlers

Type handlers enable applications to alter data that is fetched from, or sent to, the database. Documentation links for further reading: Changing Fetched Data Types with Output Type Handlers and Changing Bind Data Types using an Input Type Handler.

  • Basic output type handler

    Output type handlers enable applications to change how data is fetched from the database. For example, numbers can be returned as strings or decimal objects. LOBs can be returned as string or bytes.

    A type handler is enabled by setting the attribute on either a cursor or the connection. If set on a cursor it only affects queries executed by that cursor. If set on a connection it affects all queries executed on cursors created by that connection.

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() print("Standard output") for row in sprers.eue("select * from dept"): print(row)

    In a terminal window, run:

    python type_sprers.eu

    This shows the department number represented as digits like .

    Add an output type handler to the bottom of the file:

    def ReturnNumbersAsStrings(cursor, name, defaultType, size, precision, scale): if defaultType == cx_sprers.eu: return sprers.eu(str, 9, sprers.euize) print("Output type handler output") cur = sprers.eu() sprers.eutypehandler = ReturnNumbersAsStrings for row in sprers.eue("select * from dept"): print(row)

    This type handler converts any number columns to strings with maxium size 9.

    Run the script again:

    python type_sprers.eu

    The new output shows the department numbers are now strings within quotes like .

  • Output type handlers and variable converters

    When numbers are fetched from the database, the conversion from Oracle's decimal representation to Python's binary format may need careful handling. To avoid unexpected issues, the general recommendation is to do number operations in SQL or PL/SQL, or to use the decimal module in Python.

    Output type handlers can be combined with variable converters to change how data is fetched.

    Review :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() for value, in sprers.eue("select from dual"): print("Value:", value, "* 3 =", value * 3)

    Run the file:

    python type_sprers.eu

    The output is like:

    Value: * 3 =

    Edit the file and add a type handler that uses a Python decimal converter:

    import cx_Oracle import decimal import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() def ReturnNumbersAsDecimal(cursor, name, defaultType, size, precision, scale): if defaultType == cx_sprers.eu: return sprers.eu(str, 9, sprers.euize, outconverter = sprers.eul) sprers.eutypehandler = ReturnNumbersAsDecimal for value, in sprers.eue("select from dual"): print("Value:", value, "* 3 =", value * 3)

    The Python converter gets called with the string representation of the Oracle number. The output from is returned in the output tuple.

    Run the file again:

    python type_sprers.eu

    Output is like:

    Value: * 3 =

    Although the code demonstrates the use of outconverter, in this particular case, the variable can be created simply by using the following code to replace the outputtypehandler function defined above:

    def ReturnNumbersAsDecimal(cursor, name, defaultType, size, precision, scale): if defaultType == cx_sprers.eu: return sprers.eu(sprers.eul, arraysize = sprers.euize)
  • Input type handlers

    Input type handlers enable applications to change how data is bound to statements, or to enable new types to be bound directly without having to be converted individually.

    Review , which is similar to the final from section , with the addition of a new class and converter (shown in bold):

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() # Create table sprers.eue("""begin execute immediate 'drop table testgeometry'; exception when others then if sqlcode <> then raise; end if; end;""") sprers.eue("""create table testgeometry ( id number(9) not null, geometry sprers.eu_GEOMETRY not null)""") # Create a Python class for an SDO class mySDO(object): def __init__(self, gtype, elemInfo, ordinates): sprers.eu = gtype sprers.eufo = elemInfo sprers.eutes = ordinates # Get Oracle type information objType = sprers.eue("sprers.eu_GEOMETRY") elementInfoTypeObj = sprers.eue("sprers.eu_ELEM_INFO_ARRAY") ordinateTypeObj = sprers.eue("sprers.eu_ORDINATE_ARRAY") # Convert a Python object to sprers.eu_GEOMETRY def SDOInConverter(value): obj = sprers.euect() sprers.eu_GTYPE = sprers.eu sprers.eu_ELEM_INFO = sprers.euect() sprers.eu_ELEM_sprers.eu(sprers.eufo) sprers.eu_ORDINATES = sprers.euect() sprers.eu_sprers.eu(sprers.eutes) return obj def SDOInputTypeHandler(cursor, value, numElements): if isinstance(value, mySDO): return sprers.eu(cx_sprers.eu, arraysize = numElements, inconverter = SDOInConverter, typename = sprers.eu) sdo = mySDO(, [1, , 3], [1, 1, 5, 7]) # Python object sprers.euypehandler = SDOInputTypeHandler sprers.eue("insert into testgeometry values (:1, :2)", (1, sdo)) # Define a function to dump the contents of an Oracle object def dumpobject(obj, prefix = " "): if sprers.euection: print(prefix, "[") for value in sprers.eu(): if isinstance(value, cx_sprers.eu): dumpobject(value, prefix + " ") else: print(prefix + " ", repr(value)) print(prefix, "]") else: print(prefix, "{") for attr in sprers.euutes: value = getattr(obj, sprers.eu) if isinstance(value, cx_sprers.eu): print(prefix + " " + sprers.eu + " :") dumpobject(value, prefix + " ") else: print(prefix + " " + sprers.eu + " :", repr(value)) print(prefix, "}") # Query the row print("Querying row just inserted") sprers.eue("select id, geometry from testgeometry") for (id, obj) in cur: print("Id: ", id) dumpobject(obj)

    In the new file, a Python class is defined, which has attributes corresponding to each Oracle sprers.eu_GEOMETRY attribute. The class is used lower in the code to create a Python instance:

    sdo = mySDO(, [1, , 3], [1, 1, 5, 7])

    which is then directly bound into the INSERT statement like:

    sprers.eue("insert into testgeometry values (:1, :2)", (1, sdo))

    The mapping between Python and Oracle objects is handled in which uses the cx_Oracle and methods to create an Oracle object from the Python object values. The method is called by the input type handler whenever an instance of is inserted with the cursor.

    To confirm the behavior, run the file:

    python type_sprers.eu

7. LOBs

Oracle Database "LOB" long objects can be streamed using a LOB locator, or worked with directly as strings or bytes. Documentation link for further reading: Using CLOB and BLOB Data.

  • Fetching a CLOB using a locator

    Review the code contained in :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() print("Inserting data") sprers.eue("truncate table testclobs") longString = "" for i in range(5): char = chr(ord('A') + i) longString += char * sprers.eue("insert into testclobs values (:1, :2)", (i + 1, "String data " + longString + ' End of string')) sprers.eu() print("Querying data") sprers.eue("select * from testclobs where id = :id", {'id': 1}) (id, clob) = sprers.eune() print("CLOB length:", sprers.eu()) clobdata = sprers.eu() print("CLOB data:", clobdata)

    This inserts some test string data and then fetches one record into , which is a cx_Oracle character LOB Object. Methods on LOB include and .

    To see the output, run the file:

    python sprers.eu

    Edit the file and experiment reading chunks of data by giving start character position and length, such as

  • Fetching a CLOB as a string

    For CLOBs small enough to fit in the application memory, it is much faster to fetch them directly as strings.

    Review the code contained in . The differences from are shown in bold:

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() print("Inserting data") sprers.eue("truncate table testclobs") longString = "" for i in range(5): char = chr(ord('A') + i) longString += char * sprers.eue("insert into testclobs values (:1, :2)", (i + 1, "String data " + longString + ' End of string')) sprers.eu() def OutputTypeHandler(cursor, name, defaultType, size, precision, scale): if defaultType == cx_sprers.eu: return sprers.eu(cx_sprers.eu_STRING, arraysize = sprers.euize) sprers.eutypehandler = OutputTypeHandler print("Querying data") sprers.eue("select * from testclobs where id = :id", {'id': 1}) (id, clobdata) = sprers.eune() print("CLOB length:", len(clobdata)) print("CLOB data:", clobdata)

    The OutputTypeHandler causes cx_Oracle to fetch the CLOB as a string. Standard Python string functions such as can be used on the result.

    The output is the same as for . To check, run the file:

    python clob_sprers.eu

8. Rowfactory functions

Rowfactory functions enable queries to return objects other than tuples. They can be used to provide names for the various columns or to return custom objects.

  • Rowfactory for mapping column names

    Review the code contained in :

    import collections import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() sprers.eue("select deptno, dname from dept") rows = sprers.eull() print('Array indexes:') for row in rows: print(row[0], "->", row[1]) print('Loop target variables:') for c1, c2 in rows: print(c1, "->", c2)

    This shows two methods of accessing result set items from a data row. The first uses array indexes like . The second uses loop target variables which take the values of each row tuple.

    Run the file:

    python sprers.eu

    Both access methods gives the same results.

    To use a rowfactory function, edit and add this code at the bottom:

    print('Rowfactory:') sprers.eue("select deptno, dname from dept") sprers.eutory = sprers.euuple("MyClass", ["DeptNumber", "DeptName"]) rows = sprers.eull() for row in rows: print(sprers.eumber, "->", sprers.eume)

    This uses the Python factory function to create a subclass of tuple that allows access to the elements via indexes or the given field names.

    The function shows the use of the new named tuple fields. This coding style can help reduce coding errors.

    Run the script again:

    python sprers.eu

    The output results are the same.

9. Subclassing connections and cursors

Subclassing enables application to "hook" connection and cursor creation. This can be used to alter or log connection and execution parameters, and to extend cx_Oracle functionality. Documentation link for further reading: Tracing SQL and PL/SQL Statements.

  • Subclassing connections

    Review the code contained in :

    import cx_Oracle import db_config class MyConnection(cx_sprers.eution): def __init__(self): print("Connecting to database") return super(MyConnection, self).__init__(db_sprers.eu, db_sprers.eu, db_sprers.eu) con = MyConnection() cur = sprers.eu() sprers.eue("select count(*) from emp where deptno = :bv", (10,)) count, = sprers.eune() print("Number of rows:", count)

    This creates a new class "MyConnection" that inherits from the cx_Oracle Connection class. The method is invoked when an instance of the new class is created. It prints a message and calls the base class, passing the connection credentials.

    In the "normal" application, the application code:

    con = MyConnection()

    does not need to supply any credentials, as they are embedded in the custom subclass. All the cx_Oracle methods such as are available, as shown by the query.

    Run the file:

    python sprers.eu

    The query executes successfully.

  • Subclassing cursors

    Edit and extend the method with a new MyCursor class:

    import cx_Oracle import db_config class MyConnection(cx_sprers.eution): def __init__(self): print("Connecting to database") return super(MyConnection, self).__init__(db_sprers.eu, db_sprers.eu, db_sprers.eu) def cursor(self): return MyCursor(self) class MyCursor(cx_sprers.eu): def execute(self, statement, args): print("Executing:", statement) print("Arguments:") for argIndex, arg in enumerate(args): print(" Bind", argIndex + 1, "has value", repr(arg)) return super(MyCursor, self).execute(statement, args) def fetchone(self): print("Fetchone()") return super(MyCursor, self).fetchone() con = MyConnection() cur = sprers.eu() sprers.eue("select count(*) from emp where deptno = :bv", (10,)) count, = sprers.eune() print("Number of rows:", count)

    When the application gets a cursor from the class, the new method returns an instance of our new class.

    The "application" query code remains unchanged. The new and methods of the class get invoked. They do some logging and invoke the parent methods to do the actual statement execution.

    To confirm this, run the file again:

    python sprers.eu

Advanced Queuing

Oracle Advanced Queuing (AQ) allows messages to be passed between applications. Documentation link for further reading: Oracle Advanced Queuing (AQ).

  • Message passing with Oracle Advanced Queuing

    Review :

    import cx_Oracle import decimal import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) cur = sprers.eu() BOOK_TYPE_NAME = "UDT_BOOK" QUEUE_NAME = "BOOKS" QUEUE_TABLE_NAME = "BOOK_QUEUE_TABLE" # Cleanup sprers.eue( """begin dbms_sprers.eu_queue('""" + QUEUE_NAME + """'); dbms_sprers.eu_queue('""" + QUEUE_NAME + """'); dbms_sprers.eu_queue_table('""" + QUEUE_TABLE_NAME + """'); execute immediate 'drop type """ + BOOK_TYPE_NAME + """'; exception when others then if sqlcode <> then raise; end if; end;""") # Create a type print("Creating books type UDT_BOOK") sprers.eue(""" create type %s as object ( title varchar2(), authors varchar2(), price number(5,2) );""" % BOOK_TYPE_NAME) # Create queue table and queue and start the queue print("Creating queue table") sprers.euoc("dbms_sprers.eu_queue_table", (QUEUE_TABLE_NAME, BOOK_TYPE_NAME)) sprers.euoc("dbms_sprers.eu_queue", (QUEUE_NAME, QUEUE_TABLE_NAME)) sprers.euoc("dbms_sprers.eu_queue", (QUEUE_NAME,)) booksType = sprers.eue(BOOK_TYPE_NAME) queue = sprers.eu(QUEUE_NAME, booksType) # Enqueue a few messages print("Enqueuing messages") BOOK_DATA = [ ("The Fellowship of the Ring", "Tolkien, J.R.R.", sprers.eul("")), ("Harry Potter and the Philosopher's Stone", "Rowling, J.K.", sprers.eul("")) ] for title, authors, price in BOOK_DATA: book = sprers.euect() sprers.eu = title sprers.euS = authors sprers.eu = price print(title) sprers.eu(sprers.euperties(payload=book)) sprers.eu() # Dequeue the messages print("\nDequeuing messages") sprers.eu = cx_sprers.eu_NO_WAIT while True: props = sprers.eu() if not props: break print(sprers.eu) sprers.eu() print("\nDone.")

    This file sets up Advanced Queuing using Oracle's DBMS_AQADM package. The queue is used for passing Oracle UDT_BOOK objects. The file uses AQ interface features enhanced in cx_Oracle

    Run the file:

    python sprers.eu

    The output shows messages being queued and dequeued.

    To experiment, split the code into three files: one to create and start the queue, and two other files to queue and dequeue messages. Experiment running the queue and dequeue files concurrently in separate terminal windows.

    Try removing the call in . Now run once and then several times. The same messages will be available each time you try to dequeue them.

    Change to commit in a separate transaction by changing the "visibility" setting:

    sprers.eulity = cx_sprers.eu_IMMEDIATE

    This gives the same behavior as the original code.

    Now change the options of enqueued messages so that they expire from the queue if they have not been dequeued after four seconds:

    sprers.eu(sprers.euperties(payload=book, expiration=4))

    Now run and wait four seconds before you run . There should be no messages to dequeue.

    If you are stuck, look in the directory at the , and files.

Simple Oracle Document Access (SODA)

Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs. Documents can be inserted, queried, and retrieved from Oracle Database. By default, documents are JSON strings. SODA APIs exist in many languages. Documentation link for further reading: Simple Oracle Document Access (SODA).

  • Inserting JSON Documents

    Review :

    import cx_Oracle import db_config con = cx_sprers.eut(db_sprers.eu, db_sprers.eu, db_sprers.eu) soda = sprers.euaDatabase() # Explicit metadata is used for maximum version portability metadata = { "keyColumn": { "name":"ID" }, "contentColumn": { "name": "JSON_DOCUMENT", "sqlType": "BLOB" }, "versionColumn": { "name": "VERSION", "method": "UUID" }, "lastModifiedColumn": { "name": "LAST_MODIFIED" }, "creationTimeColumn": { "name": "CREATED_ON" } } collection = sprers.euCollection("friends", metadata) content = {'name': 'Jared', 'age': 35, 'address': {'city': 'Melbourne'}} doc = sprers.euOneAndGet(content) key = sprers.eu doc = sprers.eu().key(key).getOne() content = sprers.eutent() print('Retrieved SODA document dictionary is:') print(content)

    will create a new collection, or open an existing collection, if the name is already in use. (Due to a change in the default "sqlType" storage for Oracle Database 21c, the metadata is explicitly stated to use a BLOB column. This lets the example run with different client and database versions).

    inserts the content of a document into the database and returns a SODA Document Object. This allows access to meta data such as the document key. By default, document keys are automatically generated.

    The method is used to begin an operation that will act upon documents in the collection.

    is a dictionary. You can also get a JSON string by calling .

    Run the file:

    python sprers.eu

    The output shows the content of the new document.

  • Searching SODA Documents

    Extend to insert some more documents and perform a find filter operation:

    myDocs = [ {'name': 'Gerald', 'age': 21, 'address': {'city': 'London'}}, {'name': 'David', 'age': 28, 'address': {'city': 'Melbourne'}}, {'name': 'Shawn', 'age': 20, 'address': {'city': 'San Francisco'}} ] sprers.euMany(myDocs) filterSpec = { "sprers.eu": "Melbourne" } myDocuments = sprers.eu().filter(filterSpec).getDocuments() print('Melbourne people:') for doc in myDocuments: print(sprers.eutent()["name"])

    Run the script again:

    python sprers.eu

    The find operation filters the collection and returns documents where the city is Melbourne. Note the method is currently in preview.

    SODA supports query by example (QBE) with an extensive set of operators. Extend with a QBE to find documents where the age is less than

    filterSpec = {'age': {'$lt': 25}} myDocuments = sprers.eu().filter(filterSpec).getDocuments() print('Young people:') for doc in myDocuments: print(sprers.eutent()["name"])

    Running the script displays the names.

Summary

In this tutorial, you have learned how to:

  • Create connections
  • Use cx_Oracle connection pooling and Database Resident Connection Pooling
  • Execute queries and fetch data
  • Use bind variables
  • Use PL/SQL stored functions and procedures
  • Extend cx_Oracle classes
  • Use Oracle Advanced Queuing
  • Use the "SODA" document store API

For further reading see the cx_Oracle documentation.

Appendix: Python Primer

Python is a dynamically typed scripting language. It is most often used to run command-line scripts but is also used for web applications and web services.

Running Python

You can either:

  • Create a file of Python commands, such as . This can be run with:

    python sprers.eu
  • Alternatively run the Python interpreter by executing the command in a terminal, and then interactively enter commands. Use Ctrl-D to exit back to the operating system prompt.

When you run scripts, Python automatically creates bytecode versions of them in a folder called . These improve performance of scripts that are run multiple times. They are automatically recreated if the source file changes.

Indentation

Whitespace indentation is significant in Python. When copying examples, use the same column alignment as shown. The samples in this lab use spaces, not tabs.

The following indentation prints 'done' once after the loop has completed:

for i in range(5): print(i) print('done')

But this indentation prints 'done' in each iteration:

for i in range(5): print(i) print('done')

Strings

Python strings can be enclosed in single or double quotes:

'A string constant' "another constant"

Multi line strings use a triple-quote syntax:

""" SELECT * FROM EMP """

Variables

Variables do not need types declared:

count = 1 ename = 'Arnie'

Comments

Comments are either single line:

# a short comment

They can be multi-line using the triple-quote token to create a string that does nothing:

""" a longer comment """

Printing

Strings and variables can be displayed with a function:

print('Hello, World!') print('Value:', count)

Data Structures

Associative arrays are called 'dictionaries':

a2 = {'PI', 'E'}

Ordered arrays are called 'lists':

a3 = [, 4, 67]

Lists can be accessed via indexes.

print(a3[0]) print(a3[-1]) print(a3[])

Tuples are like lists but cannot be changed once they are created. They are created with parentheses:

a4 = (3, 7, 10)

Individual values in a tuple can be assigned to variables like:

v1, v2, v3 = a4

Now the variable v1 contains 3, the variable v2 contains 7 and the variable v3 contains

The value in a single entry tuple like ""can be assigned to a variable by putting a comma after the variable name like:

v1, = (13,)

If the assignment is:

v1 = (13,)

then will contain the whole tuple ""

Objects

Everything in Python is an object. As an example, given the of the list above, the method can be used to add a value to the list.

sprers.eu(23)

Now contains

Flow Control

Code flow can be controlled with tests and loops. The // statements look like:

if v == 2 or v == 4: print('Even') elif v == 1 or v == 3: print('Odd') else: print('Unknown number')

This also shows how the clauses are delimited with colons, and each sub block of code is indented.

Loops

A traditional loop is:

for i in range(10): print(i)

This prints the numbers from 0 to 9. The value of is incremented in each iteration.

The '' command can also be used to iterate over lists and tuples:

a5 = ['Aa', 'Bb', 'Cc'] for v in a5: print(v)

This sets to each element of the list in turn.

Functions

A function may be defined as:

def myfunc(p1, p2): "Function documentation: add two numbers" print(p1, p2) return p1 + p2

Functions may or may not return values. This function could be called using:

v3 = myfunc(1, 3)

Function calls must appear after their function definition.

Functions are also objects and have attributes. The inbuilt attribute can be used to find the function description:

print(myfunc.__doc__)

Modules

Sub-files can be included in Python scripts with an import statement.

import os import sys

Many predefined modules exist, such as the os and the sys modules.

Resources

Copyright © , , Oracle and/or its affiliates. All rights reserved
[Rose]">
:)">

'ace'


Hi!

If you use "Parametrized SQL Statements" you can insert as much data as you want. I didn't even have to specify that data type. The driver recognizes the necessary mapping and does everything. I inserted Strings of sizes up to MB into one CLOB, then things got too slow on our machines.

Just look at the docs for the fetching long description gives clob error Property and the "OracleParameter" Class, fetching long description gives clob error. In my help file the examples provided are for SELECT statements, but you use it for INSERTs too.

Best regards
Daniel
This help me a lot!!! Thanks
Nicola

If we use sprers.eucess it's a slight different Big Grin [Rose]