Arthur Axel "fREW" Schmidt > SQL-Translator-0.11016 > SQL::Translator::Producer::Oracle

Download:
SQL-Translator-0.11016.tar.gz

Dependencies

Annotate this POD

CPAN RT

New  59
Open  21
View/Report Bugs
Module Version: 1.59   Source   Latest Release: SQL-Translator-0.11020

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.

syntax highlighting: