Re: Stored Procedure (arguments)
On 2001-03-12 21:38:45 -0700, Peter Loo wrote: > 1) $DICEpackage is a store procedure we use internally. So your package is actually called $DICEpackage? There is no perl variable of this name which contains the name of the package? Then this is the problem. Perl replaced $DICEpackage with the value of the variable (an empty string). Use "-w" and "use strict" to catch such errors. at compile time. Use either single quotes for the statement or escape the $ sign with a backslash. hp -- _ | Peter J. Holzer | Any machine that doesn't have perl on it |_|_) | Sysadmin WSR / LUGA | is a sad machine indeed. | | | [EMAIL PROTECTED]| -- from the Bugzilla README __/ | http://www.hjp.at/ | PGP signature
Re: Stored Procedure (arguments)
Comments below.
--
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: "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);
> > }
-help
unsubscribe
Re: Stored Procedure (arguments)
Comments below.
--
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: "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);
> > }
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);
> > > }
>
>
>
Re: Stored Procedure (arguments)
Checking the DBD::Oracle version: > perl -MDBD::Oracle -e 'print "$DBD::Oracle::VERSION\n"' 1.06 Tom -- Thomas A. Lowery[EMAIL PROTECTED] http://tlowery.hypermart.net _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
RE: Stored Procedure (arguments)
Michael,
Thanks for all your advise. I will give them a try now.
Peter
-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 5:23 AM
To: Peter Loo; Loo, Peter # PHX; [EMAIL PROTECTED]
Subject: Re: Stored Procedure (arguments)
Comments below.
--
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: "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);
> > }
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
RE: Stored Procedure (arguments)
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
RE: InterScan NT Alert
OK. Maybe one of you guys who know security stuff better than I do can explain this email. Who is [EMAIL PROTECTED]? Does this mean that the email by Mitch Clarvit (which had an attachment) had a virus? Also, didn't the old list server used to strip attachments? > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 12, 2001 2:25 PM > To: [EMAIL PROTECTED] > Subject: InterScan NT Alert > > > Receiver, InterScan has detected virus(es) in the e-mail attachment. > > Date: Tue, 13 Mar 2001 09:24:48 +1100 (AUS Eastern Daylight Time) > Method: Mail > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > File: 'Community' VALUE=$community>"; print > ""; print > ""; print " Try > again?"; print "";print "";print > " ACTION='http://10.1.9.20/courses/coursedata.html'>";print " TYPE='SUBMIT' VALUE='OK'>";print " Choose another > option?";print ""; > Action: cleaned > Virus:Email_Flaw_MIME_Tag_Overflow >
Re: Stored Procedure (arguments)
Use Getopt::Long, it has a lot more capabilities and is 'use strict' clean.
Here's an example of my boilerplate. Note that I always include a '-h'
option to refresh my memory of the command's syntax. Run 'perldoc
Getopt::Long' for details.
# 010313 Mac First version...
$VERSION = '1.';
use Getopt::Long;
use strict;
use vars qw( $VERSION );
# Set default initial values
my $sInst= $ENV{'TWO_TASK'} || $ENV{'ORACLE_SID'} || 'def_instance';
my $sOwner = 'owner';
my $sUidPwd = 'uid/pwd';
# Syntax description
sub usage {
my ( $sOpt, $sVal, @sMsg ) = @_;
my $sHelpText = < \$sInst,
'own|o=s'=> \$sOwner,
'user|u=s' => \$sUidPwd,
'help|h' => \&usage ) or usage( 'die', 1 );
--
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: "Loo, Peter # PHX" <[EMAIL PROTECTED]>
To: "'Michael A. Chase'" <[EMAIL PROTECTED]>; "Peter Loo"
<[EMAIL PROTECTED]>; "Loo, Peter # PHX" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, March 13, 2001 9:17 AM
Subject: RE: Stored Procedure (arguments)
> 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');
# Here's how I'd do it
my ( $s, $l, $c, $t, $d, $m );
Getopt::Long::config( qw( no_ignore_case no_auto_abbrev require_order ) );
GetOptions(
'sthing!' => \$s, 's!' => \$s, # Use either -sthing or -s
'lthing|l=s'=> \$l, # Use either -lthing string or -l string
'cthing!' => \$c, 'c!' => \$c, # Can use 'cthing|c!' in recent
Getopt::Long
'tthing!' => \$t, 't!' => \$t,
'dthing!' => \$d, 'd!' => \$d,
'mthing!' => \$m, 'm!' => \$m,
'help|h' => \&usage ) or usage( 'die', 1 );
Re: Stored Procedure (arguments)
Comments below.
--
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: "Loo, Peter # PHX" <[EMAIL PROTECTED]>
To: "'Loo, Peter # PHX'" <[EMAIL PROTECTED]>; "'Michael A. Chase'"
<[EMAIL PROTECTED]>; "Peter Loo" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, March 13, 2001 9:41 AM
Subject: RE: Stored Procedure (arguments)
> 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.
>
. . .
> DB<5> p $statement
> BEGIN dsssma_load.PK_DATABASE_MAINT_MART.DROP_INDEXES(:1,:2,:3); END;
You don't need to single step through the DBI code to find the statement
string. Once you return to your code 'x $dbh->{Statement}' will print it
for you.
. . .
> 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, chunk 37.
You seem to have the right number of arguments. What types are the
arguments supposed to be?
I suspect the third argument is a BOOLEAN. The string 'FALSE' is not a
boolean value. If this package is being written for your Perl code, change
the variable to NUMBER and treat any non-NULL/non-zero as TRUE. Otherwise,
add a declare section and an IF statement to your anonymous block so you can
convert your string to a boolean value.
Re: InterScan NT Alert
My suspicion is that it's a security check for one of the list's subscribers. The post contained fragments of HTML which was mistaken for an attack on the mail reader. Take two aspirin (or don't) and it should go away. Your question and my reply will probably trigger the same complaint. The current server is spotty about stripping attachments. Some text ones get through, but not always. -- 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: "Tim Harsch" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 13, 2001 10:29 AM Subject: RE: InterScan NT Alert > OK. Maybe one of you guys who know security stuff better than I do can > explain this email. Who is [EMAIL PROTECTED]? > > Does this mean that the email by Mitch Clarvit (which had an attachment) had > a virus? > > Also, didn't the old list server used to strip attachments? > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Monday, March 12, 2001 2:25 PM > > To: [EMAIL PROTECTED] > > Subject: InterScan NT Alert > > > > > > Receiver, InterScan has detected virus(es) in the e-mail attachment. > > > > Date: Tue, 13 Mar 2001 09:24:48 +1100 (AUS Eastern Daylight Time) > > Method: Mail > > From: <[EMAIL PROTECTED]> > > To:<[EMAIL PROTECTED]> > > File: 'Community' VALUE=$community>"; print > > ""; print > > ""; print " Try > > again?"; print ""; print "";print > > " > ACTION='http://10.1.9.20/courses/coursedata.html'>";print " > TYPE='SUBMIT' VALUE='OK'>";print " Choose another > > option?";print ""; > > Action: cleaned > > Virus: Email_Flaw_MIME_Tag_Overflow
Re: Limiting the number of records selected.
> "Bill" == Bill OConnor <[EMAIL PROTECTED]> writes: Bill> I want to use a subset of the selected rows on a webpage. For Bill> instance if the query returns 100 rows I want show just 10 of Bill> them on the page, 11-20 on the next etc. > "Tony" == Tony Foiani <[EMAIL PROTECTED]> writes: Tony> Look at the discussion of the ROWNUM pseudo-column, in the Tony> Oracle SQL reference. Someone sent me some private e-mail on this topic, and I thought the list would be interested in seeing my response. > ROWNUM is useless if you have an ORDER BY clause. Incorrect. Just use the "ORDER BY" on a sub-SELECT, then grab the range of rows you want in the outer SELECTs. This is exactly what the documentation [1] tells you to do. So, a top-10 query would be: | SELECT * | FROM ( SELECT * FROM my_table ORDER BY my_col ) | WHERE ROWNUM <= 10; While grabbing the next 10 is a bit harder; I would probably just grab all of them out to the end, and only show the last 10 (possibly adding ROWNUM as a SELECTed column, so I could trivially match on it). If you know all the columns, you could do it all in SQL again: | SELECT col1, col2, col3 | FROM ( SELECT ROWNUM AS row_num, col1, col2, col3 |FROM ( SELECT col1, col2, col3 FROM my_table ORDER BY col1 ) |WHERE ROWNUM <= 20 ) | WHERE row_num >= 11; > I think Mr. Howard's sollution [SELECT TOP n] will work. In Oracle? I doubt it. TOP isn't even a reserved word! t. [1] Oracle 8i SQL Reference, Release 2 (8.1.6), A76989-01; Chapter 2 Basic Elements of Oracle SQL, Section Pseudocolumns
Fw: Error during make of DBI
Thanks Glenn,Jonathan for the help, recompiling perl using the gcc compiler did the trick. Have a good to all, Joel. - Original Message - From: "Glenn Butcher" <[EMAIL PROTECTED]> To: "Joel Laforest" <[EMAIL PROTECTED]> Sent: Sunday, March 04, 2001 10:11 PM Subject: Re: Error during make of DBI > On Fri, Mar 02, 2001 at 01:54:19PM -0500, Joel Laforest wrote: > > cp lib/DBD/ADO.pm blib/lib/DBD/ADO.pm > > /usr/bin/perl -p -e "s/~DRIVER~/Perl/g" < blib/arch/auto/DBI/Driver.xst > Perl.xsi > > /usr/bin/perl -I/usr/perl5/5.00503/sun4-solaris -I/usr/perl5/5.00503 /usr/perl5/5.00503/ExtUtils/xsubpp -typemap /usr/perl5/5.00503/ExtUtils/typemap Perl.xs >xstmp.c && mv xstmp.c Perl.c > > cc -c -xO3 -xdepend-DVERSION=\"1.13\" -DXS_VERSION=\"1.13\" -KPIC -I/u sr/perl5/5.00503/sun4-solaris/CORE -DDBI_NO_THREADS Perl.c > > cc: unrecognized option `-KPIC' > > cc: language depend not recognized > > cc: Perl.c: linker input file unused since linking not done > > /usr/bin/perl -I/usr/perl5/5.00503/sun4-solaris -I/usr/perl5/5.00503 /usr/perl5/5.00503/ExtUtils/xsubpp -typemap /usr/perl5/5.00503/ExtUtils/typemap DBI.xs >xstmp.c && mv xstmp.c DBI.c > > cc -c -xO3 -xdepend-DVERSION=\"1.13\" -DXS_VERSION=\"1.13\" -KPIC -I/u sr/perl5/5.00503/sun4-solaris/CORE -DDBI_NO_THREADS DBI.c > > > > Does anybody have any clue as to what is happening here, I have never received this error before, > > If you don't want to use Suns cc you an use gcc provided you > modify the makefile to remove the KPIC and change -xO3 -xdepend to > -O3 -depend > > -- > Glenn Butcher Mail:[EMAIL PROTECTED] Ph: +61 8 9489 7973 > UNIX and Network Administrator, Institute for Child Health Research > Horse, you are truly a creature without equal, for you fly without wings > and conquer without sword. - The Koran > >
Date format Problem solved
Jim, thanks for your response, I am using the "format" command as you suggested right in the statment. and it's working just fine. I did not have to create any queries. Thanks, for your help, I'm really glad I have access to this mailing list, it has helped me in a couple of occasions already. Mas
RE: Stored Procedure (arguments)
You seem to have the right number of arguments. What types are the
arguments supposed to be?
ANSWER: Argument1 = table name
Argument2 = "ALL"
Argument3 = BOOLEAN "FALSE" (default)
It works fine in Korn shell, but for whatever reason it give me this error:
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, chunk 3.
Because the default is FALSE, I have left it out and it appears to be
working now. It is awfully strange.
I suspect the third argument is a BOOLEAN. The string 'FALSE' is not a
boolean value. If this package is being written for your Perl code, change
the variable to NUMBER and treat any non-NULL/non-zero as TRUE. Otherwise,
add a declare section and an IF statement to your anonymous block so you can
convert your string to a boolean value.
ANSWER: I am going to try passing it a 0 or a 1 and see how it works. I am
afraid this stored procedure was written long ago and I don't have
permission to make modifications to it. It is compiled stored procedure and
we don't have the source for it. I was just asked to write a wrapper
program to call this stored procedure.
-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 12:04 PM
To: Loo, Peter # PHX; Peter Loo; [EMAIL PROTECTED]
Subject: Re: Stored Procedure (arguments)
Comments below.
--
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: "Loo, Peter # PHX" <[EMAIL PROTECTED]>
To: "'Loo, Peter # PHX'" <[EMAIL PROTECTED]>; "'Michael A. Chase'"
<[EMAIL PROTECTED]>; "Peter Loo" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, March 13, 2001 9:41 AM
Subject: RE: Stored Procedure (arguments)
> 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.
>
. . .
> DB<5> p $statement
> BEGIN dsssma_load.PK_DATABASE_MAINT_MART.DROP_INDEXES(:1,:2,:3); END;
You don't need to single step through the DBI code to find the statement
string. Once you return to your code 'x $dbh->{Statement}' will print it
for you.
. . .
> 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, chunk 37.
You seem to have the right number of arguments. What types are the
arguments supposed to be?
I suspect the third argument is a BOOLEAN. The string 'FALSE' is not a
boolean value. If this package is being written for your Perl code, change
the variable to NUMBER and treat any non-NULL/non-zero as TRUE. Otherwise,
add a declare section and an IF statement to your anonymous block so you can
convert your string to a boolean value.
Unix export
Does anyone know how I can export a variable as I would in Unix. Peter Loo
Re: Stored Procedure (arguments)
Comments below.
--
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: "Loo, Peter # PHX" <[EMAIL PROTECTED]>
To: "'Michael A. Chase'" <[EMAIL PROTECTED]>; "Loo, Peter # PHX"
<[EMAIL PROTECTED]>; "Peter Loo" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, March 13, 2001 12:21 PM
Subject: RE: Stored Procedure (arguments)
> You seem to have the right number of arguments. What types are the
> arguments supposed to be?
>
> ANSWER: Argument1 = table name
> Argument2 = "ALL"
> Argument3 = BOOLEAN "FALSE" (default)
>
> It works fine in Korn shell, but for whatever reason it give me this
error:
It worked from Korn shell because you pasted the literal value FALSE into
the statement and SQL*Plus made some adjustments to make it work.
Change your anonymous block to something like this:
$dropSQL = < 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, chunk 3.
>
> Because the default is FALSE, I have left it out and it appears to be
> working now. It is awfully strange.
>
> I suspect the third argument is a BOOLEAN. The string 'FALSE' is not a
> boolean value. If this package is being written for your Perl code,
change
> the variable to NUMBER and treat any non-NULL/non-zero as TRUE.
Otherwise,
> add a declare section and an IF statement to your anonymous block so you
can
> convert your string to a boolean value.
>
> ANSWER: I am going to try passing it a 0 or a 1 and see how it works. I
am
> afraid this stored procedure was written long ago and I don't have
> permission to make modifications to it. It is compiled stored procedure
and
> we don't have the source for it. I was just asked to write a wrapper
> program to call this stored procedure.
0 and 1 are number values, not BOOLEAN.
> -Original Message-
> From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, March 13, 2001 12:04 PM
> To: Loo, Peter # PHX; Peter Loo; [EMAIL PROTECTED]
> Subject: Re: Stored Procedure (arguments)
>
> - Original Message -
> From: "Loo, Peter # PHX" <[EMAIL PROTECTED]>
> To: "'Loo, Peter # PHX'" <[EMAIL PROTECTED]>; "'Michael A. Chase'"
> <[EMAIL PROTECTED]>; "Peter Loo" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, March 13, 2001 9:41 AM
> Subject: RE: Stored Procedure (arguments)
>
> > 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.
> >
> . . .
> > DB<5> p $statement
> > BEGIN dsssma_load.PK_DATABASE_MAINT_MART.DROP_INDEXES(:1,:2,:3); END;
>
> You don't need to single step through the DBI code to find the statement
> string. Once you return to your code 'x $dbh->{Statement}' will print it
> for you.
>
> . . .
> > 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, chunk 37.
>
> You seem to have the right number of arguments. What types are the
> arguments supposed to be?
>
> I suspect the third argument is a BOOLEAN. The string 'FALSE' is not a
> boolean value. If this package is being written for your Perl code,
change
> the variable to NUMBER and treat any non-NULL/non-zero as TRUE.
Otherwise,
> add a declare section and an IF statement to your anonymous block so you
can
> convert your string to a boolean value.
Re: Unix export
Any value you add to %ENV is added to the environment of the current process and any called subprocesses. They won't return to the program that called the Perl script though. -- 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: "Loo, Peter # PHX" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 13, 2001 12:28 PM Subject: Unix export > Does anyone know how I can export a variable as I would in Unix.
Re: Unix export
On Tue, 13 Mar 2001, Loo, Peter # PHX wrote: > Does anyone know how I can export a variable as I would in Unix. Export it where and on what OS? -- Brett http://www.chapelperilous.net/~bmccoy/ Your sister swims out to meet troop ships.
Retrieving values from Oracle Stored Procedure (CLOB problems?)
Hey folks. I'm at the depilatory stage on this one. I have two stored
procedures, the first of which is (sort of) getting executed correctly,
and the second of which is dying with a so-far-mystifying error message.
DBD::Oracle 1.0.6
Oracle Server version 8.1.6.3.0
DESCRIBE on the two procedures gets me:
PROCEDURE new_item
Argument Name TypeIn/Out Default?
-- --- --
TN VARCHAR2(128) IN
TTLVARCHAR2(255) IN
KW VARCHAR2(255) IN
NEWID NUMBER(38) OUT
Which I call (mostly successfully) with
my $sth = $db->prepare(q{
BEGIN new_item(:1, :2, :3, :4); END;});
$sth->bind_param(1,$doc_type, {TYPE=>'VARCHAR2(128)'});
$sth->bind_param(2,$question, {TYPE=>'VARCHAR2(255)'});
$sth->bind_param(3,$keywords, {TYPE=>'VARCHAR2(255)'});
$sth->bind_param_inout(4, \$insertid, 100);
$sth->execute();
The failure lies in the fact that $insertid is still null when we're all
done.
The second procedure is eerily similar, but for the fact that one of the
fields is a CLOB:
PROCEDURE new_version
Argument Name TypeIn/Out Default?
-- --- --
DOCID NUMBER(38) IN
NEWEDITOR NUMBER(38) IN
NEWDATACLOBIN
NEWID NUMBER(38) OUT
And I call this one (unsuccessfully) with :
$sth = $db->prepare(q{
BEGIN new_version(:1, :2, :3, :4); END;});
$sth->bind_param(1,$insertid, {TYPE=>'NUMBER(38)'});
$sth->bind_param(2,$owner_uid, {TYPE=>'NUMBER(38)'});
$sth->bind_param(3,$data, {TYPE=>'CLOB'});
$sth->bind_param_inout(4, \$version_id, 100);
$sth->execute();
The error message is the dreaded, apparently false,
DBD::Oracle::st execute failed: ORA-06550: line 2, column 10: PLS-00306:
wrong number or types of arguments in call to 'NEW_VERSION'
ORA-06550: line 2, column 10: PL/SQL: Statement ignored (DBD ERROR:
OCIStmtExecute) at /usr/local/www/cgi-bin/docmgr line 223.
Running under trace level 2, peeked through the log, and got little more
than that. If needed, of course I can post it.
Any ideas what might be going wrong?
thanks for any help!
AC
RE: Unix export
So if I were to add an environment to ENV like so:
$ENV{'ENVR'} = "devl";
will I be able to reference the variable $ENVR directly or I must call it as
$ENV{'ENVR'} to get the value?
-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 1:39 PM
To: Loo, Peter # PHX; [EMAIL PROTECTED]
Subject: Re: Unix export
Any value you add to %ENV is added to the environment of the current process
and any called subprocesses. They won't return to the program that called
the Perl script though.
--
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: "Loo, Peter # PHX" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 13, 2001 12:28 PM
Subject: Unix export
> Does anyone know how I can export a variable as I would in Unix.
RE: Unix export
Sun OS. Example: export SOMETHING="A_VALUE" -Original Message- From: Brett W. McCoy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 13, 2001 2:01 PM To: Loo, Peter # PHX Cc: [EMAIL PROTECTED] Subject: Re: Unix export On Tue, 13 Mar 2001, Loo, Peter # PHX wrote: > Does anyone know how I can export a variable as I would in Unix. Export it where and on what OS? -- Brett http://www.chapelperilous.net/~bmccoy/ Your sister swims out to meet troop ships.
RE: Unix export
On Tue, 13 Mar 2001, Loo, Peter # PHX wrote: > Example: > > export SOMETHING="A_VALUE" Right, I understand what exporting variables are. You said 'like in Unix'. Where are you trying to export variables 'like in Unix'? Sun OS IS Unix, but if you are using ksh and not bash, you can't do it in one line, you have to create the variable and then export it. -- Brett http://www.chapelperilous.net/~bmccoy/ In the force if Yoda's so strong, construct a sentence with words in the proper order then why can't he?
RE: Unix export
Sorry everyone for this interruption. Aaron asked me to try another forum: All of this has nothing to do with DBI. You should really ask this in another forum. try perlmonks.com or any other number of lists /irc/website etc... As far as your question you should go ahead and write the 3 line script and test it to see what the results are on your platform :) cheers, Aaron -Original Message- From: Brett W. McCoy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 13, 2001 2:32 PM To: Loo, Peter # PHX Cc: [EMAIL PROTECTED] Subject: RE: Unix export On Tue, 13 Mar 2001, Loo, Peter # PHX wrote: > Example: > > export SOMETHING="A_VALUE" Right, I understand what exporting variables are. You said 'like in Unix'. Where are you trying to export variables 'like in Unix'? Sun OS IS Unix, but if you are using ksh and not bash, you can't do it in one line, you have to create the variable and then export it. -- Brett http://www.chapelperilous.net/~bmccoy/ In the force if Yoda's so strong, construct a sentence with words in the proper order then why can't he?
Re: InterScan NT Alert
Guys, This was picked up by our Trend Micro virus scanner - not even running in paranoid mode! It detects things like mime tag overflows etc. - first one received on this list though. Probably not a virus in the attachment - just bad mime tags. Regards Chris Tim Harsch wrote: > OK. Maybe one of you guys who know security stuff better than I do can > explain this email. Who is [EMAIL PROTECTED]? > > Does this mean that the email by Mitch Clarvit (which had an attachment) had > a virus? > > Also, didn't the old list server used to strip attachments? > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Monday, March 12, 2001 2:25 PM > > To: [EMAIL PROTECTED] > > Subject: InterScan NT Alert > > > > > > Receiver, InterScan has detected virus(es) in the e-mail attachment. > > > > Date: Tue, 13 Mar 2001 09:24:48 +1100 (AUS Eastern Daylight Time) > > Method: Mail > > From: <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > File: 'Community' VALUE=$community>"; print > > ""; print > > ""; print " Try > > again?"; print "";print "";print > > " > ACTION='http://10.1.9.20/courses/coursedata.html'>";print " > TYPE='SUBMIT' VALUE='OK'>";print " Choose another > > option?";print ""; > > Action: cleaned > > Virus:Email_Flaw_MIME_Tag_Overflow > > -- Chris Fry Quillsoft Pty Ltd Specialists in Secure Internet Services and E-Commerce Solutions 10 Gray Street Kogarah NSW 2217 Australia Phone: +61 2 9553 1691 Fax: +61 2 9553 1692 Mobile: 0419 414 323 eMail: [EMAIL PROTECTED] http://www.quillsoft.com.au You can download our Public CA Certificate from:- https://ca.secureanywhere.com/htdocs/cacert.crt ** This information contains confidential information intended only for the use of the authorised recipient. If you are not an authorised recipient of this e-mail, please contact Quillsoft Pty Ltd by return e-mail. In this case, you should not read, print, re-transmit, store or act in reliance on this e-mail or any attachments, and should destroy all copies of them. This e-mail and any attachments may also contain copyright material belonging to Quillsoft Pty Ltd. The views expressed in this e-mail or attachments are the views of the author and not the views of Quillsoft Pty Ltd. You should only deal with the material contained in this e-mail if you are authorised to do so. This notice should not be removed.
Return value
Hi everybody,
I just started using PostreSQL few days ago and i have a problem. What would
PostgreSQL return for empty table or when the SELECT doesn't find any item
specified in the condition.
$sth->prepare("SELECT * from userInfo where username=$usernameField");
$sth->execute;
@vector=$sth->fetchrow()
What would be stored in the vector or How would I find if the select didn't
find anything at all.???
Sincerely,
Dawa
~o---0~
| Dawa Lama Sherpa0-|
| P.O.Box : 12-51---|
| Grinnell, IA 50112|
| E-mail : [EMAIL PROTECTED]|
| http://www.grinnell.edu/individuals/sherpa/---|
~0---0~
Re: Return value
On Tue, 13 Mar 2001, Dawa Lama wrote:
> I just started using PostreSQL few days ago and i have a problem. What would
> PostgreSQL return for empty table or when the SELECT doesn't find any item
> specified in the condition.
>
> $sth->prepare("SELECT * from userInfo where username=$usernameField");
> $sth->execute;
> @vector=$sth->fetchrow()
>
> What would be stored in the vector or How would I find if the select didn't
> find anything at all.???
PostgreSQL doesn't return anything if it doesn't find anything. I usually
do a SELECT COUNT(*) query before doing the main query to see how many
rows it will return, if I need to know. Otherwise, your array will be
empty, so you will need to test to see if anything is in it.
You might also want to wrap your code up in an eval statement so you can
throw an exception if necessary.
-- Brett
http://www.chapelperilous.net/~bmccoy/
The difference between this place and yogurt is that yogurt has a live culture.
Re: Limiting the number of records selected.
Continuing the ongoing saga... I wrote that the "top-n" queries in
Oracle can be handled by using a nested SELECT with an ORDER BY, then
using ROWNUM in the external WHERE clause. But...
> I just tried it on our instance and it doesn't work. We are on
> Oracle7 Server Release 7.3.4.5.0. Is this something that only works
> in Oracle 8+ ? It doesn't seem to like the ORDER BY clause in the
> FROM clause query. If I take ORDER BY dsc out the query runs but
> not with the desired results. What gives?
Well, I saw this feature (for doing "top-n" queries) listed as one of
the reasons to migrate to Oracle8i:
http://www.elementkjournals.com/dbm/0003/dbm0031.htm
So I wouldn't be surprised if this were indeed a version 8 feature.
Oh, and here's the 8.1.5 documentation on this feature:
http://oradoc.photo.net/ora81/DOC/server.815/a68003/rollup_c.htm#33316
(This is obviously where the docs I referenced earlier are pointing,
but it seems to be removed from the 8.1.6 documentation set. Uhg.)
> | SQL>r
> | 1 SELECT *
> | 2 FROM ( SELECT * FROM codes ORDER BY dsc )
> | 3*WHERE ROWNUM <= 10
> |FROM ( SELECT * FROM codes ORDER BY dsc )
> | *
> | ERROR at line 2:
> | ORA-00907: missing right parenthesis
>
> If I take ORDER BY dsc out the query runs but not with the desired
> results:
>
> | SQL>r
> | 1 SELECT *
> | 2 FROM ( SELECT * FROM codes )
> | 3*WHERE ROWNUM <= 10
>
> What gives?
As I said above, it probably is an Oracle 8 feature.
You can always "brute force" this programmatically; just grab them
all, in order, then display only the ones you're interested in. You
can always finish the handle after you've found those.
If the records are very wide, you might save some time by returning
only an identifying field in the desired sort order; after you grab
the id fields for the relevant range of records, you can then fetch
exactly those using an IN clause or similar. (But I'd guess that the
extra execute would be more costly, unless you have lots of very wide
fields in the SELECTed columns!)
Hm. Here's an arcane way of doing it:
http://www.caribdata.co.uk/additional/new_user.html#top_n
And a variant on the same:
http://www.cstone.net/~phh5j/sqlfaq.html#TOP
(Both were the result for a google.com search for "oracle top-n
query".)
If you have a lot of records, however, I'd expect that asking Oracle
to just give you all of them, in sequence, would be faster. Both of
the above methods appear to be quadratic algorithms, while a simple
ordered query should run in only n-log-n time.
The XSQL servlet has "max-rows" and "skip-rows" values for this
reason:
http://otn.oracle.com/tech/xml/xsql_servlet/htdocs/relnotes.htm#ID2827
But I would guess it's doing exactly as I suggest above -- get the
whole thing, and only show the ones you care about (and quit early, if
possible).
In your case, I'd just do:
| my $sql = "SELECT * FROM codes ORDER BY dsc";
| my $sth = $dbh->prepare($sql);
| $sth->execute();
| my $n = 0;
| while (my $cur = $sth->fetch())
| {
| $n++;
| next if $n < $start_row_num;
| last if $n > $last_row_num;
| # do stuff with $cur here
| }
| $sth->finish();
Hope this helps,
t.
-help
unsubscribe
Re: Return value
> "Dawa" == Dawa Lama <[EMAIL PROTECTED]> writes:
Dawa> What would PostgreSQL return for empty table or when the SELECT
Dawa> doesn't find any item specified in the condition.
I'm unfamiliar with the "fetchrow" method.
My preferred method is to use "fetch", which will return an array
reference so long as there are rows to retrieve, or an undef (thus
false) value when it is done:
| my $sth = $dbh->prepare("...");
| $sth->execute();
| my $n_rows = 0;
| while (my $cur = $sth->fetch())
| {
|$n_rows++;
|# ...
| }
| $sth->finish();
If you actually want to load an array with all the values returned in
each row, you want the "fetchrow_array" method, which has this
important clue in its documentation:
> If there are no more rows or an error occurs then fetchrow_array
> returns an empty list. You should check $sth->err afterwards (or use
> the RaiseError attribute) to discover if the empty list returned was
> due to an error.
So, our main loop would be:
| while (my @row = $sth->fetchrow_array())
| {
|$n_rows++;
|# ...
| }
This will quit as soon as it has an empty list; if the query doesn't
generate any rows at all, it will return an empty list immediately.
If you want to grab all values from one column, or all values from all
rows, investigate the "selectcol_arrayref" and "selectall_arrayref"
methods in "perldoc DBI".
t.
Re: Unix export
"Brett W. McCoy" wrote: > > On Tue, 13 Mar 2001, Loo, Peter # PHX wrote: > > > Example: > > > > export SOMETHING="A_VALUE" > > Right, I understand what exporting variables are. You said 'like in > Unix'. Where are you trying to export variables 'like in Unix'? Sun OS > IS Unix, but if you are using ksh and not bash, you can't do it in one > line, you have to create the variable and then export it. Sorry, export FOO=BAR works in ksh. It's Bourne shell (sh) where that doesn't work. > > -- Brett > http://www.chapelperilous.net/~bmccoy/ > > In the force if Yoda's so strong, construct a sentence with words in > the proper order then why can't he? -- Matthew O. Persico http://www.acecape.com/dsl AceDSL:The best ADSL in Verizon area
RE: Return value
You might also want to use fetchrow_array instead of fetchrow since fetchrow
was replaces by it and is depricated.
Ilya Sterin
-Original Message-
From: Dawa Lama [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 6:05 PM
To: Brett W. McCoy; Loo, Peter # PHX
Cc: dbi-users
Subject: Return value
Hi everybody,
I just started using PostreSQL few days ago and i have a problem. What would
PostgreSQL return for empty table or when the SELECT doesn't find any item
specified in the condition.
$sth->prepare("SELECT * from userInfo where username=$usernameField");
$sth->execute;
@vector=$sth->fetchrow()
What would be stored in the vector or How would I find if the select didn't
find anything at all.???
Sincerely,
Dawa
~o---0~
| Dawa Lama Sherpa0-|
| P.O.Box : 12-51---|
| Grinnell, IA 50112|
| E-mail : [EMAIL PROTECTED]|
| http://www.grinnell.edu/individuals/sherpa/---|
~0---0~
Re: NULL vs. Empty String
Yeah, that's right. There's a place in my application where I can check
every argument passed and change them from empty strings to undef where
relevant, but I'd rather avoid that overhead if there's some sort of
PostgreSQL and/or DBI configuration that'll do it for me.
Thanks!
David
On Wed, 14 Mar 2001, Daniel Kirkwood wrote:
>
> Of course, depending on the application, this could mean hand-modifying
> every script... so my apologies if this was not what you were after ;)
>
>
> > Could it be as simple as:
>
> > $sth = $dbh->prepare("INSERT INTO table (row1, row2) VALUES (?, ?)");
> > if ( $webinput eq "" ) { $webinput = undef ; }
>
> > $sth->execute($webinput, $otherstuff);
Re: NULL vs. Empty String
Could it be as simple as:
$sth = $dbh->prepare("INSERT INTO table (row1, row2) VALUES (?, ?)");
if ( $webinput eq "" ) { $webinput = undef ; }
$sth->execute($webinput, $otherstuff);
I don't have Postgres here to test on, but inserting an undef should
translate to a NULL on most any DBD.
Regards,
Daniel.
- Original Message -
From: "David Wheeler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, March 14, 2001 2:26 PM
Subject: NULL vs. Empty String
Hi All,
Apologies for cross-posting.
I've ported a mod_perl application from Oracle to PosgreSQL, but have run
into a difference that's significant for web work. When I get a form field
submitted, if it's empty, mod_perl assumes it to be an empty string ('').
When I inserted empty strings into Oracle tables, Oracle assumed they were
NULL and inserted them as such. Postgres, OTOH, seems to insert it as an
empty string instead. While I realize that the Posgres interpretation is
perhaps the more precise of the two, it was a feature of Oracle that I
rather liked.
Is there a way to get Posgres to assume that a '' insert should be a NULL
insert? And if not, does anyone know of a way to get mod_perl to make that
assumption instead?
TIA,
David
Re: NULL vs. Empty String
Of course, depending on the application, this could mean hand-modifying
every script... so my apologies if this was not what you were after ;)
> Could it be as simple as:
> $sth = $dbh->prepare("INSERT INTO table (row1, row2) VALUES (?, ?)");
> if ( $webinput eq "" ) { $webinput = undef ; }
> $sth->execute($webinput, $otherstuff);
NULL vs. Empty String
Hi All,
Apologies for cross-posting.
I've ported a mod_perl application from Oracle to PosgreSQL, but have run
into a difference that's significant for web work. When I get a form field
submitted, if it's empty, mod_perl assumes it to be an empty string ('').
When I inserted empty strings into Oracle tables, Oracle assumed they were
NULL and inserted them as such. Postgres, OTOH, seems to insert it as an
empty string instead. While I realize that the Posgres interpretation is
perhaps the more precise of the two, it was a feature of Oracle that I
rather liked.
Is there a way to get Posgres to assume that a '' insert should be a NULL
insert? And if not, does anyone know of a way to get mod_perl to make that
assumption instead?
TIA,
David
