FORMS_BUILDER_CLASSPATH not set after Installation of Oracle Forms Builder

September 22, 2010

To add Forms 11 support to my application Forms Toolkit, I needed to install Oracle WebLogic and Oracle Developer Tools (see download information).

While the installer reported “installation complete” after a couple of minutes, it really spent about 5 hours (on my machine) installing and configuring the software. It’s best to watch the installation log file using a tool like BareTail, because the installer won’t give you much information whether it is still active and what it is doing.

After completion, I tried to start Oracle Forms Builder, but got this error message:

FRM-91129: fatal error in runtime process: no value specified for required environment variable FORMS_BUILDER_CLASSPATH

FRM-91129: Abbruchfehler: Kein Wert für erforderliche Umgebungsvariable FORMS_BUILDER_CLASSPATH angegeben

Forms 11 does not seem to be administered by earlier versions of the Oracle Home Selector, so I had to look for some clues where Forms would get its startup information from.

Open the Registry Editor (regedit.exe) and navigate to HKLM\SOFTWARE\ORACLE\ and find a number of subkeys starting with “KEY_OH”. Navigate to the key holding values for ORACLE_HOME and ORACLE_HOME_KEY that point to your installation directory.

In Explorer, navigate to the forms\templates\registry directory of your installation (C:\oracle\Middleware\as_1\forms\templates\registry in my case), and open the file formsbuilder.reg in your favorite editor and save under a different name (e.g. my_formsbuilder.reg).

Delete all lines between “REGEDIT4” and the first line containing the %ORACLE_HOME_REG_KEY% macro. The file now looks like this:





Replace all occurrences of %ORACLE_HOME_REG_KEY% with the value of the registry key ORACLE_HOME_KEY, e.g.


Save the file, and double-click from Explorer. Acknowledge the warning that the .reg file will be merged into the registry, and the successful merge.

You can now start Oracle Forms Builder.

Note: this worked on my machine, and may not necessarily solve your problem. However, the procedure only adds 5 known keys to the registry, so it can be revoked easily.


Calculating the Length of the Longest Common Subsequence in TSQL

September 16, 2010

When trying to calculate the similarity of strings, the Levenshtein Distance comes up as one way to solve this problem, as it counts the number of additions, deletions and substitutions of characters to transform one string into the other.

The inverse solution is to count the number of characters that are the same in both strings in the same order, known as the Longest Common Subsequence problem (LCS).

The code presented here uses the same technique as the Levenshtein algorithm in storing a two-dimensional array of integers as an NVARCHAR(MAX), i.e. a string of Unicode characters encoding the integers in a matrix.

First, we need a function Max2() to retrieve the maximum of two integers:

CREATE FUNCTION [dbo].[Max2](@a int, @b int)
  IF @a > @b
    RETURN @a

