Introduction to the PL/SQL Editor

In the Gitora Editor Home, click the Connect button next to an Oracle username to open the Gitora PL/SQL Editor in a new tab.

The PL/SQL Editor shows up with an empty SQL Pad that uses the Oracle user you clicked, as its default connection information. The database object browser on the right shows the database objects in the Oracle user you clicked.

Expand the Packages Accordion in the object navigator. The list of packages in the schema will show up. Click the Edit button next to a package.

The Gitora PL/SQL Editor opens the package spec and body in the current browser tab. The editor will open any subsequent database object in a new browser tab.

Before we start editing, let’s go over the list of buttons and features in the editor.

The Toolbar

The toolbar is always at the top of the screen. Below is the list of buttons in the toolbar from left to right and a brief description for each one:

Show/Hide Method List

The Gitora PL/SQL Editor shows a powerful content navigator when a package or an object type is displayed in the editor. This button hides/shows the content navigator. It can be used to create more space for the PL/SQL editors and the SQL Pads.

Undo/Redo

Use these buttons to undo/redo changes you made in the current editor. Since a package and an object type have two editors, one for the spec and one for the body, these editors have separate undo queues.

However, if an editor is split in two using the split editor feature. The split editors share a single undo queue.

Comment Out/In

Select a block of code and click the Comment button to turn it into a block of comment. Conversely, select a block of comment and click the same button to turn the comment to a code block. This feature uses the — notation to create comments and similarly can reverse comments that start with –.

Indent

Select a code block and click the Indent button to shift the block 1 tab size to the right.

Outdent

Select a code block and click the Outdent button to shift the block 1 tab size to the left.

The Context Label

The context label in the middle of the toolbar displays the Oracle username and the database you clicked to open this tab as well as your Gitora Editor user name.

Save

Used to save the changes made to the database object open in the editor. The icon turns dark if there are changes to save.

Split Editor

Splits both the spec and the body editors in two horizontally. The icon turns dark if it is active.

Back/Forward

These buttons work similar to back and forward buttons in a browser. Gitora tracks the locations your cursor has been to in the editors. Clicking the back button takes your cursor to the previous location it was. Conversely, clicking forward button takes the cursor back to the next location.

Find and Replace

The Gitora PL/SQL Editor has a powerful Find&Replace widget that you can use to search for letters, whole words and replace them. You can even search using regular expressions. Clicking the icon the first time displays the widget. Clicking it the second time hides it. You can also use CTRL-F to show the widget.

Feedback

Please use the feedback button to share you comments or inform us about bugs and issues.

Color Mode

Click the color mode button to switch between light and dark mode.

Settings

Clicking the settings button displays the settings window.

In this window, you can set the font size, font family, tab size values for the Gitora PL/SQL editor.

Max Rows value determines the maximum number of rows a query you execute returns.

Text Length sets the maximum length of the text the editor returns. If set, longer text values will be cut at the length you specify.

Tick the Save Layout checkbox, if you’d like the Gitora PL/SQL editor to open reusing the widths and heights you set for the Method List on the left and the Object Navigator on the right.

Click the Save button to store your changes.

About

Click the icon to display the version and build number of the Gitora PL/SQL Editor.

The Workspace

The middle part of the Gitora PL/SQL Editor is your workspace. It consists of three columns. From left to right: the Method List (Package Navigator), the Editors area and the Database Object Navigator.

Each column is separated with a splitter. You can move these splitters left or right to adjust the width each column covers on the screen.

Method List (Package Navigator)

This column displays the procedures and functions in the package or object type that is open for editing in the editor.

The Search Box can be used to filter the methods in the list.

The Refresh button can be used to update the list if any of the new functions or procedures you created is not shown in the list.

The functions and procedures that are both on the spec and the body have a lighter color than the ones that exists only in the body. If a method only exists in the spec, its color is yellow.

Clicking a method name will take the cursor to its location in the active editor.

The Editors

The center of the workspace is where the code editors are. The editor has two types of editors: The PL/SQL editors and the SQL editors.

The Gitora PL/SQL Editor opens the first database object in the current tab. Subsequent objects are opened in new browser tabs.

Both editors provide auto complete suggestions. The default list of suggestions are all the words in the current open database object. After you type a dot character, the editor reads the word left of the dot and shows relevant suggestions depending on whether the word is a schema, a table, a view, a package or an object type. In the picture below, you see the editor recommending a list of method names because the user typed a package name.

Note the arrow sign > in the upper right corner of the suggestions box. Clicking the sign will expand the highlighted selection to display its full text. Change your selection and the expanded area will change with your selection. This is a toggle you can turn on/off with each editor separately.

Gitora detects [schema].[package]. syntax and can show suggestions for database objects in other schemas. In the example below, Gitora recommends the methods in the twi_call package which is in the HR schema.

The SQL Pad

Clicking the plus button next to the Body tab, creates a new SQL Pad which you can use to execute PL/SQL blocks, SQL queries, DML and DDL statements. You can create as many SQL Pads as you’d like.

SQL Pads consist of four rows. The toolbar, the worksheet, the query results and the messages rows. Let’s go over these rows one bye one:

The SQL Pad Toolbar

Each SQL Pad has its own toolbar with five buttons. From left to right:

Execute

Click this button to run your SQL statements.

Commit

