Managing Oracle Script Files

Oracle SQL Script Compatibility Requirements

Please note that the script execution engine used by default with Visual Studio does not honor Oracle specific SQL syntax nor SQL*Plus directives.

To allow the execution of Oracle SQL scripts, the Oracle Developer Tools for Visual Studio includes a SQL*Plus engine.

Oracle SQL scripts are subject to the following requirements:

Auto-Commit Mode

The SQL script is executed in auto-commit mode by default, which you can change using a SQL*Plus command in the script to turn auto-commit on or off.

SQL*Plus Command Support

Oracle Developer Tools supports most SQL*Plus script commands. The following commands are not supported:

HOST
EDIT
GET
SAVE
SPOOL
START
STORE

Character set Encoding of Script File

The Oracle SQL script file should be in the same encoding as specified in the Oracle NLS_LANG environment variable setting. Please refer to the Oracle Database Globalization Support Guide, 11g release 1 (11.1) for more information.

Special Characters in Script File Name

The SQL*Plus script file name cannot contain any of the following special characters:

*, ?, ", <, >, |, /, \

For file names containing these characters an "Invalid file name" error message appears in the output pane and the script will not run.

Adding New Scripts

You can add new scripts to an Oracle Database Project using Visual Studio.

To add a new script, do one of the following:

Right-click the Oracle Database Project root node or the individual type folder node and select Add New Item
Select the root node or folder node in Solution Explorer, then select the Project Menu and Add New Item.

The Add New Item dialog box appears.

Description of the illustration add_new_item.gif

The Add New Item dialog box lists categories on the left under Oracle Database Project Items, and script templates on the right. When you select a category, a list of the available script templates is displayed. The main categories are:

General
Data
Code
XML
User Defined Types
Security

Once you click Add, the newly created file opens in the SQL editor.

Adding Existing Scripts

You can add existing scripts to an Oracle Database Project using Visual Studio.

To add an existing script, do one of the following:

Right-click the Oracle Database Project root node or folder node and select Add Existing Item
Select the root node or folder node in Solution Explorer, then select the Project Menu and Add Existing Item.

The Add Existing Item dialog box appears. You can browse to the desired script, text, XML, or other file, and select it.

This command does not automatically open the added script.

Adding Automatically Generated Scripts for Schema Objects

Scripts can be automatically generated and added to the Oracle Database Project in several ways.

This section covers these topics:

Using Drag-and-Drop to Generate Scripts

You can drag and drop an Oracle schema object directly from Server Explorer onto an Oracle Database Project folder. This generates an Oracle SQL script in the folder.

Multiple schema objects can be selected and dragged and dropped in one operation. If multiple objects in Server Explorer are selected, a master SQL script is created in the Oracle Database Project Scripts folder. This master script contains calls to individual child SQL scripts (one for each schema object) generated into the target folder. The master script is ordered with dependencies taken into consideration.

Using Generate Create Script to Project Directly from Schema Objects

In Server Explorer, most database schema object nodes have a Generate Create Script to Project menu item. To use this menu item, an Oracle Database Project must exist within Solution Explorer.

Using the Generate Create Script to Project menu item, you can generate a SQL script for a database schema object to an Oracle Database Project in the folder corresponding to its schema object type. If that folder no longer exists or has been renamed, the script is written to the root of the project.

Multiple schema objects can be selected and then the Generate Create Script to Project menu item can be called from one of them. If multiple objects in Server Explorer are selected, a master SQL script is created in the Oracle Database Project Scripts folder. This master script contains calls to individual child SQL scripts (one for each schema object) generated into folders corresponding to the schema object types. The master script is ordered with dependencies taken into consideration.

If there are multiple Oracle Database projects open in your solution, you are prompted to select the project to add the script to.

Using Preview SQL Dialog Box to Generate Scripts to Projects

A Preview SQL dialog box can be launched from various Oracle designers and wizards. This Preview SQL Dialog contains the SQL needed to effect the changes made to the Schema object. The dialog includes a button for generating the SQL script to an Oracle Database Project for the following designers: Table, View, Procedure, Function, Trigger, Package, Synonym, and Sequence.

When you click on Add to Project in the Preview SQL dialog, the generated script is written to a new file in the folder for that Oracle Schema type.

If that folder no longer exists or has been renamed, the script is written to the root of the project. If there are multiple Oracle Database projects open in your solution, you are prompted to select the project to add the script to, but not the script name or location. If a file or files already exist by the specified name, a dialog box pops up asking whether or not you want to replace the existing file(s).