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);
> > }
