1 2 Previous Next 16 Replies Latest reply: Jun 29, 2012 12:33 PM by 946222 Go to original post RSS
      • 15. Re: Pipe line function
        in 11g such types are hidden and it is a bit more work to show them
        They are still visible in user_objects though:
        SQL> select * from v$version where rownum = 1
        Oracle Database 11g Enterprise Edition Release - Production          
        1 row selected.
        SQL> select object_name from user_objects where object_type = 'TYPE'
        4 rows selected.
        • 16. Re: Pipe line function
          Solomon Yakobson wrote:
          All Billy is saying why would one write a table function (pipelined or not) that issues a select, collects results into a nested table, passes nested table back to SQL which applies table operator to it to convert it into SQL table, fetches from it an produces absolutely same result set as we would by simply issuing that original select statement to begin with? This obviously needs justification. And one of them, as Biily noted, is some complex transformation where PL/SQL is more suitable than SQL.

          And I would add the following. What is main advantage of pipelined function? If regular table function builds complete collection and only then returns it to SQL which means SQL waits all that time while collection is being built and also memory used by collection can be substantial. Pipelined function allows you to build just one row and pipe it to SQL right away and while SQL is working with that piped row pipelined dunction continues and builds next row. Therefore by using bulk collect OP negates the sole idea behind pipelined function. And if, as Billy noted, using table function in OP given code needs justification, using pipelined table function the way OP does it makes absolutely no sense.

          Now, that's how you make a useful reply! I don't know what the other people defending Billy have in mind, but you, SY, really got the spirit of what I was talking about. This forum is full of plain "you are wrong" answers, and very few have a decent answer. Your answer is what I was expecting from Billy in the first place, this is an Ace posture, not his.

          What tipped me off, like you said, is that Aces are supposed to be a beacon of knowledge for the community, not some kind of tyrant full of himself throwing you are wrong all over the place, without giving proper explanations or treating people decently.

          You deserve to be an Ace, not him. What matter if his resume is full of stars and badges if he doesn't properly pass the knowledge to the community? Attitude is the point here.

          And I won't talk about this anymore, I've hijacked this thread long enough.
          1 2 Previous Next