Code Generation using Oracle SQL*Plus

I mentioned the requirements for code generation by calling stored procedures from sqlplus in a previous post, but plus needs more fine-tuning to create nice source code files:

set serveroutput on format wrapped;

“format wrapped” maintains spaces in the output file.

set feedback off;

suppresses the message “PL/SQL procedure successfully completed” (found here)

set trimspool on;

trims trailing spaces in the spool file

set linesize 200;

sets maximum line length before wrapping to new line (max. 32767)

spool C:\path\to\my\output.txt

sets output file name (= spool file)

exec my_proc();

generate code using dbms_output.put_line() statements

spool off

end of code generation

Generating NHibernate ClassMap in PL/SQL

In the previous post I showed how to generate C# classes from Oracle table definitions using a PL/SQL stored procedure. This post deals with the ClassMap generation.

I reuse the original two cursors for tables and columns, and replace the field/getter/setting declaration by Fluent NHibernate calls to Id(), Map(), and Reference():

begin
  dbms_output.enable(1000000);

  for obj in cObjects loop
    csclass := replace(initcap(obj.object_name), '_', '');

    dbms_output.put_line('public partial class ' || csclass
      || 'Map : ClassMap<' || csclass || '>');
    dbms_output.put_line('{');
    dbms_output.put_line('    public ' || csclass || 'Map()');
    dbms_output.put_line('    {');
    dbms_output.put_line('        Table("' || obj.object_name || '");');

    for col in cColumns(obj.object_name) loop
      csname := replace(initcap(col.column_name), '_', '');

      case col.data_type
        when 'CHAR' then
          dbms_output.put_line('        Map(x => x.' || csname || ', "' 
            || col.column_name || '");');
        when 'NUMBER' then
          if col.column_name = 'OID' then
            dbms_output.put_line('        Id(x => x.' || csname || ', "' 
              || col.column_name 
              || '").GeneratedBy.Custom("trigger-identity");');
          elsif col.column_name like '%\_OID' escape '\' 
              and col.table_name is not null then
            dbms_output.put_line('        References(x => x.'
              || replace(initcap(replace(col.column_name, '_OID', '')), '_', '')
              || ', "' || col.column_name || '");');
          else
            dbms_output.put_line('        Map(x => x.' || csname || ', "' 
              || col.column_name || '");');
          end if;
        else
          dbms_output.put_line('        Map(x => x.' || csname || ', "' 
            || col.column_name || '");');
      end case;
    end loop;

    dbms_output.put_line('    }');
    dbms_output.put_line('}');
  end loop;
end;

Generating NHibernate Class in PL/SQL

Data access classes for use in NHibernate take the form

public class MyFoo
{
  public virtual int MyBar { get; set; }
  ...
}

i.e. they are public classes and their member variables are declared public virtual with get and set accessors.

Due to this regular pattern and the fact that tables and columns can easily be retrieved in Oracle using the DBA_, ALL_, or USER_ views, we can thus write a stored procedure to generate the data access classes. The stored procedure can then be invoked during the build process to reflect changes in the data model

First, define cursors for tables and views and their columns:

create or replace
procedure dev_generate_dal_classes
is
  csdatatype varchar2(50);
  csname varchar2(50);
  csaccessor varchar2(50);

  cursor cObjects is
  select object_type, object_name
  from all_objects
  where owner = 'MYUSER'
  and object_type in ('TABLE', 'VIEW')
  order by 1, 2;

  cursor cColumns(oname in varchar2) is
  select tc.column_name, tc.data_type, tc.char_length, tc.data_length,
    tc.data_precision, tc.data_scale, tc.nullable,
    p.table_name
  from all_tab_columns tc
  left outer join all_cons_columns cc
    inner join all_constraints c on c.owner = cc.owner 
      and c.constraint_name = cc.constraint_name 
      and c.constraint_type = 'R'
    inner join all_constraints p on c.r_owner = p.owner 
      and c.r_constraint_name = p.constraint_name
  on tc.owner = cc.owner and tc.table_name = cc.table_name 
    and tc.column_name = cc.column_name
  where tc.owner = 'MYUSER'
  and tc.table_name = oname
  order by tc.column_id;

Next, we loop through all tables and views (outer cursor):

