Oracle Database 11g: Advanced PL/SQL (1Z0-146)…


Lewis Cunningham recently mentioned on twitter that he had taken this exam and it reminded me I had been meaning to take it ever since it was announced in beta, but never actually got round to it. A quick search of my blog revealed it’s nearly 2 years since I mentioned I wanted to sit this exam (here).

So yesterday I sat and passed the exam and here are some thoughts on it (without mentioning specifics about the questions)…

This is the first time I’ve sat an OCP exam done by Pearson VUE and I was pleasantly surprised. The test interface is a little cleaner and more modern looking that previous exams I’ve taken. There was one minor glitch in that every time there were two exhibits they were shown in the wrong order, such that when the question asked you to look at Exhibit 1, the content it was referring to was in Exhibit 2 and vice versa. For most questions this was obvious, but there was one that if you followed the instructions to the letter would have made the question impossible to answer correctly. Of course, if you had seen the glitch on the other questions it wasn’t a leap to assume the same problem was present on this question also.

The SQL and PL/SQL exams use lots of exhibits and some can be quite big. In a number of cases the questions can be answered without referral to the exhibits if you know your stuff because the incorrect answers reference “facts” about PL/SQL that are blatantly not true. Even when you do have to refer to the exhibits, I would suggest you treat it like you are debugging code and read the question and possible answers before reading the exhibit. Why? You don’t start debugging code before you know what the errors are. In the same way, if you know the possible answers you can ignore the majority of code in the exhibit and focus on the area of interest. Reading the whole exhibit would take ages, so be efficient about it. Ignore the fact the question tells you to look at the exhibits first and you will answer the questions much faster.

The exam is very much 11.1 focussed, so there are a few questions where the “correct” answer is somewhat dubious if you have been using 11.2 for a while. Keep in mind the 11.1 state of play and don’t try and be too clever. That said, for the most part the 11.1/11.2 issue is irrelevant.

The context of a question is a very important thing and one that I think the OCP questions sometimes lack. As an example, one question asks you which of the 4 answers can be produced by a specific built in package. The correct answer as far as I’m concerned is all four, but it is rare that questions need all answers to be checked, so this is where you need to put on your PL/SQL hat. As a direct call from PL/SQL only two answers are correct, but if you call the API via a query all four answers are possible. So you have to make a judgement call as to what context the examiners are using. I figured, as it’s a PL/SQL exam, I should ignore SQL and that turned out to be the correct move.

There were only two questions that I didn’t know the complete answer to directly, but in both cases, using a process of elimination I was pretty confident I had a reasonable shot at the correct answer. Even if you don’t know every fact relating to a subject you can usually elimitate some of the answers, which will up your odds. Don’t panic.

The exam is scheduled for 90 mins and I was done in a little over 30 mins. The examiner gave me a bit of a look when I came out that early, and another when he saw my 100% score. I just smiled and told him I’d been doing PL/SQL for 16 years and had been teaching the contents of this exam for over 2 years. It would have been a little tragic if I hadn’t come close to 100% really…

So I guess in the next few days I’ll recieve my “Oracle Advanced PL/SQL Developer Certified Professional” bumf. I quite fancy doing something else now. I obviously spent too many years in education and I need an exam fix every so often. 🙂



Author: Tim...

DBA, Developer, Author, Trainer.

