You created a record(Emp/Cust .. etc) from Standard UI for you application
which would have gone to a Database table. You know the table name. But how to fetch
the record you created??..
Well, Seems like very basic... It is if you have the ID of the record (But usually UIs doesn't show the ID), You may query with the Emp Name (or Cust Name) which you created. But If there are no index on Emp Name or Cust Name column, than your query might take years to fetch you a result.
In most of the cases DB Table have creation_date columns, Which can be used like
SELECT * FROM XXX_EMPLOYEE WHERE CREATION_DATE > SYSDATE - 1/12
(1/12 day = 1 hour , So this query will fetch all records created in last one hour).
But if there are no Index on Creation_date column and XXX_EMPOLOYEE table has huge records, this query will also take hours to execute...
An Alternative approach
Step 1 : Find the DB sequence which is used to populate ID column in this table.
The sequence is generally named as the table itself.
SELECT * FROM all_sequences WHERE sequence_name LIKE 'OE_ORDER_HEADERS%'
SEQUENCE_NAME
--------------
OE_ORDER_HEADERS_S
So for a table name OE_ORDER_HEADERS, the Sequence is found to be OE_ORDER_HEADERS_S
(In fact if you are using Oracle Applications, This is the standard
STEP2- Get the sequence NEXTVAL
Now once we have the sequence used for the table,Lets get the value sequence generates.
SELECT OE_ORDER_HEADERS_S.NEXTVAL from DUAL;
NEXTVAL
-------
22450141
STEP3- Since you created a record from UI (Or from Other Apps) in past few hours. Not much sequence should have been used after that. So lets try to query table with ID column having a value 1000 less than sequence. Also now we can join what all information(creation_date, employee_name etc) we have, to filter to exact record.
SELECT * FROM OE_ORDER_HEADERS where header_id > (22450141-1000) and creation_date > sysdate - 1/24
This should return the record(s) which you are looking for and should not take more than a few seconds.
I find this way really handy, esp while working with Oracle Applications
