creating a database table in perl

Posted on 2007-10-06
Last Modified: 2012-05-05
i'm writing a program in perl to populate a database.  the code seems to be the way it should, but when i run it, the script does not create a new table.  it drops the table but doesn't create it.  i get an error message saying table does not exist when it gets to the insert statement.


 #use strict;
         use DBI;
         my $dbh = DBI->connect( 'dbi:Oracle:XE',
                               ) || die "Database connection not made:


my $sql = qq {DROP TABLE CUSTOMER};
$dbh->do ($sql);

my $sql = qq { CREATE TABLE CUSTOMER (
            id INTEGER PRIMARY KEY,
            fname vARCHAR2(30),
            lname VACHAR2(30),
            street VARCHAR2(50),
            city VARCHAR2(30),
            state VARCHAR2(20),
            zip VARCHAR2(10),
            gender VARCHAR2(10)
$dbh->do ($sql);

my $insert_handle = $dbh->prepare_cached('INSERT INTO CUSTOMER (id,

fname,lname, street, city, state, zip, gender) VALUES (?,?,?,?,?,?,?,?) ');
      die "Couldn't prepare queries; aborting" unless defined


my @tuple;
my $index=1;

open(TEST, "custdata_CSV.csv");
while (<TEST>){
      @tuple = split(/,/,$_);
      print "$tuple[0]\n$tuple[1]\n$tuple[2]\n$tuple[3]\n$tuple[4]



Question by:jpking72
    LVL 3

    Accepted Solution

    You have a typing error near "lname"
    =>  lname VACHAR2(30)
    should be
    =>  lname VARCHAR2(30)
    (you forgot an R)
    LVL 28

    Assisted Solution

    You should be using the strict and warnings pragmas.

    Also, you should enable RaiseError on the DB connection.

    use strict;
    use warnings;
    use DBI;

    my $dbh = DBI->connect('dbi:Oracle:XE', 'username', 'password',
                           { RaiseError => 1, PrintError => 0 })
                || die "Database connection not made: $DBI::errstr";


    Your id field should be auto incremented instead of manually updating the index number.

    You should always check the return status of an open call.

    open(TEST, '<', "custdata_CSV.csv") || die "Can't open custdata_CSV.csv <$!>";

    Author Comment

    how do you do auto increment in oracle?
    LVL 28

    Expert Comment

    >> how do you do auto increment in oracle?

    I work with mysql but I assume the DBD::Oracle module supports the same syntax as DBD::mysql.

    my $sql = qq { CREATE TABLE CUSTOMER (
                                         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    Author Comment

    I've seen websites that say it can't be done that way in Oracle.  check this out:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
    In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now