Your while should read -
while($csr->fetch) {
Since you are binding params you just call fetch.
Brian
----- Original Message -----
From: "T. Murlidharan Nair" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, April 19, 2002 12:57 PM
Subject: Stored procedures and Perl DBI
>
> Hi All:
>
> Has anyone executed a stored procedures from a perl CGI. I am having
> problems when there are many rows returned. When the return result
> is
> a scalar things are fine but when the return is a result set, I am
> totally stumped.
> I need to finish this soon so please help.
> Murli
>
>
> The following is my pl/sql code
>
> CREATE OR REPLACE PROCEDURE new_worker_select_cur
> ( f_name IN OUT varchar2,
> l_name OUT varchar2,
> email OUT varchar2
> )
> AS
>
> CURSOR cursor_temp (cur_f_name IN varchar2) IS
> SELECT PREFIX_NAME, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, EMAIL,
> PEOPLE_ID, PID, SOURCE_ID,SOU
> RCE_INFO, NSF_USER_TYPE FROM test WHERE first_name = cur_f_name;
>
> temp_record cursor_temp%ROWTYPE;
>
> BEGIN
>
> IF( cursor_temp%ISOPEN) THEN
> CLOSE cursor_temp;
> END IF;
> OPEN cursor_temp(f_name);
>
> LOOP
>
> BEGIN
>
> FETCH cursor_temp INTO temp_record;
> EXIT WHEN cursor_temp%NOTFOUND;
>
> END;
> f_name := temp_record.first_name;
> l_name := temp_record.last_name;
> email := temp_record.email;
>
> DBMS_OUTPUT.put_line(f_name);
> DBMS_OUTPUT.put_line(l_name);
> DBMS_OUTPUT.put_line(email);
>
> END LOOP;
>
> RETURN;
>
> IF(cursor_temp%ISOPEN) THEN
> CLOSE cursor_temp;
> END IF;
>
>
> END NEW_WORKER_SELECT_CUR;
> ~
>
> ----------------------------------------------------------------------
> -----------------------------------------------
>
> The following is my perl code
> #!/usr/local/bin/perl
>
> use DBI;
> use CGI qw(:all);
> $q = new CGI;
>
> print $q->header(-type=>'text/plain');
>
> $ENV{'ORACLE_HOME'} = '/usr/local/apps/oracle/home';
> $ENV{'ORACLE_SID'} = 'npaci';
> $ENV{'TWO_TASK'} = 'npaci';
>
> my $dbh = DBI->connect("DBI:Oracle:",
> "userid",
> "xxxxxxxxx",
> {
> PrintError => 1, # warn() on errors
> RaiseError => 0, # don't die on error
> AutoCommit => 1, # commit executes
> # immediately
> }
> )
> or die "Cannot connect to database: $DBI::errstr";
>
> my $p_fname = 'KELLY';
> my $p_lname ;
> my $p_email;
>
> $csr = $dbh->prepare(qq{
> BEGIN
> NEW_WORKER_SELECT_CUR(:p_fname, :p_lname, :p_email);
> END;
> });
>
> $csr->bind_param_inout(":p_fname", \$p_fname,255);
> $csr->bind_param_inout(":p_lname", \$p_lname,255);
> $csr->bind_param_inout(":p_email", \$p_email,255);
> $csr->execute;
>
> print "$p_email $p_fname $p_lname \n";
>
> # It works fine when there is only one rwo returned what do you do
> for
> multiple rows.
>
> #while (@row = $csr->fetchrow_array()){
>
> # $delim = "";
> #for($i = 0; $i < @row; $i++)
> #{
> # print $delim . $row[$i];
> # $delim = ",";
> # }
> #print "\n";
>
> #}
>
> $dbh->disconnect(); # NOP under Apache::DBI
>
> exit
