Setting Up an IFS SQL Developer Environment for Efficient Development

Oracle SQL Developer is the free, standard IDE for IFS PL/SQL development. Whether you're maintaining custom packages, debugging stored procedures, or optimizing database queries, a well-configured SQL Developer environment dramatically increases productivity and reduces friction in your development workflow.

This comprehensive guide covers everything you need to know to set up SQL Developer for IFS development—from initial installation and connection configuration to advanced debugging, code templates, and keyboard shortcuts that will make you a faster, more efficient developer.

Installation and Getting Started

Downloading SQL Developer

SQL Developer is available for free from Oracle. Visit the Oracle SQL Developer download page and select the version appropriate for your operating system (Windows, macOS, or Linux).

The advantage of SQL Developer over alternative tools like PL/SQL Developer or TOAD is that it's free, officially supported by Oracle, and fully integrated with IFS development environments. Many IFS installations include SQL Developer as part of the standard development toolkit.

System Requirements

• Java Runtime Environment (JRE): SQL Developer requires Java 11 or higher. Most modern distributions come with an embedded JRE, so you may not need a separate installation.
• Oracle Client: While not strictly required for basic connections, the Oracle Client (typically version 12c or later) is recommended for advanced features like PL/SQL debugging.
• Memory: Allocate at least 2GB RAM to SQL Developer for smooth operation, especially when working with large result sets.
• Disk Space: Reserve approximately 500MB for installation and workspace.

Initial Setup

1. Download the appropriate version for your OS and extract the archive.
2. On Windows or macOS, simply run sqldeveloper.exe or sqldeveloper.app.
3. The first launch creates a workspace directory (typically ~/.sqldeveloper/ on Linux/macOS or %APPDATA%\sqldeveloper on Windows).
4. SQL Developer will prompt you to configure database connections during the initial setup wizard.

Configuring Database Connections for IFS

A properly configured database connection is the foundation of efficient IFS development. SQL Developer supports multiple connection types, but for IFS development, you'll typically use either Basic (hostname/port/SID) or TNS (tnsnames.ora) connections.

Creating a Basic Connection

The Basic connection type is the most straightforward for IFS environments:

1. Open SQL Developer and navigate to the Connections tab on the left navigator.

2. Right-click on Connections and select New Connection.

3. In the New / Select Database Connection dialog, fill in the following fields:

   • Connection Name: A friendly name for your connection (e.g., IFS_DEV, IFS_PROD, IFS_TEST). Use descriptive names that include the environment.
   • Username: The database user for IFS (typically IFSAPP or your custom application schema).
   • Password: The password for the user. Check Save Password to avoid entering it repeatedly.
   • Connection Type: Select Basic.
   • Hostname: The server name or IP address hosting your Oracle database.
   • Port: The listener port (usually 1521).
   • SID or Service Name: For traditional non-CDB instances, use the SID (e.g., ORCL). For pluggable databases (PDB), enter the service name (e.g., ifs_pdb).
   • Role: Leave as Default for normal development work. Use SYSDBA only if you have system administrator privileges.

4. Click Test to verify the connection.

5. If the test succeeds, click Save to persist the connection.

Using TNS (tnsnames.ora) Connections

If your Oracle environment uses a TNS configuration, you may prefer to use the TNS connection type:

1. Select Connection Type: TNS.
2. Configure your Oracle client's tnsnames.ora file (typically found in $ORACLE_HOME/network/admin/).
3. In SQL Developer, the TNS Name dropdown will list all configured aliases from your tnsnames.ora.
4. Select the appropriate entry and provide your username and password.

The TNS approach is useful when:

• Your organization uses centralized network configuration
• You're connecting across multiple environments
• DNS or dynamic hostname resolution is in use

Debugging Connection Issues

Issue: "Cannot create a new database connection"

Solution: Verify that the hostname, port, and SID/service name are correct. Test connectivity from the command line using:

sqlplus username/password@hostname:port/sid

Issue: "Listener refused the connection"

Solution: Confirm the database is running and the listener is active. Check the port—non-standard ports require explicit configuration in both your connection and Oracle's listener configuration.

Issue: "Invalid username/password"

Solution: Verify credentials with your database administrator. Ensure the user has appropriate grants (discussed below).

IFS-Specific Connection Requirements

For IFS Cloud environments, you may need to use:

• PL/SQL Access Provider to securely access the database
• Connection pooling for high-concurrency scenarios
• Filtered schemas if your IFS instance uses multiple app owners

The IFS Developer Studio can configure these advanced connection settings automatically when you set up your project.

Setting Up Code Templates

Code templates are one of SQL Developer's most powerful—and most underutilized—features. They allow you to insert standard SQL and PL/SQL code blocks with a keyboard shortcut, dramatically accelerating development and enforcing coding standards.

Creating Your First Template

1. Navigate to Tools → Preferences.

