Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Oracle and PL/SQL
Options
-
21-11-2008 8:20pm#1Hi
What is the best way to get experience in Oracle databases and PL/SQL? This seems to be a requirement for a lot of programming jobs and seems like a good skill to pick up.
Is there a certification you can do that would be of help getting into the area? Anyone have experience of doing any of them?
0
Comments
-
-
radioactiveman wrote: »Hi
What is the best way to get experience in Oracle databases and PL/SQL?
By using them.radioactiveman wrote: »This seems to be a requirement for a lot of programming jobs and seems like a good skill to pick up.
Is there a certification you can do that would be of help getting into the area? Anyone have experience of doing any of them?
You have to be clear on what you want - there are two distinct jobs you are alluding to, DBA and PL/SQL developer. Have a look at http://education.oracle.com there's loads of info there on both streams, plus relevant certification.
I agree with Mr. Magoo, download XE and play around with it. One thing to remember, though, is that XE is pitched at being more like Microsoft Access, but on a grander (e.g. departmental) scale, so it's the database, plus lots more besides.
There used to be a personal edition of the database, not sure if it's available any more (I get to use Enterprise edition all the time).
I've worked as a PL/SQL developer for my sins, and am currently an Oracle DBA/consultant at the moment, so feel free to ask any questions.0 -
-
-
-
Advertisement
-
Not sure what you mean? I find dual invaluable in a lot of scripting situations.
Nuance of the language? I am sure there are other languages that do it.
So it can be handled better?
Nuance of the language - that's exactly it! What I mean by Dual is that it is simply redundant in all cases I know. SELECT SYSDATE is much more clear and relevant than SELECT SYSDATE FROM DUAL, no? And with regard to double pipes, I never knew, let alone used it for the last two decades. Yes, it's ANSI SQL but no database environment I used (Foxpro, Paradox, Access, Sybase, MSSQL) ever required it. I'm not even mentioning general purpose mainstream languages that usually have a single character concatenation operator. And NO_DATA_FOUND leaves me completely speechless as having no data is one of the most natural sutuations in data manupulation logic while exceptions imply something exceptionally wrong. These are just observations of a seasoned IT pro coming close with Oracle at a rather late stage. Many things just don't make sense but, of course, every language is entitled to have its nuances
0 -
-
radioactiveman wrote: »Hi I am looking at J2EE jobs that need PL/SQL so this would be the more important one.
I have good experience in java but none in pl/sql so I would have to either get on the job training, or get a qualification and convince an employer to take me on based on that. Is actual experience the only way in???!
I honestly don't know what the market is like now, but in general terms, I would think experience is what counts. Certification would of course also help.
On the job training is one way to go, I got started in PL/SQL development while working as a data warehouse analyst. I offered to help out, and ultimately ended up spending most of my day developing PL/SQL. I eventually changed company and moved into a development role that required more PL/SQL than anything else.if dual and the || operator are the only problems you ever have with oracle think yourself very lucky. So true.
DUAL is actually so handy when creating scripts to create other scripts. It makes the statement conform to the SQL standard, e.g. SELECT X is not a fully formed SQL statement, whereas SELECT X FROM DUAL is.0 -
I honestly don't know what the market is like now, but in general terms, I would think experience is what counts. Certification would of course also help.
On the job training is one way to go, I got started in PL/SQL development while working as a data warehouse analyst. I offered to help out, and ultimately ended up spending most of my day developing PL/SQL. I eventually changed company and moved into a development role that required more PL/SQL than anything else. So true.
DUAL is actually so handy when creating scripts to create other scripts. It makes the statement conform to the SQL standard, e.g. SELECT X is not a fully formed SQL statement, whereas SELECT X FROM DUAL is.
The other strange thing I encountered is that there's no proper escape char so sometimes the only resort is to use that bloody concatenation which makes the whole string, or rather a pile of strings, hardly readable.
I still don't get what are the pros of having Dual, can you please elaborate? Even its name is misleading because for a dummy entity Single, Unity or even Abstract would be a much more appropriate word. I read some notes about the origins of this name but they didn't make much sense either. I agree about conforming to standards and readability but anyway, the cases when it should be allowed to go are more numerous than when it should be mandated to stay.
What annoys me most about Oracle might well be not its own fault. I'm stuck to using Toad and some people claim it's actually Toad's limitations that I can't simply run an ad hoc SQL code in the editor and get the output straight back. Basically, if it's something more than the simplest form of SELECT, I have to use some sophisticated tricks to get the output back in the editor. This makes debugging worse than a nightmare.
It's not than I don't see good points in Oracle. I agree it's powerful, perhaps the most powerful RDBMS. It has a robust collection and cursor support, even its SQL sometimes is more logical than that of T-SQL (like CREATE TABLE vs SELECT INTO), but its rudimentary syntax and lack of debugging facilities makes working with it efficiently pretty tough.0 -
I still don't get what are the pros of having Dual, can you please elaborate? Even its name is misleading because for a dummy entity Single, Unity or even Abstract would be a much more appropriate word. I read some notes about the origins of this name but they didn't make much sense either. I agree about conforming to standards and readability but anyway, the cases when it should be allowed to go are more numerous than when it should be mandated to stay.
As I mentioned earlier, one thing I do is a lot of scripting. I almost always do it in Oralce, as opposed to doing it via Perl, Windows or shell scripting. Say I want to create a script, that creates a filename with today's date, I would use something like:
SELECT 'LOG_FILE_' || TRUNC(SYSDATE) || '.LOG' FROM DUAL;
Now that's only a trivial example, but it is immensely powerful.What annoys me most about Oracle might well be not its own fault. I'm stuck to using Toad and some people claim it's actually Toad's limitations that I can't simply run an ad hoc SQL code in the editor and get the output straight back. Basically, if it's something more than the simplest form of SELECT, I have to use some sophisticated tricks to get the output back in the editor. This makes debugging worse than a nightmare.
Tell me about it. There is a historical context to all of this, remember Oracle is around since the 70's, so there is a lot of historical baggage, especially in terms of screen output/terminals and all that stuff that I read about once.It's not than I don't see good points in Oracle. I agree it's powerful, perhaps the most powerful RDBMS. It has a robust collection and cursor support, even its SQL sometimes is more logical than that of T-SQL (like CREATE TABLE vs SELECT INTO), but its rudimentary syntax and lack of debugging facilities makes working with it efficiently pretty tough.
The other thing about Oracle is that if you want extra, you have to pay for it. If you want a fully-featured interface to the database, like TOAD for example, it has to be paid for. Same goes with all the management tools (E.g. Enterprise Manager).
However, the good side to it is, if you are proficient on the command line (as I like to think I am), I can jump between Oracle databases running on multiple platforms (as I do, Unix, Windows and even VMS) and achieve the same results.
I find there is a lot of fear about Oracle. Techies either love it or utterly detest it. I'm not quite sure where I am at the moment. 0 -
Advertisement
-
As I mentioned earlier, one thing I do is a lot of scripting. I almost always do it in Oralce, as opposed to doing it via Perl, Windows or shell scripting. Say I want to create a script, that creates a filename with today's date, I would use something like:
SELECT 'LOG_FILE_' || TRUNC(SYSDATE) || '.LOG' FROM DUAL;
Now that's only a trivial example, but it is immensely powerful.
Tell me about it. There is a historical context to all of this, remember Oracle is around since the 70's, so there is a lot of historical baggage, especially in terms of screen output/terminals and all that stuff that I read about once.
The other thing about Oracle is that if you want extra, you have to pay for it. If you want a fully-featured interface to the database, like TOAD for example, it has to be paid for. Same goes with all the management tools (E.g. Enterprise Manager).
However, the good side to it is, if you are proficient on the command line (as I like to think I am), I can jump between Oracle databases running on multiple platforms (as I do, Unix, Windows and even VMS) and achieve the same results.
I find there is a lot of fear about Oracle. Techies either love it or utterly detest it. I'm not quite sure where I am at the moment.
Still, I can't get whySELECT 'LOG_FILE_' || TRUNC(SYSDATE) || '.LOG' FROM DUAL;
is better or quicker thanSELECT 'LOG_FILE_' || TRUNC(SYSDATE) || '.LOG';
With regard to the ad hoc SQL, my gripe is that I can't simply write several statements and execute them in one go. Like this (sorry, no Oracle home and no Internet at work so I have to make it up):DECLARE X INT; BEGIN SELECT 5 INTO X [B]FROM DUAL[/B]; SELECT * FROM TABLE1 WHERE COLUMN1=X; SELECT 10 INTO X [B]FROM DUAL[/B]; SELECT * FROM TABLE1 WHERE COLUMN1=X; END;
Code similar to this doesn't run in Toad. I think even this truncated version doesn't work:DECLARE X INT; BEGIN SELECT 5 INTO X [B]FROM DUAL[/B]; SELECT * FROM TABLE1 WHERE COLUMN1=X; END;
Moreover, I can't even call an SP and get results back (calling a function is possible with that notorious FROM DUAL :cool: ) Actually, the only workaround for calling an SP is via the debug mode which is pretty messy and time consuming.0 -
Still, I can't get why
SELECT 'LOG_FILE_' || TRUNC(SYSDATE) || '.LOG' FROM DUAL;
is better or quicker thanSELECT 'LOG_FILE_' || TRUNC(SYSDATE) || '.LOG';
Because it's not a valid SQL statement - you have a SELECT without a corresponding FROM statement.With regard to the ad hoc SQL, my gripe is that I can't simply write several statements and execute them in one go. Like this (sorry, no Oracle home and no Internet at work so I have to make it up):DECLARE X INT; BEGIN SELECT 5 INTO X [B]FROM DUAL[/B]; SELECT * FROM TABLE1 WHERE COLUMN1=X; SELECT 10 INTO X [B]FROM DUAL[/B]; SELECT * FROM TABLE1 WHERE COLUMN1=X; END;
Of course you can do something like that. You need something like x:=5 instead of the SELECT INTO. I don't have the time right now to go into detail, but look at anonymous blocks.Moreover, I can't even call an SP and get results back (calling a function is possible with that notorious FROM DUAL :cool: ) Actually, the only workaround for calling an SP is via the debug mode which is pretty messy and time consuming.
Oh bejaney, you can. Again, time does not permit me to go into detail, but you can call procedures/functions/packages using the EXEC command.
I think we have de-railed this thread completely. 0 -
Because it's not a valid SQL statement - you have a SELECT without a corresponding FROM statement.
Of course you can do something like that. You need something like x:=5 instead of the SELECT INTO. I don't have the time right now to go into detail, but look at anonymous blocks.
Oh bejaney, you can. Again, time does not permit me to go into detail, but you can call procedures/functions/packages using the EXEC command.
I think we have de-railed this thread completely.
I believe it can be useful for the Op too as he was interested in Oracle. Well, I agree to close it for myself as I got all my questions answered Since DUAL bears no meaning whatsoever, its existence is doubtful - as I suspected 
Since an Oracle expert couldn't answer a simple question off the top of his head, it seems not simple at all - as I suspected Oracle rulez! 0 -
-
I believe it can be useful for the Op too as he was interested in Oracle. Well, I agree to close it for myself as I got all my questions answered Since DUAL bears no meaning whatsoever, its existence is doubtful - as I suspected Since an Oracle expert couldn't answer a simple question off the top of his head, it seems not simple at all - as I suspected Oracle rulez!
i dont get you. your question on dual has been answered. you need it because oracle will only accept fully formed select statements. dual is not a dummy table. it is a real table with 1 record. if you delete this record then it no longer works as expected. if you add another record to the dual table then hilarity will ensue.
The real question with Oracle is who the hell is Scott. and why does he like tigers so much.0 -
-
Advertisement