Use this button to commit your changes. This button works only with a persistent database connection.

Rollback

Use this button to roll back your changes. This button works only with a persistent database connection.

DBMS Output

Use this button to toggle the DBMS Output on or off.

Connection

Use this button to change the current database connection of the SQL Pad.

When you create a new SQL Pad, by default it is connected to the same database user you clicked on to open the Gitora PL/SQL Editor. This connection is not persistent i.e. the Gitora Editor creates temporary database connections when you run the SQL statements by clicking the Execute button.

To change the database connection an SQL Pad is using click the Connect button. The database connection selection dialog shows up.

The dialog has the following actions:

No Connection: Select this option if you’d like to disconnect the editor from all database connections.

Persistent Connection: Toggle this button to on, if you’d like the connection you will select to be a persistent connection.

New Database: Click this button if the database you’d like to connect to does not exist as an option. This will take you to the Create New Database screen explained the the Introduction to the Gitora Editor Home tutorial.

Under the New Database button, you will see the list of databases you can connect. Expand the database you’d like to connect to see the available Oracle users that you can connect to.

Select the Oracle user you’d like the SQL Pad to use and the dialog will close. Your SQL Pad is now using your selection as its connection.

Select New User if you’d like to add a new Oracle username to the registered usernames. Your SQL Pad will use the new Oracle user you will register.

Persistent Database Connections

SQL Pads in Gitora PL/SQL Editor support persistent database connections. To create a persistent connection toggle the Persistent Connection button in the Connection Selection dialog as shown above.

After you select a connection, Gitora will create a persistent connection that the SQL Pad will to reuse every time you execute an SQL statement.

You can also share a persistent connection among other SQL Pads you create. To share a persistent connection:

Create a new SQL Pad.

Click the Connection button.

Toggle the Persistent Connection button to on. A new row titled Open Sessions will show up.

Clicking on the Open Sessions row will display existing persistent connections. Click the one you’d like the new SQL Pad to use and the new pad will share the same database session with the previous pad you created.

Gitora manages the persistent database connections automatically. Closing the last SQL Pad that uses an open database session will close it. Closing the browser tab will also close any unused database sessions.

The file at /gitora/datastore/GitoraEditor/default_pool_properties.json manages several properties that you can edit.

{"maxSizePerUser":25,
"timeout":3600,
"timeoutCheck":300}

maxSizePerUser property sets the maximum number of open database sessions per user.

timeout property sets the maximum number of seconds a session can remain idle before being closed by Gitora.

timeoutCheck property sets the number of seconds Gitora waits before checking if any session has timed out.

The Worksheet

The worksheet is where you write your SQL statements.

The Query Result

If you execute an SQL query, the results will show up in this row.

The Messages

The messages row has two tabs. The Errors tab shows the errors received while executing the SQL statement in the pad and the DBMS Output tab shows the results of the DBMS Output if there are any. Each SQL pad has its own message row.

The Database Object Navigator

The Database Object Navigator consists of two tabs. The Database Search tab and the Schema Object Navigator.

The schema object navigator shows the seven most commonly used database objects in the database user you are connected to. Just like in the Method List column, you can filter the lists for each object by its name and click the refresh button to display newly created objects.

Selecting and copying an object’s name with CTRL+C copies the objects name in lowercase to the clipboard. The object name is not clickable to make copying easier.

Tables

Each table has a table widget that shows its columns and the foreign keys. Both the column list and he foreign keys can be filtered and refreshed.

In the Columns tab, the column names with a star * indicate primary key columns.

Views

Each view has a view widget that displays its columns.

Each view also has a dropdown button with a list of actions. Clicking the Edit button opens the view in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a view.

Packages (Types)

Package and Types work the same way. So we will only go over packages.

Each package has a widget that displays its procedures and functions. Similar to the Method List column, the private methods are in dark color and the public methods are in a light color.

Uncompiled packages have a red icon.

Each package also has a dropdown button with a list of actions. Clicking the Edit button opens the package spec and body in the Gitora Editor. If the current tab already has an open database object, the object is opened in a new browser tab. Otherwise, it is opened in the current tab. You can also compile or drop the spec and the body of a package.

If a package’s spec or body is in an uncompiled state, the dropdown will show the relevant compile action in red.

If a package is missing the body, the compile and drop actions for the body will not show up in the button dropdown.

Functions

Each function has a dropdown button with a list of actions. Clicking the Edit button opens the function in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a function.

Procedures

Each procedure has a dropdown button with a list of actions. Clicking the Edit button opens the procedure in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a procedure.

Triggers

Each trigger has a dropdown button with a list of actions. Clicking the Edit button opens the trigger in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a trigger.

Messages

The bottom row in the editor shows error messages received from the database when you save a database object. Clicking an error will take the cursor to the location of where the error has occurred and highlight that row for a few seconds.

Database Search

The other tab in the third column of the workspace is the Database Search tab. In this tab, you can search for any text in the entire database code base or only in a schema.

The searches are case insensitive.

The results are grouped by schema and database object.

The icons indicate the type of object that the searched term is found in. The closed package icon indicates a package spec and an open package icon indicates a package body.

Click on an object to view the lines in the object that contain the searched term.

Clicking the line number will open a new Gitora PL/SQL Editor in a new tab. The new tab will have the database object with the search term open in the editor and the line number you clicked on will be the top line in the editor.