#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0

    Need assistance in correcting a simple perl script


    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 :

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,938
    Rep Power
    1225
    You didn't say how your script is failing to do what you want and I don't have an oracle database to use for testing.

    Are you receiving any errors/warnings?

    Is it only creating a single table or are all tables being created as expected?

    I don't see any insert statement, so can I assume you left that out because the tables aren't being created?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,938
    Rep Power
    1225
    Here's a simple successful test I used to on a mysql db.
    Code:
    use strict;
    use warnings;
    use DBI;
    
    my $tables = 100;
    my $rows   = 10000;
    
    my $dbh = DBI->connect ('DBI:mysql:test', 'user', 'password')
            or die "Failed to connect to database: " . DBI->errstr;
    
    for my $i (1..$tables) {
        my $sql = "CREATE TABLE test_data$i(id INT NOT NULL PRIMARY KEY,
                                            group_id INT NOT NULL,
                                            ext VARCHAR(20) NOT NULL)";
    
        $dbh->do($sql) or die DBI->errstr;
    
    }
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    Hi

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

    Rgds
    Terry

IMN logo majestic logo threadwatch logo seochat tools logo