And the following DBI perl example is thanks to q_richard_chen@yahoo.com (Richard Chen):
Hello Tom,
I was looking for such compilation of tips
on the topic. I did not find the section about doing
it using the popular perl DBI. After some fiddling I
get it working there too. Here is a complete working
example following your model using perl DBI. I think
it is a good idea that you include this in your howto
so that more people will benefit from it.
Thanks
Richard Chen
$ cat demo.pl
#!/usr/local/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect(''''dbi:Oracle:'''',''''scott'''',''''tiger'''') or die $DBI::errstr;
my $sth1 = $dbh->prepare(q{create or replace package types as
type cursorType is ref cursor;
end;});
$sth1->execute;
> >
$sth1 = $dbh->prepare(q{
create or replace function sp_ListEmp return types.cursorType
as l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;});
$sth1->execute;
$sth1 = $dbh->prepare(q{
BEGIN
:cursor := sp_ListEmp;
END;
});
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute();
while ( my @row = $sth2->fetchrow_array ) { print join("|",@row),"\n"; }
MFC + ODBC VERSION (example checked by Marcin Buchwald) marcin.buchwald@gazeta.pl Oracle server side code is just like in the VB example
CDatabase m_DB;
BOOL ok = m_DB.OpenEx(_T("DSN=orcl;UID=velvet"),CDatabase::useCursorLib);
COraSet set(&m_DB);
set.m_Value = Text1.Text;
set.Open();
while (!set.IsEOF()) {
// set members contain values of single row
// use it here
set.MoveNext();
}
set.Close();
where
COraSet::COraSet(CDatabase* pdb) : CRecordset(pdb) {
m_nParams = 1;
m_nFields = ;
m_nDefaultType = snapshot;
}
CString COraSet::GetDefaultSQL() {
return _T("{call RefTest.GetEmpData(?,?)}");
}