Christopher Jonespython-oracledb 2.3 is even smaller on Linux (24.7.2024, 06:48 UTC)
Photo by Yassine Khalfalli on Unsplash

python-oracledb 2.3 has been released. My colleague @Veronica Dumitriu has posted the release announcement. There are lots of good improvements, such as support for the new VECTOR binary format that is available in Oracle Database 23.5.

One of the smaller (excuse the pun) enhancements is that the binary packages we put on PyPI for Linux are now stripped.

With the previous python-oracledb 2.2 release the install footprint was:

$ du -sh /home/cjones/.local/lib/python3.11/site-packages/oracledb/
49M /home/cjones/.local/lib/python3.11/site-packages/oracledb/

Now with python-oracledb 2.3 :

$ du -sh /home/cjones/.local/lib/python3.11/site-packages/oracledb/
7.2M /home/cjones/.local/lib/python3.11/site-packages/oracledb/

For you micro-optimizers, we know every byte counts!

This small change brings Linux into parity with macOS, where the install footprint remains around the 11M mark.

Installing or Upgrading python-oracledb

You can install or upgrade python-oracledb by running:

python -m pip install oracledb --upgrade

The pip options --proxy and --user may be useful in some environments. See python-oracledb Installation for details.

Python-oracledb References

Home page: oracle.github.io/python-oracledb/index.html

Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html

Documentation: python-oracledb.readthedocs.io/en/latest/index.html

Release Notes: python-oracledb.readthedocs.io/en/latest/release_notes.html

Discussions: github.com/oracle/python-oracledb/discussions

Issues: github.com/oracle/python-oracledb/issues

Source Code Repository: github.com/oracle/python-oracledb

Link
PHP: Hypertext PreprocessorPHP 8.4.0 Alpha 2 available for testing (18.7.2024, 00:00 UTC)
The PHP team is pleased to announce the second testing release of PHP 8.4.0, Alpha 2. This continues the PHP 8.4 release cycle, the rough outline of which is specified in the PHP Wiki.For source downloads of PHP 8.4.0 Alpha 2 please visit the download page.Please carefully test this version and report any issues found in the bug reporting system.Please DO NOT use this version in production, it is an early test version.For more information on the new features and other changes, you can read the NEWS file, or the UPGRADING file for a complete list of upgrading notes. These files can also be found in the release archive.The next release will be Alpha 3, planned for 1 Aug 2024.The signatures for the release can be found in the manifest or on the QA site.Thank you for helping us make PHP better.
Link
Christopher JonesInstallation Cheatsheet for Oracle Instant Client on Oracle Linux (12.7.2024, 23:32 UTC)

Wondering how to install Oracle Instant Client on Oracle Linux from Oracle’s yum repository? This is what you need to know.

A non-cheating sheet — Photo by Sergiu Vălenaș on Unsplash

Oracle Instant Client enables development and deployment of applications that connect to Oracle Database, either on-premise or in the Cloud. The Instant Client libraries provide the necessary network connectivity and advanced data features to make full use of Oracle Database. The libraries are used by the Oracle APIs of popular languages and environments including Python, Node.js, Go, PHP and Ruby, as well as providing access for Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), JDBC OCI, ODBC and Pro*C applications. Tools included in Instant Client, such as SQL*Plus, SQL*Loader and Oracle Data Pump, provide quick and convenient data access.

Oracle Instant Client is available on many platforms as simple zip downloads from Oracle. This post shows how the packages can also be installed using Oracle Linux yum repositories.

Packages

18c & 19c OL7:

sudo yum install oracle-release-el7
sudo yum install oracle-instantclient19.23-basic

19c OL8:

sudo dnf install -y oracle-release-el8
sudo dnf install -y oracle-instantclient19.23-basic

19c OL9:

sudo dnf install oracle-instantclient-release-el9
sudo dnf install oracle-instantclient19.19-basic

21c OL7:

sudo yum install oracle-instantclient-release-el7
sudo yum install oracle-instantclient-basic

21c OL8:

sudo dnf install oracle-instantclient-release-el8
sudo dnf install oracle-instantclient-basic

