SQLPlus Commands, Practical DB Apps & Oracle Tools

SQL*Plus: Oracle’s Command-Line Powerhouse

SQL*Plus is Oracle’s interactive command-line tool for managing databases. It lets you:

• Execute SQL and PL/SQL statements
• Format query output
• Automate tasks via scripts
• Generate reports

stateDiagram-v2
    [*] --> SQL*Plus_Start: "sqlplus username/password@host"
    SQL*Plus_Start --> Query_Execution: "SQL> SELECT * FROM accounts;"
    Query_Execution --> Formatting: "COLUMN balance FORMAT $99,999.99"
    Formatting --> Report_Generation: "SPOOL report.txt"
    Report_Generation --> [*]
    SQL*Plus_Start --> Script_Execution: "@script.sql"
    Script_Execution --> [*]

Why it matters: Banks like Nabil Bank use SQLPlus scripts to generate daily transaction reports for auditors. NEPSE’s trading system relies on SQLPlus to log share prices in real-time.

Core SQL*Plus Commands (Exam Focus)

1. Connection & Session Management

sequenceDiagram
    participant User
    participant SQLPlus
    User->>SQLPlus: CONN username/password@host
    SQLPlus-->>User: Connected to Oracle
    User->>SQLPlus: DISCONN
    SQLPlus-->>User: Session ended

Real-world tie: When eSewa processes a payment, its backend uses CONN to connect to the central database and HOST to trigger email notifications via the server’s SMTP.

2. Query Execution & Formatting

sequenceDiagram
    participant User
    participant SQLPlus
    User->>SQLPlus: SELECT * FROM customers;
    SQLPlus-->>User: Raw output
    User->>SQLPlus: COLUMN name FORMAT A20
    User->>SQLPlus: SET LINESIZE 100
    SQLPlus-->>User: Formatted output

Key formatting commands:

• COLUMN: Control column display (e.g., COLUMN salary FORMAT $99,999.99).
• SET: Adjust page layout (e.g., SET PAGESIZE 50).
• BREAK: Group results (e.g., BREAK ON department_id).

Worked Example:

-- Format a bank loan report for Nabil Bank
COLUMN customer_name FORMAT A30 HEADING "Customer Name"
COLUMN loan_amount FORMAT $999,999.99 HEADING "Loan Amount"
COLUMN interest_rate FORMAT 99.99 HEADING "Interest (%)"
SELECT customer_name, loan_amount, interest_rate
FROM loans
WHERE status = 'Approved';

Output:

Customer Name               Loan Amount   Interest (%)
---------------------------- ------------ ------------
Ram Prasad                   $500,000.00     8.50
Sita Devi                    $250,000.00     7.25

3. Scripting & Automation

SQL*Plus scripts (.sql files) automate repetitive tasks. Example: NEPSE’s daily closing script:

-- script: daily_close.sql
SPOOL closing_report.txt
SELECT symbol, price, volume
FROM trades
WHERE trade_date = SYSDATE;
SET HEADING OFF
SELECT 'Total trades: ' || COUNT(*) FROM trades;
SPOOL OFF
@archive_old_data.sql
EXIT

Commands:

• SPOOL: Save output to a file.
• @: Execute another script.
• REM: Add comments.

Real-world use: Daraz’s inventory system uses SQL*Plus scripts to update stock levels overnight and generate low-stock alerts for sellers.

4. Error Handling & Debugging

stateDiagram-v2
    [*] --> Query_Execution
    Query_Execution --> Success: No errors
    Query_Execution --> Error: Syntax/Logic error
    Error --> SHOW_ERRORS: Display error stack
    Error --> WHILE: Debug line-by-line
    Error --> [*]

Debugging commands:

• SHOW ERRORS: List compilation errors.
• WHILE: Execute a script line-by-line.
• SET SERVEROUTPUT ON: Enable PL/SQL block output.

Example: Debugging a failed Khalti transaction log:

-- Script to check for failed transactions
WHILE
BEGIN
  INSERT INTO transaction_log VALUES ('Failed', SYSDATE, 'Khalti ID: 12345');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

5. Advanced Utilities

Example: NTC’s monthly billing system uses SQL*Loader to import 100,000+ customer records from Excel into the database nightly.

SQL*Plus vs. Other Tools

When to use SQL*Plus:

• Automating database maintenance (e.g., Nabil Bank’s nightly backups).
• Generating reports for non-technical users (e.g., NEPSE’s shareholder lists).
• Running PL/SQL scripts in cron jobs (e.g., eSewa’s daily reconciliation).

Practical Applications in Nepal

1. Banking: Loan Processing

Scenario: A bank like Global IME needs to generate a report of all loans with interest > 10%.

-- SQL*Plus script for Global IME
SET PAGESIZE 0
SET FEEDBACK OFF
SET LINESIZE 120
COLUMN customer_name FORMAT A30
COLUMN interest_rate FORMAT 99.99
SELECT customer_name, loan_amount, interest_rate
FROM loans
WHERE interest_rate > 10
ORDER BY interest_rate DESC;
SPOOL high_interest_loans.txt

Output: A formatted file sent to the risk management team.

2. E-Commerce: Order Fulfillment

Scenario: Daraz uses SQL*Plus to track delayed orders.

-- Check orders not shipped in 48 hours
SELECT order_id, customer_name, order_date
FROM orders
WHERE status = 'Processing'
AND order_date < SYSDATE - 2;

Automation: The script emails the warehouse team via HOST mail -s "Delayed Orders" report.txt admin@daraz.com.

3. Government: NTC Billing

Scenario: NTC generates monthly bills for 5 million customers.

-- SQL*Plus script for NTC billing
DEFINE month = &1  -- Prompt for month (e.g., 10 for October)
SPOOL bills_&month.txt
SELECT customer_id, name, usage_units, charge
FROM usage_data
WHERE month = &month;
SPOOL OFF

Output: A file used to print bills and update the CRM.

Common Pitfalls & Fixes

Exam Tip: How to Score Full Marks

1. Command Syntax: Always include the exact syntax. For example:
   • ❌ "Use spool to save output" → ✅ SPOOL report.txt (1 mark).
2. Real-World Tie: Link answers to Nepalese systems. Example:
   • "Nabil Bank uses SET HEADING OFF to generate CSV files for auditors."
3. Error Handling: Show how to debug. Example:

   -- For a failed Khalti transaction
   SHOW ERRORS;
   WHILE
   BEGIN
     -- PL/SQL block
   EXCEPTION
     WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;
   

4. Formatting: Highlight key commands in bold or use tables for comparisons (e.g., SQL*Plus vs. SQL Developer).
5. Practical Example: Always include a worked script for automation questions (e.g., NEPSE’s daily close).