← Back to Table of Contents

Exploring Objects and Executing SQL

This guide covers how to explore database objects and execute SQL queries in the SQLife workbench.

Table of Contents

Overview

The SQLife workbench is your central workspace for interacting with databases. It combines a powerful SQL editor with an intuitive object navigator and a feature-rich data viewer.

Main Components

SQL Editor (Top):

  • Write and edit SQL queries
  • Syntax highlighting and auto-completion
  • Error detection and formatting
  • Multiple cursor support

Data View (Bottom):

  • View query results
  • Edit table data
  • Export results
  • Multiple result set tabs

Object Navigator (Left Sidebar):

  • Browse database schemas
  • Explore tables, views, procedures, functions
  • Quick access to object operations
  • Object filtering

The sidebar also contains additional tabs:

  • Outline - Real-time hierarchical tree of the SQL blocks in the active editor
  • Function - Searchable list of built-in database functions (double-click to insert)
  • Scripts - Workspace files and Git integration
  • SFTP - Embedded remote file browser (see Getting Started for details)

Toolbar:

  • Quick access to common operations
  • Run, Stop, Commit, Rollback buttons
  • File operations (New, Open, Save)
  • Edit operations (Format, Comment, Undo/Redo)

The SQL Editor

The SQL Editor is a powerful code editor designed specifically for SQL development.

Writing SQL Queries

Basic Query:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 10
ORDER BY salary DESC;

Key Features:

  • Line numbers on the left
  • Syntax highlighting for keywords
  • Auto-indentation
  • Bracket pair highlighting
  • Multiple cursors (Alt + Click)

Syntax Highlighting and Features

Highlighted Elements:

  • SQL Keywords: SELECT, FROM, WHERE, etc. (blue)
  • Strings: Text in quotes (green)
  • Numbers: Numeric values (orange)
  • Comments: Single-line -- and multi-line /* */ (gray)
  • Current Identifier: Double-click a word to highlight all occurrences

Bracket Highlighting:

  • Place cursor next to (, ), {, }, [, ]
  • Matching bracket highlights automatically
  • Helps balance complex nested queries

Line Wrapping:

  • Enable/disable in Preferences → Editor → Line Wrap
  • Useful for long SQL statements
  • Doesn't affect saved file

Code Completion

Press Ctrl + Space to trigger auto-completion:

What Gets Completed:

  • SQL keywords (SELECT, FROM, WHERE, etc.)
  • Table names from current schema
  • Column names (after typing table name)
  • Function names (including built-in functions of the current database)
  • Schema names

Manual Trigger:

  • Press Ctrl/Cmd + Shift + P to force the suggestion popup at the caret
  • This also works when there is no word before the caret — e.g., with the caret after select in select <caret> from table1, SQLife resolves the table from the FROM clause and lists its columns

Example:

SEL[Ctrl+Space] → SELECT
FROM emp[Ctrl+Space] → FROM employees
WHERE first_n[Ctrl+Space] → WHERE first_name

Tips:

  • Type a few characters before triggering
  • Use arrow keys to navigate suggestions
  • Press Enter or Tab to accept
  • Press Esc to dismiss

Error Highlighting

The editor detects syntax errors in real-time:

Visual Indicators:

  • Red underline: Syntax error
  • Yellow underline: Warning (e.g., missing semicolon)
  • Hover over: See error message

Common Errors:

  • Missing semicolons (warning in most cases)
  • Unmatched quotes or brackets
  • Invalid SQL keywords
  • Typos in function names

Note: Error detection is best-effort and may not catch all database-specific errors until execution.

Executing SQL Statements

Running Queries

Method 1: Keyboard Shortcut

  • Press Ctrl/Cmd + Enter to execute SQL
  • Fastest method
  • Works for selected SQL or entire script

Method 2: Run Button

  • Click Run button in toolbar (▶ icon)
  • Same as the shortcut

Method 3: Menu

  • Database → Run
  • Shows keyboard shortcut

What Happens:

  1. SQL is sent to the database
  2. Status bar shows "Executing..."
  3. Results appear in Data View
  4. Time elapsed displays in result pane

Quick Query Palette

The Quick Query palette lets you run SQL against any saved connection without opening a database tab first.

How to Open:

  • Press Alt + Space (Windows/Linux) or Option + Space (macOS), or
  • Go to Database → Quick Query