begin
  dbms_output.enable(1000000);

  for obj in cObjects loop
    dbms_output.put_line('public partial class ' || 
      replace(initcap(obj.object_name), '_', ''));
    dbms_output.put_line('{');

    for col in cColumns(obj.object_name) loop

      csname := replace(initcap(col.column_name), '_', '');
      csdatatype := col.data_type;
      csaccessor := '{ get; set; }';

For the inner loop we use a parameterized cursor. Each column needs to have its data type mapped onto a C# data type:

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'NUMBER' then
          if col.column_name = 'OID' then
            csdatatype := 'long';
            csaccessor := '{ get; private set; }';
          elsif col.column_name like '%\_OID' escape '\' 
              and col.table_name is not null then
            csdatatype := replace(initcap(col.table_name), '_', '');
            csname := replace(initcap(replace(col.column_name, '_OID', '')), '_', '');
          elsif col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
          else
            csdatatype := 'long';
          end if;

        else
          csdatatype := col.data_type;

      end case;

      dbms_output.put_line('    public virtual ' || 
        csdatatype || ' ' || csname || ' ' || csaccessor);

    end loop;

    dbms_output.put_line('}');
  end loop;

end;

Restrictions:

• The example only shows string and int data type conversion.
• Primary key columns are always named OID, and foreign key columns end in _OID.
• Table and column names are generated calling INITCAP() and then removing underscores.

Oracle Stored Procedures with NHibernate

Setting up Oracle access in NHibernate (using Fluent NHibernate) was pretty straight-forward (with the exception of some nasty error messages). (See part 1, part 2)

The last missing item so far is executing Oracle stored procedures via NHibernate.

I found this blog most useful on mapping stored procedures to queries in NHibernate. The idea is to encapsulate the stored procedure call inside a named SQL query.

In Visual Studio, the .hbm.xml files need to have a Build Action of “Embedded Resource”, and the option “Copy To Output Directory” set to “Do Not Copy”. Thus they end up inside the compiled assembly and can be retrieved by the mapper.

Since my NHibernate setup is configured using Fluent, I needed to merge the Fluent configuration with the .hbm.xml files that the stored procedure declarations require. (As far as I know, Fluent does not support stored procedure declarations)

Thanks to this blog, it’s quite simple to merge various mapping mechanisms (in this case Fluent and Hbm), and the final code to create an NHibernate SessionFactory looks like this:

public class ConfigureDAL
{
  public static ISessionFactory Configure()
  {
    var c = Fluently.Configure();
    c.Database(OracleDataClientConfiguration.Oracle10
     .ConnectionString(x => x.FromConnectionStringWithKey("default"))
     .DefaultSchema("cpa"));
    c.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ConfigureDAL>());
    c.Mappings(m => m.HbmMappings.AddFromAssemblyOf<ConfigureDAL>());
    return cfg.BuildSessionFactory();
  }
}

Let’s take a simple stored procedure as example:

create or replace procedure cpa.test_store_int2(a number, b number) is
begin
  insert into cpa.dev_int (i10, i11) values (a, b);
end;

This procedure inserts a record of two integers into a table, and returns no values. This is the .hbm.xml mapping file:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
  assembly="myassembly" namespace="myassembly.mynamespace">
  <sql-query name="TestStoreInt2" callable="true">
  <query-param name="Value1" type="int" />
  <query-param name="Value2" type="int" />
begin cpa.test_store_int2(:Value1, :Value2); end;
  </sql-query>
</hibernate-mapping>

There are several things to note:

• The parameters must be listed in <query-param> nodes
• Nothing is returned, so we do not need a <return> declaration
• The stored procedure call must include the parameters using :name notation
• The stored procedure call must be wrapped inside a BEGIN END; block

If you omit the BEGIN/END, Oracle will raise the error ORA-00900 (invalid SQL statement).

The stored procedure is executed via the IQuery.List() method:

using (var session = sessionFactory.OpenSession())
{
  IQuery sp = session.GetNamedQuery("TestStoreInt2");
  sp.SetParameter<int>("Value1", 101);
  sp.SetParameter<int>("Value2", 202);
  sp.List();
}

This pattern calls for encapsulation into a (generated) method, of course. (to be continued)

Database Command-Line Tools: MS SQL Server vs. Oracle