23ai OL8:

sudo dnf install oracle-instantclient-release-23ai-el8
sudo dnf install oracle-instantclient-basic

23ai OL9:

sudo dnf install oracle-instantclient-release-23ai-el9
sudo dnf install oracle-instantclient-basic

Notes

For 18c and 19c, you need to explicitly give the RU (the ‘Release Update’ patch number) in the filename when installing. To upgrade, you need to remove the old package and install the new one. Please check the repos and keep up to date!

From 21c, any yum or dnf update will automatically pull in the latest RU. If
you need to prevent an update, then use versionlock, as discussed in the
Installing Oracle Instant Client Using RPMs documentation, e.g :

dnf versionlock oracle-instantclient-release-23ai-el8

Containers

Sample Dockerfiles:

Truncated by Planet PHP, read more at the original (another 2098 bytes)

Link
Christopher JonesThe best of Relational and JSON — at the same time (11.7.2024, 06:26 UTC)

The best of Relational and JSON — at the same time

Oracle Database 23ai JSON-Relational Duality Views allow data to be stored as rows in tables to provide the benefits of SQL access in a relational model, while also allowing read and write access as JSON documents to the exact same data. This blog shows how to use the new views in Python.

Photo by Pietro Jeng on Unsplash

The relational model is great: you can avoid data duplication; data consistency is guaranteed; and you have access via a very powerful, very efficient language — SQL. But, for developers, the requirement to define a relational schema — to decide on tables, columns and data types — before beginning to code is a chore. It’s not easy to predict future uses for the system, some of which may be difficult with the chosen schema.

This is why you love JSON. A JSON object can contain all the information for one use case without the need to use SQL to join tables. Access is via a simple query, or a single call to a database API. JSON is schema-flexible so, as your use cases evolve over the lifetime of a system, you can easily modify applications. But there are drawbacks: a single hierarchy may only allow a few use cases. Data can end up being duplicated, which affects not just space but makes it very hard to keep consistent. Optimization is harder. So what appears to be a simple model can end up causing long term complexity.

This is where Oracle Database 23ai JSON-Relational Duality Views are an outright game changer — not a phrase I use lightly. These duality views build on Oracle Database’s long history of JSON support and longer history with the relational model.

Let’s start by creating two relational tables:

      drop table if exists AuthorTab;
drop table if exists BookTab;

create table AuthorTab (
AuthorId number generated by default on null as identity primary key,
AuthorName varchar2(100)
);

create table BookTab (
BookId number generated by default on null as identity primary key,
BookTitle varchar2(100),
AuthorId number references AuthorTab (AuthorId)
);

insert into AuthorTab values (1, 'Isabel M. Rich');
insert into AuthorTab values (2, 'Bobbie Cool');
insert into AuthorTab values (3, 'Charlie Shore');
insert into BookTab values (1, 'The Mysterious Dog', 1);
insert into BookTab values (2, 'The Mysterious Pony', 1);
insert into BookTab values (3, 'The Mysterious Tiger', 1);
insert into BookTab values (4, 'Self Help for Programmers', 2);
insert into BookTab values (5, 'More Self Help for Programmers', 2);
insert into BookTab values (6, 'Travel Guide Volume I', 3);
insert into BookTab values (7, 'Travel Guide Volume II', 3);

commit;

These can obviously be accessed via relational SELECT, INSERT etc statements which are trivial to execute in Python using python-oracledb, for example:

      import oracledb

connection = oracledb.connect(user="cj", password="MySecret",
dsn="localhost/orclpdb1")

with connection.cursor() as cursor:
sql = """select AuthorName, BookTitle
from AuthorTab, BookTab
where AuthorTab.AuthorId = BookTab.AuthorId"""
for r in cursor.execute(sql):
print(r)

The output lists the authors and their books:

      ('Isabel M. Rich', 'The Mysterious Dog')
('Isabel M. Rich', 'The Mysterious Pony')
('Isabel M. Rich', 'The Mysterious Tiger')
('Bobbie Cool', 'Self Help for Programmers')
('Bobbie Cool', 'More Self Help for Programmers')
('Charlie Shore', 'Travel Guide Volume I')
('Charlie Shore', 'Travel Guide Volume II')

