regd Pl/Sql blocks in DBI

[Prabaharan Dorairajan]

Hi,

 I am unable to pass values from perl to Oracle using DBI.


 Could any one help me?

 Here is the program:
#!/depot/perl/rel/bin/perl
use DBI;
my (dbh,sth);
$dbh  = DBI->connect("orcl", "scott", "tiger","oracle");

my $AddrlineSql     = (q{
                         DECLARE
                           v_address1             varchar2(100);
                           v_address2             varchar2(100);
                           v_city                 varchar2(35);
                           v_state                varchar2(35);
                           v_zip                  varchar2(15);
                           v_msoid                number;
                           v_siteid               number;
                           v_ErrorCode            number;
                           amsid                  varchar2(20);

                         BEGIN
                           select SA.addr_line1, SA.addr_line2, SA.city,
SA.state, SA.postcode,
                                  S.mso_id, S.site_id into
                           v_address1, v_address2, v_city, v_state, v_zip,
v_msoid, v_siteid
                           from amsmgr.ams_account A,
                                service_sites S,
                                service_site_address SA
                           where A.ams_account_id = :amsid
                           and A.caps_account_id = S.acct_id
                           and S.site_id = SA.site_id;

                           dbms_output.put_line(v_address1 || '|' ||
v_address2 || '|' || v_city ||
                                                '|' || v_state || '|' ||
v_zip || '|' || v_msoid || '|' || v_siteid || '|');
                         EXCEPTION
                           when OTHERS then
                             v_ErrorCode := SQLCODE;
                            dbms_output.put_line('CAPSDB error=' ||
v_ErrorCode || '|');
                         END;
                       });
     $sth = $dbh->prepare($AddrlineSql);
    $dbh->{RaiseError} = 1;
    $dbh->func( 1000000, 'dbms_output_enable');
     foreach $AMS_ID (<>) {
        print $AMS_ID;
       $sth->bind_param(":amsid",$AMS_ID);
      $sth->execute;
      $text = $amsh->func( 'dbms_output_get' );
      print " address line is $text \n
    }


  I am running the above program  as

 prg1.pl file_1

 where file_1 contains the ams_ids.

 Eventhough file_1 has valid ams_ids, which is passed through bind_param to
the pl/sql blocks,

  I am always getting exception error instead of the correct one.

 Is Anything wrong in the above program ?

 Appreciate your Help.

thanks
Praba