Select against functions or procedures
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Select against functions or procedures

  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Select against functions or procedures

    Hi all,

    Is it possible to create a stored procedure/function that returns records so that I can just call by the following query :

    SELECT * FROM MyProcedure()

    I know I can do this in SQL Server, but how to do this in Oracle ?

    If it is impossible, what is your suggestion when you have a situation where you find queries that are duplicated across several caller and want to put them in 1 place to be reusable ?

    Any help would be greatly appreciated.

    Best Regards,

    Setya

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Try creating a view. In a view you can have references to stored procedures if there is something that needs to be calculated and only a function will work. You can not select from a function. You can also create a stored procedure that returns a ref cursor.
    this space intentionally left blank

  3. #3
    Join Date
    Oct 2008
    Posts
    2

    Select against functions of procedures

    Hi,

    Thanks for your suggestions.

    Using Views is no option in our case since they can not accept parameters.

    Calling stored proc. that returns a ref cursor needs more than 1 step which is impossible to apply because I need to put the function/stored proc in statement like Oracle MERGE.

    Regards,

    Setya

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    But you can use a where clause on a view.
    So make sure that what ever you are filtering by is in a column.
    this space intentionally left blank

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Quote Originally Posted by Setya
    If it is impossible, what is your suggestion when you have a situation where you find queries that are duplicated across several caller and want to put them in 1 place to be reusable ?
    What I personally do is use Dynamic SQL. Instead of SELECT * FROM SomeFunc(), it becomes more like:

    l_v_SQL := 'SELECT * FROM '||SomeBuildSQLFunc();

    OPEN l_v_RefCur FOR l_v_SQL;

    This has many advantages, including the ability to tweak the 'common' SQL in special cases. I can pass some parameters in that the function can act upon to modify the SQL for any special cases.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

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



Click Here to Expand Forum to Full Width