oracular (3) SQL::Translator::Producer::Oracle.3pm.gz

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.