SQL Server provides a command-line tool called sqlcmd (2005, 2008) (deprecating osql (up to 2000), see this blog for comparison) to access a database from the command line and execute SQL scripts.

It implements a number of parameters, among them -q and -Q to pass a SQL statement to  be executed. Any results generated by the script are redirected to stdout, and can be written to file using standard redirection mechanism ( >, | ).

The equivalent in the Oracle world is sqlplus (the product name is historically written as SQL*Plus).

Sqlplus does not provide a -q parameter. It rather accepts a (.sql) file name parameter using the notation @path\to\filename.sql (The <<EOF notation to pass a SQL script directly does not work under Windows), and output formating has to be set up in that file. File output is typically configured using the SPOOL command.

I found that the minimum requirement for output formating and spooling is:

set serveroutput on format wrapped;
spool c:\path\to\output.txt
... SQL statements ...
spool off
exit

If you plan to output results using dbms_output.put_line(), you probably need to call dbms_output.enable(1000000);

Silverlight + WCF = SecurityException

First baby steps in Silverlight (SL 4, VS 2010) to prototype a WCF service client:

I originally started out by embedding a WCF service in a web application, which provides methods to query a database accessed via NHibernate. A WinForm test client successfully retrieved results from the service.

Migrating this solution to Silverlight was more difficult.

Referencing a web service in a Silverlight project is different from other projects: First, all classes used by the web serviced are generated as proxy classes in the SL project. (Other projects use the original class definitions in the original assemblies). Proxy classes are mapped to the original classes provided by the web service using the attribute

[System.Runtime.Serialization.DataContractAttribute(Name="ClassName",
Namespace="http://schemas.datacontract.org/2004/07/NameSpace")]

Second, there are no synchronous calls to the web service. Every call has its Async() method and Completed event.

There are two ways in reference a service: the option “Message Contracts” will generate a Request and Response class for each method, containing all the parameters and results. Without that option, the calls directly receive their parameters .

When trying to retrieve a result from the web service, however, Silverlight threw an exception before invoking the Complete event:

An error occurred while trying to make a request to URI “http://localhost:50505/my.svc’. This could be due to attempting to access a service in a cross-domain way without a proper cross-domain policy in place, or a policy that is unsuitable for SOAP services. You may need to contact the owner of the service to publish a cross-domain policy file and to ensure it allows SOAP-related HTTP headers to be sent. This error may also be caused by using internal types in the web service proxy without using the InternalsVisibleToAttribute attribute. Please see the inner exception for more details.

Fehler beim Senden einer Anforderung an den URI “http://localhost:50505/my.svc”. Ursache ist möglicherweise, dass ohne die entsprechende domänenübergreifende Richtlinie oder mit einer nicht für SOAP-Dienste geeigneten Richtlinie domänenübergreifend auf einen Dienst zugegriffen wurde. Möglicherweise müssen Sie sich an den Besitzer des Diensts wenden, damit eine domänenübergreifende Richtliniendatei veröffentlicht und das Senden von sich auf SOAP beziehenden HTTP-Headern zugelassen wird. Dieser Fehler kann auch durch Verwendung von internen Typen im Webdienstproxy ohne das InternalsVisibleToAttribute-Attribut verursacht werden. Weitere Details finden Sie in der inneren Ausnahme.

The problem is caused by a missing crossdomain.xml in the root directory of the web service application which defines cross-domain access from other applications. The file needs to have the following contents:

<?xml version="1.0"?>
<!DOCTYPE cross-domain-policy SYSTEM "http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
  <allow-access-from domain="*" />
  <allow-http-request-headers-from domain="*" headers="SOAPAction" />
</cross-domain-policy>

Thanks to Stack Overflow’s aaginor for the hint.

Accessing MediaWiki via JSON API

In its first version, YuJisho provided a web search interface to a collection of freely available dictionaries. The obvious extension to that principle is to include other encyclopedias and online dictionaries as well.

MediaWiki wikis not only display their contents in the /wiki/ root directory, but also provide a Query API via the /w/api.php URL. This API provides results in various formats, among them JSON, which is typically used by JavaScript clients.

JavaScript code can query this API to search for article titles in a given wiki. jQuery implements the getJSON() method to asynchronously retrieve results. If more than one request is to be executed, the ajax() method has to used with the parameter mode set to ‘queue’.

