25 October 2008

Oracle , stored procedures and table statements

I drive for two hours every day to go to and from the work. In my car I hav an mp3 player and I spend those hours listening to trade podcasts. Mostly .NET rocks, FLOSS weekly and Stack Overflow. There are others , but they are not currently worth mentioning. I heard in one episode of .NET rocks that the internet is killing the software printing industry. They said that less and less people have time to read and study a book on any topic for several months before starting to work with the technology they were studying. Now, everybody uses Google.
Developers usually open their IDE, or editor, read some tutorials and then start coding. When they hit a problem they google the solution for it. Nobody reads. Nobody has the time to learn a technology that will change inside a year. I guess is true. Thats how I started doing it. Learning by googling. And really, I think thats the wrong way to do it.
See, here is my rationale. You can acomplish lots of thing by doing that. But I guarantee that you wont do it right, You are bound to miss some fundamental things. Or you are going to use the wrong solution for the problem you are facing. All things which would be easily avoided if you've taken time to learn a technology properly.
But thats not possible. You have so much to learn, so many possibilites. It is not possible to learn them throughly. It would take you all your working day to just keep up with all of that, I wish I could. But I can't.
What I do is pick a technology, buy a book or two and separate one hour a day, each working day to learn it chapter by chapter. These are technologies I like or think will be critical for my work for a longer period of time. Like SQL Server, or ASP.NET.
Now things will come that will require of me to learn something new just for one project. The technology in question may be extensive, but I don't have the time and the energy to learn it properly. In those cases I resort to Google to be my guide.
And thats what happened with stored procedures and Oracle. See, I'm kinda the db guy for my team. So for our latest project I got stuck doing database work besides my regular web work, Our client uses an Oracle database. We planned several stored procedures to handle all data transactions between the client and the database. Now, I haven't ever worked with Oracle and PL/SQL. And the project was way under way , so I didn't have much time to learn PL/SQL. So I started googling.
My first task was to create two procedures which would create some new tables and destroy them respectively. Some alter table statements were thrown in the mix.
Now , the first problem was that I couldn't do CREATE AND ALTER TABLE statements inside a stored procedure. I had to use EXECUTE IMMEDIATE, which runs all sorts of queries inside the stored procedure.
Now the following problem was that the created stored procedures couldn't create tables becuase my user couldn't explicitly do that . My user , which was handed to me by the client, received its permissions through roles. In order for a stored procedure created by my user to be able to create tables and so , my user must have the CREATE TABLE privilege granted to it specific.
Here is the code:



It took me two whole days to do that. Fantastic. One site that helped me a lot was this.

No comments:

Post a Comment