Q: Oracle & Client/Server

Greetings Oracle Oracles!

I am getting very fustrated with my attempts of converting some MS SQL Server stuff over to Oracle, and perhaps you can help:

I have 3rd party client tools (like Crystal Reports, Excel) and want these tools to graphically produce a report based on data returned from Oracle. The nature of the data comming back, is the result of some calculations done on the Server (Oracle) side ... In essence implementing a cross-tab kind of query.

In SQL Server I'd do something like:

    <<START OF PROCEDURE>>
    SELECT <<stuff>> FROM <<Some Tables>> WHERE <<joins etc.>>     INTO <<a temporary table>>

    <<Update the temporary table with count's, stats, etc>>

    << finally >>
    SELECT * from <<temporary table>>
    DROP TABLE <<temporary table>>

    << END OF PROCEDURE>>

In Oracle I'm having difficulty, becase, while under a PL/SQL block, SELECT can only go INTO a variable, and not return records from a table, temporary or otherwise.

Summary:

    I want to, from a client based app to issue a SQL statement similar to:
"EXEC foobar;"

    and that app, get back a record set that looks like a normal
"SELECT..FROM", yet was in reality produced, and subsequently erased on the
SERVER side.

Any help/pointers/direction would be greatly appreciated:

Mark Wignall
email: mark.t.wignall_at_tek.com Received on Thu Oct 02 1997 - 00:00:00 CEST