Need help to correct a simple 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 :

#!/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;
[download]

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

Comment on Need help to correct a simple script Download Code
Re: Need help to correct a simple script by blue_cowdawg (Prior) on Jan 07, 2013 at 15:36 UTC
so... what errors are you seeing? Peter L. Berghold -- Unix Professional Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg	[reply]
Re: Need help to correct a simple script by marto (Chancellor) on Jan 07, 2013 at 15:42 UTC
Hi, at first glance `$count=1` [download] Should be: `$count=1;` [download] Also Oracle will complain about that SQL statement: `CREATE TABLE test_data($count)(` [download] Shoud be: `CREATE TABLE test_data$count(` [download] 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.	[reply] [d/l] [select]
Re^2: Need help to correct a simple script by space_monk (Hermit) on Jan 08, 2013 at 09:52 UTC
With respect to Update 2, you also can't use bind variables in respect of table names - see comment below. A Monk aims to give answers to those who have none, and to learn from those who know more.	[reply]
Re: Need help to correct a simple script by space_monk (Hermit) on Jan 07, 2013 at 15:57 UTC
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` [download] This incorporates martos suggestions above, and I'll update if anyone spots any other issues. A Monk aims to give answers to those who have none, and to learn from those who know more.	[reply] [d/l]
Re: Need help to correct a simple script by space_monk (Hermit) on Jan 07, 2013 at 16:08 UTC
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) +)); }` [download] Unfortunately you can't prepare and execute this statement with bound variables, as the table name is formed from one of the variables. A Monk aims to give answers to those who have none, and to learn from those who know more.	[reply] [d/l]
Re^2: Need help to correct a simple script by terrykhatri531 (Novice) on Jan 07, 2013 at 18:36 UTC
Hi Thank you for your quick responses, I adjusted my script accordingly and now it works like a charm. Rgds Terry	[reply]


Welcome to the Monastery
	PerlMonks

Need help to correct a simple script

The best material for plates (tableware) is: