0

Is it possible to call an Oracle Pipelined Function without specifying TABLE( ) operator, like in the following example? 

SELECT * FROM MyFunction()

I don't want to use the following, due to compatibility with SqlServer.

SELECT * FROM TABLE( MyFunction() )

Can anyone help me, please?

Thank you!

  • 1
    Did you try it? What happened when you did? What does the manual say about the `SELECT` statement syntax? – mustaccio Jun 25 '15 at 13:50
  • 3
    Short answer, no. Longer answer, if your real function takes no arguments, as in your example, then you can create a view that queries the pipelined table function (using the `table` operator) and then query the view from your application without needing the `table` operator. Of course, a pipelined table function that takes no arguments can almost always be written more efficiently in pure SQL. – Justin Cave Jun 25 '15 at 13:54
  • 1
    A view would help you? `create view MyFunction as select * from TABLE(MyFunction())`; might not help in case parametrized calls.. – Maheswaran Ravisankar Jun 25 '15 at 14:37
  • I really need to pass parameters to MyFunction in my real scenario. –  Jun 26 '15 at 15:00

1 Answers1

0

First, table function is not a good idea in Oracle, since it loads all the data to the memory, and no indexes will be used in your query any more.

Second, table functions work totaly differerent in SQL Server & Oracle, My best suggestion is to use dynamicSql, the function will build sql string and return it and the main procedure will execute it, should work in oracle and sql server as well.

Elisheva Wasserman
  • 504
  • 2
  • 9
  • Functions that build a large collection and return it wil use a lot of memory. But pipelined functions are different. From [the manual](http://docs.oracle.com/database/121/LNPLS/tuning.htm#LNPLS915): "A pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. Response time improves because the entire collection need not be constructed and returned to the server before the query can return a single result row. (Also, the function needs less memory, because the object cache need not materialize the entire collection.)" – Jon Heller Jun 25 '15 at 19:21
  • @Jon Heller, In the end It still in the memory and not in the disc , from my own experience, changing the pipeline function to dynamic sql improved my queries performance in high percentages – Elisheva Wasserman Jun 26 '15 at 07:06
  • @ElishevaWasserman, I need to use Table Valued Function, because we're porting an SqlServer .NET application with Entity Framework to Oracle. We can't change the model. The aim is to use TVF with Entity Framework. –  Jun 30 '15 at 12:43
  • Can you be more specific? 1. as far as I know, stored function in oracle are not supported by EF,2. when you use entityframework, usually you don't have to worry about different provides – Elisheva Wasserman Jun 30 '15 at 14:44