seed-forge Oracle Database Knowledge Base
Source: Compiled from Oracle official documentation, JDBC best practices, and common integration patterns.
Connection Types
| Format | Example | Status |
|---|---|---|
| Service Name | jdbc:oracle:thin:@//host:port/service_name |
Recommended |
| SID (legacy) | jdbc:oracle:thin:@host:port:SID |
Legacy, avoid |
| TNS | jdbc:oracle:thin:@tns_alias |
Enterprise with tnsnames.ora |
| EZConnect | jdbc:oracle:thin:@host:port/service_name |
Simple, no config file |
- Always prefer Service Name over SID for new projects.
- Easy Connect Plus (19c+):
jdbc:oracle:thin:@tcp://host:port/service_name?wallet_location=/path.
JDBC Driver
| Type | Description | When to use |
|---|---|---|
| Thin (Type 4) | Pure Java, no Oracle client | Default choice |
| Thick (OCI, Type 2) | Requires Oracle Client | Advanced features (TAF, Advanced Queuing) |
| UCP | Universal Connection Pool | Production pooling |
- Thin driver is sufficient for 95% of use cases.
- Use
ojdbc8.jarfor JDK 8+,ojdbc11.jarfor JDK 11+. - Add
orai18n.jarfor internationalization support.
Connection Pool
| Pool | Recommended for |
|---|---|
| UCP (Oracle) | Oracle-specific features, best performance |
| HikariCP | Spring Boot, general-purpose, simpler config |
HikariCP config for Oracle:
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@//host:1521/service_name
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL
LOB Handling
CLOBfor large text (>4000 chars);BLOBfor binary data.- Use
setStringForClob()/setBinaryStream()for inserts. - For reads, use
getCharacterStream()/getBinaryStream()— avoidgetString()on large CLOBs. - LOB locator vs data:
oracle.jdbc.useStreamForLobOutput=truefor streaming writes.
Character Set
- AL32UTF8 is the recommended database character set (Unicode).
- JDBC driver auto-detects charset from database; no client-side config needed.
- Verify:
SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
SQL Dialect Differences (vs MySQL/PostgreSQL)
| Feature | Oracle | MySQL/PostgreSQL |
|---|---|---|
| Auto-increment | SEQUENCE + TRIGGER or IDENTITY (12c+) |
AUTO_INCREMENT / SERIAL |
| LIMIT | FETCH FIRST n ROWS ONLY (12c+) or ROWNUM |
LIMIT n |
| String concat | ` | |
| Date functions | SYSDATE, ADD_MONTHS(), MONTHS_BETWEEN() |
NOW(), DATE_ADD() |
| Boolean | No native BOOLEAN in SQL (use NUMBER(1)) | Native BOOLEAN |
| Outer join syntax | (+) legacy or ANSI LEFT JOIN |
ANSI LEFT JOIN |
Oracle Wallet
- Wallet stores credentials for mutual TLS authentication.
- Location: set
oracle.net.wallet_locationin connection properties. - For Autonomous Database: download wallet zip, extract to directory, reference in connection string.
Guardrails
- Use Service Name format, not SID.
- Always use connection pooling (UCP or HikariCP).
- Use bind variables (prepared statements) — never string concatenation for SQL.
- Set
oracle.jdbc.ReadTimeoutandoracle.net.CONNECT_TIMEOUT. - For LOBs >4KB, always use streaming API.