Setting Up an IFS SQL Developer Environment for Efficient Development

Oracle SQL Developer is a free Oracle database IDE that is useful when you have authorised database access to an IFS Applications 10 development environment or an IFS Cloud remote deployment. It is not the IFS source-development tool and it does not provide database access to customers of IFS-managed Cloud. Within those boundaries, it is excellent for inspecting packages, debugging stored procedures and analysing SQL.

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 alternatives such as PL/SQL Developer or Toad is that it is free and maintained by Oracle. It is a separate tool from IFS Developer Studio, so source changes still belong in the IFS component/customer solution and must be delivered through the supported IFS build process.

System Requirements

• Java runtime: Use the JDK version stated on the download page for the exact SQL Developer release. Some distributions include it; others require a separate supported JDK.
• Oracle Client: The default JDBC connection does not require an Oracle Client installation. Configure an OCI/Thick connection only when you have a specific feature or authentication requirement for it.
• Memory and disk: Size the workstation for the result sets and extensions you actually use; avoid pulling very large IFS data sets into a desktop grid.

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 version-specific user settings. Typical locations are under ~/.sqldeveloper/ on Linux/macOS and %APPDATA%\SQL Developer on Windows, although individual files on macOS can also live under ~/Library/Application Support/SQLDeveloper/.
4. SQL Developer may ask whether to migrate settings from an older release. After it opens, create or import the database connections you are authorised to use; there is no mandatory connection-configuration 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.
   • Color: Give production a conspicuous border colour and use a consistent scheme for DEV and TEST. This is a visual guardrail, not an access control.
   • Username: Your named development account, a purpose-built read-only account, or the application owner only where the environment's IFS development runbook explicitly requires it. Do not use the application owner for routine production queries.
   • Password: Prefer an approved wallet or credential store. If local password saving is permitted, protect the workstation and never share an exported connection containing credentials.
   • 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 IFS work. A separate DBA-controlled connection may use SYSDBA for an authorised database administration task; it is not an IFS development role.

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. Point SQL Developer at the directory containing the approved tnsnames.ora, either through Tools → Preferences → Database → Advanced → Tnsnames Directory in releases that expose that preference, or through your organisation's supported TNS_ADMIN/Oracle network configuration.
3. Reopen the connection dialog if necessary. The TNS Name dropdown will list the aliases SQL Developer found in that file.
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@hostname:port/service_name

Let SQL*Plus prompt for the password, or use an approved Oracle wallet, so the secret is not exposed in shell history or the process list.

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-managed Cloud, stop here: customer developers do not connect SQL Developer to the managed application database. Use API Explorer, projections, supported reporting/data services and the Lifecycle Experience tooling instead. For a remote deployment, obtain the service name, network route and least-privileged credentials from the team that operates that environment. IFS Developer Studio has its own project and database configuration; it does not automatically turn a SQL Developer connection into a live Cloud connection.

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. Give the row a useful Id and enter the actual SQL or PL/SQL in Template. In current SQL Developer releases, the ID is only a label; it is not an expansion keyword or abbreviation.
5. Click OK, start typing the beginning of the template text in a worksheet, and choose it from Completion Insight. Press Ctrl+Space if the completion popup is not already visible.

Common IFS Templates to Create

Basic SELECT Template:

Use bind variables for values. After inserting the template, replace the descriptive identifiers with the approved IFS view and columns for the task.

Procedure Template:

Exception Handler Template:

Bulk Insert Template:

This is scaffolding for a customer-owned staging or utility table, not permission to insert into an IFS-owned base table. Do not add an unconditional COMMIT to a reusable template. Transaction ownership belongs to the calling business operation, and direct DML against IFS-owned base tables is not a supported substitute for the released API/model logic.

Using Templates with Completion Insight

Code templates participate in SQL Developer's normal Completion Insight list. To tune that behaviour:

1. Go to Tools → Preferences → Code Editor → Completion Insight.
2. Enable the SQL Worksheet auto-popup if you want suggestions while typing, and choose a delay that does not interrupt normal editing.
3. Use Ctrl+Space to request Completion Insight explicitly at any time.

