John,
I am not getting any error message.
But the assigned value to the bind variable is not
passed.
Any one help me ?
thanks in advance
praba
-----Original Message-----
From: John Joseph Trammell [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 08, 2001 6:44 AM
To: [EMAIL PROTECTED]
Subject: Re: regd Pl/Sql blocks in DBI
On Thu, Jun 07, 2001 at 06:13:25PM -0700, Prabaharan Dorairajan wrote:
> 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
#!/depot/perl/rel/bin/perl -w
use strict;
> use DBI;
> my (dbh,sth);
???
> $dbh = DBI->connect("orcl", "scott", "tiger","oracle");
my $dbh = DBI->connect(...) || die "can't connect";
> 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;
> });
The enclosing parentheses are unnecessary.
> $sth = $dbh->prepare($AddrlineSql);
my $sth = $dbh->prepare(...) || die "can't prepare";
> $dbh->{RaiseError} = 1;
Maybe this should be in connect() above?
> $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.
Please post the exact error text -- it's important. I'm a PL/SQL
novice, so I may not be able to help you with that side.
