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.jar for JDK 8+, ojdbc11.jar for JDK 11+.
• Add orai18n.jar for 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

• CLOB for large text (>4000 chars); BLOB for binary data.
• Use setStringForClob() / setBinaryStream() for inserts.
• For reads, use getCharacterStream() / getBinaryStream() — avoid getString() on large CLOBs.
• LOB locator vs data: oracle.jdbc.useStreamForLobOutput=true for 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_location in 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.ReadTimeout and oracle.net.CONNECT_TIMEOUT.
• For LOBs >4KB, always use streaming API.