The fun is to create a duality view. In your SQL editor run:

create or replace json relational duality view BookDV as
BookTab @insert @update @delete
{
_id: BookId,
book_title: BookTitle,
author: AuthorTab @insert @update
{
author_id: AuthorId,
author_name: AuthorName
}
};

This creates a view that is accessible using JSON. The syntax shown here is GraphQL, le

Truncated by Planet PHP, read more at the original (another 6376 bytes)

Link
Christopher JonesUsing the %ROWTYPE attribute with python-oracledb (6.7.2024, 05:33 UTC)

When using Python to interact with Oracle Database PL/SQL stored procedures, you may encounter PL/SQL code that uses the %ROWTYPE attribute. This is used to declare a record that represents either a full or partial row of a database table or view. This blog post shows how to use %ROWTYPE in python-oracledb.

Photo by Mike Petrucci on Unsplash

Let’s start with a PL/SQL function that returns a row of the sample schema LOCATIONS table using LOCATIONS%ROWTYPE as the return type. You might create this in Python like:

      import getpass
import oracledb

un = 'cj'
cs = 'localhost/orclpdb1'
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')
connection = oracledb.connect(user=un, password=pw, dsn=cs)

with connection.cursor() as cursor:
cursor.execute("""
create or replace function TestFuncOUT return locations%rowtype as
p locations%rowtype;
begin
select * into p from locations where rownum < 2;
return p;
end;""")
if cursor.warning:
print(cursor.warning)

The %ROWTYPE attribute defines a record, so you can use python-oracledb’s gettype() object functionality to get the record "shape", and pass this as the expected return type to callfunc() when you invoke TestFuncOUT:

    rt = connection.gettype("LOCATIONS%ROWTYPE")
r = cursor.callfunc("TESTFUNCOUT", rt)

The python-oracledb type of r is oracledb.DbObject. You can manipulate and view it using that class's methods. A handy convenience function that prints an instance is dump_object() defined in samples/object_dump.py. Calling it on the return value from TestFuncOUT:

dump_object(r)

gives:

{
LOCATION_ID: 1000
STREET_ADDRESS: '1297 Via Cola di Rie'
POSTAL_CODE: '00989'
CITY: 'Roma'
STATE_PROVINCE: None
COUNTRY_ID: 'IT'
}

which is the row returned from the query.

If you need to construct a similar object directly in python-oracledb to pass into the database, you can use newobject() and set any desired fields. For example:

    rt = connection.gettype("LOCATIONS%ROWTYPE")
r = rt.newobject()
r.CITY = 'Roma'

Passing a value from Python into a PL/SQL block that expects a %ROWTYPE parameter or bind variable is straightforward. Let’s create a PL/SQL procedure that has an IN parameter of type %ROWTYPE:

cursor.execute("""
create or replace procedure TestProcIN(p in locations%rowtype, city out varchar2) as
begin
city := p.city;
end;""")
if cursor.warning:
print(cursor.warning)

Now you can call callproc() passing the variable r from the previous callfunc() or newobject() examples in the appropriate parameter position, for example:

    c = cursor.var(oracledb.DB_TYPE_VARCHAR)
cursor.callproc("TESTPROCIN", [r, c])
print(c.getvalue())

This prints:

Roma

Conclusion

PL/SQL %ROWTYPE attribute values map to python-oracledb’s DbObject instances, allowing easy manipulation and interaction between Python and Oracle Database.

Installing or Upgrading python-oracledb

You can install or upgrade python-oracledb by running:

python -m pip install oracledb --upgrade

The pip options --proxy and --user may be useful in some environments. See python-oracledb Installation for details.

Python-oracledb References

Home page: oracle.github.io/python-oracledb/index.html

Installation instructions:

Truncated by Planet PHP, read more at the original (another 1322 bytes)

