First you don't necessarily have to use commit() every time you update, you
can just set {AutoCommit => 1} which is the default, unless you set it to 0,
and it will commit each query itself.
Then you must use bind_param_inout to retrieve the value. See perldoc
DBD::Oracle.
Ilya
-----Original Message-----
From: Rozengurtel, Daniel
To: Sterin, Ilya
Sent: 07/12/2001 7:08 AM
Subject: RE: Single value
Oh, thanx Ilya
I will surely experiment that when my boss is out :)
About update that you and other people were advising: I have managed to
do
so. The problem was on the Oracle side:
In the code I have there are many updates that are running inside
foreach
loop. It was not updating because the ONLY commit I had was out of the
loop.
So I guess after the first update was performed the table got locked and
all
the next updates could not take place. So i had to put after each update
a
commiat statment. Its a small and stupied thing but gave me a lot of
sweat.
One more thing. I am thinking of maybe to pass all the values from perl
to
one store proc, and let the proc do all the selects and update for a
specific INSTRUMENT_ID inside a foreach loop.. But I need that store
proc to
return a value to a perl variable(s). Will dbh->do/execute be of a help
to
me? Do these method return anything else except for a true/false and rc
code? Should I use something else maybe?
Thanx a lot all of you people. I am really glad there is a place one can
go
and "fetch" a very valuable info from experienced people. :)
Thanx Daniel
> -----Original Message-----
> From: Sterin, Ilya [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, July 11, 2001 9:00 PM
> To: Rozengurtel, Daniel
> Subject: RE: Single value
>
> That would depend on lots of *stuff*. You will most likely have to
> experiment with both and use CPU timing with times() function to see
both
> in
> action. I would bet the difference would be so minimal, that it
wouldn't
> matter one way or another, especially for one insert. I once wrote a
> program in C with OCI, for testing purposes, that inserted 10000 rows
and
> extracted these rows, then I wrote an equivalent in perl (about 200
less
> lines though:), there was no difference at times OCI ran faster at
times
> Perl ran faster, so it really depends on network conditions, etc...
> If you want to experiment with real efficiency, you might want to try
> Tim's
> new Oracle::OCI module, which you can easily interface with
> DBI/DBD::Oracle.
> For example, OCI requires about two pages of code to
> connect/allocate/initialize, etc..., so you can connect with DBI and
then
> pass the handle to the Oracle::OCI function that will do the insert.
It's
> really great, so if you have time to experiment, you might want to
> download
> and install it.
>
> HPH
> Ilya
>
> > -----Original Message-----
> > From: Rozengurtel, Daniel
[mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, July 11, 2001 5:49 PM
> > To: 'Sterin, Ilya'
> > Subject: RE: Single value
> >
> >
> > Ilya I have a question:
> > Will it be more efficient passing values i want to update to a store
> proc
> > and executing it on Oracle directly rather then updating using
> > emeded sql in
> > Perl?
> > Thanx
> >
> > PS: total recs to update 30000
> >
> > > -----Original Message-----
> > > From: Sterin, Ilya [SMTP:[EMAIL PROTECTED]]
> > > Sent: Wednesday, July 11, 2001 4:53 PM
> > > To: 'Rozengurtel, Daniel '
> > > Subject: RE: Single value
> > >
> > > First you are binding 'DONE' when there are no place
> > holders????? Another
> > > problem is that you are using q{} and your variables will not
> > interpolate.
> > > qq{} should be used in this case.
> > >
> > > How about this...
> > >
> > > my $rows_updated = $dbh->do(qq{
> > > UPDATE ISSUES_CURRENT
> > > SET ISS_TYP=$iss_typ,
> > > ACTIVY_STAT_TYP=$actv_typ,
> > > TICKER=$ticker,
> > > CUSIP=$cusip,
> > > ISIN=$isin,
> > > SEDOL=$sedol,
> > > DENOM_CURR_CDE=$curr
> > > WHERE INSTR_ID=$instr_id}) || die $dbh->errstr;
> > >
> > >
> > > Ilya
> > >
> > > -----Original Message-----
> > > From: Rozengurtel, Daniel
> > > To: Sterin, Ilya
> > > Sent: 07/11/2001 2:30 PM
> > > Subject: RE: Single value
> > >
> > > Hello all,
> > > I am trying to update a table with some values and it seems to me
I am
> > > kinda
> > > stuck :(
> > > This is what i do:
> > >
> > > my $rows_updated = $dbh->do(q{
> > > UPDATE ISSUES_CURRENT
> > > SET ISS_TYP=$iss_typ,
> > > ACTIVY_STAT_TYP=$actv_typ,
> > > TICKER=$ticker,
> > > CUSIP=$cusip,
> > > ISIN=$isin,
> > > SEDOL=$sedol,
> > > DENOM_CURR_CDE=$curr
> > > WHERE INSTR_ID=$instr_id}, undef, 'DONE') || die $dbh->errstr;
> > >
> > > $dbh->commit;
> > >
> > > I am sure variables have non-null values
> > > This is an error I get:
> > >
> > > execute called with 1 bind variables when 0 are needed at
> > > /usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/DBI.pm line 940.
> > > Issuing rollback() for database handle being DESTROY'd without
> explicit
> > > disconnect().
> > >
> > > Please if you have any suggestinons, thanx
> > >
> > > > -----Original Message-----
> > > > From: Sterin, Ilya [SMTP:[EMAIL PROTECTED]]
> > > > Sent: Wednesday, July 11, 2001 2:21 PM
> > > > To: 'Rozengurtel, Daniel '; '[EMAIL PROTECTED] '
> > > > Subject: RE: Single value
> > > >
> > > > >Hello people.
> > > >
> > > > >Does anybody know how to retrieve a single value from a DB?
> > > > >EX: select ID from my_table where my_key='12345';
> > > > >I have used a :selectrow_array() but its not a good idea to
> > > instantiate
> > > > >an
> > > > >array for retrieving only one value.
> > > >
> > > > Why not? There is ofcourse fetchrow_arrayref, fetchrow_hashref,
but
> > > in
> > > > your
> > > > case array() method is the best, since you are only retrieving
one
> > > value,
> > > > so
> > > > a list with one value is returned.
> > > >
> > > > Ilya
> > > >
> > > > >Thanx Dan
> > > >
> > > >
> > >
_____________________________________________________________________
> > > IMPORTANT NOTICES:
> > > This message is intended only for the addressee. Please
> notify
> > > the
> > > sender by e-mail if you are not the intended recipient. If you are
not
> > > the
> > > intended recipient, you may not copy, disclose, or distribute this
> > > message
> > > or its contents to any other person and any such actions may be
> > > unlawful.
> > >
> > > Banc of America Securities LLC("BAS") does not accept
time
> > > sensitive, action-oriented messages or transaction orders,
including
> > > orders
> > > to purchase or sell securities, via e-mail.
> > >
> > > BAS reserves the right to monitor and review the content
of
> all
> > > messages sent to or from this e-mail address. Messages sent to or
from
> > > this
> > > e-mail address may be stored on the BAS e-mail system.
> > >
> > >
> >
_____________________________________________________________________
> > IMPORTANT NOTICES:
> > This message is intended only for the addressee. Please
> > notify the
> > sender by e-mail if you are not the intended recipient. If you are
not
> the
> > intended recipient, you may not copy, disclose, or distribute this
> message
> > or its contents to any other person and any such actions may be
> unlawful.
> >
> > Banc of America Securities LLC("BAS") does not accept time
> > sensitive, action-oriented messages or transaction orders,
> > including orders
> > to purchase or sell securities, via e-mail.
> >
> > BAS reserves the right to monitor and review the content of
all
> > messages sent to or from this e-mail address. Messages sent to or
> > from this
> > e-mail address may be stored on the BAS e-mail system.
> >
>
>
_____________________________________________________________________
IMPORTANT NOTICES:
This message is intended only for the addressee. Please notify
the
sender by e-mail if you are not the intended recipient. If you are not
the
intended recipient, you may not copy, disclose, or distribute this
message
or its contents to any other person and any such actions may be
unlawful.
Banc of America Securities LLC("BAS") does not accept time
sensitive, action-oriented messages or transaction orders, including
orders
to purchase or sell securities, via e-mail.
BAS reserves the right to monitor and review the content of all
messages sent to or from this e-mail address. Messages sent to or from
this
e-mail address may be stored on the BAS e-mail system.
