SQLAlchemy Tip: Print Raw SQL with Literal Values (Oracle Edition)

🐍 SQLAlchemy Tip: Print Raw SQL with Literal Values (Oracle Edition)

When working with SQLAlchemy, especially in complex applications or when integrating with Oracle databases, it’s super helpful to see exactly what SQL is being executed—including the values being bound to the query.

That’s where this little gem of a snippet comes in:

from sqlalchemy.sql import text
from sqlalchemy.dialects.oracle import dialect

sql = "SELECT * FROM users WHERE user_id = :id"
compiled = text(sql).compile(
    dialect=dialect(), 
    compile_kwargs={"literal_binds": True}
)
print(str(compiled))

🧠 What’s Going On Here?

• text(sql): Creates a SQL expression from a raw SQL string.

• compile(dialect=dialect()): Tells SQLAlchemy which dialect to use (in this case, Oracle).

• compile_kwargs={"literal_binds": True}: Replaces bind parameters like :id with their actual literal values—super useful for debugging or logging.

🔍 Why You Need This

When debugging, the normal str(statement) will show bind parameters (:id) instead of actual values. But sometimes you just want to see the whole shebang—a raw SQL string with everything baked in. This is especially useful when:

• Logging slow queries

• Diagnosing issues in Oracle’s SQL Developer or TOAD

• Explaining a bug to your DBA without sounding like you made it up

⚠️ Caution

This is great for debugging, but don’t use this to generate SQL for production execution—it’s meant for inspection, not execution. Values will be hardcoded, which means no SQL injection protection.

---

🧪 Example Output

SELECT * FROM users WHERE user_id = 42

Neat, right?

---

Got another SQLAlchemy trick up your sleeve? Drop it in the comments—let's make debugging a little less mysterious and a lot more fun.

🧙‍♂️ Happy querying!

When Your Oracle Object Pulls a Vanishing Act

When Your Oracle Object Pulls a Vanishing Act: A Friendly Guide to Hunting Down ORA‑08103

Picture this: you’re cruising through your Oracle session, confident that your SQL is about to work its magic—when suddenly, BOOM:

ORA‑08103: object no longer exists

No one dropped a table. No ghost removals in the night. So what gives? Grab your detective hat (and maybe a cup of hot chocolate) as we walk through a fun, step‑by‑step diagnosis to unmask that mischievous object.

1. Snag the Suspect SQL

First things first—identify the precise SQL that tripped the error so you don’t chase the wrong culprit. Right after the ORA‑08103, run:

-- If you lack access to V$SQL, try GV$SQL or request appropriate privileges

SELECT sql_id, sql_text
  FROM v$sql
 WHERE sql_text LIKE '%<snippet of your failing SQL>%';

Note: If GV$SQL still doesn’t exist, you may need DBA help or 

query historical views like DBA_HIST_SQLTEXT.

Jot down that SQL_ID and the full SQL_TEXT. Now you know exactly which statement to focus on.

2. Flip on the Oracle Forensics

Oracle can sprinkle a little extra context into your session’s trace file. Enable the error‑stack trace just once in the same session:

ALTER SESSION
  SET EVENTS '8103 trace name errorstack level 3';

Then rerun your failing statement. Oracle will scribble file‑and‑block info into a trace for you to examine.

3. Sniff Out the Trace File

Next, find where Oracle hid that trace:

SELECT value
  FROM v$diag_info
 WHERE name = 'Default Trace File';

Head to that path on your server’s diag/.../trace directory and open the trace file.

4. Decode the Clues: File#, Block#, Obj#

Inside the trace you’ll see lines like:

ORA-08103: object no longer exists
file 5, block 12345, obj# 67890

Those three numbers are your breadcrumb trail.

5. Unmask the Phantom Object

Map the obj# back to a human‑readable name:

SELECT owner, object_name, object_type, status
  FROM dba_objects
 WHERE object_id = 67890;

If that comes up blank, try searching segments:

SELECT owner, segment_name, segment_type
  FROM dba_segments
 WHERE header_file  = 5
   AND header_block = 12345;

6. Check Its Vital Signs

Even if the object “exists,” it could be invalid or offline. Confirm its health:

SELECT tablespace_name, status
  FROM dba_objects
 WHERE object_id = 67890;

7. Audit for Sneaky DDL or Recovery

Maybe a nightly script or a flashback operation played Houdini. Peek at your audit trail for any drop/restore action in the past day:

SELECT username, action_name, timestamp
  FROM dba_audit_trail
 WHERE obj$owner = 'MY_OWNER'
   AND obj$name  = 'MY_OBJECT'
   AND timestamp > SYSDATE - 1;

8. Inspect for Corruption

Valid objects can still be corrupt. Run a structure validation:

ANALYZE TABLE my_owner.my_table VALIDATE STRUCTURE CASCADE;

Then peek at dba_errors or user_errors for any red flags.

9. Bring in the Heavy Artillery: Rebuild or Repair

• If it’s an index:

  ALTER INDEX my_owner.my_index REBUILD;

• If it’s a table with bad blocks:

  Consider exporting it (expdp), dropping/re‑creating the segment, and importing back—or use DBMS_REPAIR to isolate and fix corrupt blocks.

10. Double‑Check Your Datafiles

Sometimes the problem lives at the datafile level. Trace pointed you to file# = 5? Verify its status:

SELECT file_name, status
  FROM dba_data_files
 WHERE file_id = 5;

11. Reproduce a Simple Select

Finally, confirm whether the object itself is playable or still ghostly:

SELECT *
  FROM my_owner.my_object
 WHERE ROWNUM = 1;

If that bombs, the object truly is gone or broken.
If that passes, your larger DML/DDL might be invoking a race (think partition moves or online maintenance). Isolate your statement into smaller bits to pinpoint the culprit.

