Oracle SQL Programming Tips

I am going to share a few little I use for day-to-day SQL programming tasks

Build ADO database connection strings using UDL files

• A little known and documented feature in Windows (at least I haven’t had much luck finding loads of documentation on it) is the Universal Data Link connection files. These create the really confusing ConnectionStrings used in ADODB.Connection.ConnectionString properties. The easiest way to create one of these is to right-mouse click on the Windows Desktop, go to New, and choose Text File (Note: Microsoft’s knowledge base states that when you click on New, the “Microsoft Data Link” should be visible. It is not in my case). When the file is created, change the file extension from .TXT to .UDL. When you want to create a ConnectionString, double-click the new UDL file. It will open the Data Link Properties Wizard. Set your provider, connection information and you are set. All you need to do to get that connection string is open the file in Notepad (or your editor of choice), copy that string, and paste it into your ConnectionString assignment. Alternatively, you can also set the ConnectionString property to point directly to the UDL file itself and avoid hard coding the string into your program. See Microsoft Knowledge Base article Q189680 for more info.

SQL Code Beautifier

One pet peeve of mine is code formatting. If your working with SQL, there is a nice, freely available SQL code beautifier available for when someone sends you their horribly formatted 100 line SQL statement in one line, or if you need to do a quick format of SQL to put into a program, etc. The SQL Code Beautifier is available here. (Note: Java is required)

Query optimization in Oracle

• Working with Oracle can be a daunting task, especially if you are not familiar with Oracles Cost-Based Optimizer. If you have queries in your application that you suspect might be causing issues, use Oracle Query Analyzer from SqlPlus to show if your query needs a little tweaking. The syntax for Oracle 8i and above is (it may work in earlier versions):
  
  set autotrace traceonly explain
  
  There is one prerequisite. You must have access to a Plan_Table with write permissions either through a synonym, or in your local schema. The Plan_Table definition I use is from “Oracle 8i DBA Bible” from IDG Books (although some of these fields are unnecessary). The table looks like so:
  
  STATEMENT_ID - VARCHAR2(30)
  TIMESTAMP - DATE
  REMARKS - VARCHAR2(80)
  OPERATION - VARCHAR2(30)
  OPTIONS - VARCHAR2(30)
  OBJECT_NODE - VARCHAR2(128)
  OBJECT_OWNER - VARCHAR2(30)
  OBJECT_NAME - VARCHAR2(30)
  OBJECT_INSTANCE - NUMBER
  OBJECT_TYPE - VARCHAR2(30)
  OPTIMIZER - VARCHAR2(255)
  SEARCH_COLUMNS - NUMBER
  ID - NUMBER
  PARENT_ID - NUMBER
  POSITION - NUMBER
  COST - NUMBER
  CARDINALITY - NUMBER
  BYTES - NUMBER
  OTHER_TAG - VARCHAR2(255)
  OTHER - LONG
  PARTITION_START - VARCHAR2(255)
  PARTITION_STOP - VARCHAR2(255)
  PARTITION_ID - NUMBER(38)
  DISTRIBUTION - VARCHAR2(30)
  
  Now, when you run a query, it will not actually execute the query, and will display Oracles execution plan. The execution plan relies very heavily on table statistics, so if you are getting full table scans; work with your DBA to optimize tables for the queries you are using and update the table and index statistics. A good reference for SQL tuning is “Oracle SQL Tuning Pocket Reference” from O’Reilly.
  

Use SQL Loader for data loads

• Unfourtunatly sending large delimited text files is still a widely used practice for data transfers. If you are using Oracle, there is a way to make loading these files into a table much easier than using a proprietary method. Oracle provides a command line utility called SqlLdr (SQL Loader) for loading in flat text files. Using a control file, you tell which delimited fields to into which the correct fields in your target table. Since SQL Loader can be a difficult program, I will refer you to the following links on SQL Loader, they do a far better job explaining than I could, and they contain the source for a SQL script to create the control file:


• http://www.oracleutilities.com/OSUtil/sqlldr.html


• http://www.orafaq.com/faqloadr.htm