18 thoughts on “Oracle Database 11g: Advanced PL/SQL (1Z0-146)…”

  1. Thanks Tim, interesting reading.

    I’m doing PL/SQL for some years now, but as you might know you are almost ever running in the same circles because of the stuff you (have to) do in your company – so a look beyond my daily work is something I really lack (system packages and stuff).

    I never did the OCA/OCP in the first place, because I didn’t have a real chance to finish it (we don’t use Forms) . But for some few years there is an PL/SQL OCP now, so I plan to do it this year (finally). OCA first, OCP later.

    Do you have any good book to prepare for this tests? I found lots of stuff for DBA OCP, but nothing for PL/SQL 🙁

  2. Hi.

    I don’t really have any recfommendations. I SQL exam years ago when doing the OCP DBA (7.3) and did the PL/SQL exam just based on my years of programming. No real preparation.

    As for the recent exam, I’ve been teaching a 2 day course for the last couple of years that includes most of that syllabus. As a result, I didn’t really go looking for a certification guide for any of this track.



  3. Tim,

    Congrats! 100% is awesome. You did better than me.

    I meant to blog about this topic but it’s stuck somewhere in my todo pile. It is a very large pile. 😉

    There were two areas where I did poorly. The first was setting up and using external C programs. I haven’t done that at all since 2003 or 2004 and even then I didn’t do it much.

    The second was lobs. I did well with the secure files stuff but I think I missed every one of the questions on setting up the tables for LOBS and using dbms_redefinition to convert from lobs to secure files.

    I can’t complain too much because a. I did pass and b. they do cover that in the syllabus.

    From what I’ve heard, they have a large pool of questions and each test will show a different variety of questions. I got the LOBby batch. 😉

    Oh well. Next time (thinking about the data warehouse cert) I’ll study the syllabus and make sure I bone up on those topics specifically.

    To CMI’s question, I don’t know of any study material and I have to wonder how many people are OCP Dev certified? You and I are the only ones I know of. Does Oracle publish a list? I think I might go check. Be interesting to see how popular the cert is.

    Congrats again.


  4. Hi.

    Yes, there is a pool of questions, so you can’t really compare scores between people. For all you know you would have got 100% on my test and I would have failed yours. 🙂



  5. hah. I seriously doubt you would have failed. I seriously doubt I would have gotten 100%. 😉

    I just hope I don’t have to do lobs any time soon. heh


  6. Hey Tim,

    Congrats and not surprised that Mr. PL/SQL like yourself would pass with flying colors 🙂

    After all you did write a book on advanced PL/SQL


    PS: Yes, I do like the new Pearson VUE interface better than Prometric as well. When I took and passed the Oracle R12 EBS OCP exam, I did this on Pearson and enjoyed it better than the old testing platform.

  7. Hi Tim,

    you mentioned that you didn’t use a book to prepare. What if I am not that good (or insecure :-)) and I want to prepare using books. Which book(s) would you recommend?


  8. Hi.

    You will probably get most of the way there by reading the PL/SQL user guide in the Oracle documentation, which is free.



  9. Tim
    I just failed the Adavanced PL/SQL Exam by 5 points. I cannot find the Oracle 11.1 PL/SQL user guide that you mention. Can you give me a link to it or a link to any other materials that you would suggest.

  10. An update for myself, since I now passed both 144 and 146.

    I have to say: 146 (OCP) was way easier then 144 (OCA). 144 was just sick because of the very short timespan (90 Minutes) and the huge load of questions (80). So you had ~1 minute per question. The problem was: you can not think about answers, marking questions to review them later is pointless because you don’t have f**king time to do this.

    Maybe I was unlucky with the questions asked in the two tries but I doubt it. I had the feeling the huge code chunks you *had* to read (because of the “whats the outcome?”) reduced the time you had for reading AND answering the “easy” questions to maybe 10-15 seconds.

    146 felt like holidays to me after this p.i.t.a. called 1Z0-144.

    And when I talked to our DBAs I wondered if 144 is “working as intented” – they had 120minutes for 60 questions which consists just of some few sentences (so you didn’t use up your time for this question by just reading it carefully) and almost no code blocks.

    Ok I’ll stop the rant about this painful 144 here and be happy I finally made it.

  11. Hi.

    I did 147, which has the same time limit, but 14 less questions.

    According to the website, 144 should be 80 questions in 90 minutes, so it appears to be correct. 🙁

    Anyway, well done. Passing these exams is a skill in its own right, quite apart from the technical knowledge required. 🙂



  12. Hi Guys ,
    I’m an oracle certified proffessional and I want to do the Advanced PL/SQL exam .Please let me know which material can I use to do this.


  13. Congratulation on passing this exam, Tim. And thanks for your thoughts about it.
    I am going to pass this exam eventually. I’m from Poland and there’s no exams in polish language. So I thing Oracle should give more time (for example 30 minutes) to the people how have to take exams not in their own language.


  14. I am an honored OCP too. My experience with the exam was good. Question to time ratio was pretty well as compared to 144 exam. But the questions were tricky and test your understanding to the depth.

    I used “Oracle Advanced PL/SQL Developer Professional Guide” by Saurabh K. Gupta to prepare for the exam. The book is really good and helped me alot in understanding the concepts.

Comments are closed.