What You Can Type:

  • SQL - Execute any statement; results render in the fast data view
  • schema.table - Auto-runs SELECT * FROM schema.table;
  • @connection - Switch to another saved connection (e.g., @postgres); Enter resolves a partial name to the highlighted suggestion

Tips:

  • The palette reopens on the connection you used last
  • Suggestions and results share a single result area and toggle between each other
  • Press Tab to complete a name with the highlighted object

Run Asynchronously

For long-running queries that may take minutes or hours:

How to Use:

  1. Go to Database → Run → Run Asynchronously
  2. Or right-click in editor and select Run Asynchronously
  3. Query executes in background
  4. You can continue working in the editor
  5. Results appear when complete

When to Use:

  • Complex analytical queries
  • Large data exports
  • Batch operations
  • Report generation

Visual Feedback:

  • Tab shows busy indicator (animated icon)
  • Status bar shows progress
  • Can switch to other tabs while running

Stopping Execution

If a query takes too long or was started by mistake:

How to Stop:

  1. Click Stop button in toolbar (⏹ icon)
  2. Or press Esc (if cursor is in editor)
  3. Or Database → Stop

What Happens:

  • SQL execution is cancelled
  • Partial results may be shown (if any)
  • Transaction remains open (commit or rollback as needed)

Note: Some databases may take time to respond to cancellation.

Running Selected SQL

When you have multiple statements in the editor:

How to Run Selection:

  1. Select the SQL you want to execute (click and drag)
  2. Press Ctrl/Cmd + Enter or click Run
  3. Only the selected SQL executes

Example:

-- Statement 1
SELECT * FROM employees;

-- Statement 2
SELECT * FROM departments;

-- To run only Statement 1, select it and press Ctrl/Cmd + Enter

Tips:

  • Select entire statement including semicolon
  • Partial selection may cause syntax errors
  • Use for testing individual statements

Running Multiple Statements

To execute multiple statements in sequence:

Method 1: Run All (No Selection)

  1. Ensure no text is selected
  2. Press Ctrl/Cmd + Enter or click Run
  3. All statements execute in order

Method 2: Statement-by-Statement

  • SQLife automatically detects statement boundaries
  • Each statement executes separately
  • Results appear in separate tabs

Example:

-- These all execute when you press Ctrl/Cmd + Enter (with no selection)
INSERT INTO departments (dept_id, dept_name) VALUES (100, 'IT');
INSERT INTO departments (dept_id, dept_name) VALUES (110, 'Sales');
INSERT INTO departments (dept_id, dept_name) VALUES (120, 'HR');
COMMIT;

Error Handling:

  • If one statement fails, you'll be prompted:

    "An error occurred. Do you want to continue?"

  • Click Yes to continue with next statement
  • Click No to stop execution

Best Practice:

  • Test each statement individually first
  • Group related statements together
  • Add comments for clarity

Transaction Management

SQLife uses manual commit mode (autocommit = false) for data safety.

Commit Changes

Permanently save changes to the database:

How to Commit:

  • Press Ctrl/Cmd + Shift + C, or
  • Click Commit button in toolbar (✓ icon), or
  • Database → Commit

When to Commit:

  • After INSERT, UPDATE, DELETE statements
  • After DDL changes (CREATE, ALTER, DROP)
  • When you're sure the changes are correct
  • Before closing connection or tab

Visual Feedback:

  • Status bar shows "Transaction committed"
  • Changes are permanent

Example Workflow:

-- 1. Make changes
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;

-- 2. Verify changes
SELECT * FROM employees WHERE dept_id = 10;

-- 3. If correct, press Ctrl+Shift+C to commit

Rollback Changes

Undo uncommitted changes:

How to Rollback:

  • Press Ctrl/Cmd + Shift + R, or
  • Click Rollback button in toolbar (↶ icon), or
  • Database → Rollback

When to Rollback:

  • After making a mistake
  • If changes are incorrect
  • Before closing without saving changes
  • To undo recent modifications

Visual Feedback:

  • Status bar shows "Transaction rolled back"
  • Changes are discarded

Example:

-- Oops, wrong department!
DELETE FROM employees WHERE dept_id = 10;

-- Realize mistake, press Ctrl+Shift+R to rollback
-- No data is actually deleted

