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 = <<HERE;
DECLARE
bool BOOLEAN : = FALSE;
BEGIN
IF 'TRUE' = :3 THEN bool = TRUE; END IF;
$schema.PK_DATABASE_MAINT_MART.DROP_INDEXES( :1, :2, bool );
END;
HERE
> 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 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, <IN> 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.
