Oracle PL/SQL's maximum identifier length

I wanted to call it GetTransactionTemplateCountForUser. Oracle has decided that's not a good idea:

Error: PLS-00114: identifier 'GETTRANSACTIONTEMPLATECOUNTFOR' too long

I guess 30 characters ought to be enough for anyone, right?

Everything about dynamic search conditions in T-SQL

There are several common methods for using dynamic search conditions (a.k.a. optional parameters) in SQL. By dynamic search conditions, I am talking about passing through several criteria to a stored procedure or similar, but only filtering by the criteria that have values specified (i.e. are not null).

To illustrate this, here is one common method:

SELECT *
FROM Orders
WHERE
 (@productName IS NULL OR Orders.ProductName LIKE @productName + '%')
 AND
 (@orderDate IS NULL OR Orders.DatePlaced = @orderDate)

This will let you filter the orders by product name, the date placed, or return all results unfiltered. Unfortunately SQL Server can't figure out how to use an index for this situation, so you end up with full table scans and the subsequent performance hit (which may or may not be a problem for you depending on things like your table size).

There are several variations on this theme, including using COALESCE (note retraction at top of that post), or inverting the conditions (using (@productName IS NOT NULL AND Orders.ProductName=@productName) OR ...) which can help by calculating the filter earlier and potentially allowing index scans. Another option is to use dynamic SQL to build up the appropriate condition, but that way is fraught with its own perils.

Erland Sommarskog's article, Dynamic Search Conditions in T-SQL, covers everything you ever wanted to know about this topic, but were afraid to ask. It really is a must read for anyone working with SQL (unless you've given up on the whole structured query language thing and are holding out for LINQ-to-SQL ;)).

Guard against SQL injection in dynamic PL/SQL

I am slowly coming to terms with Oracle again, after a decade or so of using SQL Server exclusively. One way to guard again SQL injection in SQL Server is to use parameterisation and sp_executesql.

From Raul Garcia's example:

  declare @cmd nvarchar(max)
  declare @parameters nvarchar(max)
  set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'
  set @parameters = '@name sysname'
  EXEC sp_executesql @cmd, @parameters, @name = @name 

To do this parameterisation in Oracle (I am using 10g) you can use bind arguments, as per this code snippet:

  l_username := 'dav';
  OPEN l_cursor FOR
    'SELECT *
     FROM myTable t
     WHERE 
       t.created_by LIKE :username || ''%'''
     USING l_username; 
  -- fetch from, then close cursor --

Here :username is bound to the value in l_username via the USING clause, so no tricky '''; DROP TABLE myTable; --' style nonsense here, thanks very much :).

Rob Conery on text mining and analysis

Rob has posted part 1 of a series on mining unstructured data. He goes through some basics on ETL (Extraction, Transformation, Loading) and natural language parsing, implements a text miner using LinqToSql, and analyses the results. For the next part we are promised some data warehousing tricks, more analysis techniques, and some OLAP with Excel. It has been an interesting read for me as I am pretty unfamiliar with this area of IT.

Information Schema views in SQL Server 2005

I keep forgetting the exact syntax for this and have to do some hit-and-miss with Books On-Line.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Employee' ORDER BY ORDINAL_POSITION

See the MSDN article for more information. It is generally easier using these views than mucking about with sysobjects, syscomments etc.

Execution context for SPs using dynamic SQL in SQL Server 2005

This book excerpt discusses specifying execution context for stored procedure code, which allows dynamic SQL in an SP to execute as the SP owner (among other contexts). The basic syntax is:

CREATE PROCEDURE dbo.usp_DoSomething( @someParameter nvarchar(10) ) WITH EXECUTE AS OWNER AS ...

Where OWNER can be a number of different values. Obviously this does not in itself provide any security (in fact, it allows you to circumvent it by elevating privileges), but it does allow you to build some on top of this. The caller's account can be denied SELECT access on a certain table, but can still run the SP as the owner to get back a controller result from a SELECT operation, just as in static SQL.

Potential damage from SQL injection can be minimised by having the owner (or the context in which the SP is executing) only having SELECT access (and therefore unable to inject fun things like DROP TABLE, for example). Of course, this is no excuse for not exercising appropriate levels of caution when using dynamic SQL :-)

At work we found this approach useful for simplifying complex static queries by building more efficient dynamic SQL, but still using static SQL-style security and not requiring the caller to have SELECT access on all the referenced tables.

SQL Prompt final beta

David Hayden has noted the final beta release of RedGate's SQL Prompt 3 (formerly PromptSQL prior to being acquired by RedGate). I ditched an earlier version of this product after it consistently had speed issues, was annoyingly invasive when typing and troubles intellisensing after upgrading to SQL Server 2005. It worked great with SQL Server 2000 though, so it was time for me to give this another go.

My initial reaction is that it is a vastly improved product. Performance even for remote databases over the WAN was very fast. The release notes list a number of additional tweaks you can use to boost performance. It is easy to customise how it interacts with the editor in terms of when the auto-complete features appear and how they are selected.

The UI is nicer too - the auto-complete popup is sorted into categories that can be switched between using CTRL and the left and right arrows. This allows the auto-complete list to be quickly filtered to show a subset of all completions (such as only tables, views, SPs, etc.). By default it suggests a number of generally sensible completions based on the current context. A very nice feature is the column picker category - it lists all the columns available in the current query along with checkboxes. This is fantastic for writing long SELECT lists and ORDER BY clauses. Very nice so far! I'll definitely keep it installed and see how it stacks up to longer term use.