> I use reference cursor parameters for this in Oracle.
>
> If the query results come from a single select within the procedure, your
> PL/SQL guys can define a reference cursor for the select and pass you that
> in an IN/OUT parameter. There are examples of how to treat this parameter
> as a DBI statement handle in the DBD::Oracle docs - look for "Binding
> Cursors".
>
> If the query results are more complex, and are not being retrieved with a
> single select, Oracle 8 provides GLOBAL TEMPORARY tables that are great
> for building up temporary result sets. The PL/SQL procedure dumps its
> results into a global temporary table, rather than writing it out with
> dbms_output, and then passes you a reference cursor parameter as above,
> based on "SELECT <columns> FROM <temp_results_table>", which you can again
> treat as an open DBI statement handle.
>
> The data in the temp table is private to the session (multiple sessions
> can write to the table, but each session will only see the data it wrote),
> and is removed for you at the end of the current transaction or session,
> depending on how the temp table was defined.
>
> You don't actually need the ref cursor if you know which table the
> procedure is writing to - you can just do your own select if you like.
>
> I've used all three of these methods to good effect. I prefer passing a
> cursor back from the procedure, because then you, the perl programmer,
> don't need to know what the PL/SQL is actually doing to provide you with
> the data. You just need to have a definition of the columns returned by
> the cursor.
>
>
> HTH
>
> Simon
>
> ----------
> From: Jon Williams
> Sent: Wednesday, September 5, 2001 13:50 PM
> To: [EMAIL PROTECTED]
> Subject: DBI With Oracle & PL/SQL
>
> Have been using DBI with MySQL & Oracle for some time
> now. Recently, the powers that be (i.e my bosses)
> decided to use PL/SQL stored procedures for some of
> our rather complex queries. The PL/SQL guys are
> writing the query results to DBMS_OUTPUT. I cannot
> get to the query results via standard DBI syntax (i.e
> fetchrow_hashref or fetchrow_arrayref etc...). To get
> around this I've been using the 'dbms_output_get'
> method to retrieve query results:
>
> my @results = $db_handle -> func ( 'dbms_output_get'
> );
>
> Is this the preferred method of retrieving results
> from a PL/SQL procedure? Should the PL/SQL guys be
> giving me the results in some other fashion instead of
> writing to DBMS_OUTPUT? I scanned through a friends
> copy of "Programming the Perl DBI" and all I could
> find on the subject of DBMS_OUTPUT was a statement
> that said DBMS_OUTPUT is usually reserved for
> informational or trace messages from PL/SQL
> procedures.
>
> Any help you could provide would be great.
>
> Thanks.
>
> Jon.
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Get email alerts & NEW webcam video instant messaging with Yahoo!
> Messenger
> http://im.yahoo.com
>
>
>
>
>
***********************************************************************
Confidentiality: This e-mail and its attachments are intended for the
above named recipient(s) only and may be confidential and/or
privileged. If they have come to you in error you must take no action
based on them, nor must you copy or disclose them or any part of their
contents to any person or organisation; please reply to this e-mail
and highlight the error immediately and delete this e-mail and its
attachments from your computer system.
Security Warning: Please note that this e-mail has been created in the
knowledge that Internet e-mail is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when e-mailing us.
Viruses: Although we have taken steps to ensure that this e-mail and
its attachments are free from any virus, we advise that in keeping
with good computing practice the recipient should ensure they are
actually virus free
***********************************************************************
