Retrieving database information in Oracle 12 April 2006 at 00:00
Here are some useful queries when you're looking at an Oracle database:

Getting Table Information

SELECT table_name, owner, tablespace_name FROM all_tables

Getting Column Information

SELECT column_name, table_name, data_type, data_length FROM user_tab_columns
Oracle text queries : basic full text searches 28 April 2006 at 00:00
Text queries are a very complex subject in Oracle, but briefly, the following is enough for a basic full text search. The example searches help text in a table called "help_topic" with the column "help_text".

Creating the index

CREATE INDEX help_text_idx ON help_topic (help_text) INDEXTYPE IS CTXSYS.CONTEXT;

Running queries against the index

Placing a "?" in front of each word in the query enables a fuzzy search (i.e. words close to the fuzzy word - "?dog" finds "doug", "dojo", "dogs", "dog", etc.)

-- OR query
SELECT SCORE(1) AS score, help_text from help_topic 
WHERE CONTAINS(help_text, '?princesses | ?dogs',1) > 0

-- AND query
SELECT SCORE(1) AS score, help_text from help_topic 
WHERE CONTAINS(help_text, '?cats & ?dogs', 1) > 0
-- phrase
SELECT SCORE(1) AS score, help_text from help_topic 
WHERE CONTAINS(help_text, '"some arbitrary text"', 1) > 0

Rebuilding the index

Whenever the underlying data changes you will need to rebuild your index, like so:


Resync the index

When you add or update records, this call is an extremely fast way of resyncing the index. This should only be called at the end of a transaction (not with every add/delete) and will result in fragmentation - you should optimize or rebuild your index regularly.


Optimising the index

This can be done with FAST, FULL, or TOKEN - FAST is obviously the quickest and compacts fragmented rows, but it does not remove old data.

C# method to help prevent SQL injection attacks 20 April 2006 at 00:00
This method is useful in checking input values for SQL injection attempts. There are far better solutions out there (ORMs, parameterised queries), etc, but is better than nothing, particularly if you want to just make quick fixes to existing code.

  /// <summary>
  /// This static method can be used to check input strings for SQL injection attacks.
  /// It does a basic check for INSERT, UPDATE, DELETE and TRUNCATE statements that 
  /// could be potentially inserted into form fields.  This should be used sparingly 
  /// as it does use regular expressions to evaluate the input.  If many fields are 
  /// being checked, it's a good idea to concatenate the values of those fields and 
  /// do a single check against them all.
  /// <p></p>
  /// This method does not return anything - it raises a <see cref="SqlInjectionException"/>
  /// exception if the string contains possibly damaging SQL - it is up to the calling 
  /// class to handle that exception.
  /// </summary>
  /// <param name="inputString">The input string to be checked for SQL injection attacks.</param>
  public static void CheckForSqlInjection(string inputString) 
    Regex regEx = new Regex(
      "UPDATE[s|t].*[d|w]+[s|t]SETs|" + 
      "DELETE[s|t]FROM[st].*[d|w]+|" +
      "INSERT[st]INTO[st].*[d|w]+|" + 
    if (regEx.IsMatch(inputString.ToUpper())) 
      throw new SqlInjectionException(inputString);