Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: Stored Proc, Select query

    (using 8i) I have been looking for an elegant solution to return records from an Oracle SP. Unfortunately I have not found consistent and clear answers. I don't understand the logic in not allowing a straightforward solution to return records from an Oracle DB using an SP. I am a Sybase and MSSQL user, which may explain my frustration when it comes to working with Oracle.

    Can anyone tell me...
    1.) What are the Oracle creators thinking by allowing all but select queries to be straight forward like in Sybase when in an SP? Is there a logical explanation?

    2.) What is the best way to accomplish this? Are there any Tutorials or Articles you can recommend?

    Thanks for your help!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Stored Proc, Select query

    Originally posted by timmoser
    (using 8i) I have been looking for an elegant solution to return records from an Oracle SP. Unfortunately I have not found consistent and clear answers. I don't understand the logic in not allowing a straightforward solution to return records from an Oracle DB using an SP. I am a Sybase and MSSQL user, which may explain my frustration when it comes to working with Oracle.

    Can anyone tell me...
    1.) What are the Oracle creators thinking by allowing all but select queries to be straight forward like in Sybase when in an SP? Is there a logical explanation?
    This is because PL/SQL is a proper modular language, not a command processor. PL/SQL will not just fire query results at the screen like SQL Plus does, you have full control over how much data is fetched and into what.

    Originally posted by timmoser
    2.) What is the best way to accomplish this? Are there any Tutorials or Articles you can recommend?
    Probably a REF CURSOR, depending on what is is you actually want to do. You could get the book "PL/SQL Programming" by Steven Feuerstein, and the AskTom website (asktom.oracle.com) is a good place to search for examples of good PL/SQL code.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Do a google search on 'oracle stored procedure returning resultset' and you will find a number of tutorials using different technologies to retrieve a result set.

    I would also recommend the following url as a good overview of the differences between oracle and other databases.

    http://www.databasejournal.com/featu...le.php/1403591

    Theres also a pt 2 on the site somewhere.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •