I had the pleasure this week of implementing a simple script to connect to oracle, exact information and re-write it into another vendor format. It seems simple on the surface, of course once you figure out this mumble-jumble stuff. Unfortunately, the tns files were the easiest parts, the longest part was perfecting the query and getting data out of it. It's very much like the old mysql_* commands, except just using oci_* statements.

Setting up a simple connection what relatively easy, here's the boilerplate I ended up writing:

<?php
$conn = oci_connect(env('DB_USERNAME'), env('DB_PASSWORD'), env('DB_HOSTNAME'));

if($conn) {
    $oci_query = oci_parse("SELECT * FROM ALL_TABLES");
    while($row = oci_fetch_array($oci_query, OCI_ASSOC+OCI_RETURN_NULLS)) {
        foreach($row as $pkey => $data) {
            // we have all the data we need in here now, finally
        }
    }
    oci_free_statement($oci_query);
    oci_close($conn);
}

It seems relatively simple here, but this is a trimmed-down version of at least 30 or 40 iterations. It's not so much the code itself, it's more the query, especially when integrating third-party systems where you have no real visibility over the structure and they're unwilling to share, you must reverse engineer the entire stack using specially-crafted SQL queries and a little magic.

That's all for this dev log, my adventure dealing with Oracle Nightmares is not over yet.

devlog

Mike

Senior Software Engineer, Labber, Sysadmin. I make things scale rapidly. Optimize everything.

Read More