and then the T-SQL version of the algorithm:

    @s_i NCHAR(1), @t_j NCHAR(1)

  SET @n = LEN(@s)
  IF @n = 0 RETURN 0

  SET @m = LEN(@t)
  IF @m = 0 RETURN 0

  SET @d = REPLICATE(NCHAR(0),(@n+1)*(@m+1))

  SET @i = 1
  WHILE @i <= @n BEGIN
    SET @s_i = SUBSTRING(@s,@i,1)

    SET @j = 1
    WHILE @j <= @m BEGIN
      SET @t_j = SUBSTRING(@t,@j,1)

      IF @s_i = @t_j

        SET @d = STUFF(@d,@j*(@n+1)+@i+1,1,
            SUBSTRING(@d, (@j-1)*(@n+1)+@i-1+1, 1)


        SET @d = STUFF(@d,@j*(@n+1)+@i+1,1,NCHAR(dbo.Max2(

      SET @j = @j+1
    SET @i = @i+1

  SET @LD = UNICODE(SUBSTRING(@d,@n*(@m+1)+@m+1,1))

dbscript 1.03 released

September 15, 2010

dbscript, the integrated database versioning, documentation and code generation web application, has finally been migrated to .Net 3.5 (SP 1).

This makes it easier to install, as the separate installation of ASP.Net Ajax libraries is not necessary anymore.

The draw-back is that it is no longer possible to run the web application on Windows 2000 (Server), if that is a concern to anybody.

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
HTML (single file) view view view view
MediaWiki view view view view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view

dbscript is available for download here.

Solving 2 ASP.Net Run-Time Errors

September 15, 2010

Sys.Extended is undefined

Firefox Web Developer Toolbar shows the error

Sys.Extended is undefined

If you click Information, View JavaScript, browse through the code and you may find the section for a JavaScript file called


(your codes may differ), containing an error message from ASP.Net that a directory cannot be found.

Solution: add the missing directory, even if it is empty.

Could not load type ‘System.Web.UI.ScriptReferenceBase’

The browser displays the error message:

Error Message: Could not load type ‘System.Web.UI.ScriptReferenceBase’ from assembly ‘System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35’.

Der Typ “System.Web.UI.ScriptReferenceBase” in der Assembly “System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35” konnte nicht geladen werden.

Solution: Install .Net Framework 3.5 Service Pack 1

Third Anniversary

September 13, 2010

12 month summary:

Of course, there were a couple of releases of dbscript, my database versioning, documentation, and code generation application. Other applications such as SMOscript and oraddlscript were also updated for bug fixes or enhanced functionality.

I wrote wpxslgui, a tool to convert WordPress XML Exports into HTML and Word HTML. YuJisho is a CJK character web dictionary based on freely available dictionary data with a simple user interface and fast responses (below 1 second). checktsql is a small tool to check all T-SQL objects in a MS SQL Server database for validity and reports error caused by referencing missing objects.

Projects I worked on using Entity Framework and NHibernate also generated a couple of entries.

The raw numbers: 162,000 views, 285 posts, 4000 downloads.

Searching TSQL Stored Procedures (and other Modules)

September 10, 2010

Every now and then I come across the question, “How do I search my stored procedures for a certain table or column name in MS SQL Server?”. You might have, too…

SQL Server up to version 2000 provided the view syscomments, which is still implemented in versions 2005 and higher as sys.syscomments. (All system catalog views have been moved to the sys schema in 2005)

The major drawback with syscomments is that its text column containing the stored procedure code is defined as NVARCHAR(4000), the longest possible string value in SQL Server 2000 (apart from NTEXT). So if the procedure (or function) code is longer than 4000 characters, the code is sliced into several 4000 character records. And as it happens, the string you are searching for may end up right at the boundary of two records, complicating the search algorithm.

SQL Server 2005 introduced the sys.sql_modules view which has a Definition NVARCHAR(MAX) column holding code, thus resolving this issue of split code. (check the remarks if you want to search DEFAULT and CHECK expressions, too)

So now that you happily accepted that search sql_modules is better than searching syscomments, here is the code to perform the search in TSQL:


SET @Search = 'MyOldColumnName'

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, -- o.type,
       o.type_desc   -- , sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition LIKE N'%' + @Search + N'%'
ORDER BY o.type, OBJECT_NAME(sm.object_id);

As you see, the search is executed using the LIKE operation. But what if you want to search for wildcards as literals, such as _ and %, or the regex characters [, ], –  (because, for example, your database object names may contain underscores) ?

Simply escape the characters using the [] notation:

SET @Search = REPLACE(@Search, '[', '[[]')
SET @Search = REPLACE(@Search, '%', '[%]')
SET @Search = REPLACE(@Search, '_', '[_]')

and run the query after the REPLACE operations.

But what if you want to search for whole words only? Use PATINDEX using a regex which excludes alphabetical characters and underscores before and after the search string:

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, 
    o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE PATINDEX(N'%[^a-zA-Z_]' + @Search + N'[^a-zA-Z_]%', sm.definition) > 0
ORDER BY o.type, OBJECT_NAME(sm.object_id);

Preparing web.config for Deployment using PowerShell

September 7, 2010

After you build your Web Project in Visual Studio, it would be nice to automatically process the developer’s web.config to remove any sensitive information, such as login credentials, and other information that sits in the config file for debugging or testing purposes.

As described in the previous entry, the build batch generates a compiled web application, and renames the web.config to web.publish.config, to avoid accidentally overwriting an existing web.config during an upgrade.

This PowerShell script opens the web.publish.config, replaces the connection string with a place holder, and removes two config sections (the XML manipulations shown are for illustration only):

$config = "c:\path-to\published\app\web.publish.config"

[xml]$cfg = Get-Content $config

"$config loaded"

$node = $cfg.SelectSingleNode("//connectionStrings/add[@name='default']")
$node.connectionString = "Data Source=LOCALHOST;Initial Catalog=THECATALOG;Persist Security Info=True;User ID=USERNAME;password=PASSWORD"

$node = $cfg.SelectSingleNode("//appSettings")
if ($node) { $node.RemoveAll() }

$node = $cfg.SelectSingleNode("//system.web/pages/controls")
if ($node) { $node.RemoveAll() }

$sr = New-Object System.IO.StreamWriter $config

"$config saved"

The script loads the web.config, performs some XML operations (notice the inline addressing of the connectionString attribute), and saves the XML document to its original name.