Migrating Data from Oracle to SQL Server

We were indulged in an another challenge during our cover of Azure. We had used the App Service (A platform as service) to host our Apps and Database, then we used the Logic Apps to automate process now we are laid with another challenge of  migrating our archive data, from an Obsolete Oracle 9i to the cloud.

Challenges

1. Database was more than 12Gigs and had Big Tables (Some went to extent of having more than 10 million records)
2. Ensure what Source had is what the destination got
3. Network interruptions

What did we use

Microsoft came to assist us and they offered us to use the SQL Server Migration Assistant 7.8 for this task.

Observation

Although its not a successful task, but we were able to fix issues manually and migrated the data. Manual task went to the extent of even removing constraints, then resolve the issue and finally recreate them. This issue did haunt us the most. For integrity we had to ensure the Row count is equal in Source and Destination.

Restoring Data in Azure Sql Server

I successfully restored my data after I have accidently updated it incorrectly. For the restoration process I referred the following article:

The Super Article Here

You would require SQL Database Migration Wizard. However its stored in Codeplex which in due course will be shutdown. I have attached the File for convenience.

SQL Database Migration Wizard

Digest Authentication with RestSharp

This original source of the content was available at www.ifjeffcandoit.com, but ironically the website was not operational by the time of writing. This article helped me a lot in solving the trouble I had in Digest Authentication. Since I could not reach author for permission, for the benefit others, I did a copy and paste of the Cached version.

The orginal content goes as:

The Situation

Recently I worked on a project that involved integration between a data center and a 3rd party via a RESTful API. For securing the service, the 3rd party had used:

• SSL-Encryption
• Digest Authentication

I did some research for REST clients that would work with .NET and decided that RestSharp was a good option for our purposes.

The problem

Out of the box, Restsharp does not support Digest authentication but it allows for you to write your own implementation of IAuthenticator. The Internet was my friend again as I found this link which gave the simplest implementation of digest authentication for Restsharp imaginable. These few lines of code worked for my initial tests.

BUT… when I needed to integrate querystring values into a GET call, the authentication would fail.
The reason for the failure was because hash being included in the Authentication portion of the digest authentication within Restsharp did not include the the querystring.
For example:

In this URI http://example.com/authors?subject=princesses only the “http://example.com/authors” was included in the authentication header (I used fiddler to glean the values).

How to get around this?

Some more research showed that I wasn’t the only one who had come across this problem. The most helpful link I found was here: http://stackoverflow.com/questions/3109507/httpwebrequests-sends-parameterless-uri-in-authorization-header.

The comments by Andomar and Gerfboy were what lead me to my “solution”.
Andomar posted the C# code for DigestAuthFixer which I slightly modified to include Gerfboy’s changes.

Gerfboy pointed out that the “opaque” value was required – the 3rd party’s server was sending this value as part of the auth response. Sending this value back in my request was required.

One other change I had to make was an “if” in my authentication method. I only wanted to do this “fix” if it was a GET – the post/put actions would fail otherwise. I’m assuming these failed because my “fixer” was doing a GET, and not matching the action of the call.

Below is the code I needed to add to my project to get Digest Authentication to work with RestSharp.

// The fix was found here:
// http://stackoverflow.com/questions/3109507/httpwebrequests-sends-parameterless-uri-in-authorization-header
// I needed to call the service directly to get the nonce and opaque values.  Once I could get those
// the header could be built and sent with the request.

public class DigestAuthenticator : IAuthenticator
{
private readonly string _user;
private readonly string _pass;

public DigestAuthenticator(string user, string pass)
{
_user = user;
_pass = pass;
}

public void Authenticate(IRestClient client, IRestRequest request)
{
request.Credentials = new NetworkCredential(_user, _pass);

// TODO: Figure out how to remove the if.. currently PUT does not work if the DigestAuthFixer is in place
if (request.Method == Method.GET)
{
var url = client.BuildUri(request).ToString();
var uri = new Uri(url);

var digestAuthFixer = new DigestAuthFixer(client.BaseUrl, _user, _pass);
digestAuthFixer.GrabResponse(uri.PathAndQuery);
var digestHeader = digestAuthFixer.GetDigestHeader(uri.PathAndQuery);
request.AddParameter("Authorization", digestHeader, ParameterType.HttpHeader);
}

}
}

