File Library

'If you can do it in SQL, use SQL'. But sometimes even the very powerful version of SQL that Oracle provides is not enough and you need more, like loops, conditions etc. If you can make the output of a function like it's a table then you can use it in SQL and have access to all the power PL/SQL provides. Of course you can put a call to the PL/SQL Code in your SELECT part of your statement or in the WHERE clause, but then this code is executed many times (once for every row in the result (SELECT) or once for each row checked (WHERE)).
Sometimes you are posed with the question of creating a SQL script based on the data in a table so this can be entered into a different database. For example when using data in both the Development environment as well as the Testing, Acceptance or Production environment. Of course you can easily create a script for this purpose but we are programmers, so basically we are lazy. If we can have the computer do stuff for us, then that’s the way to go. Funny thing is though that building this code will usually take longer to complete but the good news is that it can be used over and over again. And it will improve over time, being able to handle more types of fields in your table. After attending this session you will be able to build your own functions that can be used as tables in SQL so you can leverage all the power of PL/SQL in your SQL environment.