Link
PHP: Hypertext PreprocessorPHP 8.4.0 Alpha 1 available for testing (5.7.2024, 00:00 UTC)
The PHP team is pleased to announce the first testing release of PHP 8.4.0, Alpha 1. This starts the PHP 8.4 release cycle, the rough outline of which is specified in the PHP Wiki.For source downloads of PHP 8.4.0 Alpha 1 please visit the download page.Please carefully test this version and report any issues found using the bug tracking system.Please DO NOT use this version in production, it is an early test version.For more information on the new features and other changes, you can read the NEWS file, or the UPGRADING file for a complete list of upgrading notes. These files can also be found in the release archive.The next release will be Alpha 2, planned for 18 Jul 2024.The signatures for the release can be found in the manifest or on the QA site.Thank you for helping us make PHP better.
Link
Evert PotCreating a fake download counter with Web Components (2.7.2024, 16:07 UTC)

Over the years I’ve written several open source libraries. They’re mostly unglamorous and utilitarian, but a bunch of them obtained got a decent download count, so I thought it would be fun to try and get a grand total and show a ‘live’ download counter on my blog.

This is how that looks like:

My open source packages were downloaded roughly 138945563 times.

Like most live counters, this number isn’t tracked in real-time. Instead it just uses a start number and updates the number based on an average number of downloads.

One day it might be nice to make it live, but this is a static blog and this would need proper hosting and a database.

Why is this number so high?

This data comes from NPM and Packagist, and they both count any download. So this number doesn’t represent necessarily 138 million users, but simply this many downloads by any means including bots, CI environments and so on. I think for both package managers the goal was probably not to have a realistic representation of users, but rather a number that makes developers feel good. And I like that. It’s nice to see a number go up and it’s still a nice proxy for relative popularity.

The Web Component

This seemed like a good use-case for a web component. Always wanted to build one! I was surprised how easy it was.

This is how this looks in the HTML:

<p>
  My open source packages were downloaded roughly
  <strong>
    <download-counter inc-per-day="157444" date="2024-06-29T15:34:00Z" >
      138945563
    </download-counter>
  </strong> times.
</p>

What’s nice is that if Javascript is not enabled, or Web Components are not supported, this will just fall back on showing the static number.

I included 3 parameters:

  • The last recorded download count (in the element value)
  • When that number was recorded (date)
  • Average number of downloads per day.

I wanted to include the date because I only intend to update these numbers rarely, so we need to know how many downloads have elapsed since the last time.

Writing the web component was surprisingly straightforward too. Here is it in its fully glory:

class DownloadCounter extends HTMLElement {

  connectedCallback() {
    this.count = +this.textContent;
    this.date = new Date(this.getAttribute('date'));
    this.inc = (+this.getAttribute('inc-per-day')) / (3600 * 24 * 1000)
    this.calculateCurrentDownloads();

  }

