Brothers In Code

I had a lightbulb moment yesterday.  My new employer is mostly an oracle shop so I end up spending a lot of time translating what I know about sql server into oracle terms.  But one thing I've struggled with is SQL Plus vs. PL/SQL.  That is until yesterday.   Maybe I never took the time before but a sequence of searches forced me in the right direction.

I'm using very few stored proceedures lately (reasons for which will come in a different post).  Consequently I was looking for ways to execute a batch of statements .net code with the Oracle Data Provider and found my answer here on OTN.  But then i looked at how he was getting variable data out of the command.  He was basically doing what I've always wanted to do with Oracle's SQL Developer.  When using the Sql Server query tools (query analyzer, SSMS), I'd often write myself maintenance procedures that I just kept in a file.  It's no problem to create variables and run sql statements in the exact same way you would from inside of a stored procedure.  Sometimes I could copy nearly the same code into a SqlCommand and it would run just fine.  Oracle isn't like that.  Variables (pl/sql variables at least) must be declared within the 'declare' section of a begin/end block.  These blocks can be 'anonymous' (not in a stored procedure) but once you enter the block things change.  You can't simply do a 'select column from table' for example or you get an 'INTO clause expected' error.  Oracle assumes that you're now in a programming block and not connected to some user interface so it wants you to store the data in some variable.  But how was this .net code getting this v_maxnum variable out?  On some sort of protocol level there must be a way for a human to do the exact same thing.

Finally thanks to Wikipedia, I got it.  SQL Plus is very similar to the combination of cmd.exe and SqlCmd/isql?  The big difference is that line between sqlcmd and t-sql is blurred a little more blurred than it is with SQL Plus and pl/sql.  But once I dug in to sqlcmd a little I realized that other than begin/end blocks it's really not that different.  I guess I really never used sqlcmd for anything but a single statement from the command line.

The big realization came now when I understood the variable types in SQL Plus.  It has both substitution variables (&var and &&var) and regular variables (var myvar varchar2).  But unlike sqlcmd's variables being t-sql variables, SQL Plus's variables are different than PL/SQL variables.  However, PL/SQL can 'bind' SQL Plus variables.  Take the following example.

var myDate varchar2;
begin
  select sysdate into :myDate from dual;
end;
/
print myDate

Now I finally understood what the data provider was doing and consequently I understood SQL Plus a lot better too.

A lot of these posts are merely for my own quick reference.  The upside of that is that you'll get a taste for the way I actually do something rather than just a rehash of a reference page.

For simpler projects I just create a class with some static properties that just pull from AppConfig variables.  At least from there I have intellisense to help me remember all of my configuration variables.  But the appSettings section app.config or web.config can get messy pretty fast so creating a custom section isn't that much work to clean it up.

First I'll create a ConfigurationSections folder (which creates an automatic namespace) and an AppConfig class:

The GeneralConfigurationSectionClass looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

namespace MyCust.MyProj.ConfigurationSections
{
  public class GeneralConfigurationSection : ConfigurationSection
  {
    [ConfigurationProperty("TestKey")]
    public String TestKey
    {
      get { return (String)this["TestKey"]; }
      set { this["TestKey"] = value; }
    }
    [ConfigurationProperty("ConnectionString")]
    public String ConnectionString
    {
      get { return (String)this["ConnectionString"]; }
      set { this["ConnectionString"] = value; }
    }
  }
}

Then I'll create a property that pulls the config data and returns this class in the AppConfig.cs

  public class AppConfig
  {
    public static GeneralConfigurationSection General
    {
      get
      {
        return (GeneralConfigurationSection)ConfigurationManager.GetSection("MyCust.MyProj/General");
      }
    }

  }

And last add the new section to the app/web.config.  First add the section group:

<configSections>
  <sectionGroup name="MyCust.MyProj.WebServices">
    <section name="General" type="MyCust.MyProj.WebServices.ConfigurationSections.GeneralConfigurationSection, MyCust.MyProj.WebServices" allowLocation="true" allowDefinition="Everywhere"/>
  </sectionGroup>
</configSections>

And then add the section itself into the configuration element:

<MyCust.MyProj.WebServices>
  <General
    TestKey="32AAAC4-8DB2-4059-852A-73BBBBBB9698"
    ConnectionString="Data Source=SqlServer;User Id=sa_app;Password=pwd;"
  />
</MyCust.MyProj.WebServices>

Usage is then a nice and clean line:

AppConfig.General.ConnectionString

B

I'll log errors to the event log for just about any app that I write with something like the following:

EventLog eventLog = new EventLog();
eventLog.Source = "My App Name";
eventLog.WriteEntry(message, EventLogEntryType.Error);

This is no problem for a windows app, but for an asp.net app running under a restricted account, you'll likely get an error:

System.Security.SecurityException: Requested registry access is not allowed.

The issue described here recommends either adding the event source manually in the registry or creating an EventLogInstaller.  Unless you're creating a commercial software package, you're probabaly going to go the registry route.  The only issue here is that the article leaves a peice out.  Adding only the key will allow your app to write to the event log but rather than your own neatly formatted error message you'll see:

The description for Event ID ( 0 ) in Source (<application name>) could not be found. It contains the following insertion string(s):

If you look at other keys you'll see they contain an "EventMessageFile" reg_expand_sz (expandable string value).  Asp.net 2.0 or higher apps will have the value "c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\EventLogMessages.dll".  If you want a quick fix, here's the reg file code (make sure you save as unicode):

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\YourSourceNameHere]
"EventMessageFile"=hex(2):63,00,3a,00,5c,00,57,00,49,00,4e,00,44,00,4f,00,57,\
  00,53,00,5c,00,4d,00,69,00,63,00,72,00,6f,00,73,00,6f,00,66,00,74,00,2e,00,\
  4e,00,45,00,54,00,5c,00,46,00,72,00,61,00,6d,00,65,00,77,00,6f,00,72,00,6b,\
  00,5c,00,76,00,32,00,2e,00,30,00,2e,00,35,00,30,00,37,00,32,00,37,00,5c,00,\
  45,00,76,00,65,00,6e,00,74,00,4c,00,6f,00,67,00,4d,00,65,00,73,00,73,00,61,\
  00,67,00,65,00,73,00,2e,00,64,00,6c,00,6c,00,00,00
 
 
 
 Here's the .net 4.0 version....

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\eventlog\Application\YourSourceNameHere]
"EventMessageFile"=hex(2):63,00,3a,00,5c,00,57,00,49,00,4e,00,44,00,4f,00,57,\
  00,53,00,5c,00,4d,00,69,00,63,00,72,00,6f,00,73,00,6f,00,66,00,74,00,2e,00,\
  4e,00,45,00,54,00,5c,00,46,00,72,00,61,00,6d,00,65,00,77,00,6f,00,72,00,6b,\
  00,5c,00,76,00,34,00,2e,00,30,00,2e,00,33,00,30,00,33,00,31,00,39,00,5c,00,\
  45,00,76,00,65,00,6e,00,74,00,4c,00,6f,00,67,00,4d,00,65,00,73,00,73,00,61,\
  00,67,00,65,00,73,00,2e,00,64,00,6c,00,6c,00,00,00