# OracleDB Client

Execute SQL queries and statements against Oracle databases with full type safety and runtime validation.

## Methods

| Method                                      | Description                                                                |
| ------------------------------------------- | -------------------------------------------------------------------------- |
| `query<T>(sql, schema, params?, metadata?)` | Execute a SELECT query and return validated, typed results                 |
| `execute(sql, params?, metadata?)`          | Execute a statement (INSERT, UPDATE, DELETE) and return affected row count |

## Usage

### Basic Query with Schema Validation

```typescript
import { api, z, oracledb } from "@superblocksteam/sdk-api";

// Integration ID from the integrations panel
const PROD_ORACLE = "a1b2c3d4-5678-90ab-cdef-oracle000001";

const UserSchema = z.object({
  ID: z.number(),
  NAME: z.string(),
  EMAIL: z.string().email(),
  CREATED_AT: z.string(),
});

export default api({
  integrations: {
    oracledb: oracledb(PROD_ORACLE),
  },
  name: "OracleDBExample",
  input: z.object({
    status: z.string(),
  }),
  output: z.object({
    users: z.array(UserSchema),
  }),
  async run(ctx, { status }) {
    const users = await ctx.integrations.oracledb.query(
      "SELECT ID, NAME, EMAIL, CREATED_AT FROM USERS WHERE STATUS = :1",
      UserSchema,
      [status],
    );

    return { users };
  },
});
```

### Executing INSERT, UPDATE, DELETE Statements

```typescript
// INSERT
await ctx.integrations.oracledb.execute(
  "INSERT INTO USERS (NAME, EMAIL) VALUES (:1, :2)",
  ["John Doe", "john@example.com"],
);

// UPDATE
const updateResult = await ctx.integrations.oracledb.execute(
  "UPDATE USERS SET LAST_LOGIN = SYSDATE WHERE ID = :1",
  [userId],
);
console.log(`Updated ${updateResult.rowCount} rows`);

// DELETE
const deleteResult = await ctx.integrations.oracledb.execute(
  "DELETE FROM SESSIONS WHERE EXPIRES_AT < SYSDATE",
);
console.log(`Deleted ${deleteResult.rowCount} expired sessions`);
```

### Using Oracle-Specific Functions

```typescript
const AnalyticsSchema = z.object({
  REPORT_DATE: z.string(),
  TOTAL_SALES: z.string(),
  ORDER_COUNT: z.coerce.number(),
});

const analytics = await ctx.integrations.oracledb.query(
  `SELECT
    TRUNC(ORDER_DATE) as REPORT_DATE,
    SUM(TOTAL) as TOTAL_SALES,
    COUNT(*) as ORDER_COUNT
  FROM ORDERS
  WHERE ORDER_DATE >= SYSDATE - 30
  GROUP BY TRUNC(ORDER_DATE)
  ORDER BY REPORT_DATE DESC`,
  AnalyticsSchema,
);
```

## Trace Metadata

All methods accept an optional `metadata` parameter as the last argument for diagnostics labeling. See the [root SDK README](../../../README.md#trace-metadata) for details.

## Common Pitfalls

### Uppercase Column Names

Oracle returns column names in UPPERCASE by default:

```typescript
// CORRECT - Match Oracle's uppercase column names
const schema = z.object({
  ID: z.number(),
  NAME: z.string(),
});

// Or use aliases for lowercase
// SELECT id as "id", name as "name" FROM users
```

### NUMBER Values May Be Strings

Large Oracle NUMBER columns may be returned as strings:

```typescript
const schema = z.object({
  AMOUNT: z.string().transform((val) => parseFloat(val)),
});
```

### Schema Parameter is Required

```typescript
// CORRECT - Schema is required
const users = await ctx.integrations.oracledb.query(
  "SELECT * FROM USERS",
  UserSchema,
);
```

### NULL Handling

```typescript
const schema = z.object({
  NAME: z.string(),
  BIO: z.string().nullable(),
});
```

## Error Handling

```typescript
import { QueryValidationError } from "@superblocksteam/sdk-api";

try {
  const users = await ctx.integrations.oracledb.query(
    "SELECT * FROM USERS",
    UserSchema,
  );
} catch (error) {
  if (error instanceof QueryValidationError) {
    console.error("Row index:", error.details.rowIndex);
    console.error("Validation errors:", error.details.errors);
  }
}
```

## API Reference

- [Oracle Database Documentation](https://docs.oracle.com/en/database/)
- [Oracle SQL Reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/)
- [Oracle Data Types](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html)