Posts in category: oracle sql

Introduction

PLSQL Developer is an Integrated Development Environment (IDE) for developing stored program
units in an Oracle Database. Using PLSQL Developer you can conveniently create the server-part of your client/server applications.

As a worst-case scenario, up to now you might have been working like this:
• You will use a text editor to write program units (procedures, triggers, etc.).
• If there is a compilation error, you have to find out where it is located in the source file, correct it,
switch back to SQL*Plus to recompile it, only to find the next error.
• You use SQL*Plus or the client-part of your application to compile the source files and to test the program unit.
• In case of a runtime error, again you have a hard time locating the cause of the problem and
correcting it.
• You use the Explain Plan utility or tkprof to optimize your SQL statements.
• To view or modify other objects and data in your database, you use SQL*Plus or yet another tool.
These tasks – editing, compiling, correcting, testing, debugging, optimizing and querying – can all be
performed without leaving PL/SQL Developer’s IDE. Furthermore, PL/SQL Developer provides several
other tools that can be helpful during every day PL/SQL development.

Table of Contents

• Introduction
• Editing
• Compiling & correcting
• Testing & debugging
• Optimizing
• Querying
• Running SQL scripts
• Creating and modifying table definitions
• Diagrams
• Reporting
• Graphics
• Projects
• Tools

Editing

PL/SQL Developer, like any other serious development environment, assumes that you store your
source files on disk. Other tools just let you edit sources in the database, but this does not allow for any version control or deployment scheme. The source files can be run through SQL*Plus, so you can deploy them on any platform without using PL/SQL Developer. You can edit many files at once through a standard multiple document interface.

The editor offers a wide range of assistance to the programmer. There is context sensitive help on SQL statements and PL/SQL statements. We’ve all been there: you start typing substr, but have forgotten the exact meaning of the parameters. Now, you can simply hit F1 and you’re taken to the appropriate topic in the SQL Reference Manual. Tables, views and program units can be described for you in a roll-up window from within the editor in the same way. A Code Assistant is integrated into the editor that automatically displays information of database objects as you type their name, allowing you to browse and pick elements from this description.

For large package or type bodies, the program editor provides a tree view with the code contents for easy navigation, highlights code structures and variable references, allows you to fold/unfold code sections, and provides hyperlink navigation. The Query Builder allows you to graphically create select statements. PL/SQL Developer’s extensible templates make it easy to insert standard SQL and PL/SQL code into your programs. Al editors use the appropriate SQL, PL/SQL and SQL*Plus syntax highlighting to make your code more readable.

Compiling & correcting

From within the editor you can compile a source file. In case of a compilation error you are
automatically taken to the appropriate source line. All compilation errors are reported in a list at the bottom of the editor. This list can include hints for code that may indicate common programming errors or violations of user-defined naming conventions.

Because you can compile a source file without saving it, you can safely explore many alternatives of solving a problem. The editor keeps track of the fact that you have changed the file without saving or compiling it.

Testing & debugging

To test your program unit, you can write a test script. The body of the test script contains a PL/SQL block in which you can program the test-code. Any variables that you want to use in the PL/SQL block can be declared, assigned a value for input, and viewed after execution.

When you execute a test script, a runtime error might occur. In this case, PL/SQL Developer allows you to view the sources of the error stack. Each source line of the error stack that was involved in the runtime error is highlighted, so you can easily backtrack to the cause of the problem.

If you are using Oracle 7.3.4 or later you can use PL/SQL Developer’s integrated debugger. You can step through your code, set breakpoints, view/set variables, view the call stack, and so on.

On Oracle8i and later you can additionally use the dbms_trace feature to log selected events of a program run. This can help you analyze the program flow and exceptions.

Output from calls to the dbms_output and the PL/SQL Web Toolkit packages are automatically shown in a corresponding tab page of the Test Window.

For regression testing you can use the Test Manager to quickly run and verify a set of Test Scripts.

Optimizing

To optimize the SQL statements in your program units, Oracle’s Explain Plan utility can be a big help. Therefore it is integrated into PL/SQL Developer’s IDE. By simply selecting the SQL statement in the source file and pressing F5, the query plan is visually presented to you in a separate Explain Plan window. You can then modify the statement to optimize its query plan outside the source file, and copy it back afterwards.

You can also view statistics about executed SQL statements and PL/SQL program units. These statistics can include elapsed time, CPU time, logical reads, physical reads, physical writes, and so on. Oracle8i introduced a PL/SQL Profiler that allows you to profile your PL/SQL code. For each executed line of PL/SQL code you can determine the execution time, and how many times it was executed.

Querying

To query the data in the database, you can use a SQL window to execute any SQL statement. All executed statements are kept in a history buffer, so you can easily re-execute them. Any query results are conveniently displayed in a separate grid that you can subsequently use to insert, update, or delete records. The result grid can additionally be used in a Query By Example mode, so that you can easily find the information you need.

To query database objects you can use the Object Browser. All relevant properties of database objects such as tables, views, sequences, functions, procedures, packages, types and triggers can be viewed, including any dependencies between the objects. The Object Browser uses a tree view similar to the explorer in Windows for easy point-and-click browsing

Running SQL scripts

PL/SQL Developer includes a Command Window that can be used to run SQL scripts or execute SQL statements. You can additionally execute commands that are very similar to the SQL*Plus commands that you may be familiar to.

Creating and modifying table definitions

You can easily create and modify table definitions with using any SQL statement. Just fill in the definition in a dialog window to modify columns, constraints, indexes, privileges, storage information, comments, and so on. You can apply this definition in the database, and view, modify and save the resulting SQL.

Diagrams

To visualize the database objects of your application or project and their relations, you can create diagrams. A diagram can be used for documentation purposes, but can also serve as a workspace to work with related objects.

Reporting

PL/SQL Developer comes with a number of standard reports, which are HTML based. You can view these reports within PL/SQL Developer, print them, or save them as HTML files. You can also create your own custom reports. Reports can be made easily accessible from the reports menu

Graphics

The Graph Window can be launched from within a SQL Window or Report Window to obtain a graphical representation of the queried data.

Projects

To organize your work you can use PL/SQL Developer’s project concept. A project consists of a number of files and database objects. These objects are easily accessible through the Project Items Window, and can be compiled through a single mouse-click.

Tools

PL/SQL Developer provides several tools that can be helpful during development. These tools include a Find Database Object tool, allowing you to search for text in database object sources, a Compile Invalid Objects tool, to quickly compile objects that have become invalid during development, Export and Import tools, a Data Generator tool to create test data, an Export User Objects tool to export the DDL statements of a user’s objects, a Compare User Objects tool to compare the object definitions of 2 users, a Session information tool, an Event monitor, a Compare Table Data tool to compare and equalize table contents, and a DBMS Scheduler tool to view and manage your scheduler objects and jobs. In addition to these standard tools, you can define your own tools and include them in PL/SQL Developer’s tools menu.

If you need to download the Oracle 11gR2 64 bit file click here.
To follow the installation process  Oracle 11gR2

If you like this post then share your view in the comment box.

To install SQL Developer click here