Auto-Commit vs Manual Commit

Manual Commit (SQLife Default):

  • Safer: Changes must be explicitly committed
  • Flexible: Can rollback mistakes
  • Best for: Data modifications, testing, development

Auto-Commit (Not Recommended):

  • Changes commit immediately
  • Cannot be rolled back
  • Not available in SQLife (by design)

Important Reminders:

  • Always commit or rollback before closing
  • Tab close prompt reminds you if uncommitted changes exist
  • Uncommitted changes are lost on disconnect

Viewing Query Results

Data View Basics

After running a query, results appear in the Data View:

Components:

  • Tab: Shows result set name (e.g., "Result 1", "EMPLOYEES")
  • Table: Displays rows and columns
  • Toolbar: Refresh, Export, Lock/Unlock, navigation
  • Status Bar: Row count, execution time

Column Headers:

  • Click to sort (ascending/descending)
  • Right-click for more options
  • Resize by dragging border
  • Reorder by dragging column

Row Navigation:

  • Scroll vertically to see more rows
  • Page through data with Next button
  • Jump to specific row with navigation controls

Multiple Result Sets

When running multiple SELECT statements:

Tab Layout:

  • Each result set appears in a separate tab
  • Tabs labeled "Result 1", "Result 2", etc.
  • Or named after table (if simple query)

Switching Between Results:

  • Click tab to view
  • Close individual tabs with × button
  • Right-click tab for more options

Example:

SELECT * FROM employees;
SELECT * FROM departments;
SELECT * FROM jobs;

Creates three result tabs.

Result Set Operations

Sorting:

  1. Click column header to sort ascending
  2. Click again to sort descending
  3. Click a third time to clear sort

Filtering (Visual):

  • Scroll to find data
  • Use Ctrl+F to search within results
  • Export and filter externally for complex filtering

Copying Data:

  1. Select cells (click and drag)
  2. Ctrl/Cmd + C to copy
  3. Paste into Excel, text editor, etc.

See Also:

Using DBMS_OUTPUT (Oracle)

Oracle's DBMS_OUTPUT package lets you print messages from PL/SQL.

Enabling DBMS_OUTPUT

In SQLife, DBMS_OUTPUT is automatically enabled for Oracle connections.

