ORACLE: Limit Returned records with rownum

The following query will get the latest entered_on date from the result table.   — This select will get the latest result entered_on date — to check after a refresh what’s the latest data — SELECT * FROM ( your_query_here ) WHERE ROWNUM <= N. select * from (select entered_on from result where trim(entered_on) is […]

ORACLE: Show Statistic Updates

The following Oracle SQL command allows you to see the statistics information, grouping by the last analyzed date (the is the name of schema you need to substitute into the SQL query: select to_char(last_analyzed,’MON-DD-YYYY’), count(*) from dba_tables where owner=” group by to_char(last_analyzed,’MON-DD-YYYY’) order by to_char(last_analyzed,’MON-DD-YYYY’); The output looks like this: TO_CHAR(LAST_ANALYZED,’MON-DD-YYYY’) —————————————————————————— COUNT(*) ———- FEB-01-2016 […]

ORACLE: drop a table if it already exist

Oracle does not have a simple if table exists, drop it command like in MySQL. However, you can use this piece of code in your Oracle pSQL code to achieve the same thing: catching the exception ORA-942 table not exist. — drop tables if exists TEST_TABLE BEGIN EXECUTE IMMEDIATE ‘DROP TABLE TEST_TABLE CASCADE CONSTRAINTS’; EXCEPTION […]

person Jason Huangaccess_time May 5, 2015launch Read More

Oracle SQL: Escape in LIKE clause

The underscore in oracle SQL, when it’s used in LIKE clause represent a one character wild card. For example: like ‘V_KAA’ (returns VaKAA, VbKAA) But you want to find something like V_KAAA (including a under score_), you would need to escape the _ character in like clause. For example: select * from table where ID […]

Global System View

you can easily use these global views to get specific oracle system information: select * from [VIEW name] ALL_ALL_TABLES: shows all tables even with DBA system tables USER_ALL_TABLES : shows all tables available to user DBA_ALL_TABLES : all dba objects and system tables The view USER_TAB_COLUMNS lists all the columns detail information on the all […]