Oracle SQL: Essential Environment Commands

• SET DEFINE OFF

  • Purpose: Controls whether SQL*Plus and SQL Developer interpret the ampersand (&) as a substitution variable prefix or as a literal character.
  • Interview Insight: Critical for script automation and data migration—failing to toggle this off in automated deployment scripts will cause pipeline execution to hang indefinitely while waiting for user input on strings containing & (e.g., 'AT&T' or URL parameter strings).

  SET DEFINE OFF;
  UPDATE endpoints
  SET url = 'https://example.com/api?user=1&type=full'
  WHERE endpoint_id = 101;
  

• SET TIMING ON

  • Purpose: Controls whether SQL*Plus displays the elapsed execution time for each SQL statement or PL/SQL block.
  • Interview Insight: Essential for basic performance tuning and benchmarking queries directly in the CLI.

• SET FEEDBACK ON | OFF | n

  • Purpose: Displays the number of records returned by a query.
  • Correction: It displays feedback when a script selects at least n records (the default is usually 1 or 6 depending on the version).

• SET VERIFY ON | OFF

  • Purpose: Toggles the display of substitution variables before and after SQL Developer/SQL*Plus replaces them with runtime values.
  • Behavior: ON lists the text; OFF suppresses the listing. To see this output clearly in SQL Developer, you must execute your script using the Run Script (F5) icon.
  • ON: Lists both original and substituted text.
  • OFF: Suppresses the listing.

• SET LINESIZE n (Corrected from "Set Line")

  • Purpose: Sets the total number of characters that SQL*Plus displays on one line before wrapping to a new line.
  • Example: SET LINESIZE 200;

• SET PAGESIZE n

  • Purpose: Sets the number of lines printed on each page of output (including headers).
  • Example: SET PAGESIZE 10;

• SET LONG n

  • Purpose: Sets the maximum width (in bytes) for fetching and displaying large data types: BLOB, BFILE, CLOB, LONG, NCLOB, and XMLType.
  • Architecture Note: Querying large columns requires sufficient local memory to hold the data footprint specified by SET LONG.

💡 Interview Tip: To view your entire current environment setup or check all configuration statuses, run the SHOW ALL command at the prompt.

SQL Developer & SQL*Plus Substitution Controls

• The SET VERIFY command toggles the display of substitution variable replacements in both environments.
• In SQL Developer, executing scripts via Run Script (F5) renders the command transformation inside the Script Output tab.
• SQL*Plus provides system environment variables that can be inspected at any time using the SHOW ALL command.

-- Defining and undefining substitution variables
DEFINE employee_num = 200;

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num;

UNDEFINE employee_num;

📌 NOTES / CRITICAL INTERVIEW CONTEXT

• SQL*Plus vs. SQL Developer: System environment settings (SET commands) are client-side parameters; they do not affect database instance execution plans or server parameters.
• Substitution vs. Bind Variables: Substitution variables (&var, &&var) perform textual replacement before parsing (leading to hard parses). Bind variables (:var) pass parameters to execution plans without re-parsing, encouraging plan reuse in the Shared Pool.