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
