← Back to Recording

Summary

Overview

This course session is a hands-on technical training focused on troubleshooting Oracle SQL Developer connectivity issues and introducing basic PL/SQL SELECT statement usage. The trainer guides participants through diagnosing an ORA-12541 error (server not running), correcting hostname mismatches in Oracle configuration files (tnsnames.ora and listener.ora), restarting systems to apply changes, and then transitioning into PL/SQL data retrieval patterns. The session combines live debugging, configuration editing, and foundational PL/SQL syntax instruction.

Topic (Timeline)

1. Connectivity Setup and Troubleshooting [00:00:10 - 00:09:44]

  • Trainer verifies all participants have access to their desktops and Oracle SQL Developer installed (shortcut provided).
  • Participants confirm access; some experience issues with incorrect interfaces (e.g., Command Prompt instead of SQL Developer).
  • Trainer instructs participants to use “local” connection and enter password: ashblue.
  • Participant “Jerry” encounters ORA-12541 error: “TNS:listener does not currently know of service requested in connect descriptor.”
  • Trainer guides Jerry to:
    • Open terminal and run hostname to identify system name: jeremias-93dn.
    • Navigate to Oracle configuration folder and open tnsnames.ora in Visual Studio Code.
    • Replace all instances of ray-lhnw-resize with jeremias-93dn.
    • Save changes using Ctrl+S.
  • Trainer then guides Jerry to edit listener.ora using the same method, ensuring hostname consistency.
  • Trainer shares a Google Sheet link containing commands to start the Oracle listener service.
  • Participants are instructed to run lsnrctl start in terminal to initiate the listener.
  • Trainer notes that after hostname changes, a system restart is required for changes to take effect.
  • All participants with similar errors are advised to restart their computers.

2. PL/SQL SELECT Statement Fundamentals [00:09:53 - 00:12:04]

  • While participants restart their systems, trainer shifts to theoretical instruction on PL/SQL.
  • Explains that the SELECT statement in PL/SQL retrieves data from database tables and can be used in two contexts:
    • Standard SQL: SELECT column FROM table;
    • PL/SQL block: Requires DECLARE, BEGIN, and END structure.
  • Describes variable declaration within DECLARE section, data retrieval in BEGIN, and output handling.
  • Mentions handling exceptions such as NO_DATA_FOUND and TOO_MANY_ROWS (implied by “no data found or too many errors”).
  • Indicates intent to demonstrate practical examples but pauses due to ongoing restarts.

Appendix

Key Principles

  • Hostname Consistency: Oracle client configuration files (tnsnames.ora, listener.ora) must match the system’s actual hostname.
  • Listener Control: Use lsnrctl start to manually start the Oracle listener service after configuration changes.
  • System Restart Required: Changes to Oracle network configuration files require a full system reboot to be recognized.
  • PL/SQL Structure: Always use DECLARE (variables), BEGIN (logic/query), and END; (block termination) for procedural SQL.

Tools Used

  • Oracle SQL Developer
  • Command Prompt (Windows)
  • Visual Studio Code (for editing .ora files)
  • Google Sheet (for sharing commands)

Common Pitfalls

  • Opening Command Prompt instead of SQL Developer.
  • Using incorrect hostname in configuration files (e.g., mismatched server name).
  • Forgetting to restart system after editing tnsnames.ora or listener.ora.
  • Not verifying connection status before attempting queries.

Practice Suggestions

  • Manually edit tnsnames.ora and listener.ora on a local Oracle instance using correct hostname.
  • Practice writing PL/SQL blocks that use SELECT INTO with variable declarations.
  • Simulate NO_DATA_FOUND and TOO_MANY_ROWS exceptions using sample queries with restrictive WHERE clauses.