---
name: "SQL::Translator::Producer::Oracle"
section: "3pm"
platform: "linux"
package: "libsql-translator-perl"
version: "1.66-1"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/SQL::Translator::Producer::Oracle"
---

# SQL::Translator::Producer::Oracle(3pm)

Oracle SQL producer

## NAME

SQL::Translator::Producer::Oracle - Oracle SQL producer

## SYNOPSIS

```
  use SQL::Translator;
  my $t = SQL::Translator->new( parser => '...', producer => 'Oracle' );
  print $translator->translate( $file );
```

## DESCRIPTION

Creates an SQL DDL suitable for Oracle.

## producer\_args

- delay\_constraints

  This option remove the primary key and other key constraints from the CREATE TABLE statement and adds ALTER TABLEs at the end with it.

- quote\_field\_names

  Controls whether quotes are being used around column names in generated DDL.

- quote\_table\_names

  Controls whether quotes are being used around table, sequence and trigger names in generated DDL.

## NOTES

### Autoincremental primary keys

This producer uses sequences and triggers to autoincrement primary key columns, if necessary. SQLPlus and DBI expect a slightly different syntax of CREATE TRIGGER statement. You might have noticed that this producer returns a scalar containing all statements concatenated by newlines or an array of single statements depending on the context (scalar, array) it has been called in.

SQLPlus expects following trigger syntax:

```
    CREATE OR REPLACE TRIGGER ai_person_id
    BEFORE INSERT ON person
    FOR EACH ROW WHEN (
     new.id IS NULL OR new.id = 0
    )
    BEGIN
     SELECT sq_person_id.nextval
     INTO :new.id
     FROM dual;
    END;
    /
```

Whereas if you want to create the same trigger using "do" in DBI, you need to omit the last slash:

```
    my $dbh = DBI->connect('dbi:Oracle:mysid', 'scott', 'tiger');
    $dbh->do("
        CREATE OR REPLACE TRIGGER ai_person_id
        BEFORE INSERT ON person
        FOR EACH ROW WHEN (
         new.id IS NULL OR new.id = 0
        )
        BEGIN
         SELECT sq_person_id.nextval
         INTO :new.id
         FROM dual;
        END;
    ");
```

If you call this producer in array context, we expect you want to process the returned array of statements using DBI like "deploy" in DBIx::Class::Schema does.

To get this working we removed the slash in those statements in version 0.09002 of SQL::Translator when called in array context. In scalar context the slash will be still there to ensure compatibility with SQLPlus.

## CREDITS

Mad props to Tim Bunce for much of the logic stolen from his "mysql2ora" script.

## AUTHORS

Ken Youens-Clark \<kclark@cpan.org\>, Alexander Hartmaier \<abraxxa@cpan.org\>, Fabien Wernli \<faxmodem@cpan.org\>.

## SEE ALSO

SQL::Translator, DDL::Oracle, mysql2ora.