Re: Question about passing a stored procedure file to DBD-Oracle

  On 11/02/2011 12:33 PM, [email protected] wrote:
Never tried that myself but I do not see any reason why it should not 
work.  As long as the $record is correctly formatted.

To start try this on the prepare
$DBH->prepare( "BEGIN".$record."END;" );

That might do the trick.

Without seeing the original SQL it will be hard to give you a 100% answer.

Cheers
John Scoles

> I'm sorry in advance if this is not the correct forum/mechanism to ask 
> this question - I have been googling this for a while, as well as 
> re-reading all of the CPAN man page for DBD-Oracle.
>
> I need to pass a physical file( or the contents of that file), that 
> contains code to create an Oracle stored procedure into DBD-Oracle. 
>  So like SQL PLus, where I'd say >/ @package.spb/, I have been trying 
> to do the same in every way imaginable with $DBH->prepare / do.   As 
> of right now, this is not working:
>
> # load file contents
> open (FILEDATA, "$sql");
> while ($record = <FILEDATA>) {
> print $record;
>       }
> close(FILEDATA);
>
> # prepare the stored procedure
> if ($sth = $DBH->prepare( $record )){
> print $LOG "prepared: $HoH{$filename}{'full_filename'} \n";
>
> # execute the stored procedure
> if ($sth->execute()){
> print $LOG "it made it: $HoH{$filename}{'full_filename'} \n";
> $HoH{$filename}{'build_state'} = 'COMPILED';
>         } else {
> # update the hash, since we will not be loading this one
> print $LOG "couldnt excute: $HoH{$filename}{'full_filename'} \n";
> $HoH{$filename}{'build_state'} = 'FAILED - COULD NOT EXECUTE';
>         }
>       } else {
> # update the hash, since we will not be loading this one
> print $LOG "couldnt prepare: $HoH{$filename}{'full_filename'} \n";
> $HoH{$filename}{'build_state'} = 'FAILED - COULD NOT PREPARE';
>       } # preparing sql
>
> When executed, I get: * DBD::Oracle::db prepare failed: ORA-24373: 
> invalid length specified for statement (DBD ERROR: OCIStmtPrepare) at 
> C:\Tronweb\TW_AUTOMATED_BUILDS\scripts\release_cvs_to_dev.pl line 261*.
>
> This file that I'm trying to prepare is small, and I've even set up my 
> Oracle session as:
>
> #Open an Oracle session
>     $DBH = DBI->connect( "dbi:Oracle:$sid", $usr, $pwd )
>        or die "Unable to  connect to $sid: $DBI::errstr";
> $DBH->{RaiseError}  = 1; # Shows the errors if CREATE PROCEDURE fails
>     $DBH->{LongReadLen} = 5242880;
> $DBH->{LongTruncOk} = 0;
>
> Do you have any suggestions for this dilemma?
>
> THANK YOU for any advice / direction that you can provide!
> 	
> 	*T.j. Randall* _
> [email protected]_ <mailto:[email protected]>
> Systems Analyst | IT Application Services | Desk: 508.949.4493 | Cell: 
> 774.633.9123 | "Problems are the price of progress."-- Charles F. 
> Kettering
>
> 	
> 	
>
>