  calculateCurrentDownloads() {

    const currentDownloads =
      Math.floor(
      this.

Truncated by Planet PHP, read more at the original (another 20389 bytes)

Link
Christopher JonesOracle Application Continuity — for continuous availability (27.6.2024, 06:35 UTC)

Oracle Application Continuity — for continuous availability

Applications need to be resilient to infrastructure glitches. Oracle Database has powerful features that help your users get their jobs done, without them being aware of planned or unplanned database downtime.

Enjoying some “downtime”. Photo by Rapha Wilde on Unsplash

My colleagues Veronica Dumitriu and Nancy Ikeda previously posted a blog How to Make Application Continuity Most Effective in Oracle Database 23c covering AC (“Application Continuity”) and the related TAC (“Transparent Application Continuity”) features. These High Availability technologies are part of Oracle Database and Oracle Client. They allow applications to continue uninterrupted if there is an outage accessing one database node. They function by the driver client libraries recording the “work” that an application does. If the database instance becomes unavailable before all that “work” is committed to the database, the driver transparently connects to another database instance and replays all the recorded work, before letting the app continue as if there had been no outage. AC and TAC need a database that is configured with multiple instances, e.g RAC.

Veronica and Nancy’s post uses examples from the C language Oracle Call Interface API, which is one of the primary APIs for data access. Here I want to share some working notes about AC and TAC with python-oracledb, the Oracle Database driver for Python.

Before my sabbatical at the start of the year, I had been looking at python-oracledb with AC and TAC, in particular how to run the acchk’ tool to check application coverage. AC and TAC are implemented in Oracle Client libraries, and so are transparently available to python-oracledb apps when using Thick mode against Oracle Datase (on-premise or in the Cloud). AC and TAC are similarly available to other drivers like node-oracledb in Thick mode.

AC and TAC are primarily used for unplanned database outages. Applications that use an Oracle connection pool will handle planned downtime gracefully.

My working notes show how to enable AC and TAC on Oracle Autonomous Database and see what protection they give a sample application. As good as AC and TAC are, there are some things they can’t (and shouldn’t) recover.

You can use acchk to review which code paths in your app will be protected and what database work will be replayed by AC or TAC in the event the database instance currently in use has a failure.

A few general app suggestions:

  • Use python-oracledb Thick mode. This mode is a must.
  • Use a connection pool. Python-oracledb doesn’t (yet) expose the OCIRequestBegin() and OCIRequestEnd() functionality.
  • Use ORDER BY or GROUP BY in SQL statements
  • Set Connection.module and Connection.action
  • Use the Oracle Database 19.11+ acchck coverage report tool
     — This is the successor to Ora*CHK
     — Use it by enabling AC or TAC and then:
     — Use PL/SQL DBMS_APP_CONT_ADMIN package to enable recording
     — Run your app
     — Use DBMS_APP_CONT_ADMIN to get a coverage report

Testing acchk

I was testing using Oracle Autonomous Database. To enable acchk in an on-premise database, review your Oracle documentation.

Enable AC:

sqlplus -l admin@'tcps://adb.xxxx.oraclecloud.com:1522/xxxxx_cjdb_tpurgent.adb.oraclecloud.com?wallet_lo

Truncated by Planet PHP, read more at the original (another 1745 bytes)

Link
Rob AllenUsing Monolog's TestHandler (25.6.2024, 10:00 UTC)

When I write integration tests with PHPUnit, I find it helpful use Monolog's TestHandler to check that the logs I expect are generated.

It's reasonably common that classes that write to a log take a PSR\Log\LoggerInterface in their constructor and then use that for logging. This usually logs to the error handler for pushing to Sentry or whatnot. This makes testing easy!

For testing we can use the TestHandler which stores the logs it receives and then they can then be inspected.

We set up like this:

<?php

namespace Test\Integration;

use Monolog\Handler\TestHandler;
use Monolog\Logger;
use PHPUnit\Framework\TestCase;

class FooTest extends TestCase
{
    protected TestHandler $testHandler;
    protected Logger $logger;

    public function setUp(): void
    {
        $this->testHandler = new TestHandler();
        $this->logger = new Logger('test', [$this->testHandler]);
    }

    // tests here
}

Then we can use it in a test like this:

public function testFoo(): void
{
    $foo = new Foo($this->logger);

    $result = $foo->bar();

    $this->assertTrue($result);

    $this->assertTrue($this->testHandler->hasInfoThatContains('Created Baz'));
}

TestHandler has a number of methods that allow you to determine if a specific log has been written, along with getRecords(), clear(), etc to allow full inspection and control of the test log. Peruse the source for the class to see all the options, noting particularly the set of convenience methods in the DocBlock.

If your system under test uses a PSR-3 logger, then Monolog's TestHandler is a lovely way to test that your logs are as expected.

Link
Christopher JonesTechnical Briefs on VECTOR with python-oracledb and node-oracledb (21.6.2024, 21:42 UTC)

For those that like ‘official’ things, technical briefs Using Oracle Database 23ai Vectors in python-oracledb and Using Oracle Database vectors in node-oracledb discuss using the Oracle Database 23ai VECTOR data type with the respective database drivers.

Photo by TE LUN OU YANG on Unsplash
Link
LinksRSS 0.92   RDF 1.
Atom Feed   100% Popoon
PHP5 powered   PEAR
ButtonsPlanet PHP   Planet PHP
Planet PHP