I created your procedure, and used the code below and was able to insert both values. So the code is fine(which you already know), I don't do any CGI programming, so I can't offer much help.
On 14-Jun-2002 Powell, Bruce wrote: > 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. ---------------------------------- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 14-Jun-2002 Time: 11:23:13 ----------------------------------