2. In the left navigator, expand Database and click SQL Editor Code Templates.

3. Click Add Template to create a new template.

4. Define the template:

   • Id: A short keyword you'll type to invoke the template (e.g., sel, proc, trig).
   • Abbreviation: Optional longer form of the keyword.
   • Template: The actual code block that will be inserted.

5. Click OK.

Common IFS Templates to Create

Basic SELECT Template:

sel
select [(columns)] from [(table)] where [(condition)];

When you type sel and press Ctrl+Space, SQL Developer will insert the code and highlight the [(columns)] placeholder, allowing you to tab through fields.

Procedure Template:

proc
create or replace package body pkg_name as
  procedure proc_name (p_param in varchar2) is
  begin
    -- implementation
    dbms_output.put_line('Procedure executed');
  end proc_name;
end pkg_name;
/

Exception Handler Template:

exc
exception
  when others then
    dbms_output.put_line('Error: ' || sqlerrm);
    raise;

Bulk Insert Template:

bulk
declare
  type t_bulk is table of [(table_name)]%rowtype;
  v_bulk t_bulk;
begin
  forall i in v_bulk.first .. v_bulk.last
    insert into [(table_name)] values v_bulk(i);
  commit;
end;
/

Using Templates with AutoReplace

For even faster development, enable AutoReplace to automatically expand templates when you type their keyword and press space, tab, or Enter:

1. Go to Tools → Preferences → Database → SQL Editor.
2. Check Use Code Completion > AutoReplace.

Now typing sel (with a space) will immediately expand to your SELECT template without requiring Ctrl+Space.

Warning: Enabling AutoReplace can interfere with natural typing if your template keywords conflict with SQL keywords. Test carefully.

Organizing Templates by Category

Create a naming convention for your templates:

• sel_ for SELECT templates: sel_basic, sel_with_join, sel_aggregate
• proc_ for procedure templates: proc_basic, proc_with_cursor, proc_with_exception
• pkg_ for package templates: pkg_full, pkg_spec_only

This prevents accidental triggering and keeps your template list organized.

Configuring the PL/SQL Debugger

SQL Developer includes a powerful PL/SQL debugger that allows you to step through procedures, inspect variables, and set breakpoints. Debugging is essential for diagnosing complex issues in IFS customizations.

Enabling the Debugger

Before debugging, ensure that:

1. DBMS_DEBUG_JDWP package is installed in your database. This is typically included in Oracle Database 11g and later.

2. Your database user has debugging privileges. The IFSAPP user or your custom schema should have:

3. Network access is configured. The debugger uses TCP/IP to communicate between SQL Developer and the database. Ensure firewalls allow this communication (typically port 4321, configurable in preferences).

Setting Up a Debug Session

1. In the Connections navigator, right-click your IFS connection and select Open Connection.
2. Navigate to a stored procedure or function you wish to debug.
3. Right-click the procedure and select Debug Procedure (or use F11 as a shortcut).
4. The Debug Runner dialog will appear, allowing you to set input parameters.
5. Click OK to start the debug session.

Using Breakpoints and Watches

Setting Breakpoints:

• Click in the left margin of the code editor to set a breakpoint (a red circle will appear).
• The debugger will pause execution when it reaches a breakpoint.
• Right-click a breakpoint to set conditions (e.g., break only when a variable exceeds a threshold).

Inspecting Variables:

• Use the Debug tab (visible during debugging) to inspect variable values.
• The Watches panel allows you to monitor specific variables throughout execution.
• Hover your mouse over a variable in the editor to see its current value in a tooltip.

Stepping Through Code:

• Step Over (F10): Execute the current line without stepping into called procedures.
• Step Into (F11): Step into the next called procedure.
• Step Out (Ctrl+Shift+F11): Execute the rest of the current procedure and return to the caller.
• Continue (F5): Resume execution until the next breakpoint or end of procedure.

Common Debugging Scenarios

Debugging a Procedure Called by IFS:

When debugging a custom procedure that's invoked by an IFS process, you may not have direct execution parameters. Instead:

1. Write a simple anonymous block that calls your procedure:

2. Right-click this block and select Debug to step through your custom logic.

Debugging Cursor Loops:

When iterating through cursor results, use watches to monitor:

• The cursor position (implicit %rowcount)
• Loop counter variables
• The current row's values

This helps identify off-by-one errors or unexpected early loop termination.

Keyboard Shortcuts and Productivity Tweaks

Keyboard shortcuts are the speed multiplier for any IDE. Learn these SQL Developer shortcuts to accelerate your workflow:

Essential Navigation Shortcuts

Code Editing Shortcuts

Window Management Shortcuts

Custom Shortcuts

You can add your own shortcuts by going to Tools → Preferences → Shortcut Keys. Some useful custom shortcuts for IFS developers:

