seeking some decent Perl DBI <=> Oracle stored procedures examples...

I've been searching the web and the newsgroups for some good, non trivial examples of Oracle stored procedures invoked from Perl.

All the examples have been of such simple, trivial functions that I think I only understand some of the syntax and issues. I believe from the postings I've read that I understand the mechanism for calling the stored procedure, and the mechanism for binding perl variables to parameters so one can pass in data and get data back.

But, I want to do something more complex than any examples I've seen. Maybe everyone else just knows how to do this and I'm just missing something, but here goes...

I want to put a reasonably complex query into a stored procedure. The procedure will take a couple of IN parameters for the query. Then the query can generate 0, 1, or more rows.

Do I:

  1.) write a single stored procedure that returns

      a cursor, and then use the DBI fetchrows function
      using that cursor to get all the data? Or maybe
      some other DBI method to fetch the data?

  2.) write a single stored procedure that
      loops over the rows storing the results in
      an array and then access the array from Perl?
      Or some other data type?

  3.) write TWO stored procedures, one which
      opens the cursor and returns the cursor
      to perl, and the other takes the cursor
      as an INOUT parameter, fetches the next
      row, and returns that row to perl via
      an OUT procedure?
      I'd sit in a loop calling this second
      procedure until there are no more rows.

Maybe I'm such searching in all the wrong places, but I can't believe, as popular as Oracle and Perl are, that I can't find tons of examples of Perl and stored procedure interfaces. Instead, there's barely a handful, and they do such trivial operations that they don't expose the full richness of what can be done, and how to do it.

If there is a good set of examples somewhere, simply pointing me to that location would be great!

Thanks!

-ken wood Received on Wed Apr 21 1999 - 11:54:28 CDT