It is becoming more common that I find myself surprised by self-proclaimed, seasoned SQL developers who do not even understand some of the basic power that is possible with an Oracle SQL SELECT statement. I am not talking about some of the more complex analytical functions; I have seen SQL developers lost when it comes to simple subselect statements (a “SELECT” inside a “SELECT”). Most people understand that they can nest a SELECT statement inside the WHERE clause of another SELECT statement, but many do not realize that it is possible inside the FROM clause (perhaps, my most common use) … Read More
Copy Table Columns and Data
As you may know, in Oracle the syntax from creating a table that is the same as another is: 1 CREATE NEWTABLE AS SELECT * FROM TABLEcreate NEWTABLE as select * from TABLE In SQL Server, the syntax is slightly different: 1 SELECT * INTO NEWTABLE FROM TABLEselect * into NEWTABLE from TABLE Note: This will not copy indexes, constraints, triggers, or other attributes of the table. It will copy the column names, data type, and data only.
Oracle Books for Developers
Following are some of the best Oracle books for developers. The only thing that gets DBA’s more upset than making a mistake that compromises a production database is when a developer makes the mistake. Even if it is merely culture-driven, developers need to be on top of their game when it comes to database-reliant enterprise applications. It only makes you that much better. Here are some good places to start: Cost-Based Oracle Fundamentals – Jonathan Lewis Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions – Thomas Kyte Effective Oracle by Design – Thomas Kyte
Oracle Materialized View Manual Refresh
Following is the call that can be made to refresh your materialized view in Oracle manually: 1 EXECUTE DBMS_SNAPSHOT.REFRESH('[MV_NAME]’,'[refresh_option]’);execute DBMS_SNAPSHOT.REFRESH(‘[MV_NAME]’,'[refresh_option]’); Refresh Options: ? – Uses default F or f – Fast C or c – Complete A – Always complete
Oracle Materialized View DDL
Are you a TOAD fanatic when it comes to “anything-Oracle”? If so, you probably realized that TOAD will not show you the DDL for a materialized view in any of the tabs in the schema browser. The “Scripts” tab will look similar to a table’s script. Following is an SQL statement you can run to find out the SQL used by the materialized view: 1 SELECT query FROM user_mviews WHERE mview_name='[MV_NAME]’;select query from user_mviews where mview_name='[MV_NAME]’; Enjoy!
TOAD SQL Editor Useful Shortcuts
SHIFT-F9
This will run only the SQL that your cursor is on. You do not need to highlight the line or the entire SQL statement; simply place your cursor anywhere inside the SQL statement and click SHIFT-F9.
CTRL-F9
This will verify (parse) the statement without executing it.
CTRL-E
This will execute an explain plan on the current statement.
F8
This will bring up your previous SQL statement. If you use SHIFT-F9 and are disciplined about keeping all iterations of executed statements in the screen…
Oracle Autoincrement
Unlike Oracle, other less expensive and less robust databases make autoincrementing a simple task…typically a couple of clicks in some easy to understand UI. Some examples are with SQL Server, MySQL, and Access. As we know, with Oracle’s additional “flexibility” can come additional work (but this is sometimes for a legitimate reason). Approach 1 Following are the steps on how you can mimic an autoincrement in Oracle: 1) create a table 2) create a sequence 3) create a trigger 4) insert record(s) CREATE TABLE test_table ( test_id INT PRIMARY KEY, test_col varchar2(100)); CREATE SEQUENCE test_seq; CREATE TRIGGER test_trigger … Read More