tag:blogger.com,1999:blog-85260141160285363342024-02-08T16:33:23.900+01:00SQL sands meditationthoughts about SQL, BI, Oracle, SQL Server, DB Administration and DWHMohamed Mehdihttp://www.blogger.com/profile/06628978704676040571noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-8526014116028536334.post-32402205914067029882012-05-14T13:31:00.003+01:002012-05-14T13:35:23.846+01:00Find the last update date of a recordSince 10g, Oracle provides a new pseudocolumn ORA_ROWSCN, For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated.<br />
SCN_TO_TIMESTAMP is a function used to convert the SCN to a readable date format.<br />
<pre class="brush: sql">
SELECT empno, ename, ora_rowscn, scn_to_timestamp(ora_rowscn)
FROM emp
WHERE ename = 'SMITH' ;
EMPNO ENAME ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
----- ---------- ---------- -----------------------------
7369 SMITH 239054665 14/05/12 15:26:07,000000000
</pre>
Note that when the SCN is too old, you could get the following error : <b>ORA-08181</b>: specified number is not a valid system change numberMohamed Mehdihttp://www.blogger.com/profile/06628978704676040571noreply@blogger.com0tag:blogger.com,1999:blog-8526014116028536334.post-10978428283248794742011-05-25T10:41:00.000+01:002012-05-14T11:48:46.842+01:00How to query on LONG datatype columnsYou can't search LONG data types columns directly, due to its restrictions, Oracle recommends that you convert existing LONG columns to LOB columns.<br />
<br />
In this example, I need to search all the mviews where query like '%foo%', since the column QUERY is a LONG, the first step is to re-create the Dictionary Data Table ALL_MVIEWS and convert using to_lob() function :<br />
<br />
<pre class="brush: sql">
CREATE TABLE all_mviews_clob AS SELECT mview_name, to_lob(query) AS query
FROM all_mviews ;
--now I am able to search on the QUERY column
SELECT * FROM all_mviews_clob WHERE upper(query) LIKE '%FOO%' ;
</pre>Mohamed Mehdihttp://www.blogger.com/profile/06628978704676040571noreply@blogger.com0