Need help to correct a simple script

[terrykhatri531]

Hi, I am no perl programmer however I need to create a script that creates a desired number of tables with desired number of inserts per table, here is the code :

#!/usr/bin/perl
use DBI;

$tables=100;
$rows=10000;
$count=1

if (!($dbh = DBI->connect ('DBI:Oracle:orcl',"hr","hr")))
{
die ("Failed to connect to database: " . DBI->errstr);
};

$sql = "CREATE TABLE test_data($count)(".
            "id PRIMARY KEY,".   
            "group_id NOT NULL,".
            "created_at NOT NULL,".
            "text NOT NULL) AS ".
                    "SELECT ".
                        "rownum,".
                        "MOD(rownum, 5),".
                        "TO_DATE('1-jan-07', 'dd-mon-yy') + INTERVAL '
+1' MINUTE * rownum,".
                        "CAST ('xyz' || rownum AS VARCHAR2(50)) ".
                    "FROM dual ".
                    "CONNECT BY LEVEL <= $rows";


while ($count<$tables)
{
if (!($dbh->do ($sql)))
{
die ("Failed to insert row: " . DBI->errstr);
};

 $count++;
}
$dbh->commit;

$dbh->disconnect;

It should create tables from test_data1 to test_data100

Can someone be please kind enough to correct the code.

Thank you very much in advance.

Rgds

Terry

[marto]

Hi, at first glance

$count=1

Should be:

$count=1;

Also Oracle will complain about that SQL statement:

CREATE TABLE test_data($count)(

Shoud be:

CREATE TABLE test_data$count(

See Use strict and warnings, Create Table and the DBI documentation. Pay attention to the section on placeholders and bind variables.

Update: At second glance there's other problems. Let me fix this and update.

Update 2:

With Oracle (and perhaps other databases) Bind variables can be used for DML (Select, insert) but not DDL (Create, Alter, Drop) statements. (See What are the difference between DDL, DML and DCL commands?). This means that my previous advice on placeholders and bind variables won't help you much, though the topic is still worth reading. I'd try to create a stored procedure within Oracle to do the table create, and call if from your Perl script passing in the appropriate table name.

[space_monk]

With respect to Update 2, you also can't use bind variables in respect of table names - see comment below.

[space_monk]

I like to write complex SQL statements as HEREDOCs, as removing the Perl syntax from within the SQL syntax makes for much easier reading...

i.e. it is much easier to read your CREATE TABLE statement when written as....

my $sql = <<EOF;
CREATE TABLE test_data$count(
    id PRIMARY KEY,   
    group_id NOT NULL,
    created_at NOT NULL,
    text NOT NULL) AS 
        SELECT 
            rownum,
            MOD(rownum, 5),
            TO_DATE('1-jan-07', 'dd-mon-yy') + INTERVAL '
+1' MINUTE * rownum,
            CAST ('xyz' || rownum AS VARCHAR2(50)) 
            FROM dual 
            CONNECT BY LEVEL <= $rows
EOF

This incorporates martos suggestions above, and I'll update if anyone spots any other issues.

[blue_cowdawg]

so... what errors are you seeing?

[space_monk]

The SQL needs to be declared in the loop, as you're changing the variable $count each time. Consider the more idiomatic

foreach my $count (1..$tables) {
   my $sql =<<EOF;
....
EOF

    die ("Failed to insert row: " . DBI->errstr) if (!($dbh->do ($sql)
+));

}

Unfortunately you can't prepare and execute this statement with bound variables, as the table name is formed from one of the variables.

[terrykhatri531]

Hi

Thank you for your quick responses, I adjusted my script accordingly and now it works like a charm.

Rgds

Terry