public class DigestAuthFixer
{
private static string _host;
private static string _user;
private static string _password;
private static string _realm;
private static string _nonce;
private static string _qop;
private static string _cnonce;
private static string _opaque;
private static DateTime _cnonceDate;
private static int _nc;

public DigestAuthFixer(string host, string user, string password)
{
_host = host;
_user = user;
_password = password;
}

private string CalculateMd5Hash(
string input)
{
var inputBytes = Encoding.ASCII.GetBytes(input);
var hash = MD5.Create().ComputeHash(inputBytes);
var sb = new StringBuilder();
foreach (var b in hash)
sb.Append(b.ToString("x2"));
return sb.ToString();
}

private string GrabHeaderVar(
string varName,
string header)
{
var regHeader = new Regex(string.Format(@"{0}=""([^""]*)""", varName));
var matchHeader = regHeader.Match(header);
if (matchHeader.Success)
return matchHeader.Groups[1].Value;
throw new ApplicationException(string.Format("Header {0} not found", varName));
}

public string GetDigestHeader(
string dir)
{
_nc = _nc + 1;

var ha1 = CalculateMd5Hash(string.Format("{0}:{1}:{2}", _user, _realm, _password));
var ha2 = CalculateMd5Hash(string.Format("{0}:{1}", "GET", dir));
var digestResponse =
CalculateMd5Hash(string.Format("{0}:{1}:{2:00000000}:{3}:{4}:{5}", ha1, _nonce, _nc, _cnonce, _qop, ha2));

return string.Format("Digest username=\"{0}\", realm=\"{1}\", nonce=\"{2}\", uri=\"{3}\", " +
"algorithm=MD5, response=\"{4}\", opaque=\"{8}\", qop={5}, nc={6:00000000}, cnonce=\"{7}\"",
_user, _realm, _nonce, dir, digestResponse, _qop, _nc, _cnonce, _opaque);
}

public void GrabResponse(
string dir)
{
var url = _host + dir;
var uri = new Uri(url);

var request = (HttpWebRequest)WebRequest.Create(uri);

// If we've got a recent Auth header, re-use it!
if (!string.IsNullOrEmpty(_cnonce) &&
DateTime.Now.Subtract(_cnonceDate).TotalHours < 1.0)
{
request.Headers.Add("Authorization", GetDigestHeader(dir));
}

HttpWebResponse response;
try
{
response = (HttpWebResponse)request.GetResponse();
}
catch (WebException ex)
{
// Try to fix a 401 exception by adding a Authorization header
if (ex.Response == null || ((HttpWebResponse)ex.Response).StatusCode != HttpStatusCode.Unauthorized)
throw;

var wwwAuthenticateHeader = ex.Response.Headers["WWW-Authenticate"];
_realm = GrabHeaderVar("realm", wwwAuthenticateHeader);
_nonce = GrabHeaderVar("nonce", wwwAuthenticateHeader);
_qop = GrabHeaderVar("qop", wwwAuthenticateHeader);

_nc = 0;
_opaque = GrabHeaderVar("opaque", wwwAuthenticateHeader);
_cnonce = new Random().Next(123400, 9999999).ToString(CultureInfo.InvariantCulture);
_cnonceDate = DateTime.Now;
}

}
}

Client Code:

public class WebClientProxy
{
private readonly string _url;
private readonly string _userId;
private readonly string _password;
private readonly int _merchantId;

public WebClientProxy(string url, string userId, string password, int merchantId)
{
_url = url;
_userId = userId;
_password = password;
_merchantId = merchantId;
}

public T Execute<T>(RestRequest request) where T : new()
{
var client = new RestClient
{
BaseUrl = _url,
Authenticator = new DigestAuthenticator(_userId, _password),

};

var response = client.Execute<T>(request);

if (response.ErrorException != null)
{
throw response.ErrorException;
}
return response.Data;
}

public Somethings SomethingGetAllValuesSince(DateTime sinceDate)
{
var request = new RestRequest
{
Resource = "something/{SomethingId}/accounts",
RequestFormat = DataFormat.Json,
Method = method
};
request.AddParameter("SomethingId", _somethingId, ParameterType.UrlSegment);
request.AddParameter("since", sinceDateValue, ParameterType.GetOrPost);
return Execute<Somethings>(request);
}

}

Cached version of the content can be seen here: http://webcache.googleusercontent.com/search?q=cache:Sm-c6n7aGN8J:www.ifjeffcandoit.com/2013/05/16/digest-authentication-with-restsharp/+&cd=1&hl=en&ct=clnk&gl=lk

