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.