Example PL/SQL Block:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL!');
    DBMS_OUTPUT.PUT_LINE('Current time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
END;
/

Viewing Output

Where Output Appears:

  • After executing PL/SQL block
  • In the Output pane (below editor)
  • Or in Messages tab

Output Format:

Hello from PL/SQL!
Current time: 14:35:22

PL/SQL procedure successfully completed.

Buffer Size

Default buffer size is usually sufficient. If you get buffer overflow:

Manual Setting (if needed):

BEGIN
    DBMS_OUTPUT.ENABLE(1000000); -- 1 MB buffer
END;
/

Common Uses

Debugging PL/SQL:

CREATE OR REPLACE PROCEDURE calculate_bonus(emp_id IN NUMBER) IS
    v_salary NUMBER;
    v_bonus NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = emp_id;
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
    
    v_bonus := v_salary * 0.1;
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
    
    -- More logic...
END;
/

BEGIN
    calculate_bonus(100);
END;
/

Loop Progress:

BEGIN
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Processing record ' || i);
        -- Do something
    END LOOP;
END;
/

Explain Plans

Analyze how the database executes your queries.

Viewing Execution Plans

How to Get Explain Plan:

  1. Write your query in the editor
  2. Select the query (or place cursor in it)
  3. Press Ctrl/Cmd + E, or
  4. Click Explain button in toolbar (📊 icon), or
  5. Database → Explain

What Happens:

  • Database generates execution plan
  • Explain window opens showing plan details
  • Original query remains unchanged (not executed)

Understanding Explain Output

Tree View:

SELECT STATEMENT
└─ TABLE ACCESS FULL
   └─ EMPLOYEES

Key Information:

  • Operation: What the database does (scan, join, sort, etc.)
  • Object Name: Table or index involved
  • Cost: Estimated resource usage (lower is better)
  • Cardinality: Estimated number of rows
  • Bytes: Estimated data size

Common Operations:

  • TABLE ACCESS FULL: Full table scan (may be slow for large tables)
  • INDEX RANGE SCAN: Using an index (usually fast)
  • NESTED LOOPS: Join method
  • HASH JOIN: Another join method
  • SORT: Sorting operation

Plain Text View: Switch to plain text for copy-paste:

  1. Click Plain Text tab in Explain window
  2. Copy entire plan
  3. Share with DBA or paste into documentation

Interpreting Plans

Good Signs:

  • Low cost values
  • Index usage on large tables
  • Few rows processed

Warning Signs:

  • Full table scans on large tables
  • High cost values
  • Cartesian products (missing join conditions)

Example Analysis:

-- Slow query
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
-- Plan shows TABLE ACCESS FULL (bad)

-- Faster query
SELECT * FROM employees WHERE last_name = 'SMITH';
-- Plan shows INDEX RANGE SCAN (good)

Tip: Run EXPLAIN before executing expensive queries to catch performance issues early.

The Workbench Toolbar

Quick access to common operations.

File Operations

New (Ctrl/Cmd + N):

  • Opens new connection tab or file tab
  • Choose from menu

Open (Ctrl/Cmd + O):

  • Opens SQL file from disk
  • Shows recent files list
  • Supports drag-and-drop

Save (Ctrl/Cmd + S):

  • Saves current SQL file
  • Prompts for filename if new file
  • Saves with current encoding

Save As (Ctrl/Cmd + Shift + S):

  • Saves with new filename
  • Choose location and name

Edit Operations

Undo (Ctrl/Cmd + Z):

  • Undo last edit
  • Multiple levels supported

Redo (Ctrl/Cmd + Shift + Z):

  • Redo undone edit

Cut/Copy/Paste (Ctrl/Cmd + X/C/V):

  • Standard clipboard operations
  • Works with selected text

Indent/Unindent (Ctrl/Cmd + ] / [):

  • Indent selected lines
  • Unindent selected lines
  • Respects tab size setting

Comment/Uncomment (Ctrl/Cmd + /):

  • Toggle SQL comments (--)
  • Works on single line or selection

Format (Ctrl/Cmd + Shift + F):

  • Auto-format SQL code
  • Improves readability
  • Follows SQL formatting rules

Query Execution

Run (Ctrl/Cmd + Enter):

Stop (Esc):

Explain (Ctrl/Cmd + E):

Transaction Controls

Commit (Ctrl/Cmd + Shift + C):

Rollback (Ctrl/Cmd + Shift + R):

Using the Outline View

The Outline View provides a structured navigation for your SQL code, similar to code outlines in modern IDEs.

What is the Outline View?

The Outline View is a tab in the left sidebar (next to Objects, Scripts, Function, and SFTP tabs) that automatically parses your SQL/PL-SQL code and displays its structure in a hierarchical tree view. It's particularly useful for navigating large SQL scripts with multiple procedures, functions, and packages.

Key Benefits:

  • Quick navigation to specific code sections
  • Visual overview of code structure
  • Easier code understanding and organization
  • Automatic parsing as you type

Accessing the Outline View

How to Open:

  1. Look at the left sidebar of the workbench
  2. Click the Outline tab (tree icon rotated 90°)
  3. The Outline View appears, showing the structure of your current SQL code

Keyboard Shortcut:

  • Ctrl/Cmd + O switches to the Outline tab in the sidebar by default
  • Ctrl/Cmd + Shift + O opens the outline as a floating popup window
  • Check Preferences → Key Mapping for the assigned shortcuts

When It's Useful:

  • Working with large PL/SQL packages
  • Navigating complex stored procedures
  • Understanding unfamiliar code
  • Jumping between multiple functions quickly

What the Outline View Shows

The Outline View displays different types of SQL and PL/SQL blocks:

Procedures:

  • Format: [PROC] procedure_name(param1:TYPE, param2:TYPE)
  • Shows parameters with their types
  • Indicates if declared in spec and/or implemented in body

Functions:

  • Format: [FUNC] function_name(param1:TYPE) → RETURN_TYPE
  • Shows parameters and return type
  • Displays spec and body status

Packages:

  • Format: [PKG SPEC] package_name or [PKG BODY] package_name
  • Nested routines shown as child items
  • Expand to see contained procedures and functions

SQL Statements:

  • Format: [SQL] CREATE TABLE → table_name
  • Shows DDL statements (CREATE, ALTER, DROP)
  • Displays DML statements (SELECT, INSERT, UPDATE, DELETE)

Triggers:

  • Format: [TRIG] trigger_name
  • Shows trigger definitions

Anonymous Blocks:

  • Format: [BLOCK] Line 5
  • Shows BEGIN...END blocks by line number

Using the Outline View

Basic Navigation:

  1. Type or paste SQL/PL-SQL code in the editor
  2. The Outline View automatically parses and updates (300ms delay)
  3. Tree items appear representing code blocks
  4. Click any item to jump to that location in the editor

What Happens When You Click:

  • Caret moves to the start of the selected block
  • Editor scrolls to make the code visible
  • Focus returns to the editor for immediate editing

Expanding/Collapsing:

  • Click ▶ to expand packages and see nested routines
  • Click ▼ to collapse sections
  • Useful for focusing on specific parts of large files

Example Code Structure:

CREATE OR REPLACE PACKAGE employee_pkg AS
    PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER);
    FUNCTION get_bonus(p_emp_id NUMBER) RETURN NUMBER;