Azure Learning: Change Web App Time Zone

https://blogs.msdn.microsoft.com/tomholl/2015/04/06/changing-the-server-time-zone-on-azure-web-apps/

Deploying ClickOnce™ Project without Microsoft Visual Studio

Environment Used:

• Windows 7 Professional Terminals SP1
• Windows XP Professional SP3
• Microsoft Visual Studio 2012 Professional
• Windows SDK for Windows 7 and .NET Framework 4.0

 

What you will need:

• Visual Studio for Build the project; For developer environment
• MageUI.exe Graphical Tool (You need to install the latest Windows SDK) for Deployment environment

 

MageUI.exe is available under C:\Program Files\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools

 

Steps:

1. In Visual Studio, go to the Project Properties > Application Tab > Under Resources > Select “Create application without Manifest” from the drop down
2. Build your project.
3. [1]Follow the steps from the MSDN’s walkthrough: Manually Deploying a ClickOnce Application > To Deploy an application with MageUI.exe graphical tool

Things MageUI will not be creating, but created in Visual Studio Publish Wizard are:

• Setup.exe
• Publish Web Page

 

Resources:

• http://www.microsoft.com/en-us/download/details.aspx?id=8279
• http://msdn.microsoft.com/en-us/library/xc3tc5xx.aspx
• http://stackoverflow.com/questions/5337458/error-deploying-clickonce-application-reference-in-the-manifest-does-not-match

 

---

[1] In the Clause 3 under To deploy an application with the MageUI.exe graphical tool: Copy all the files in Project’s bin folder to deployment directory.

 

Copy Horizontal Cells as Vertically in Excel ?

In Excel use Paste Special > transpose....

Answer Source

 

Migration from VS 2005 to VS 2012: Include Prerequisites with a ClickOnce Application

Another issue I faced while migrating from VS 2005 to VS 2012 was How to Include Prerequisites with a ClickOnce Application.

Click here for the solution

Migration from VS 2005 to VS 2012: Crystal Reports

The following links allows you to download the necessary Crystal Report bundle
SAP Crystal Reports for Visual Studio 2012

The following is the Service Packs associated with Crystal Report product
 Crystal Reports/SAP Crystal Reports Service Packs

Tharangas best, Indias worst, and a first in ODIs

Tharangas best, Indias worst, and a first in ODIs

Reading a Lotus Notes Mail Box

You need the Lotus Domino Objects to be added into your application. Refer: http://www.ibm.com/developerworks/lotus/library/domino-msnet/

You need to know the Path of your Lotus Notes Mail-box:

http://www.livepim.com/lotus-notes-database.htm

 

Domino.NotesSession s = new Domino.NotesSession();
            Domino.NotesDatabase db;
            Domino.NotesView vw;
            Domino.NotesDocument doc;

            try
            {
                //Leave blank Password, then It will prompt for Password, It basically authenticates with the *.id file
                s.Initialize("mypassword");

                //If Server is blank, which Local otherwise specify the Server
                //Place the Mailbox path Next
                db = s.GetDatabase("", @"C:\Documents and Settings\me\Local Settings\Application Data\Lotus\Notes\Data\mail\me\mymailbox.nsf", false);
                if (db != null)
                {
                    //Inbox is special/hidden folder
                    vw = db.GetView("($Inbox)");
                    doc = vw.GetFirstDocument();

                    while (doc != null)
                    {
                        String Subject = ((object[])doc.GetItemValue("Subject"))[0] as String;
                        String From = ((object[])doc.GetItemValue("From"))[0] as String;

                        textBox1.Text += Subject;
                        textBox1.Text += From;
                        doc = vw.GetNextDocument(doc);
                    }
                }
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }

 

 

Additional Resources:

http://stackoverflow.com/questions/1798126/getting-null-value-for-notesdocument-for-mail-item-c-sharp

http://stackoverflow.com/questions/1136725/reading-domino-server-mailbox-using-c-sharp

http://stackoverflow.com/questions/306462/c-lotus-interop-getting-message-information

http://stackoverflow.com/questions/4222387/inbox-returning-null-while-trying-to-access-lotus-mail-inbox-using-c-sharp

MODI Viewer

It appears that MODI (Microsoft Office Document and Imaging) Viewer control does not release the previous file if a new file to be swapped; I had facing the same trouble when I tried to open a new ".tif" file after the previous was viewed. Note here is that I use the same file name for view and delete it and finally if a new file is been given I rename it back for any new to the same file name which I had deleted.

Check how I overcame it here

An article I got while googling on the same topic can be seen here

Good Luck!

Having Text and Image in One Single Column of DataGridView

DataGridView's Cell_Painting event could be utilized to achieve the above. You need a DataGridView and ImageList in which an Image to be attached. The following simple code in event will be able to achieve this:

private void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
 {

    if (e.RowIndex >= 0 && e.ColumnIndex == 0 && Convert.ToInt32(e.Value.ToString()) > 0)
    {
        e.PaintBackground(e.ClipBounds, false);
       dataGridView1[e.ColumnIndex, e.RowIndex].ToolTipText = e.Value.ToString();
       PointF p = e.CellBounds.Location;
       p.X += imageList1.ImageSize.Width;

      e.Graphics.DrawImage(imageList1.Images[0], e.CellBounds.X, e.CellBounds.Y, 16, 16);
       e.Graphics.DrawString(e.Value.ToString(), e.CellStyle.Font, Brushes.Black, p);
       e.Handled = true;
    }

}

 

Refer DataGridView FAQ in the following Thread:
http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/b5ab78d6-a760-4f29-ac89-46bad51ba30a

Installing the Visual Studio 2005 Image Library

The Visual Studio 2005 Image Library is copied to your computer when you install Visual Studio. To access the files in the image library, you must extract them from the file VS2005ImageLibrary.zip.

To install the Visual Studio 2005 Image Library

1. Locate the file VS2005ImageLibrary.zip. This file is normally installed in \...\Program Files\Microsoft Visual Studio 8\Common7\VS2005ImageLibrary\.
2. Right-click VS2005ImageLibrary.zip and click Extract All.
   The Extraction Wizard appears.
3. Follow the directions in the wizard to extract the images.

Refer MSDN Link

How to configure OracleXEClient to connect to OracleXE?

1. Create a new user Variable called “TNS_ADMIN”
Assume ORACLE_HOME of XE is in C:\oracle\product\oraclexe
Since when you install Oracle 10g Express Edition, a tnsnames.ora file has already been created for you in C:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
Either set TNS_ADMIN in DOS prompt or in a New User Variable
You can set TNS_ADMIN to your local path of Oracle Express Database
set TNS_ADMIN= C:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
or you can copy tnsnames.ora from C:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora to C:\oracle\product\XEClient, and set TNS_ADMIN in the User Variable

Click on [Start], Programs, Oracle Client 10g Express Edition, Run SQL Command Line
SQL> connect sys/ora10g_manager@xe as sysdba

Other ways to connect to Oracle Express in DOS prompt

Method 1:
Set TNS_ADMIN in DOS prompt
In DOS prompt,
C:\> set TNS_ADMIN=C:\oracle\product\XEClient
C:\> C:\oracle\product\XEClient\bin\sqlplus.exe /nolog
SQL> connect sys/ora10g_manager@XE as sysdba

Method 2:
No need to set TNS_ADMIN in DOS prompt but enter the full connection string in one line of DOS command
Set NLS_LANG in DOS Prompt
C:\>set NLS_LANG=English
Enter the following DOS command all in ONE line
C:\>sqlplus "sys/ora10g_manager@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE))) as sysdba

Source: https://forums.oracle.com/forums/thread.jspa?threadID=576930

Default Values via BindingSource's AddingNew Event

I new that BindingSource's AddingNew event can handle all default value assignment, but was so frustrated that I could not find any articles/tutorials which guided me on how to use it. Although MSDN had description, but it did not have samples on how to use it. However, with few long hour searches I came to a source which enlighted me on how to use this event.

A sample code looks like this:
 

private void branchBindingSource_AddingNew(object sender, AddingNewEventArgs e)
{

//// Get data table view 
DataView dataTableView = branchBindingSource.List as DataView;


//// Create row from view
DataRowView rowView = dataTableView.AddNew();


rowView["BranchID"] = -1;
rowView["BranchCode"] = "001";
rowView["BranchDescription"] = "002";

//// Set New row
e.NewObject = rowView;

branchBindingSource.MoveLast(); 

}

 

 

source: http://devexpress.com/Support/Center/p/Q96282.aspx

Opening a Solution Causes VS 2005 to crash and restart

Earlier today, I had to include a code snippet to my project. Unrealizing the consequences it might result in, I went ahead compiling and got an error message saying “Object reference not set to an instance of an object” and finally my VS 2005 crashed. This error message keeps on greeting every time to I tried to reopen the project.

Being clueless of what has gone wrong, I googled tirelessly to find a solution to overcome this problem, but without a joy.

While this error message keeps on coming, I try to realize that my VS were actually running on debugging on all of my Windows forms. Since the Code snippet I included was to my base form’s shown event, so every time I reopen VS 2005 it opens this Windows Forms and sends this error message.
In the Error Signature I witnessed the following:

AppName: devenv.exe
AppVer: 8.0.50727.42
AppStamp:4333e699
ModeName: system.design.ni.dll
ModVer: 2.0.50727.3053
ModStamp:4889df30
fDebug:0
Offset: 006b8532

I came across an article on running devenv.exe in “Visual Studio 2005 Command Prompt” and was enlightened me that I could build, rebuild, clean…etc project and its files. So I went to Visual Studio’s Project folder and located the *.cs file which was giving the error and edited via Notepad. Then I deleted the code snippet that was the causing the problem and replaced by a simple “Hello World” message box. Finally I build the project by trying my luck via VS 2005 Command Prompt by running the following commands:


devenv [either *.csproj or *.sln file of your project] /clean

devenv [either *.csproj or *.sln file of your project] /build

devenv [either *.csproj or *.sln file of your project] /rebuild


and it worked! I was finally able to open the project via Visual Studio and it worked well… No errors!

Wrestlemania 26: I made it!

I could only imagine myself being in Phoenix and yelling at 72,000+ people saying that I’ve made it!
Though I could not make it realistically, I’m at least glad that I got the DVD of it which I repeatedly saw it many times so far and even gave a torrid time to my DVD player by hitting the rewind button more often.

Although bit disappointed that the Rated-R could not claim the World Heavyweight championship, but he did not disappoint any by delivering a special kind of a “Spear” to Chris Jerico. I heard some one say it’s the “Edge-de-cution”. However I’m more than assured that the glory is not far away for Edge. Wish you all the best!

The viper was never far away from the show; however he delivered the ideal, if not the best RKO to finish things off with Legacy. I reckon that it’s the best I have ever seen in my bare eyes.

John Cena Vs. Dave Batista was more intense…. And ultimately it’s the never give up attitude that changed the course of the match. I was just astonished to see the US Navy performing a special performance before Cena enters to the ring. My memory diverts to the Wrestlemania 25. . In fact the “Attitude-Adjustment” he delivered was still in my minds.

Coming to Shawn Micheals, I never thought the Odds favors him, yet it was almost the identical Wrestlemania 25 match. Never the less both gave an exceptional performance and Sportsmanship of Undertaker should be admired a lot.

The intensity was awesome and all I could say that Wrestlemania keeps getting better and better. Can’t hardly wait to see Wrestlemania 27… and If god could send me to Atlanta next April I’ll consider it as just a miracle that happened.

Master/Detail Updation: Reflecting Identity Value

Master-Detail is quite easy to explain, but its complexity lies when you’re implementing it particularly when the identity columns rely on database given values. Researching on this topic for several months, days and hours, finally things lightened up for me.

Instead having to rely on an auto increment value, I have to rely on a value set provided via trigger. Have to admit myself that the resource below was very helpful indeed to the quest of solving this problem:

http://www.eggheadcafe.com/forumarchives/NETFrameworkADONET/Nov2005/post24232248.asp
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d

The environment I was working with was Windows XP Pro SP+3, Visual Studio 2005, C#.net, Oracle XE DB, Oracle 10g client, System.Data.OracleCleint and TableAdapters;

To illustrate how I went on, I extracted the following tables from my project:

Journal(journalid*, xsactdate, xsactdesc);
JournalAccount(Journalid*, accountid*, xsactvalue);
PKEY(journal_PKEY);

I had the following triggers written for the Journal & JournalAccount table respectively.

Trigger for Journal Table as:

DECLARE
   nJournal_PKey PKey.Journal_PKey%TYPE;
   nChkJournalID Journal.JournalID%TYPE;
BEGIN
   -- this is to prevent posted Journals from being
   -- updated

   IF ( INSERTING ) THEN
      nChkJournalID := :new.JournalID;
   ELSE
      nChkJournalID := :old.JournalID;
   END IF;

   IF ( INSERTING AND ( :new.JournalID IS NULL ) ) THEN

      SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;

      UPDATE PKey SET Journal_PKey = Journal_PKey + 1;

      :new.JournalID   := nJournal_PKey + 1;
   END IF;
