I grow tired of the endless stream of SQL going into my head on daily bases. It all looks the same, but with subtle differences. I'm just no so interested to learn what different flavors of create table exist out there. But I must. But I so needed to advance my self career wise.
So I soldier on. Learning T-SQL every day, one hour a day. My personal learning time. And during my project time I'm learning PL/SQL. Of course my project time doesn't allow me much time to learn all that I need about the damned Oracle SQL dialect, so I learn during my break, during my free time at home in the evenings experimenting on the Oracle 10g EX database I've installed on my Ubuntu machine for just that purpose.
But it tires me so. I would rather learn Jquery or Dojo, or read some new PHP book (like PHP in Action from Manning), or continue my studies of Django or learn a new cool language like Erlang or OCaml. But not. I'm stuck with T-SQL and PL/SQL. It is my choice. One that I had to make.
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
03 November 2008
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:
GRANT CREATE TABLE TO UserName;
CREATE STORED PROCEDURE TestProc
AS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE TestTable (name VARCHAR2 NULL)';
END;
It took me two whole days to do that. Fantastic. One site that helped me a lot was this.
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:
GRANT CREATE TABLE TO UserName;
CREATE STORED PROCEDURE TestProc
AS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE TestTable (name VARCHAR2 NULL)';
END;
It took me two whole days to do that. Fantastic. One site that helped me a lot was this.
Subscribe to:
Posts (Atom)