END employee_pkg;
/

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
    PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER) IS
    BEGIN
        INSERT INTO employees(name, salary) VALUES(p_name, p_salary);
    END;
    
    FUNCTION get_bonus(p_emp_id NUMBER) RETURN NUMBER IS
        v_bonus NUMBER;
    BEGIN
        SELECT salary * 0.1 INTO v_bonus FROM employees WHERE emp_id = p_emp_id;
        RETURN v_bonus;
    END;
END employee_pkg;
/

Outline View Shows:

└─ [PKG SPEC] employee_pkg
   ├─ [PROC] hire_employee(p_name:VARCHAR2, p_salary:NUMBER)
   └─ [FUNC] get_bonus(p_emp_id:NUMBER) → NUMBER
└─ [PKG BODY] employee_pkg
   ├─ [PROC] hire_employee(p_name:VARCHAR2, p_salary:NUMBER)
   └─ [FUNC] get_bonus(p_emp_id:NUMBER) → NUMBER

Database Support

Fully Supported:

  • Oracle: Complete PL/SQL parsing including packages, procedures, functions, triggers
  • Recognizes both package specs and bodies
  • Shows parameter lists and return types

Limited Support:

  • MySQL/PostgreSQL: Basic SQL statement recognition
  • Procedures and functions may be partially supported
  • Check the Outline View with your database to see what's detected

Not Parsed:

  • Comments and whitespace (not shown as separate items)
  • Variable declarations (shown as part of blocks)
  • Internal block structure beyond top-level routines

Tips for Using the Outline View

Keep It Visible:

  • Pin the Outline View tab for quick access
  • Resize the sidebar if tree items are too narrow
  • Collapse it when working with simple queries

Use with Large Scripts:

  • Essential for navigating files with 1000+ lines
  • Quickly jump between procedures without scrolling
  • Find specific functions in large packages

Combine with Find:

  • Use Ctrl/Cmd + F to search for text
  • Use Outline View to jump to specific blocks
  • Together they provide comprehensive navigation