There is no supported promise that typing an arbitrary template ID such as sel followed by a space will expand it. The template's actual text drives matching, so start with distinctive, useful SQL and select the desired entry from the popup.

Organizing Templates by Category

Create a naming convention for the template IDs so exported settings remain understandable:

• sel_ for SELECT templates: sel_basic, sel_with_join, sel_aggregate
• proc_ for procedure templates: proc_basic, proc_with_cursor, proc_with_exception
• api_ for IFS API package templates: api_body, api_spec

These IDs keep the preferences list organised; they do not control triggering.

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 narrowly scoped debugging privileges. A DBA can grant DEBUG CONNECT SESSION and DEBUG on the specific customer-owned program units you need. Avoid a blanket DEBUG ANY PROCEDURE grant to the application owner.

3. The callback path is configured. JDWP debugging makes a database-to-workstation callback. Agree the address and port range with the DBA/network team and configure the required Oracle network ACL; there is no universal IFS debugger port to open. SQL Developer 26.2 documents default debugger low/high ports of 40000 and 49000, but verify the preferences for your installed release and narrow the permitted range where policy requires it. Restrict the rule to the developer workstation and remove it when no longer needed.

4. The customer-owned unit is compiled for debugging. In SQL Developer, use Compile for Debug only in an authorised development environment. Debug compilation adds symbol information and can change optimisation behaviour, so do not casually apply it to production or to IFS-owned Core packages.

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.
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: Execute the current line without stepping into a called subprogram.
• Step Into: Enter the next called subprogram when debug information is available.
• Step Out: Leave the current subprogram and return to its caller.
• Resume: Continue until the next breakpoint or the end of the run.

Use the Debug menu or toolbar for these actions and check Tools → Preferences → Shortcut Keys for your installed keymap. SQL Developer's defaults differ from the Visual Studio-style F10/F11 bindings often copied into tutorials; in current Oracle documentation, F9 is used for debugger Resume.

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. The exact bindings vary by SQL Developer release, operating system and imported keyboard scheme, so confirm them under Tools → Preferences → Shortcut Keys. These are documented in the current SQL Developer 26.2 guide:

Custom Shortcuts

You can add your own shortcuts under Tools → Preferences → Shortcut Keys. Search the Command list for actions such as Format, Export or Compile for Debug, then choose a conflict-free binding. Treat team documentation as the source for custom mappings rather than presenting them as SQL Developer defaults.

Companion Tools and Optional Extensions

SQL Developer is powerful out of the box. A few adjacent Oracle tools and release-dependent extensions can complement it, but they are not all SQL Developer plug-ins.

Useful Companion Tools

SQLcl (SQL Command Line): Oracle's standalone command-line interface for Oracle Database. It shares useful SQL Developer features, but it is not a wrapper around the desktop application. It is useful for repeatable scripts in authorised database environments.

Oracle REST Data Services (ORDS): ORDS tooling is relevant to applications that you deliberately expose through ORDS. IFS Cloud projections are served by the IFS application stack, not by customer-configured ORDS, so use IFS API Explorer or an HTTP client to test them.

Built-in Git integration: Current SQL Developer releases provide Git operations through the Team menu and the Files navigator. That integration works on files; it does not understand or deploy an IFS customer solution for you.

Marketplace and Installation

1. Go to Help → Check for Updates to view updates and extensions available for your installed release.
2. Review the publisher, compatibility and support status before installing anything on a controlled development workstation.

For IFS work, prioritise the built-in worksheet, debugger, Explain Plan/Autotrace and file-based Git features before adding third-party extensions.

IFS-Specific Development Workflow

Integrating with IFS Developer Studio

If you're using IFS Developer Studio, treat SQL Developer as a separate diagnostic companion:

1. Keep the Developer Studio project connected only to the authorised development/build database defined by the project runbook.
2. Enable code insight in SQL Developer when it helps you inspect IFS views and packages.
3. Make source changes in Developer Studio/source control. A worksheet compile changes the connected database object, but it does not update, synchronise or deploy the corresponding IFS source file.

