Category: Oracle

PHP oci_bind_by_name Caveat (a.k.a. a reminder to read the documentation)

This is readily apparent when you actually read the documentation for oci_bind_by_name … but, if you quickly skim through the doc and get mostly what it’s telling you, you can lose data when implementing oci_bind_by_name in your code. That’s because the variable that gets bound into the name is a reference. It’s right there in the description

And again in the more verbose part of the description

Well? They really mean It!

I usually iterate through arrays with for $x=0; $x<count($arrayOfData);$x++ — which is fine because $arrayOfData[$x] is a valid location in memory that contains the data I want. I had a program, though, that accepted user input. Input that frequently contains a bunch of extraneous newline characters in the middle of the data. To avoid making the users clean up their input, I just threw an array_filter on the input. But I don’t need the reorganized data, so I didn’t “waste” the cycles to re-index the filtered array. Instead, I used

$iIterator=0;
for( $itemInput in array_filter( $arrayOfData ) ) {
     oci_bind_by_name($stmt, ‘:placeholder’.$iIterator++, $itemInput);
}

Which is fine in most circumstances. But, with oci_bind_by_name … the memory address where $inputItem happened to be stashed is bound to the names :placeholder0, :placeholder1, …, :placeholdern – that memory address happened to still contain the last element of arrayOfData I happened to stash there because the server isn’t so busy that memory is being reallocated a fraction of a second later. But this loop does not bind each element of arrayOfData to its corresponding placeholder — instead of saying “select * from MyTable where ID IN (1,2,3,4,5)” … I had “select * from MyTable where ID IN (5,5,5,5,5)”.

In this case you need to use the array index

for( $x = 0; $x < count( $arrayOfData ); $x++ ) {
     $strPlaceholderName = ":inputclli".$x;
     oci_bind_by_name($stmt, $strPlaceholderName, $arrayOfData[$x]);
}

So each unique memory address is bound to a placeholder.

Oracle Collection Instead of Dual

I’m still retrofitting a bunch of SQL queries to use bind_by_name and came across a strange scenario. I created a recursive query (STARTS WITH / CONNECT BY PRIOR) but I needed to grab the original value too. The quickest way to accomplish this was to union in something like “select 12345CDE as equipment_id from dual”. But the only way to get a bunch of these original values grafted onto the result set is to iterate through the array once to build my :placeholder1, :placeholder2, …, placeholderN placeholders and then iterate through the array again to bind each placeholder to its proper value.

I’ve been working with Oracle collections for LIKE and IN queries, and thought I could use a table that only exists within the query to glom the entire array into a single placeholder. It works! A query like

select column_value equipment_id from TABLE(sys.ODCIVARCHAR2LIST('12345CDE', '23456BCD', '34567ABC') );

Adds each of the values to my result set.

Which means I can use a query like “select column_value as equipment_id from TABLE(:myIDs)” and bind the collection to :myIDs.

Oracle – Collections and IN or LIKE Queries

I’ve been retrofitting a lot of PHP/SQL queries to use oci_bind_by_name recently. When using “IN” clauses, you can iterate through your array twice, but it’s an inefficient approach.

// Build the query string with a bunch of placeholders
$strQuery = "select Col1, Col2, Col3 from TableName where ColName IN (";
for($i=0; $i < count($array); $i++){
    if($i > 0){
        $strQuery = $strQuery . ", ";
    }
    $strQuery = $strQuery . ":bindvar" . $i;
}
$strQuery = $strQuery . ")";
... 
// Then bind each placeholder to something
for($i=0; $i < count($array); $i++){
    oci_bind_by_name($stmt, ":bindvar".$i, $array[$i]);
}

Building a table from the array data and using an Oracle collection object creates cleaner code and avoids a second iteration of the array:

$strQuery = "SELECT indexID, objName FROM table WHERE objName in (SELECT column_value FROM table(:myIds))";
$stmt = oci_parse($conn, $strQuery);

$coll = oci_new_collection($kpiprd_conn, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayValues as $strValue) {
     $coll->append($strValue);
}
oci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);
oci_set_prefetch($stmt, 300);
oci_execute($stmt);

A simple like clause is quite straight-forward

$strNameLikeString = "SomeName%";
$strQuery = "SELECT ds_dvrsty_set_nm from ds_dvrsty_set WHERE ds_dvrsty_set_nm LIKE :divsetnm ORDER BY ds_dvrsty_set_nm DESC fetch first 1 row only";

$stmt = oci_parse($connDB, $strQuery);
oci_bind_by_name($stmt, ":divsetnm", $strNameLikeString);
oci_set_prefetch($stmt, 300);
oci_execute($stmt);

