Blog Archives

Doctrine ORM with Oracle’s OFFSET and LIMIT

Posted on September 1, 2022
by

To handle LIMIT and OFFSET within Oracle DBs you have to use some magic. There is a Blog post, which describes the general procedure. Doctrine uses this ROWNUM stuff too, it is implemented within OraclePlatform.php. Doctrine needs two integer values for LIMIT and OFFSET. But you can also set both to NULL, the methods of the Query class (setMaxResults() and setFirstResult()) accept NULL values too.

If you think, it is a good idea to send  PHP_INT_MAX as default for LIMIT, it would be a fail. Within the OraclePlatform.php Doctrine must add the given OFFSET (maybe 20) to the LIMIT (maybe PHP_INT_MAX), so you will run into a datatype overflow. This will result into PHP_INT_MIN, a very large negative number. The resulting SQL would try to filter your ResultSet from OFFSET (20) to a large negative number, it would be empty always (except you will set the OFFSET to 0).

Also, if you set a large LIMIT to force getting all records from a query, you force Doctrine to wrap your query with some of the ROWNUM stuff, which results in a more complex query and an increased query time. Let LIMIT = null, if you need all records. Only set OFFSET > 0 (or != null), if you need the next page of the results.

 

Leave a Reply ?

Install OCI8 for PHP

Posted on January 12, 2017
by 
rpm -ivh oracle-instantclient-basic-linux.XXX.rpm 
rpm -ivh oracle-instantclient-devel-linux.XXX.rpm

yum install php-pear php-devel zlib zlib-devel bc libaio glibc 
yum groupinstall "Development Tools"

ln -s /usr/include/oracle/XXX/client64 /usr/include/oracle/XXX/client
ln -s /usr/lib/oracle/XXX/client64 /usr/lib/oracle/XXX/client
mkdir /opt/iclient

Create a file inside /etc/profile.d named oracle.sh and put this as the content:

export LD_LIBRARY_PATH=/usr/lib/oracle/XXX/client64/lib:${LD_LIBRARY_PATH}
export TNS_ADMIN=/opt/iclient
export NLS_LANG=GERMAN_GERMANY.UTF8
export SQLPATH=/usr/lib/oracle/XXX/client64/lib:${SQLPATH}

And run it so we’ll have LD_LIBRARY_PATH as an environment variable.

source /etc/profile.d/oracle.sh

Use ‘oci8’ to install for PHP 8.
Use ‘oci8-2.2.0’ to install for PHP7
Use ‘oci8-2.0.12’ to install for PHP 5.2 – PHP 5.6.
Use ‘oci8-1.4.10’ to install for PHP 4.3.9 – PHP 5.1.
The OCI8 extension can be linked with Oracle client libraries from Oracle Database 19.9, 12.2, 12.1, 11, or 10.2.

pear download pecl/oci8-2.0.12 
tar -xvf oci8-2.0.12.tgz 
cd oci8-2.0.12

phpize ./configure --with-oci8=shared,instantclient,/usr/lib/oracle/XXX/client64/lib
./configure --with-oci8=shared,instantclient,/usr/lib/oracle/XXX/client64/lib
make
make install

If you run into such an error:

error: oci8_dtrace_gen.h: No such file or directory

try to solve it with Stackoverflow:26145605.
To enable the extension, add a file named oci8.ini in /etc/php.d with this content:

extension=oci8.so

Restart Apache Webserver.

Copy your tnsnames.ora and sqlnet.ora to /opt/iclient.

Leave a Reply ?

FMX.Decompiler.1

Posted on November 11, 2015
by

Currently I work on a decompiler for .fmx files, which are compiled Oracle Forms applications. Such applications can be built by Oracle Form Builder, which stores .fmb files (uncompiled) and .fmx files (compiled).

Why I need that? We use Oracle Forms since a lot of years (Forms6 ++) and we have thousends of commits within our Subversion for our Forms applications. But for some old applications we only have the .fmx files and we cannot find the associated .fmb. The application can still be used, but it is not possible to enhance or bugfix it. To rescue such applications, it would be helpful to get the definitions from the .fmx.

Search

Google returns only posts, where people need such a decompiler too, but nobody has a solution. Seems to be undiscovered country. Or, Oracle suppress such a knowledge. Hm, seems to be a home improvement project.

Idea

FMX files are used by the Oracle Forms Runtime but also by an Java based client/server architecture. So I think, the .fmx files contain the description of the application, but no executable code for a specific platform. So it should be possible to decompile it and restore the former .fmb.

Show it in Hex

I have load a simple .fmx file into a hex-editor. Currently I use DHex. The file contains plaintext structures like names for windows, canvases, recordgroups and so on. So the first try confirms my presumption, there are descriptions and no executable code.

Leave a Reply ?