Comments

Hi Steve,is there a difference when I execute the query as simple SQL or inside a PL/SQL procedure?In the latter case it returns no rows for an object belonging to another schema for which I have been granted DEBUG and EXECUTE priveleges.Do I need some extra privileges in that case?It puzzles me...

Post a Comment

Popular posts from this blog

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this series.

Here's my plan for the series:

1. I will start (in this post) with some very simple examples and exploration of a few use cases.

2. Explore table functions that return more than one value per row (via object types).

I have recently received a couple of requests for recommendations regarding unit testing of PL/SQL programs. I thought I would share with you what I told them.

First, some background: unit testing refers to the process of testing individual subprograms for correctness, as opposed to overall application testing (which, these days, almost always means visiting a website).

The basic idea behind unit testing is that if you verify that each individual subprogram works correctly, then you are much less likely to have bugs in higher-level programs that call those tested subprograms. And when you do, you know you can focus on the way the tested subprograms are used, and not the subprograms themselves.

The most important application of a unit test is to participate in a regression test, which can be run to verify one's code works today as well as it did yesterday. That will greatly reduce the chance of you upgrading the application and users complaining that a bunch of features that worked…

Please stop reading this post, and read that one. When you are done, come on back here for my thoughts on Sten's thoughts.
OK. You read it. Here we go.

First, thanks, Sten, for being such an interesting, wise, sometimes provocative voice in our community.

Next, Sten writes:

Now, on the one hand, I certainly agree that the vast majority of young developers are currently caught up in the modern version of a Gold Rush, which is: "Build an app using JavaScript, pay no attention to that database behind the curtain."
But I can assure you that I still do meet young PL/SQL programmers, regularly, when I am at conferences and doing onsite presentations at companies.
So, young person who writes PL/SQL: do not be afraid! You are not alone! And you are super-smart to have made the choice you did. :-)
Next, Sten offers this advice to managers: