Being a great fan of all things SQL Server, I had hoped to remain an Oracle virgin but alas it was not to be! I couldn’t begin to call myself an Oracle expert but here are a few simple tips I picked up.
When writing stored procedures in Oracle 10g, you can reduce possible maintenance time by declaring the type of stored procedure parameters like so:
‘COLUMN_NAME’ IN ‘COLUMN_NAME’.%type
rather than explicitly declaring a data type.
Oracle have released the Oracle Developer Tools for Visual Studio .NET which includes a schema browser, integrated context sensitive help and a PL/SQL debugger integrated into Visual Studio 2005.
There is a basic article describing how to Build a .NET Application on the Oracle Database, although you would almost certainly be better off using a code generation tool such as MyGeneration or the CodeSmith NetTiers 2 templates (with the CodeSmith Oracle provider) to generate your Data Access Layer (or any one of the others available).
As an aside, this looks interesting but I haven’t actually used it: Convert SQL Stored Procedures to ORACLE.
Here is a question for any Oracle experts out there. I spent some time looking through 2 well known and respected Oracle books, and googling to see whether the 30 character identifier limit in Oracle is configurable, as I reasoned that it surely must be, right. Right? Well, I could not find a way. A 30 character limit is a real pain when you are using code generation. Is it possible to increase this limit?