I have unlimited space in Table Space, I have 30 gig of hard drive space, I
am confuesd. Here is what I got posting whole code because I have two
problems. On Mandrake 9 current versionsDBI and DBD::Oracle
First Error is:
DBD::Oracle::st execute failed: ORA-01658: unable to create INITIAL extent
for segment in tablespace WEBDATA (DBD ERROR: OCIStmtExecute) [for statement
``CREATE TABLE IS_Volume (
iPeriod INTEGER,
icVolume INTEGER,
icRevenue INTEGER,
icBonus INTEGER,
ipVolume INTEGER,
ipRevenue INTEGER,
ipBonus INTEGER
)
'' with params: ]) at ./importv2 line 111.
Cannot Create table IS_Volume:
ORA-01658: unable to create INITIAL extent for segment in tablespace WEBDATA
(DBD ERROR: OCIStmtExecute)
So not to be held back I #'ed out all the IS_Volume related stuff and ran
the script and got this:
DBD::Oracle::st execute failed: ORA-01653: unable to extend table
WEBDATA.SALES_VOLUME by 256 in tablespace WEBDATA (DBD ERROR:
OCIStmtExecute) [for statement ``INSERT INTO Sales_Volume VALUES ( '200302',
'SR2313', '45.000', '1260.00', '405.00', '0.00', '35.000', '980.00' )'' with
params: ]) at ./importv2 line 348, <SALES_VOLUME> line 55229.
Can not execute Sales_vol data import:
ORA-01653: unable to extend table WEBDATA.SALES_VOLUME by 256 in tablespace
WEBDATA (DBD ERROR: OCIStmtExecute)
To preface I am in no way a Oracle DB and this is my first major perl
undertaking so any feed back will be extremely helpful, even if on either
Oracle or Perl side.
CODE
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------------------
#!/usr/bin/perl
use DBI; #Opens DBI
print "\n\n\n\nBegining import of files from SAP\n";
if ($ARGV[0] eq "new") { #determines wether to delete files or nor
print "\n\n\n\nDeleting old files\n\n";
my $deleted = unlink glob "*.txt";
print "Deleted $deleted files.\n";
#Retrieves files from ftp
@files = qw ( Distributor.txt Sales_Rep.txt Sales_vol.txt
2003_GOV_Sales.txt 2003_SBS_Sales.txt 2003_SB_NAS.txt );
foreach $files (@files) {
system "wget ftp:/(REMOVED)";
print "\n\n\n\nReceived file $files\n\n\n\n";
}
}
#Connection to database
my $dbh = DBI->connect( "dbi REMOVED )
or die "Cannot connect to Oracle Server\n: $DBI::errstr\n";
#Dropping Tables
#Sales_Rep
my $sth = $dbh->prepare( "DROP TABLE Sales_Rep")
or die "Can not Prep Drop Table rep\n: $DBI::errstr\n";
$sth->execute
or die "Can not execute Drop Table rep\n: $DBI::errstr\n";
#NAS_Volume
$sth = $dbh->prepare( "DROP TABLE NAS_Volume" )
or die "Can not Prep Drop Table NAS_Volume:\n $DBI::errstr\n";
$sth->execute
or die "Can not Execute Dropt Table NAS_Volume:\n $DBI::errstr\n";
#Sales_Volume
$sth = $dbh->prepare( "DROP TABLE Sales_Volume" )
or die "Can not Prep Drop Table Sales_Volume:\n $DBI::errstr\n";
$sth->execute
or die "Can not Execute Drop Table Sales_Volume:\n $DBI::errstr\n";
#GOV_Volume
$sth = $dbh->prepare( "DROP TABLE GOV_Volume" )
or die "Can not Prep Drop Table GOV_Volume:\n $DBI::errstr\n";
$sth->execute
or die "Can not Execute Drop Table GOV_Volume:\n $DBI::errstr\n";
#Creatinng Tables
#Sales_Rep
$sth = $dbh->prepare( "CREATE TABLE Sales_Rep (
sRepID VARCHAR(6),
sName VARCHAR(30)
)
")
or die "Cannot prep for table rep\n: $DBI::errstr\n";
$sth->execute
or die "Cannot create table rep\n: $DBI::errstr\n";
#Sales_Volume
$sth = $dbh->prepare( "CREATE TABLE Sales_Volume (
iPeriod INTEGER,
sTSM VARCHAR(10),
icVolume INTEGER,
icRevenue INTEGER,
icBonus INTEGER,
ipVolume INTEGER,
ipRevenue INTEGER,
ipBonus INTEGER
)
" )
or die "Cannot prep for table Sales_Volume:\n $DBI::errstr\n";
$sth->execute
or die "Cannot Create table Sales_Volume:\n $DBI::errstr\n";
#NAS_Volume
$sth = $dbh->prepare( "CREATE TABLE NAS_Volume (
sNAD VARCHAR(10),
iPeriod INTEGER,
icVolume INTEGER,
icRevenue INTEGER,
icBonus INTEGER,
ipVolume INTEGER,
ipRevenue INTEGER,
ipBonus INTEGER
)
" )
or die "Cannot prep for table NAS_Volume:\n $DBI::errstr\n";
$sth->execute
or die "Cannot Create table NAS_Volume:\n $DBI::errstr\n";
#GOV_Volume
$sth = $dbh->prepare( "CREATE TABLE GOV_Volume (
sGOV VARCHAR(10),
iPeriod INTEGER,
icVolume INTEGER,
icRevenue INTEGER,
icBonus INTEGER,
ipVolume INTEGER,
ipRevenue INTEGER,
ipBonus INTEGER
)
" )
or die "Cannot prep for table GOV_Volume:\n $DBI::errstr\n";
$sth->execute
or die "Cannot Create table GOV_Volume:\n $DBI::errstr\n";
#IS_Volume
$sth = $dbh->prepare( "CREATE TABLE IS_Volume (
iPeriod INTEGER,
icVolume INTEGER,
icRevenue INTEGER,
icBonus INTEGER,
ipVolume INTEGER,
ipRevenue INTEGER,
ipBonus INTEGER
)
" )
or die "Cannot prep for table IS_Volume:\n $DBI::errstr\n";
$sth->execute
or die "Cannot Create table IS_Volume:\n $DBI::errstr\n";
#Opening Sales_Rep.txt
unless (open SALES_REP, "Sales_Rep.txt") {
die "Can not open Sales_Rep.txt:\n $!";
}
while (<SALES_REP>) {
chomp;
($srnu,$srna) = split /\^/, $_;
my $qsrnu = $dbh->quote( $srnu );
my $qsrna = $dbh->quote( $srna );
$sth = $dbh->prepare( "INSERT INTO Sales_Rep VALUES ( $qsrnu, $qsrna
)" )
or die "Can not prep Sales_Rep data for import:\n $DBI::errstr\n";
$sth->execute
or die "Can not execute Sales_Rep data import:\n $DBI::errstr\n";
}
close SALES_REP;
#OPENING NAS_VOLUME
unless (open NAS_VOLUME, "2003_SB_NAS.txt") {
die "Can not open 2003_SB_NAS.tx";
}
while (<NAS_VOLUME>) {
if ($. gt 1 ) {
chomp;
(undef, undef, undef, undef, $sNAD, $iPeriod, $icVolume,
$icRevenue, $icBonus, $ipVolume, $ipRevenue, $ipBonus, undef) = split /\~/,
$_;
$sNAD =~ s/\s+//g;
$iPeriod =~ s/\s+//g;
$icVolume =~ s/\s+//g;
$icRevenue =~ s/\s+//g;
$icBonus =~ s/\s+//g;
$ipVolume =~ s/\s+//g;
$ipRevenue =~ s/\s+//g;
$ipBonus =~ s/\s+//g;
if ($icVolume =~ /\-$/) {
my $minus = chop $icVolume;
$icVolume = $minus . $icVolume;
}
if ($icRevenue =~ /\-$/) {
my $minus = chop $icRevenue;
$icRevenue = $minus . $icRevenue;
}
if ($icBonus =~ /\-$/) {
my $minus = chop $icBonus;
$icBonus = $minus . $icBonus;
}
if ($ipVolume =~ /\-$/) {
my $minus = chop $ipVolume;
$ipVolume = $minus . $ipVolume;
}
if ($ipRevenue =~ /\-$/) {
my $minus = chop $ipRevenue;
$ipRevenue = $minus . $ipRevenue;
}
if ($ipBonus =~ /\-$/) {
my $minus = chop $ipBonus;
$ipBonus = $minus . $ipBonus;
}
my $qiPeriod = $dbh->quote( $iPeriod );
my $qsNAD = $dbh->quote( $sNAD );
my $qicVolume = $dbh->quote( $icVolume );
my $qicRevenue = $dbh->quote( $icRevenue );
my $qicBonus = $dbh->quote( $icBonus );
my $qipVolume = $dbh->quote( $ipVolume );
my $qipRevenue = $dbh->quote( $ipRevenue );
my $qipBonus = $dbh->quote( $ipBonus );
$sth = $dbh->prepare( "INSERT INTO NAS_Volume VALUES (
$qsNAD, $qiPeriod, $qicVolume, $qicRevenue, $qicBonus, $qipVolume,
$qipRevenue, $qipBonus )" )
or die "Can not prep NAS_vol data for import:\n
$DBI::errstr\n";
$sth->execute
or die "Can not execute NAS_vol data import:\n
$DBI::errstr\n";
print "NAS: $.\n"
}
}
close NAS_VOLUME;
#OPENING GOV_VOLUME
unless (open GOV_VOLUME, "2003_GOV_Sales.txt") {
die "Can not open 2003_GOV_Sales.tx";
}
while (<GOV_VOLUME>) {
if ($. gt 1 ) {
chomp;
(undef, undef, undef, undef, $sGOV, $iPeriod, $icVolume,
$icRevenue, $icBonus, $ipVolume, $ipRevenue, $ipBonus, undef) = split /\~/,
$_;
$sGOV =~ s/\s+//g;
$iPeriod =~ s/\s+//g;
$icVolume =~ s/\s+//g;
$icRevenue =~ s/\s+//g;
$icBonus =~ s/\s+//g;
$ipVolume =~ s/\s+//g;
$ipRevenue =~ s/\s+//g;
$ipBonus =~ s/\s+//g;
if ($icVolume =~ /\-$/) {
my $minus = chop $icVolume;
$icVolume = $minus . $icVolume;
}
if ($icRevenue =~ /\-$/) {
my $minus = chop $icRevenue;
$icRevenue = $minus . $icRevenue;
}
if ($icBonus =~ /\-$/) {
my $minus = chop $icBonus;
$icBonus = $minus . $icBonus;
}
if ($ipVolume =~ /\-$/) {
my $minus = chop $ipVolume;
$ipVolume = $minus . $ipVolume;
}
if ($ipRevenue =~ /\-$/) {
my $minus = chop $ipRevenue;
$ipRevenue = $minus . $ipRevenue;
}
if ($ipBonus =~ /\-$/) {
my $minus = chop $ipBonus;
$ipBonus = $minus . $ipBonus;
}
my $qiPeriod = $dbh->quote( $iPeriod );
my $qsGOV = $dbh->quote( $sGOV );
my $qicVolume = $dbh->quote( $icVolume );
my $qicRevenue = $dbh->quote( $icRevenue );
my $qicBonus = $dbh->quote( $icBonus );
my $qipVolume = $dbh->quote( $ipVolume );
my $qipRevenue = $dbh->quote( $ipRevenue );
my $qipBonus = $dbh->quote( $ipBonus );
$sth = $dbh->prepare( "INSERT INTO GOV_Volume VALUES (
$qsGOV, $qiPeriod, $qicVolume, $qicRevenue, $qicBonus, $qipVolume,
$qipRevenue, $qipBonus )" )
or die "Can not prep GOV_vol data for import:\n
$DBI::errstr\n";
$sth->execute
or die "Can not execute GOV_vol data import:\n
$DBI::errstr\n";
print "GOV: $.\n"
}
}
close GOV_VOLUME;
#OPENING IS_VOLUME
unless (open IS_VOLUME, "2003_SBS_Sales.txt") {
die "Can not open 2003_SBS_SALES.tx";
}
while (<IS_VOLUME>) {
if ($. gt 1 ) {
chomp;
(undef, undef, undef, $iPeriod, $icVolume, $icRevenue,
$icBonus, $ipVolume, $ipRevenue, $ipBonus, undef) = split /\~/, $_;
$iPeriod =~ s/\s+//g;
$icVolume =~ s/\s+//g;
$icRevenue =~ s/\s+//g;
$icBonus =~ s/\s+//g;
$ipVolume =~ s/\s+//g;
$ipRevenue =~ s/\s+//g;
$ipBonus =~ s/\s+//g;
if ($icVolume =~ /\-$/) {
my $minus = chop $icVolume;
$icVolume = $minus . $icVolume;
}
if ($icRevenue =~ /\-$/) {
my $minus = chop $icRevenue;
$icRevenue = $minus . $icRevenue;
}
if ($icBonus =~ /\-$/) {
my $minus = chop $icBonus;
$icBonus = $minus . $icBonus;
}
if ($ipVolume =~ /\-$/) {
my $minus = chop $ipVolume;
$ipVolume = $minus . $ipVolume;
}
if ($ipRevenue =~ /\-$/) {
my $minus = chop $ipRevenue;
$ipRevenue = $minus . $ipRevenue;
}
if ($ipBonus =~ /\-$/) {
my $minus = chop $ipBonus;
$ipBonus = $minus . $ipBonus;
}
my $qiPeriod = $dbh->quote( $iPeriod );
my $qicVolume = $dbh->quote( $icVolume );
my $qicRevenue = $dbh->quote( $icRevenue );
my $qicBonus = $dbh->quote( $icBonus );
my $qipVolume = $dbh->quote( $ipVolume );
my $qipRevenue = $dbh->quote( $ipRevenue );
my $qipBonus = $dbh->quote( $ipBonus );
$sth = $dbh->prepare( "INSERT INTO IS_Volume VALUES (
$qiPeriod, $qicVolume, $qicRevenue, $qicBonus, $qipVolume, $qipRevenue,
$qipBonus )" )
or die "Can not prep IS_vol data for import:\n
$DBI::errstr\n";
$sth->execute
or die "Can not execute IS_vol data import:\n
$DBI::errstr\n";
print "IS: $.\n"
}
}
close IS_VOLUME;
#OPENING SALES_VOLUME
unless (open SALES_VOLUME, "Sales_vol.txt") {
die "Can not open Sales_vol.txt";
}
while (<SALES_VOLUME>) {
if ($. gt 1) {
chomp;
($iPeriod, undef, $sTSM, undef, undef, undef, undef, undef,
$icVolume, $icRevenue, $icPGCP, $ipVolume, $ipRevenue, $ipPGCP) = split
/\^/, $_;
if ( length($sTSM) == 6 ) {
$sTSMk = $sTSM;
} else {
$sTSMk = "NODATA";
}
if ($icVolume =~ /\-$/) {
my $minus = chop $icVolume;
$icVolume = $minus . $icVolume;
}
if ($icRevenue =~ /\-$/) {
my $minus = chop $icRevenue;
$icRevenue = $minus . $icRevenue;
}
if ($icPGCP =~ /\-$/) {
my $minus = chop $icPGCP;
$icPGCP = $minus . $icPGCP;
}
if ($ipVolume =~ /\-$/) {
my $minus = chop $ipVolume;
$ipVolume = $minus . $ipVolume;
}
if ($ipRevenue =~ /\-$/) {
my $minus = chop $ipRevenue;
$ipRevenue = $minus . $ipRevenue;
}
if ($ipPGCP =~ /\-$/) {
my $minus = chop $ipPGCP;
$ipPGCP = $minus . $ipPGCP;
}
my $qiPeriod = $dbh->quote( $iPeriod );
my $qsTSM = $dbh->quote( $sTSMk );
my $qicVolume = $dbh->quote( $icVolume );
my $qicRevenue = $dbh->quote( $icRevenue );
my $qicPGCP = $dbh->quote( $icPGCP );
my $qipVolume = $dbh->quote( $ipVolume );
my $qipRevenue = $dbh->quote( $ipRevenue );
my $qipPGCP = $dbh->quote( $ipPGCP );
$sth = $dbh->prepare( "INSERT INTO Sales_Volume VALUES (
$qiPeriod, $qsTSM, $qicVolume, $qicRevenue, $qicPGCP, $qipVolume,
$qipRevenue, $qipPGCP )" )
or die "Can not prep Sales_vol data for import:\n
$DBI::errstr\n";
$sth->execute
or die "Can not execute Sales_vol data import:\n
$DBI::errstr\n";
print "Sales: $.\n"
}
}
close SALES_VOLUME;
#Close Database
$dbh->disconnect
or die "Disconnection Fialed: $DBI::errstr\n";