END;

Trigger for JournalAccount Table:

DECLARE
   nJournal_PKey PKey.Journal_PKey%TYPE;
   nChkJournalID Journal.JournalID%TYPE;
BEGIN
   -- this is to prevent posted Journals from being
   -- updated

   IF ( INSERTING ) THEN
      nChkJournalID := :new.JournalID;
   ELSE
      nChkJournalID := :old.JournalID;
   END IF;

   IF ( INSERTING AND ( :new.JournalID IS NULL ) ) THEN

      SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;

      :new.JournalID   := nJournal_PKey;
   END IF;
END;

Having followed what David Sceppa and Jason Kresowaty alias BinaryCoder wrote in their postings, things were going ok for me, except for the fact that Identities aren’t reflected upon saving to the table. Since the TableAdapters created without the InsertCommand, I have to include it by myself.

There were certain things which I need to modify, particularly Insert Query. Look into my Insert Query and you may see the Oracle Keyword “RETURNING”. The keyword is basically enables us to use it as an output parameter. I have inserted the temporary identity and getting back the value assigned via the trigger as an output parameter.

The following describes the “RETURNING” clause of Oracle: http://www.myoracleguide.com/s/Returning.htm

Finally have to amend the

System.Data.DataRowVersion

to

System.Data.DataRowVersion.Proposed

and the identity field (JournalID in my case)

System.Data.ParameterDirection

to

System.Data.ParameterDirection.InputOutput

The following is the InsertCommand in the TableAdapter for the Journal Table:

this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand(); this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "INSERT INTO JOURNALACCOUNT(JOURNALID, ACCOUNTID, XSACTVALUE, DISCOUNTEDPAYMENT) VALUES (:JOURNALID, :ACCOUNTID, :XSACTVALUE,'N') RETURNING JOURNALID INTO :JOURNALID";
this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("ACCOUNTID", System.Data.OracleClient.OracleType.Char, 15, System.Data.ParameterDirection.Input, "ACCOUNTID", System.Data.DataRowVersion.Current, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTVALUE", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.Input, "XSACTVALUE", System.Data.DataRowVersion.Current, false, null));

The following is the insertcommand in the tableadapter for the JournalAccount table:

this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand();
this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "INSERT INTO JKSBSCHEMA.JOURNAL(JOURNALID, XSACTDATE, XSACTDESC) VALUES (:JOURNALID, :XSACTDATE, :XSACTDESC) RETURNING JOURNALID INTO :JOURNALID";
this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
this._adapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.Both;
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDATE", System.Data.OracleClient.OracleType.DateTime, 7, System.Data.ParameterDirection.Input, "XSACTDATE", System.Data.DataRowVersion.Current, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDESC", System.Data.OracleClient.OracleType.VarChar, 40, System.Data.ParameterDirection.Input, "XSACTDESC", System.Data.DataRowVersion.Current, false, null));

Refer My Post @ MSDN Forums: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fbfdc09b-41bf-4290-9dea-6a9a85d5becf

The Banker Quote

While browsing the internet, I came up with an interesting quote by Mark Twain which apparently was highlighted under an individuals signature. Check this out:

A banker is a fellow who lends you his umbrella when the sun is shining, but wants it back the minute it begins to rain.
Mark Twain (1835 - 1910)

ORA-06502: PL/SQL: numeric or value error

This error was a real nightmare for me where I'm being in middle of a migration process of our legacy system. Though there was real hits when you google this problem, but nothing was helpfull for me.

The context of the problem occurs when I did run it on a WinXP pro sp3+ workstation which had installed the latest ODP.NET. However its quite extraordinary to see it was not reproduced in a machine installed Oracle Express Edition 11g (without oracle client).

Later, I understood that this problems occurs if you have a VarChar2 parameter(s) when executing a Oracle Stored Procedure. So people advised to have the size and Parameter direction in the parameter. I incresed the size everytime of the parameter and had the parameter direction as "input", yet this error kept bugging me. (My stored procedures did not have any return/output parameters, so I'm not in a possition to comment its behavior).

Finally after googling for days and finding one post in the Oracle forum from one guy who said to use the direction as "InputOutput" did the work for me. Furthermore to the parameter direction, I also gave all of the VarChar2 parameter size as 2000.

Produced Environment: .NET framework 2.0, Win XP Pro sp3 and the latest ODP.NET