Working with an Oracle database has never been easier, now that Oracle has released its Oracle Developer Tools for Visual Studio .NET. If you have experience working with a SQL Server database within Visual Studio .NET, you will discover the Oracle tool set is very recognizable and straightforward to become familiar with. The following features are accessible from this tool set.
Oracle Developer Tools allows you to develop on a Windows platform, while the database may exist on any platform. Oracle states that this toolset is compatible with 10g, 9i, or 8i database instances. I strongly recommend that you take the time to read Oracle’s product documentation before you jump in headfirst. Just to offer an enticement, the Oracle Explorer Window looks very similar to other tools that you may be comfortable working with.
If you do not have a great deal of experience working with an Oracle database, the integrated help system will be an invaluable tool. By simply selecting a SQL or PL/SQL keyword and pressing the F1 key, you will be provided with examples and comprehensive explanations regarding that keyword.
The goal of this article is to discuss in detail the feature set, and allow you to walk away with a basic understanding of this product so that you may be more productive within the Visual Studio .NET IDE.
To work with an Oracle database within the Visual Studio .NET IDE is as simple as selecting the node that you wish to work with. As you will notice in figure 1, you have access to tables, views, procedures, functions, packages, synonyms, sequences, XML database, and finally Java classes.
Figure 1: Example Oracle Explorer Window
One thing that you should be aware of after installing this product is that the explorer tab is not visible by default; you must add it manually. To accomplish this step, click Tools and Connect To Oracle Database. Once you do this, a window will present itself where you must select your data source, authentication method, and role. In this example, I am connecting to a data source named PORTAL and using the username HR that has the necessary rights within the HR schema. Be sure to test your connection to ensure you have connectivity to your database.
Note that you can also access Oracle Explorer by Clicking View and selecting Oracle Explorer.
Figure 2: Add Connection Properties
With all of these entities available directly from within the IDE, it is simply a matter of expanding the particular node that you wish to work with. While I will not cover each of these in detail, I will touch on those items that you as a developer may find the most useful during your development efforts.
First, I will create a new relational table. This is accomplished by right clicking on the table parent node. As well, you can create an XML table if you have such a need.
Figure 3: Create New Table
Once you choose New Relational Table, you will be presented with a window such as the following.
Figure 4: Create Relational Table Properties
From this point, you are able to create columns, define the data type, default values, constraints, and indexes, and define the storage properties. In this example, I have established two columns named columnid and fname. As well, I established the columnid column as the primary key. The last thing to do to create this table is to click the Save button. I should also mention that you have the option to preview your SQL.
Now that this basic table is in place, what if you need to modify the existing table or add new columns and triggers, or you simply need to generate the SQL for this table? Once again, it is as easy as right clicking the NEWTABLE node and selecting the function that you need.
Figure 5: Modifying Existing Table
A nice feature that I personally find most useful is being able to generate a script with a single click of the mouse. To add, edit, or delete data, all that is required is to choose Retrieve Data.
As you can gather, it is relatively effortless to create, modify, and delete tables, add triggers, and generate SQL Scripts, all from within the IDE, even if your knowledge of database administration is minimal.
One drawback that I feel could be improved upon is Intellisense. There would be an immense advantage if the functions and procedures from the Oracle database installation were accessible as they are from other commercial products, but for now I must applaud Oracle for its Developer Tools, as it is a much-welcomed addition the Visual Studio .NET IDE for those developers working with an Oracle database.
Creating a procedure is a speedy and trouble-free process, as long as you are familiar with PL/SQL. However, even if you are not a seasoned PL/SQL developer, Oracle has again done an outstanding job with the integrated help system. When you are at a standstill, all you have to do is highlight any SQL or PL/SQL keyword and the context sensitive help will be displayed by pressing the F1 key. To create a new procedure, click the procedure node and select New PL/SQL Procedure.
Figure 6: Add New PL/SQL Procedure
From here, you can define the parameters and parameter types all within a window similar to the table window shown earlier in figure 4. In this example I have created a new procedure named NewEmployee. I established two parameters, one named p_emp_id with a data type of number, and the second named p_start_date with a data type of date. Now all that remains is to click the Save button and the example code in listing 1 is generated.
Listing 1: Default New Employee Procedure
CREATE PROCEDURE "HR"."PROCEDURE1" (
"P_EMP_ID" IN NUMBER,
"P_START_DATE" IN DATE) IS
-- Declare constants and variables in this section.
-- Example: <Variable Identifier> <DATATYPE>
-- <Variable Identifier> CONSTANT <DATATYPE>
-- varEname VARCHAR2(40);
-- varComm REAL;
-- varSalary CONSTANT NUMBER:=1000;
-- comm_missing EXCEPTION;
BEGIN -- executable part starts here
-- Write PL/SQL and SQL statements to implement the processing logic
-- of subprogram. Example:
-- SELECT ENAME,
-- INTO varEname,
-- FROM EMP
-- WHERE EMPNO = 7369;
-- IF varComm IS NULL THEN
-- RAISE comm_missing;
-- END IF;
-- EXCEPTION -- exception-handling part starts here
-- WHEN comm_missing THEN
-- dbms_output.put_line('Commision is NULL');
At this point, all that is necessary is to modify the generated code in listing 1 to execute an insert into the job_history table.
Listing 2: Modified New Employee Procedure
PROCEDURE "HR"."PROCEDURE1" (
"P_EMP_ID" IN NUMBER,
"P_START_DATE" IN DATE) IS
INSERT INTO job_history (employee_id, start_date)
As you can see, creating a procedure is comparatively effortless and straightforward. Once again one drawback that I feel could be improved upon is Intellisense.
The query window is the heart of this tool set. You can access the Query Window by right clicking on the connection node and selecting Query Window. Here you can write your SQL statements and choose the style of output, such as grid output or text output. As well, you can write and execute multiple statements; however when executing multiple statements you must set the style of the output to text if you wish to retrieve the appropriate data. For example, I want to select a specific employee from the employees table and pull up that employee's job history.
Listing 3: Example Ad-Hoc Multiple Statement
select first_name, last_name, phone_number, hire_date, salary from employees where employee_id = 102;
select * from job_history where employee_id = 102;
Now all there is to do is to click the Execute button and, in your output window, you should see something similar to the following.
Figure 7: Ad-Hoc Multiple Statement Query Editor and Query Output
Another feature that I find beneficial is when you make a SQL syntax error, the query output tells you exactly what the problem is along with the relevant Oracle error code. This type of feedback makes it a much simpler process to pinpoint where your mistakes reside and what they are.
Creating a sequence is probably the simplest thing to do. By clicking New Sequence, the following window is displayed.
Figure 8: Creating a Sequence
The first step is to define the name of the sequence. In this case, I have a table named employees so it only makes sense to name the sequence seq_employees. While this is only a recommendation, you should use some sort of naming standard with your database objects. Next, define the type, values, and options of the sequence. If you are not sure what options to select, please refer to the following.
As I previously stated, I was only planning to cover a few of the features. To recap, I touched on working with table design, Oracle explorer, procedures, and sequences, all within the IDE. Overall, Oracle has done an outstanding job in integrating this toolset with the Visual Studio .NET IDE. I must admit that this is a much-welcomed addition, and it does make everyday tasks easier when working with an Oracle database.
Download Oracle Developer Tools for Visual Studio .NET
Mythbuster: Normalizing with Integers is Always Best
Oracle .NET Developer Center
Feel free to discuss this article at my Blog.