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

2011-02-14 Thread John Scoles 


  
  
On 11/02/2011 12:33 PM, tran...@commerceinsurance.com 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 
tran...@commerceinsurance.com
  
  
  Systems Analyst | IT
Application Services
| Desk: 508.949.4493 | Cell: 774.633.9123 | Problems
are the price of
progress."-- Charles F. Kettering

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

2011-02-14 Thread Charles Jardine 
On 11/02/11 17:33, tran...@commerceinsurance.com wrote:
 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);

You have opened the file named $sql, read it line-by line,
printing each line as yo go.

$record is now undefined. Passing it to prepare won't work.

  
   # prepare the stored procedure
   if ($sth = $DBH-prepare( $record )){
 print $LOG prepared: $HoH{$filename}{'full_filename'} \n; 


-- 
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679