Here is the debug status:
devserver:/usr/local/apps/sma/devl/bin>runAgg_TAlgnRxTerrSpcltDrugPlan.pl
Loading DB routines from perl5db.pl version 1.0402
Emacs support available.
Enter h or `h h' for help.
main::(sma_run_aggs.pl:28): require "getopt.pl";
DB<1> b 121
DB<2> c
SCHEMA = dsssma_load
CACHE = rxCache_NO_CASH.conf
LOCAL = rxLocalize_TAlgnRxTerrSpcltDrugPlan.conf
TABLES = t_algn_rx_terr_spclt_drug_plan
LOAD = t_rx_load_cycle
MART = Y
main::(sma_run_aggs.pl:121): if ($opt_m) {
DB<2> s
main::(sma_run_aggs.pl:122): $DICEpackage =
"$SCHEMA.PK_DATABASE_MAINT_MART";
DB<2>
main::(sma_run_aggs.pl:128): $dweLoadCache =
"${COMMONBIN}/dwe_load_cache_2 -d $CACHEDIR -f $opt_c " .
main::(sma_run_aggs.pl:129): "-u $DBUSER -p $DBPASS -s
$DBCONN";
DB<2> p $DICEpackage
dsssma_load.PK_DATABASE_MAINT_MART
DB<3> b 172
DB<4> c
main::(sma_run_aggs.pl:172): print STDERR "\n\nDropping indexes
(${tableName}).\n";
DB<4> p $DICEpackage
dsssma_load.PK_DATABASE_MAINT_MART
DB<5> s
main::(sma_run_aggs.pl:173): $sth = $dbh->prepare("BEGIN
$DICEpackage.DROP_INDEXES(:1,:2,:3); END;");
DB<5> s
DBD::Oracle::db::prepare(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/D
BD/Oracle.pm:282):
282: my($dbh, $statement, @attribs)= @_;
DB<5>
DBD::Oracle::db::prepare(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/D
BD/Oracle.pm:286):
286: my $sth = DBI::_new_sth($dbh, {
287: 'Statement' => $statement,
DB<5> p $statement
BEGIN dsssma_load.PK_DATABASE_MAINT_MART.DROP_INDEXES(:1,:2,:3); END;
DB<6> s
DBI::_new_sth(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:738):
738: my ($dbh, $initial_attr, $imp_data) = @_;
DB<6> p $dbh
DB<7> s
DBI::_new_sth(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:739):
739: my $imp_class = $dbh->{ImplementorClass}
740: || Carp::croak("DBI _new_sth: $dbh has no
ImplementorClass");
DB<7> p $dbh
DBI::db=HASH(0x2c199c)
DB<8> p $initial_attr
HASH(0x3abab0)
DB<9> p $imp_data
DB<10>
DBI::_new_sth(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:741):
741: substr($imp_class,-4,4) = '::st';
DB<10>
DBI::_new_sth(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:742):
742: my $app_class = ref $dbh;
DB<10>
DBI::_new_sth(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:743):
743: substr($app_class,-4,4) = '::st';
DB<10>
DBI::_new_sth(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:744):
744: my $attr = {
745: 'ImplementorClass' => $imp_class,
746: %$initial_attr,
747: 'Type' => 'st',
748: 'Database' => $dbh,
DB<10>
DBI::_new_sth(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:750):
750: _new_handle($app_class, $dbh, $attr, $imp_data);
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:66
7):
667: my($class, $parent, $attr, $imp_data) = @_;
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:66
9):
669: Carp::croak('Usage: DBI::_new_handle'
670: .'($class_name, parent_handle, \%attr, $imp_data)'."\n"
671: .'got: ('.join(", ",$class, $parent, $attr,
$imp_data).")\n")
672: unless(@_ == 4 and (!$parent or ref $parent)
673: and ref $attr eq 'HASH');
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:67
5):
675: my $imp_class = $attr->{ImplementorClass} or
676: Carp::croak("_new_handle($class): 'ImplementorClass'
attribute not given");
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:67
8):
678: DBI->trace_msg(" New $class (for $imp_class, parent=$parent,
id=".($imp_data||'').")\n")
679: if $DBI::dbi_debug >= 3;
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:68
2):
682: my(%hash, $i, $h);
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:68
3):
683: $i = tie %hash, $class, $attr; # ref to inner hash (for
driver)
DB<10>
DBI::st::TIEHASH(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:69
3):
693: sub DBI::st::TIEHASH { bless $_[1] => $_[0] };
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:68
4):
684: $h = bless \%hash, $class; # ref to outer hash (for
application)
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:68
7):
687: DBI::_setup_handle($h, $imp_class, $parent, $imp_data);
DB<10>
DBI::_new_handle(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/DBI.pm:68
9):
689: return $h unless wantarray;
DB<10>
DBD::Oracle::db::prepare(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/D
BD/Oracle.pm:293):
293: DBD::Oracle::st::_prepare($sth, $statement, @attribs)
294: or return undef;
DB<10>
DBD::Oracle::db::prepare(/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/D
BD/Oracle.pm:296):
296: $sth;
DB<10>
main::(sma_run_aggs.pl:174): $sth->execute("$tableName", "ALL",
"FALSE");
DB<10>
DBD::Oracle::st execute failed: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'DROP_INDEXES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute) at sma_run_aggs.pl
line 174, <IN> chunk 37.
eval {...} called at sma_run_aggs.pl line 142
main::(sma_run_aggs.pl:239): if ($@) {
DB<10> p $sth
DBI::st=HASH(0x3aae84)
DB<11> q
runAgg_TAlgnRxTerrSpcltDrugPlan.pl[2]: /export/home/ip00622: cannot execute
-----Original Message-----
From: Loo, Peter # PHX [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 10:18 AM
To: 'Michael A. Chase'; Peter Loo; Loo, Peter # PHX; [EMAIL PROTECTED]
Subject: RE: Stored Procedure (arguments)
Hi Michael and All,
I am trying to use getopt.pl, however, it give me errors and terminate when
I use 'use strict;'.
aztec:/usr/local/apps/sma/devl/bin>runAgg_TAlgnRxTerrSpcltDrugPlan.pl
Loading DB routines from perl5db.pl version 1.0402
Emacs support available.
Enter h or `h h' for help.
Global symbol "$opt_s" requires explicit package name at sma_run_aggs.pl
line 53.
Global symbol "$opt_l" requires explicit package name at sma_run_aggs.pl
line 53.
Global symbol "$opt_c" requires explicit package name at sma_run_aggs.pl
line 73.
Global symbol "$opt_t" requires explicit package name at sma_run_aggs.pl
line 75.
Global symbol "$opt_d" requires explicit package name at sma_run_aggs.pl
line 76.
Global symbol "$opt_m" requires explicit package name at sma_run_aggs.pl
line 77.
Execution of sma_run_aggs.pl aborted due to compilation errors.
Debugged program terminated. Use q to quit or R to restart,
use O inhibit_exit to avoid stopping after program termination,
h q, h R or h O to get additional info.
This is how I call getopt.pl:
use strict;
use Cwd;
use DBI;
require "getopt.pl";
Getopt('sl:ctdm');
Thanks.
Peter
-----Original Message-----
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 8:02 AM
To: Peter Loo; Loo, Peter # PHX; [EMAIL PROTECTED]
Subject: Re: Stored Procedure (arguments)
I forgot to mention. You are not checking for errors after the prepare()
calls. Either set $dbh->{RaiseError} to 1 or check after all connect(),
prepare(), execute(), and fetch*() calls or you will get errors after the
real cause and have more trouble tracking down the real cause.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Michael A. Chase" <[EMAIL PROTECTED]>
To: "Peter Loo" <[EMAIL PROTECTED]>; "Loo, Peter # PHX"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, March 13, 2001 4:23 AM
Subject: Re: Stored Procedure (arguments)
> ----- Original Message -----
> From: "Peter Loo" <[EMAIL PROTECTED]>
> To: "Michael A. Chase" <[EMAIL PROTECTED]>; "Loo, Peter # PHX"
> <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Monday, March 12, 2001 8:38 PM
> Subject: RE: Stored Procedure (arguments)
>
>
> > 1) $DICEpackage is a store procedure we use internally.
>
> That's your error this time. Perl is trying to interpolate the variable
> $DICEpackage into the string. As mentioned by Peter J Holzer, -w in the
#!
> line should have caught this. 'use strict;' (without the quotes) is also
a
> good idea to prevent syntax irregularities from growing into program
errors.
>
> > 2) The reason that $dbh->prepare is in loop to dynamically handle
multiple
> > tables.
>
> The only reason the prepares should be inside the loop is if the text of
the
> SQL changes inside the loop. Since it does not for either statement, you
> are paying a severe penalty by re-preparing both statements inside the
loop.
> The execute() calls are the only DBI statements that need to be inside the
> loop.
>
> > 3) I have tried bind variables with commas and yet it didn't work.
>
> Run 'perldoc DBI' and read the sections on bind_param() and placeholders
to
> see examples of how to use placeholders/bind variables and a discussion of
> their limitiations. The commas are required as a matter of SQL syntax.
>
> There are also examples of procedure calls in DBI-Oracle-1.06/Oracle.ex/.
> Procedure calls are coverd by proc.pl, but all the examples are worth a
> look.
>
> > 4) {} is a style I had adopted to identify that a variable was
previously
> > declared.
>
> All variables should be previously declared in production code as required
> by '-w' and 'use strict' to avoid the problem that brought you here. Use
> both and let the Perl interpreter help you write good code.
>
> > 6) I will give DBI->trace a try after I have figured out how it works.
>
> It's described in the fine manual.
>
> > -----Original Message-----
> > From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, March 12, 2001 8:38 PM
> > To: Loo, Peter # PHX; [EMAIL PROTECTED]
> > Subject: Re: Stored Procedure (arguments)
> >
> >
> > 1. What is the value in $DICEpackage?
> > 2. You should call $dbh->prepare() outside the loop. Keep both prepared
> > handles in separate variables.
> > 3. The bind variables (:1, :2, :3) definitely need to be separated by
> commas
> > (,) in both SQL statements.
> > 4. You don't need either the quotes or braces in the first argument to
> > either $sth->execute(). Just use $tableName in that position.
> > 5. The braces are also not needed in ${tableName} in the print
statement.
> > 6. Try adding DBI->trace(2,"file_name"); before this section to see
what's
> > really happening.
> > ----- Original Message -----
> > From: "Loo, Peter # PHX" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Monday, March 12, 2001 3:15 PM
> > Subject: Stored Procedure (arguments)
> >
> > > Can someone please tell me what this is all about?
> > >
> > > DBD::Oracle::st execute failed: ORA-01036: illegal variable
name/number
> > (DBD
> > > ERROR: OCIBindByName) at sma_run_aggs.pl line 172.
> > >
> > > Here is my syntax:
> > >
> > > foreach $tableName (@tableList) {
> > > print STDERR "\n\nDropping indexes (${tableName}).\n";
> > > $sth = $dbh->prepare("BEGIN $DICEpackage.DROP_INDEXES(:1:2:3);
> > END;");
> > > $sth->execute("${tableName}", "ALL", "FALSE"); <=== Line 172
> > > sub_dbms_output_errors($SearchString, $dbh);
> > > print STDERR "Truncating table (${tableName}).\n";
> > > $sth = $dbh->prepare("BEGIN $DICEpackage.TRUNCATE_TABLE(:1:2);
> > END;");
> > > $sth->execute("${tableName}", "ALL");
> > > sub_dbms_output_errors($SearchString, $dbh);
> > > }
>
>
>