Out of all available Wikimedia projects, wikipedia.org and wiktionary.org languages have been selected that are most closely related to CJK characters (Chinese, Japanese, Korean) or for which most translations exist in the data (English, German, French, Russian).

So from now on, if you search on YuJisho (for example: 東京 (Tokyo), 北京 (Beijing)), every result page will automatically perform a JavaScript search in various wikis, and provide links to the relevant wiki pages.

3 Ways to Write a Query in NHibernate

Getting started with NHibernate and NHibernate Linq, I compared the various ways to write a database query in that framework.

I came up with the following alternatives:

using NHibernate criteria:

var foos = 
  session.CreateCriteria<Foo>()
    .AddOrder(NHibernate.Criterion.Order.Asc("Id"))
    .List<Foo>();

using NHibernate Linq:

var foos = 
  session.Linq<Foo>().OrderBy(f => f.Id);

using NHibernate Linq with Linq syntax:

var foos = 
  from foo in session.Linq<Foo>() 
  orderby foo.Id 
  select foo;

All 3 statements actually generate the same SELECT statement. I guess I prefer the 2nd version.

Bookmarklet: View MSDN in English

If you visit MSDN directly or via a search result, you will be redirected to a location-specific URL containing the language setting, such as en-us or de-de.

Since the machine-generated translations of MSDN range from bad to incomprehensible, I wrote a small Javascript bookmarklet to redirect me to the English original of an MSDN article (tested in Firefox).

Simply right-click on the bookmark bar and select “New Bookmark…”, add a name and this address:

javascript:window.location=window.location.href.replace("de-de","en-us");

Adjust the de-de string to the locale you are typically redirected to. Click OK.

If you visit MSDN again, simply click the bookmarklet to view the MSDN article in English.

Oracle-specific issues in NHibernate

After managing to get NHibernate connect to an Oracle database, the brave developer needs to cope with two specialties of Oracle databases, namely handling Boolean values, and Identity columns generated by INSERT triggers.

Boolean values

Oracle databases do not implement a Boolean data type (while PL/SQL does), as opposed to MS SQL Server’s data type Bit. Instead, the standard way is to have a CHAR(1) with allowed values “Y” and “N”:

CREATE TABLE "FOO"
(
  ...
  "IS_ACTIVE" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE,
  ...
  CONSTRAINT "CHK_FOO_IS_ACTIVE" CHECK (IS_ACTIVE IN ('Y', 'N')) ENABLE,
  ...
)

To access such a column in NHibernate in a type-safe way (i.e. C# bool), you need to map it to the YesNoType. In Fluent, this is done using .Map().CustomType<>() in the ClassMap constructor:

  Map(x => x.IsActive, "IS_ACTIVE").CustomType<YesNoType>();

Trigger-generated Identities

Oracle databases do not implement an Auto-Increment or Identity mechanism. Instead, sequences are used to provide incremental values for primary key columns.

The sequence values are either provided in the INSERT statement reading the sequence’s NEXTVAL property, or generated by a BEFORE INSERT FOR EACH ROW trigger.

If you declare a primary key column in Fluent using the Id() method, NHibernate will generate an INSERT statement like this:

INSERT INTO cpa.FOO (OID, ID, IS_ACTIVE)
VALUES (cpa.hibernate_sequence.nextval, ?, ?)
returning OID into :nhIdOutParam

implementing the first variant. The trigger-generated method is supported by specifying GeneratedBy.Custom(“trigger-identity”) (works in Fluent build 636):

Id(x => x.Oid).GeneratedBy.Custom("trigger-identity");

The trigger-identity generator will generate an INSERT statement like this:

INSERT INTO cpa.FOO (ID, DESCRIPTION) 
VALUES (?, ?) 
returning OID into :nhIdOutParam

See this blog for other generators in NHibernate.

Not Oracle-specific, but probably interesting:

Table name aliases

Table name aliases are specified using the Table(“tablename”) method in the ClassMap constructor.

Column name aliases

Column name aliases can be specified by passing a second argument to the Id(), Map(), and References() methods:

Table("FOO");
Id(x => x.Oid, "OID").GeneratedBy.Custom("trigger-identity");
Map(x => x.Id, "ID");
References(x => x.Bar, "BAR_OID");