But what about an array of inputs essentially reproducing the LIKE ANY predicate in PostgreSQL? There’s not a direct equivalent in Oracle, and iterating through the array twice to build out a query WHERE (Field1 LIKE ‘Thing1%’ OR Field1 LIKE ‘Thing2%’ OR Field1 LIKE ‘Thing3%’) is undesirable. The with EXISTS allows me to create a LIKE ANY type query and only iterate through my array once to bind variables to placeholders using the same collection approach as was used with the IN clause. 

$arrayLocs = array('ERIEPAXE%', 'HNCKOHXA%', 'LTRKARXK%');
$strQuery = "SELECT location_id, clli_code FROM network_location WHERE EXISTS (select 1 FROM TABLE(:likelocs) WHERE clli_code LIKE column_value)";
$stmt = oci_parse($connDB, $strQuery);

$coll = oci_new_collection($connDB, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayLocs as $strLocation) {
    $coll->append($strLocation);
}
oci_bind_by_name($stmt, ':likelocs', $coll, -1, OCI_B_NTY);
oci_execute($stmt);
print "<table>\n";
print "<tr><th>Loc ID</th><th>CLLI</th></tr>\n";
while ($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print "<tr><td>" . $row['LOCATION_ID'] . "</td><td>" . $row['CLLI_CODE'] . "</td></tr>\n";
}
print "</table>\n";

There are many different collection types in Oracle which can be used with oci_new_collection. A full list of the system collection types can be queried from the database. 

SELECT * FROM SYS.ALL_TYPES WHERE TYPECODE = 'COLLECTION' and OWNER = 'SYS';

Oracle Function – Keeping Null Records With LISTAGG

I have been using LISTAGG to group a bunch of records together to be presented in a single HTML table cell. Problem is LISTAGG doesn’t do anything with null field values. As such, the data doesn’t line up across columns. The three ID values have two string values, which basically get centered in the cell. You cannot tell which ID value goes to which name value.

By adding a concatenation to the LISTAGG value, something will be included in the result set even when the record value is null.

Voila — records line up and I can tell the first ID doesn’t have an associated string value.

Oracle: Query Timestamp Today

I wanted a quick way to query a logging table for today’s records. I figured there had to be some way to put a variable “today” into the query rather than put in the numbers for the current date. Voila — a query that retrieves records where the timestamp is greater than or equal to today:

select *
from ossa_central_logging
where action_ts >= cast(trunc(current_timestamp) as timestamp)
order by action_ts desc;

Oracle – LISTAGG

I needed to collapse multiple rows into a single row — the circuits within a diversity set are stored within the ds_dvrsty_set_circuit table as individual rows & the ds_dvrsty_set_id links the multiple rows. What I wanted was a set ID, set name, and the list of circuits within the set.

To accomplish this, I found LISTAGG which is a little bit like STUFF in MSSQL. This query produces a single row for each diversity set that contains the set ID, the set name, and a comma delimited list of set members.

SELECT
     ds_dvrsty_set_circuit.ds_dvrsty_set_id,
     (select ds_dvrsty_set.ds_dvrsty_set_nm from ds_dvrsty_set where ds_dvrsty_set_id = ds_dvrsty_set_circuit.ds_dvrsty_set_id) as set_name,
     LISTAGG(ds_dvrsty_set_circuit.circuit_design_id,  ',') WITHIN GROUP(ORDER BY ds_dvrsty_set_circuit.ds_dvrsty_set_id) AS member_circuits
FROM
     ds_dvrsty_set_circuit
     left outer join ds_dvrsty_set on ds_dvrsty_set.ds_dvrsty_set_id = ds_dvrsty_set_circuit.DS_DVRSTY_SET_ID
WHERE
     ds_dvrsty_set_circuit.ds_dvrsty_set_id in (select distinct ds_dvrsty_set_id from ds_dvrsty_set_circuit where circuit_design_id in (14445678, 5078901) )
AND
     ds_dvrsty_set.ds_dvrsty_set_nm like '43%'
GROUP BY
     ds_dvrsty_set_circuit.ds_dvrsty_set_id
ORDER BY
     ds_dvrsty_set_circuit.ds_dvrsty_set_id;

Voila — exactly what I needed. If the searched circuit design IDs appear in more than one set, there is a new row for each set ID.

Oracle – Select Top

I discovered the “rownum” trick early in my usage of Oracle databases — especially useful for sampling data to see what’s in there, something like “select * from dataTable where rownum < 6” gets you the first five records. But that’s not suitable if you want to sort the records. In this particular case, I have a series of names. I want to find the highest number value in the series so I can name my object with the next sequential name.

Enter “fetch first”  … this appears to be available since 12c (so older database installations may still require a more convoluted solution):

SELECT set_name from set_data
WHERE set_name LIKE 'Something-With-A-Series-%'
ORDER BY set_name DESC
fetch first 1 row only;

Which returns the last name in the series.