1 2 Previous Next 21 Replies Latest reply: Feb 11, 2013 11:41 PM by Billy~Verreynne Go to original post RSS
      • 15. Re: sql vs pl/sql
        Karthick_Arp
        Etbin wrote:
        Thank you for the link.
        Each time I'm discussing the matter I remember having read it suggesting to search for Feuerstein on Ask Tom.
        It's between favorites now to have the link at hand as things are very much alike to Tom's disagreements and not getting any better.

        Regards

        Etbin
        This subject has always been a topic of discussion. Been 5 years but the memories still remain fresh!!
        • 16. Re: sql vs pl/sql
          Rahul_India
          All
          Actually i have just started my career.I am very confused ATM.
          Steve in his video's says to focus on PL/SQL as much as possible and limiting the use of SQL while experienced people on this forum voice their opinion on using SQL.
          • 17. Re: sql vs pl/sql
            Billy~Verreynne
            Well, Steve is then wrong if he thinks SQL needs to be limited in PL/SQL. As he is also wrong, IMO, when it comes to coding standards and dealing with the issue of scope.
            • 18. Re: sql vs pl/sql
              Rahul_India
              Billy  Verreynne  wrote:
              Well, Steve is then wrong if he thinks SQL needs to be limited in PL/SQL. As he is also wrong, IMO, when it comes to coding standards and dealing with the issue of scope.
              But most experts including Tom Kyte consider him one of the best pl/sql developer around.
              • 19. Re: sql vs pl/sql
                Billy~Verreynne
                Rahul India wrote:

                But most experts including Tom Kyte consider him one of the best pl/sql developer around.
                He's not the only PL/SQL developer around.

                In my view, you need to look at the contents (of a rule/suggestion/recommendation/advice/standard/etc) and not the person behind it.

                I agree with most of what Tom says, not because Tom says it. Because it confirms my experience and because I can substantiate it myself with sample and test cases provided.

                Steve often harps on software engineering basics - from a pure PL/SQL perspective. With much of that I agree with and think he has been doing an excellent job in that regard. But I disagree with other stuff. I for example want justification as to why an associative array is used as a fetch buffer and not a normal array. Or why reserved words need to be written (contrary to standards in Java and .Net) in upper case. Or what is considered "too much SQL" in PL/SQL?

                After all PL/SQL is two languages. It is PL - Programming Logic. And it is SQL - Structured Query Language. It is tightly integrated.

                PL/SQL allows you to use the right language for the specific task at hand. It is very seldom a case of "+oh, PL and SQL can both do this, which one to use?+". That is a case of inexperience and a lack of knowledge. There is very little overlap between the two languages.
                • 20. Re: sql vs pl/sql
                  Stew Ashton
                  Billy, thanks for your thought-provoking reply. While reading my remarks, please keep in mind that I do agree with you about the value of doing SQL in the database and providing high-level APIs to the application layer. Even then, the app developer has to understand some things about the database.
                  Billy  Verreynne  wrote:
                  Stew, using bind variables in this respect is more of an issue of how to use the OCI (Oracle Call Interface). Does not matter whether the client makes a SQL or PL/SQL call - bind variables need to be used.
                  When the app developer uses OCI, JDBC or some other "client" software, the interface tells him how to do bind variables, but not why. No bind variables (in an OLTP environment) = SQL injection risks and scalability problems due to excessive parsing. Those are database fundamentals, not OCI or JDBC fundamentals. Only the application developer can solve a parsing problem.
                  One of the best examples I have seen of this was back with Oracle 7.3...The client app (think they used PowerBuilder back then) only made use of PL/SQL calls and from an app developer perspective, nothing need to be understood of the database, beyond that.
                  Powerbuilder is/was a client-server product. The user had his own session with his own identity in the database: since the session stayed "his" between calls, the user could do multiple fetches from the same cursor and updates could be done with pessimistic locking.

                  Today, there are lots of Web applications: they are stateless, use generic Oracle users, do optimistic locking and have to paginate queries because they can't fetch from the same cursor twice.

                  Granted, most of these differences can be hidden in PL/SQL, but not all: the interfaces between the app and PL/SQL have to change. As an example, only the app developer can notify the database of the identity of the user.

                  There are some architecture/database fundamentals that everyone involved must understand.

                  There is also the problem, as in my company, where the app developers are all there is: no one else is there to do the database design and code the high-level APIs. In the developer community as a whole, we need lots more knowledge of the database, not less.

                  Edited by: Stew Ashton on Feb 11, 2013 9:16 PM
                  • 21. Re: sql vs pl/sql
                    Billy~Verreynne
                    Stew Ashton wrote:

                    In the developer community as a whole, we need lots more knowledge of the database, not less.
                    Agree wholeheartedly. But the exact opposite has been happening over the last decade. Beans. Hibernate. Etc.

                    All "technologies" that dumb down developers when it comes to databases.

                    Quote Fred Brook to developers today, "+Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious.+" (1975), and they stare back at you with a confused wtf-are-you-saying face.

                    The fundamentals we've learned, and learned the hard way, are ignored today when it comes J2EE and .Net architectures - where their prophets proclaim that it is all about the application and code.. and not data.

                    And they don't get it that data is what is critical, and not the application.

                    Even a simplistic example does not enable the penny to drop - </i>what is important to you - your Gb's worth of ogg/mp3 music (data), or your media player (application)?</i>

                    I've have had this argument numerous times through the last decade. There is no talking sense to these developers - kind of like dealing with J2EE as a religion where the only requirement is faith, and no real world fact, logic and evidence will ever shake that blind faith.

                    So my take is - don't bother. You want to use my database? You'll do it via a PL/SQL abstraction layer. You will not directly touch a single column or single table using SQL.

                    This approach works. Even when the developers are severely database impaired.
                    1 2 Previous Next