SQL::Tr...::Oracle(3)

🦋 SQL::Tr...::Oracle(3) — the Perl manual page, bestie edition 🦋

okay so this is SQL::Tr...::Oracle(3), straight out of the User Contributed Perl Documentation shelf, and it's literally the Oracle SQL producer. yes, that Oracle. come with me 💅

---

✨ NAME ✨

SQL::Tr...::Oracle — Oracle SQL producer. that's it, that's the whole vibe. it makes Oracle SQL. slay.

---

🌸 SYNOPSIS 🌸

use SQL::Translator;

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

yes bestie, you literally get two lines and you're off to the races. no cap. 🫶

---

💖 DESCRIPTION 💖

Creates an SQL DDL suitable for Oracle. that's the entire description and honestly? iconic. minimal. letting the work speak for itself. periodt. ✨

---

🎀 producer_args 🎀

these are the little knobs you can turn, and you will want to turn them:

• delay_constraints — this option removes the primary key and other key constraints from the CREATE TABLE statement and adds ALTER TABLEs at the end with it. so like, the constraints get fashionably late to the party instead of showing up in the table itself 🦋
• quote_field_names — controls whether quotes are being used around column names in generated DDL. quotes on? quotes off? your call, bestie.
• quote_table_names — controls whether quotes are being used around table, sequence and trigger names in generated DDL. table names, sequence names, trigger names — all of them get the quote treatment if you say so. 🎀

---

🔥 NOTES 🔥

👑 Autoincremental primary keys 👑

This producer uses sequences and triggers to autoincrement primary key columns, if necessary. so it's not out here doing it for fun, it does it when it's needed. main character energy only when earned.

Here's the tea though: 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. yes, it's giving "I contain multitudes." 😭

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. makes sense, that's the whole point of array context honestly.

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. so: array context = no slash, scalar context = slash stays. do not come for the slash, the slash is load-bearing. 💀

---

🌈 CREDITS 🌈

Mad props to Tim Bunce for much of the logic stolen from his mysql2ora script. we love a credited icon. 👑

---

💜 AUTHORS 💜

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

three legends. we stan. 🫶

---

⭐ SEE ALSO ⭐

• SQL::Translator
• DDL::Oracle
• mysql2ora

go read those too, iykyk. 🦋

---

mwah 💋 that's the whole page — brought to you by perl v5.42.0, dated 2024-12-12, filed under SQL::Tr...::Oracle(3). stay slay, stay normalized, and may all your triggers compile on the first try. 🎀✨