G'day SpaceCowboy,

You have a lot of mistakes in your code. Here's a selection:

• There is no dbi module on CPAN. You probably meant DBI; if not, provide a link to this dbi module.
• Don't use smart quotes. You'll run into all sorts of problems; e.g.

  $ perl -MO=Deparse -e '$dbh->do("alter session set current_schema = th
  +e current schema”);'
  Can't find string terminator '"' anywhere before EOF at -e line 1.
  
  $ perl -MO=Deparse -e 'print “third script executed”;'
  Unrecognized character \xE2; marked by <-- HERE after print <-- HERE n
  +ear column 7 at -e line 1.
  [download]

• You used $dbh once; then left out the sigil in a multitude of places thereafter.
• You have ttemp in one place; that should probably be temp.

We all make typos but, with this many, you're quite likely to spend more time debugging than coding. Pay more attention to the code you're writing: don't make a rod for your back.

"Any general wisdom here?"

The following is intended to be very generic. There's not enough up-front information for me to provide working code. Adapt what I have here for your specific needs.

I'd probably start with a front-end script that might look something like:

#!/usr/bin/env perl

use strict;
use warnings;

use SpaceCowboy::Database::Module;

my $database_credentials = { ... };

my $sdm = SpaceCowboy::Database::Module::->new($database_credentials);

$sdm->$_() for qw{primary first second third};
[download]

I don't know what you had in mind for "***import database credentials here"; perhaps Getopt::Long, a config file, or something else. I'll leave how you populate $database_credentials up to you.

In a separate file (.../SpaceCowboy/Database/Module.pm), code SpaceCowboy::Database::Module which does all the work. Here's a rough example of what that might look like:

package SpaceCowboy::Database::Module;

use 5.010;
use strict;
use warnings;

use DBI;

sub new {
    my ($class, $params) = @_;

    return bless $params => $class;
}

{
    my $DBH;

    sub dbh {
        my ($self) = @_;
        $DBH //= $self->_connect();
    }
}

sub _connect {
    my ($self) = @_;
    # Use credentials in DBI->connect(...);
    #   e.g. $self->host(), $self->username(), etc.
    return DBI->connect(...);
}

sub primary {
    my ($self) = @_;
    $self->dbh()->do("alter session ...");
    return;
}

sub first {
    my ($self) = @_;
    $self->dbh()->do("create ...");
    $self->dbh()->do("insert ...");
    return;
}

# ditto for second() and third()

1;
[download]

I've included a very basic new() method; however, you'll probably find one of the many frameworks available are easier to work with — Moo might be a good one to use here. See also: "perlintro: OO Perl" and perlootut.

The database handle, $DBH, is lexically scoped such that only dbh() can access it directly. It is only ever set once.

You'll need to look at the DBD::whatever documentation, for whatever database you intend to use, to find out the correct way to call DBI->connect(...).

"how can I serialize the execution?"

You could use flags for each of primary(), first(), etc. Set these to indicate which parts have completed successfully; do not rerun the flagged parts. You'll possibly need a reset() method, to turn these off, so that a fresh run from the start is possible.

"Is it possible to write multiple statements like dbh->do("create temp schema", "insert table");"

That may be dependent on the database you're using. You could write:

$self->dbh()->do($_) for @statements;
[download]

Be aware that might be problematic if you're also trying to flag the success of individual statements.

Ask yourself why you think you'd need this. If you can't come up with a good reason, don't do it.