• Return to Oracle APEX Home

Oracle SQL Developer provides an interface to Oracle Application Express allowing you to browse, monitor, and manage your database applications, and perform other Oracle Application Express tasks. For details about SQL Developer and to download the latest version, visit the Oracle SQL Developer OTN site.
[Note: You can use Oracle SQL Developer 1.2.1 and above with Oracle Application Express 3.0.1 and above.]

Using SQL Developer, you can perform the following functions:

• Browse your Application Express Applications
• Export and Import Applications
• Drop Applications
• Deploy Applications
• Modify Applications
• Export Pages
• Tune your Queries
• Included Application Express Reports
• Custom Exception Reports

Browse

You can use the intuitive SQL Developer browser to view details about your applications. Within SQL Developer, when you expand a connection whose schema owns any Application Express applications, you will see a node named Application Express. Further expanding this node will display a list of all the applications owned by that schema. You can click an application and a detail tab will display with all the application-level details. This includes Lists of Values, Lists, Templates, Tabs, etc. You can expand any application and you will see a list of the pages that make up that application. Clicking a page displays a detail tab with all the page-level details. This includes Regions, Items, Buttons, Processes, etc.

If you want to compare details of different pages or different applications, you can use the Freeze View feature (click the icon that looks like a push pin). This allows you open more than one detail tab and then you can stack them next to each other.

Export and Import Applications

From within the Connection Navigator, you can export and import Application Express applications. To export, you simply right-click the desired application and select Quick DDL. You can save to a file, a SQL Developer Worksheet, or the Clipboard. You can also see the DDL that makes up the export file by clicking on the SQL tab when you are viewing an application.

To Import, you right-click the main Application Express node and select Import Application. A wizard will guide you through selecting your file and you will be given the opportunity to rename the application, set a new Application Alias, Build Status and Application ID. This process both imports and installs the application. You can also choose to run the installation script which will install supporting objects, if the application export contains supporting objects. Once the import is complete, you will need to refresh the navigator to display the newly created application (you can use the icon that looks like double arrows or right-click Application Express and select Refresh).

Please note: When installing your supporting objects using SQL Developer, you will experience errors if your PL/SQL statements are not ended with a slash (/) as in the following example.

			begin
     run_this.procedure;
   end;
   

These statement do not cause errors when installing using Oracle APEX. To test, you can display your installation script as a single script and execute either in a SQL Developer worksheet or in SQL*Plus. This will highlight any errors that your users might encounter.

Deploy Applications

If you need to copy an application from one instance to another or one workspace to another, rather than exporting and then importing, you can just use the deploy feature. You simply right-click the source application and select Deploy Application. When prompted, select another connection for your target and then customize all the same options you get when you import.

Modify Applications

From with SQL Developer, there are several application-level attributes that you can modify. These include, Alias, Name, Status, Global Notification and Proxy. As with Export, Import and Deploy, Modify Application is available as a right-click off of a selected application.

Export Pages

Just as with applications, you can also export pages. You either right-click the desired page or just click on the SQL tab when you are viewing the details of the page. Using the right-click, you will be prompted to save the resulting file. With the SQL tab, you would manually and copy and paste to a desired location.

Tune Queries

While browsing your application, you can cut and paste your queries into a SQL Worksheet and execute them. Your queries will be the List of Values Query on the Lists of Values tab, the Region Source on the Regions tab, the validation expression on the validations tab - anywhere you can put SQL or PL/SQL. Please note that when you paste, you will need to remove the double-quotes that will be wrapped around your code.

The SQL Worksheet supports bind variables so your item names can be left in and you can supply values at execution time. You can run the Explain Plan or an Autotrace to look for places to improve your performance. If you change your query, the performance improvements can be checked by monitoring the execution time of the query. Once you have perfected your query, you will need to use the Application Express interface to update your application - the SQL Developer interface to Application Express is read-only.

Included Application Express Reports

Under the Reports tab, within Data Dictionary Reports, there is a section for Application Express. There is a report for Applications, Pages, Schemas and Workspace. Each Applications and Pages are master/detail report. For Applications, you can select an application from the report above and then the details for that application will be displayed below.

To customize these reports, you can click the Run Report in SQL Worksheet icon. Within the SQL Worksheet, you can further customize the SQL and then save it as a User Defined (or custom) report.

Custom Exception Reports

Application Express provides many Application and Workspace reports that allow you to view your application objects in different ways but quite often, a developer needs to check for something that does not have a report. You can now use SQL Developer to write queries against your applications and then save those queries as custom reports. This can quickly allow you to check for all items that are using a certain label template, find all regions of a certain type, or list out all your static lov values.

The reports you create would be located on the Reports tab under User Defined Reports. You simply right-click the User Defined Reports node and can add a report or add a folder. This allows you to capture and reuse all your best queries using the Application Express views.