Wrapping Up: From Mystery to Mastery

Diagnosing ORA‑08103 boils down to classic detective work:

1. Identify the precise SQL
2. Enable Oracle’s tracing
3. Decode file/block/object IDs
4. Map them back to names
5. Check for invalid status, hidden DDL, or corruption
6. Rebuild, repair, or recover as needed

Follow these steps, and you’ll go from “object no longer exists” to “object under control” faster than you can say “hot chocolate break!”

Happy debugging, and may your tables stay firmly in place! 😎

How to Use Google Java Format with Spotless

 

Introduction

Bringing Order to Chaos: Google Java Format and Spotless in Action—Automating Code Formatting for a Cleaner, More Efficient Codebase.

We've all been there. You push your code, feeling like a rockstar, only to be met with a wave of nitpicky code review comments: "Indentation is off," "Use spaces, not tabs," "Dude, who puts a curly brace on the same line?"

It's a never-ending battle—unless you have an enforcer. Enter Google Java Format, the ultimate formatting referee that settles these debates once and for all. But who has time to manually run a formatter on every file? That's where Spotless comes in, automating the entire process and ensuring every commit is clean, compliant, and free of formatting drama.

In this guide, we’ll walk you through setting up Google Java Format with Spotless in both Gradle and Maven projects. By the end, you'll wonder how you ever coded without it!

Why Use Google Java Format?

Imagine merging your feature branch and seeing a pull request filled with hundreds of unnecessary diffs—tabs switched to spaces, misplaced brackets, random newlines. It's like your code went on a formatting rollercoaster.

With Google Java Format, you:

• Automate Code Formatting – Because life's too short for manual indentation fixes.

• Ensure Consistency – No more "tabs vs. spaces" holy wars.

• Simplify Integration – Works like a charm with Gradle and Maven.

• Reduce Merge Conflicts – Fewer unnecessary changes mean happier developers.

So let’s set it up and finally move on to actual coding.

Setting Up Google Java Format with Spotless in Gradle

Step 1: Add the Spotless Plugin to build.gradle

First, install the Spotless plugin.

If using Kotlin DSL (build.gradle.kts):

plugins {
    id("com.diffplug.spotless") version "6.0.0"
}

If using Groovy DSL (build.gradle):

plugins {
    id 'com.diffplug.spotless' version '6.0.0'
}

Step 2: Configure Spotless to Use Google Java Format

Now, set Spotless to auto-format your Java files.

Kotlin DSL (build.gradle.kts)

spotless {
    java {
        target("src/**/*.java")
        googleJavaFormat()
    }
}

Groovy DSL (build.gradle)

spotless {
    java {
        target 'src/**/*.java'
        googleJavaFormat()
    }
}

Step 3: Apply Formatting

Run this to format all Java files:

gradle spotlessApply

Check for compliance before committing:

gradle spotlessCheck

If it fails, just run spotlessApply to fix everything automagically. 🚀

Setting Up Google Java Format with Spotless in Maven

Step 1: Add the Spotless Plugin to pom.xml

For Maven users, add this to your <build> section:

<build>
    <plugins>
        <plugin>
            <groupId>com.diffplug.spotless</groupId>
            <artifactId>spotless-maven-plugin</artifactId>
            <version>2.35.0</version>
            <executions>
                <execution>
                    <goals>
                        <goal>apply</goal>
                    </goals>
                </execution>
            </executions>
            <configuration>
                <formats>
                    <format>
                        <includes>
                            <include>src/**/*.java</include>
                        </includes>
                        <googleJavaFormat/>
                    </format>
                </formats>
            </configuration>
        </plugin>
    </plugins>
</build>

Step 2: Apply Formatting

Run:

mvn spotless:apply

To check compliance:

mvn spotless:check

Now your Java files are always in top shape!

Customizing Spotless

Need more control? Here are some tweaks.

Use a Specific Google Java Format Version

By default, Spotless grabs the latest version, but you can lock it down:

Gradle

spotless {
    java {
        googleJavaFormat("1.15.0")
    }
}

Maven

<configuration>
    <googleJavaFormat>
        <version>1.15.0</version>
    </googleJavaFormat>
</configuration>

Exclude Certain Files

Want to keep specific files untouched? No problem.

Gradle

spotless {
    java {
        target("src/**/*.java")
        googleJavaFormat()
        exclude("src/main/java/com/example/IgnoreThisFile.java")
    }
}

Maven

<excludes>
    <exclude>src/main/java/com/example/IgnoreThisFile.java</exclude>
</excludes>

Enforce Formatting in CI/CD

Make sure no rogue formatting sneaks into your codebase!

GitHub Actions Integration

Create a .github/workflows/spotless.yml file:

name: Spotless Check
on: [push, pull_request]
jobs:
  check-format:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-java@v3
        with:
          distribution: 'temurin'
          java-version: '17'
      - name: Set up Gradle
        uses: gradle/gradle-build-action@v2
      - name: Run Spotless Check
        run: ./gradlew spotlessCheck

For Maven:

- name: Run Spotless Check
  run: mvn spotless:check

Now every PR will be checked before it gets merged. No more formatting surprises! 🎉

Conclusion

Imagine a world where formatting discussions never waste another second of your time. That world is here. Google Java Format + Spotless means you spend more time writing great code and less time arguing over curly braces.

Now, it’s your turn! Have you tried Google Java Format with Spotless? What’s your biggest formatting headache? Drop a comment below—we’d love to hear your thoughts!

🚀 Looking for more Java productivity tips? Check out our other posts:

• How to Automate Code Quality Checks in Java

• Top 10 Gradle Plugins for Java Developers

• Debugging Like a Pro: Best Tools for Java Devs

Happy coding, and may your commits always be spotless! 😎