Version Control and SQL Development

For team-based IFS projects:

1. Store authoritative source in Git: Keep the IFS customer-solution structure and file types required by the target release. Store ad-hoc diagnostic scripts separately as .sql files rather than exporting generated database packages and treating those exports as source.
2. Use the Git client that suits the team: SQL Developer exposes Git through the Team menu; a command-line or dedicated Git client is equally valid. Review changes to the actual customer-solution files before committing.
3. Use the supported build and delivery output: Let IFS generation/build tooling create the deployable artefacts. Do not hand-combine generated IFS packages into a generic production script and bypass the normal delivery controls.

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 where the optimiser spends time. A full scan or nested loop can be the correct plan; judge it using row counts, selectivity and measured execution statistics.
3. For IFS queries, look for:
   • Large misestimates between expected and actual row counts
   • Repeated work caused by an avoidable join or predicate
   • Stale-statistics symptoms—escalate these to the DBA/IFS operations runbook rather than gathering statistics ad hoc on IFS-owned objects

Explain Plan and Autotrace are different: Explain Plan estimates a plan without running the statement, while Autotrace executes it and can collect runtime statistics. Autotrace needs the supporting plan table and catalogue privileges configured by the DBA. Do not run an expensive statement merely to obtain Autotrace output on production.

Best Practices for IFS SQL Development

Coding Standards

1. Use the IFS source conventions for the target release. Public API packages commonly end in _API; PL/SQL parameters and local variables conventionally use a trailing underscore.
2. Handle only errors you can add value to. Use WHEN OTHERS only when you log useful context and re-raise, or when the documented business contract requires a deliberate translation; do not add it mechanically to every block.
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. The IFS application owner should not be granted SYSDBA; use a separately controlled DBA identity for database administration.
3. Coordinate auditing for sensitive procedures with the DBA and the IFS security/operations design.
4. Test in non-production environments first, then deliver through the supported IFS lifecycle route.

Productivity Workflow

A typical session where database access is supported:

1. Start safely: Open the named development connection and confirm its coloured border, user and service before running anything.
2. Review context: Inspect the Git diff and use F8 / View → History when you need to recover a diagnostic statement.
3. Develop from source: Make deliverable changes in the customer-solution source/Developer Studio, using worksheets for controlled experiments and repeatable test harnesses.
4. Compile through the right route: CREATE OR REPLACE in a worksheet and the object editor both compile a database object, but an IFS deliverable should go through the supported generation/build process so the database and repository cannot drift.
5. Debug proportionately: Use a direct harness, trace/logging or the debugger according to how the code is invoked.
6. Commit: Review and commit only the authoritative source and intentional diagnostic scripts.
7. Deploy: Use the project's IFS delivery tooling and approvals to move the change to test and production.

Key Takeaways

• Set up clearly named and coloured connections for DEV, TEST and any permitted production access, with production privileges kept narrow.
• Use code templates through Completion Insight to insert reviewed scaffolding without relying on invented expansion shortcuts.
• Use the PL/SQL debugger selectively for reproducible development calls; trace and durable logging remain better for asynchronous or production-only behaviour.
• Use keyboard shortcuts to eliminate context-switching and mouse movement; every second counts when you're deep in development.
• Keep the IFS customer solution in version control whether you use SQL Developer's Team menu or another Git client.
• 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

For teams with supported database access, a well-configured SQL Developer environment is a valuable part of efficient IFS development. By investing time in proper setup—connections, templates, debugging configuration, and keyboard shortcuts—you'll recoup that investment through faster diagnosis, fewer mistakes and a more enjoyable development experience.

For authorised Apps 10 or remote-deployment database work, SQL Developer's combination of power, flexibility and zero licence cost makes it a practical companion to the IFS toolchain. Configure it with clear environment names, narrow privileges and a source-first delivery discipline, and it will save time without creating an unsupported route around IFS lifecycle controls.

References

• Oracle SQL Developer 26.2 documentation
• IFS Developer Studio