• Compile with Errors: Ctrl+Shift+C (compile without clearing the editor)
• Export to CSV: Ctrl+E (quick export of query results)
• Format Code: Ctrl+Alt+F (auto-format PL/SQL for readability)

Extensions and Useful Add-ons

While SQL Developer is powerful out of the box, extensions can further enhance your development environment.

Notable Extensions

SQLcl (SQL Command Line): A command-line wrapper for SQL Developer that supports scripting and automation. Useful for continuous integration and DevOps pipelines.

Oracle REST Data Services (ORDS): If your IFS instance exposes REST APIs, ORDS integration in SQL Developer allows you to test and debug APIs directly.

Code Formatter Extensions: Third-party formatters can enforce stricter code style rules for team consistency.

Marketplace and Installation

1. Go to Help → Check for Updates to access the Oracle Extension Marketplace.
2. Browse available extensions and click Install to add them to your SQL Developer installation.

For IFS-specific development, focus on:

• PL/SQL debugging enhancements
• Performance profiling tools
• Version control integration (Git, Subversion)

IFS-Specific Development Workflow

Integrating with IFS Developer Studio

If you're using IFS Developer Studio, SQL Developer serves as the embedded PL/SQL editor. To maximize integration:

1. Configure the database connection in Developer Studio project properties to match your SQL Developer connection.
2. Enable code insight (Tools → Preferences → Code Editor → Completion Insight) so you get autocomplete for IFS table and package names.
3. Use synchronized editing: When you modify a procedure in SQL Developer, it automatically compiles and deploys if your project is configured for live compilation.

Version Control and SQL Development

For team-based IFS projects:

1. Store your SQL scripts in Git: Each procedure, function, and package should have a dedicated .sql file in version control.
2. Use SQL Developer's Version Control integration: Go to View → Version Control to manage commits and branches without leaving the IDE.
3. Create deployment scripts: Combine multiple .sql files into a single deployment script that can be run against different environments.

Performance Tuning with SQL Developer

SQL Developer includes the Autotrace and Explain Plan features to help optimize queries:

1. Click the Autotrace icon in the toolbar before executing a query.
2. View the execution plan to identify inefficient joins, missing indexes, or full table scans.
3. For IFS queries, look for:
   • Full table scans on large tables (should use indexes)
   • Nested loops with high iteration counts (consider hash joins)
   • Missing optimizer statistics (run dbms_stats.gather_table_stats)

Best Practices for IFS SQL Development

Coding Standards

1. Use consistent naming conventions aligned with your IFS schema (e.g., pkg_ for packages, v_ for variables, p_ for parameters).
2. Always include error handling with exception when others then blocks.
3. Avoid hardcoded values. Use parameters and configuration tables instead.
4. Document complex logic with inline comments explaining the "why" not just the "what."

Security Considerations

1. Never hardcode passwords. Use secure wallet or Oracle's credential store.
2. Limit grants to least-privilege principles. IFSAPP shouldn't need SYSDBA unless necessary.
3. Enable auditing for sensitive procedures that modify critical data.
4. Test in non-production environments first, especially when deploying to IFS Cloud.

Productivity Workflow

A typical IFS development session:

1. Start your day: Open your primary database connection.
2. Review changes: Use Connections → Recent SQL to see your most-used scripts.
3. Develop: Write PL/SQL in the worksheet, testing incrementally.
4. Compile: Compile procedures in the procedure editor (not the worksheet) to ensure they're stored in the database.
5. Debug: If issues arise, use the debugger to trace execution.
6. Commit: Save your scripts to Git and document changes in a commit message.
7. Deploy: Use your CI/CD pipeline or IFS deployment tools to move code to test/production.

Key Takeaways

• Set up multiple named connections for your dev, test, and production environments to prevent accidental overwrites.
• Leverage code templates to enforce consistency and accelerate coding—this alone can save hours per week.
• Master the PL/SQL debugger for complex issues; it's far superior to adding debug logging throughout your code.
• Use keyboard shortcuts to eliminate context-switching and mouse movement; every second counts when you're deep in development.
• Integrate SQL Developer with version control to maintain a clean history of all database changes.
• Regularly update SQL Developer to benefit from performance improvements and new features.
• Configure your code formatter and style early so your team works with consistent code standards.

Conclusion

A well-configured SQL Developer environment is a cornerstone of efficient IFS development. By investing time in proper setup—connections, templates, debugging configuration, and keyboard shortcuts—you'll recoup that investment many times over through faster development cycles, fewer bugs, and a more enjoyable development experience.

Whether you're maintaining existing IFS customizations or building new functionality, SQL Developer's combination of power, flexibility, and zero licensing cost makes it the natural choice for IFS PL/SQL development. Take the time to configure it properly, and you'll wonder how you ever developed without it.

---

Need help setting up SQL Developer for your IFS environment? Syrett Consultancy specializes in IFS development and can help optimize your development toolchain. Contact us to learn more.