selecting record from oracle

To select last record, I was using this when my database was MySQL:

$result = mysql_query("SELECT Id FROM test ORDER BY LENGTH(Id), Id ASC"); $count = mysql_numrows($result); if($count != 0) { $lastid = mysql_result($result,$count-1,"Id"); }

...and it works fine. But now my table is in an Oracle database - I wrote:

$stid = oci_parse($conn, "SELECT Id FROM test ORDER BY LENGTH(Id), Id ASC"); oci_execute($stid); $p = oci_parse($conn, "SELECT COUNT(ID) FROM test"); oci_execute($p); $count = oci_fetch_array($p); if($count[0] != 0) { $lastid = oci_result($stid, $count[0]-1); }

It doesn't work - I am not getting the last record.


If you read the documentation for oci_result, you'll find you're using the second parameter incorrectly.

The second parameter is what you use to retrieve the column - it can be the column name, or the ordinal value. Ordinal value is a number, starting at one (1) based on the columns specified in the SELECT clause. Ordinals aren't a recommended practice, because if the query changes -- if you forget to update the ordinal reference your retrieval of values is screwed. Effectively, the subsequent COUNT query returns a value higher than the number of columns in the first queries SELECT clause.

$stid = oci_parse($conn, "SELECT Id FROM test ORDER BY LENGTH(Id), Id ASC"); oci_execute($stid); $lastid = oci_result($stid, 1);

oci_result lacks the third parameter that mysql_result provides.

To make things work in Oracle, use:

$stid = oci_parse($conn, "SELECT x.id FROM (SELECT Id FROM test ORDER BY LENGTH(Id) DESC, Id DESC) x WHERE ROWNUM = 1"); oci_execute($stid); $lastid = oci_result($stid, 1);

The updated query will return one row, the latest based on reversing the ORDER BY you were using previously.


And what are you using this for? If it is to determine the next Id, then <strong>don't do this</strong>, but use a sequence instead. If not then you can use select max(id) from testid to get the maximum value of the column.


First create a sequence to store the sequential number :

create sequence testid_seq;

Then when you insert a record, use

insert into testid(id, ...) values ("D111-" || testid_seq.nextval, ... );

And just fill in the dots with the info you want to insert.


  • Insert Data in Oracle DB using PHP
  • Handle a PL/SQL function returning a record in PHP
  • Using oci_parse and oci_execute
  • oci_bind_by_name not working in PHP
  • unable to obtain stable firefox connection in 60 seconds
  • Execute Success but num_rows return 0 [PHP-MySQL]
  • How to export MS Access table into a csv file in Python using e.g. pypyodbc
  • Displaying Data From Multiple MySQL Tables
  • Converting query results into DataFrame in python
  • Android fill_parent issue
  • FileReader+canvas image loading problem
  • Pass value from viewmodel to script in zk
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Counter field in MS Access, how to generate?
  • Finding past revisions of files in StarTeam w/ .NET SDK / C#
  • Java applet as stand-alone Windows application?
  • Incrementing object id automatically JS constructor (static method and variable)
  • Javascript Callbacks with Object constructor
  • How to check if every primary key value is being referenced as foreign key in another table
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • jquery mobile loadPage not working
  • Do create extension work in single-user mode in postgres?
  • Jquery - Jquery Wysiwyg return html as a string
  • Return words with double consecutive letters
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • How to delete a row from a dynamic generate table using jquery?
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • json Serialization in asp
  • SQL merge duplicate rows and join values that are different
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How to stop GridView from loading again when I press back button?
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • Reading document lines to the user (python)
  • How to load view controller without button in storyboard?