First, I am sorry for this discussion getting so far off topic. The use of a pinned procedure is a database issue, not a perl dbi issue. That said.....
That is exactly what we are doing here is a benchmarking. The reason for this is how Oracle handles calls. If I can pin a procedure in memory it just goes then and runs the parsed hash. If it is not pinned in memory it can fall out and have to be read/parsed/hashed all over again. I am looking at this from the database end, not the perl/DBI end. This system will be doing several things at one time and SGA memory could become well used. By pinning the procedure in memory, it will not be released after each use and fall out. This saves the time of reloading the procedure in memory. -----Original Message----- From: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Friday, June 14, 2002 10:01 AM To: Powell, Bruce Cc: 'Scott T. Hildreth'; [EMAIL PROTECTED] Subject: Re: Multiple IN variables for Oracle Procedure On Fri, Jun 14, 2002 at 09:25:33AM -0600, Powell, Bruce wrote: > Yes, I did test it first with a simple insert statement. The reason I am > using a procedure is speed. I can pin the procedure in memory and save time > on multiple inserts. What makes you think it would be faster than doing execute() calls on a prepared statement handle? Have you actually benchmarked it? Tim. > -----Original Message----- > From: Scott T. Hildreth [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 14, 2002 09:19 AM > To: Powell, Bruce > Cc: [EMAIL PROTECTED] > Subject: RE: Multiple IN variables for Oracle Procedure > > > > Have you tried running it from the command line, not as a CGI script? > This may be an example, but you really don't need a stored procedure here. > > $sth = $dbh->prepare('insert into some table values(?,?)'); > $sth->execute($data1, $data2). > > ...but your procedure may do alot more than you sent here. > > On 13-Jun-2002 Powell, Bruce wrote: > > I am currently having an issue with sending multiple variables to a Oracle > > procedure. When I do this the CGI script I am using reports that the > script > > has run successfully. But when I check the database it only received the > > first variable. > > > > Example: > > > > If my procedure looks like this: > > > > create or replace procedure foo > > (v_some_data IN some_table.some_column%TYPE, > > v_some_data2 IN some_table.another _column%TYPE) > > as > > begin > > insert into some_table > > values(v_some_data, v_some_data2); > > end; > > / > > > > and I evoke the command by the following: > > > > my $dbh = DBI->connect("dbi:Oracle:$srvc", $user, $passwd, > > { AutoCommit=>0, RaiseError=>1 } ) > > or die "connect problem: $DBI::errstr"; > > > > $sth = $dbh->prepare(qq{ > > BEGIN > > FOO(:some_data, :some_data2); > > END; > > }); > > $sth->bind_param(":some_data", $some_data); > > $sth->bind_param(":some_data2", $some_data2); > > $sth->execute; > > > > $dbh->disconnect(); > > exit (0); > > > > I am using DBD::Oracle-1.12 and DBI 1.21. Any help would be appreciated. > > > > -Bruce Powell > > > > > > > > > ___________________________________________________________________________ > > CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is > > > for the sole use of the intended recipient(s) and may contain confidential > > > and privileged information. Any unauthorized review, use, disclosure or > > distribution is prohibited. If you are not the intended recipient, please > > > contact the sender by reply e-mail and destroy all copies of the original > > message. > > ---------------------------------- > E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> > Date: 14-Jun-2002 > Time: 10:14:21 > ---------------------------------- > > > > ___________________________________________________________________________ > CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is > for the sole use of the intended recipient(s) and may contain confidential > and privileged information. Any unauthorized review, use, disclosure or > distribution is prohibited. If you are not the intended recipient, please > contact the sender by reply e-mail and destroy all copies of the original > message. ___________________________________________________________________________ CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