Refresh Behavior:

  • Updates automatically as you type (300ms debounce)
  • No manual refresh needed
  • Parsing happens in background (won't freeze UI)

Troubleshooting

Outline View is Empty:

  • Cause: No parseable SQL/PL-SQL code in editor
  • Solution: Write some SQL code, or open a file with PL/SQL

Code Not Appearing:

  • Cause: Syntax errors prevent parsing
  • Solution: Fix syntax errors (check red underlines in editor)

Slow Parsing:

  • Cause: Very large files (10,000+ lines)
  • Solution: Consider splitting into smaller files

Wrong Database Type:

  • Cause: Using MySQL/PostgreSQL syntax with Oracle parser
  • Solution: Outline View is optimized for Oracle; may have limited support for other databases

Exploring the Object Navigator

The left sidebar provides quick access to database objects.

Schema Selection

Schema Dropdown:

  • Located at top of Object Navigator
  • Lists all schemas you can access
  • Click to switch schemas

Favorites:

  • Star icon next to frequently used schemas
  • Click star to add/remove from favorites
  • Favorites appear at top of list

Last Schema:

  • SQLife remembers last selected schema per connection
  • Restores on reconnect

Filter Schemas:

  • Type in dropdown to filter schema list
  • Useful for databases with many schemas

Object Tree Navigation

Expand/Collapse Folders:

  • Click ▶ to expand object type folder
  • Click ▼ to collapse

Object Types:

  • Tables: User tables with data
  • Views: Virtual tables
  • Procedures: Stored procedures
  • Functions: User-defined functions
  • Indexes: Database indexes
  • Constraints: Primary keys, foreign keys, unique, check
  • Triggers: Event-driven code
  • Sequences: Number generators (Oracle, PostgreSQL)
  • Materialized Views: Cached query results (Oracle, PostgreSQL)
  • Synonyms: Aliases for objects (Oracle)
  • Database Links: Remote database connections (Oracle)
  • DBMS Jobs: Scheduled jobs (Oracle)
  • Packages: PL/SQL packages (Oracle)
  • Extensions: PostgreSQL extensions

Table Sub-Items:

  • Expand a table to see:
    • Columns
    • Indexes
    • Constraints
    • Triggers
    • Partitions (if any)

Object Filter

Filter Box:

  • Located below schema dropdown
  • Type to filter object names
  • Case-insensitive search
  • Updates tree in real-time

Filter Examples:

  • Type emp to show only objects containing "emp"
  • Type order to find ORDER_DETAILS, ORDERS, etc.
  • Clear filter to show all objects

Tips:

  • Filter applies to current schema only
  • Filters all object types simultaneously
  • Regular expressions not supported

Quick Actions

Right-Click Context Menu: Different options based on object type:

Tables:

  • Query Data
  • Edit Data
  • Rename Table Name
  • Drop Table
  • Refresh
  • Object Definition
  • Property

Views:

  • Query Data
  • Rename View Name
  • Drop View
  • Compile View (Oracle only)
  • Object Definition

Procedures/Functions:

  • Rename
  • Drop
  • Compile (Oracle only)
  • Object Definition

See Also:

Working with SQL Files

Opening SQL Files

Method 1: Menu

  1. File → Open (Ctrl/Cmd + O)
  2. Browse to SQL file
  3. Click Open

Method 2: Recent Files

  1. File → Reopen
  2. Select from recent files list

Method 3: Drag and Drop

  • Drag SQL file from file explorer
  • Drop onto SQLife window
  • File opens in new tab

Method 4: Scripts Workspace

  • Click Scripts tab in sidebar
  • Navigate to file
  • Double-click to open

Supported Encodings:

  • UTF-8 (default)
  • UTF-16
  • ISO-8859-1
  • And more (selected automatically or manually)

Saving SQL Files

Save (Ctrl/Cmd + S):

  • Saves changes to current file
  • If new file, prompts for filename

Save As (Ctrl/Cmd + Shift + S):

  • Save with new name/location
  • Creates copy of current file

Auto-Save:

  • SQLife auto-saves state for crash recovery
  • Does NOT auto-save file to disk
  • Temporary recovery files in ~/.sqlife/recovery/

Encoding:

  • Default encoding: UTF-8
  • Change in status bar (bottom right)
  • Saves with selected encoding

Recent Files

Accessing Recent Files:

  1. File → Reopen
  2. List of recently opened files
  3. Click to reopen

Recent Files Limit:

  • Configure in Preferences → General
  • Default: 10 files
  • Maximum: 50 files

Clear Recent Files:

  • Not directly available
  • Reopen file history persists until limit reached

Editor Preferences

Customize the SQL Editor to your liking:

Preferences → Editor:

Font Family:

  • Choose from installed monospace fonts
  • Recommended: Consolas, Monaco, Menlo, Courier New

Font Size:

  • Range: 8-32 pt
  • Default: 14 pt

Line Wrap:

  • Enable to wrap long lines
  • Disable for horizontal scrolling

Tab Size:

  • Spaces per tab: 2, 4, 8
  • Default: 4

Indent By Space:

  • Use spaces instead of tabs
  • Recommended for SQL files

More Preferences:

Tips and Best Practices

Writing Clean SQL

Use Formatting:

  • Press Ctrl/Cmd + Shift + F regularly
  • Keep SQL readable
  • Follow team conventions

Add Comments:

-- Get all employees in IT department
SELECT emp_id, emp_name, salary
FROM employees
WHERE dept_id = (
    SELECT dept_id 
    FROM departments 
    WHERE dept_name = 'IT'
);

Use Meaningful Names:

-- Good
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Avoid
SELECT a.c1, a.c2, b.c3
FROM t1 a
JOIN t2 b ON a.c4 = b.c4;

Query Optimization

Test with EXPLAIN:

  • Always explain before running expensive queries
  • Look for full table scans
  • Ensure indexes are used

Limit Results During Development:

-- Add LIMIT/ROWNUM during testing
SELECT * FROM huge_table WHERE 1=1
FETCH FIRST 100 ROWS ONLY; -- Oracle 12c+, PostgreSQL

-- Oracle older versions
SELECT * FROM huge_table WHERE ROWNUM <= 100;

Use Specific Columns:

-- Better
SELECT employee_id, first_name, last_name FROM employees;

-- Avoid (unless you need all columns)
SELECT * FROM employees;

Transaction Safety

Test with SELECT First:

-- 1. Preview what will be changed
SELECT * FROM employees WHERE dept_id = 10;

-- 2. Make change
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;

-- 3. Verify
SELECT * FROM employees WHERE dept_id = 10;

-- 4. If correct, commit
COMMIT;

Use Transactions for Multiple Changes:

BEGIN
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Keyboard Shortcuts Mastery

Learn These:

  • Ctrl/Cmd + Enter: Run query (most used!)
  • Ctrl+Shift+C: Commit
  • Ctrl+Shift+R: Rollback
  • Ctrl+/: Comment/uncomment
  • Ctrl+Shift+F: Format
  • Ctrl+F: Find
  • Ctrl+H: Replace

Customize:

  • Preferences → Key Mapping
  • Change shortcuts to match your workflow

Working with Large Result Sets

Pagination:

  • Use Next button to load more rows
  • The row limit is configurable in Preferences → Data View

Export Instead of Viewing:

  • For very large results, export directly
  • Use Database → Export Data
  • Avoid loading millions of rows in UI

Troubleshooting

Query Executes But No Results

Possible Causes:

Uncommitted Changes in Another Session:

  • Another session modified data but didn't commit
  • Solution: Commit or rollback the other session

Wrong Schema Selected:

  • Objects in different schema
  • Solution: Check schema dropdown, switch if needed

Query Returns Zero Rows:

  • WHERE clause filters all rows
  • Solution: Verify filter conditions, check data

Error: "Invalid SQL Statement"

Possible Causes:

Syntax Error:

  • Missing comma, bracket, quote
  • Solution: Check error message, fix syntax

Database-Specific Syntax:

  • Using Oracle syntax in MySQL, etc.
  • Solution: Use correct syntax for your database

Incomplete Statement:

  • Missing semicolon or END keyword
  • Solution: Complete the statement

Cannot Commit or Rollback

Possible Causes:

No Active Transaction:

  • Nothing to commit
  • Solution: Normal, no action needed

Connection Lost:

  • Network interruption
  • Solution: Reconnect and retry

Database Error:

  • Constraint violation, trigger error
  • Solution: Check error message, fix underlying issue

Explain Plan Fails

Possible Causes:

Invalid SQL:

  • Syntax error prevents plan generation
  • Solution: Fix syntax first

Insufficient Privileges:

  • Need SELECT privilege on all referenced objects
  • Solution: Request privileges from DBA

Database-Specific:

  • Some databases have limited EXPLAIN support
  • Solution: Check database documentation

Editor Performance Issues

Possible Causes:

Very Large File:

  • Files over 10 MB may be slow
  • Solution: Split into smaller files or use SQL Executor

Many Objects in Tree:

  • Thousands of tables slow down navigation
  • Solution: Use object filter to narrow down

Low Memory:

  • Close unused connections when the workstation or database is under resource pressure
  • Solution: Reduce result-set size, split large scripts, or increase the Java heap according to the deployment configuration

Next Steps

Now that you know how to explore objects and execute SQL:

  1. Master object operations:

  2. Work with data:

  3. Execute SQL scripts:

  4. Customize your experience:

You're now equipped to effectively explore and query your databases!

For more help, see:

Happy querying! 🐰

← Previous Chapter
Table of Contents
Next Chapter →