Posts Tagged ‘oracle sertifikācija’

My Oracle certifications what-ifs and lessons learned: have good skills and you’ll not need luck

My takeaway from the Big Data course “Data processing systems” I was blogging about is passing the course exam and reusing the knowledge for an Oracle certification.

I passed course exam in the end of December and I had a month till the next semester and I decided to use it for additional learning to apply for Oracle certification Oracle Big Data 2017 Certification Implementation Specialist.

I will return to Big Data topic when I have the next Big Data course in following semesters. This semester my challenges are

  • Data warehousing
  • Statistics.

I hope to start blogging about them soon.

Meanwhile in this blog entry may I share some my lessons learned from Oracle certifications,

they have about 200 different certifications in 10 categories. You can browse:

–Certifications and exams required (NB: combinations available)

–Exams and what certification this exam is useful for

I have done four (I know a guy holding 32) and it makes me think I have some things to say about the process. I have

Credentials are granted based on a combination of passing exams, training and performance-based assignments, depending on the level of certification.

Oracle has a good marketing:

  • “Get the job you want – earn an Oracle Certification”
  • “86% of hiring managers surveyed say that IT certifications are a priority during the candidate evaluation process” etc.


How to choose from exam set?

It depends on

  • your plans towards certification path
  • value (eg passing the exam 1Z0-071 suits for 4 certifications!)
  • project requirements
  • your new job or career change dreams (I did is several years ago and I know my OCA certificate was the trump when I, at that time Informix developer, was confirmed at my current Oracle expert position)
  • your current skills (why not go for some ‘done’? – I do that a lot)
  • Oracle environment available
  • Oracle versions covered in exam
  • exam price
  • accessibility (proctored (classroom)/non proctored (online))
  • exam length and passing score
  • for fresh exams there might be less training materials available
  • your attitude and readiness to learn and challenge yourself (I do that a lot)

If exams are similar, then search like : ‘1Z0-071 vs 1Z0-061’ etc. I usually choose the hardest one to learn more.

Some time ago SQL was only a tool for me. Like hammer, partially because I was working in Informix and had no feeling I can do miracles in SQL and Informix SPL (apologies for liking Oracle SQL and PLSQL better).

Little by little I realized I want more and decided to study Oracle in parallel to get ready for changing job profile. I read about the Order of the Wooden Pretzel, inspired by the famous Steven Feuerstein quote “SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens”. To prove that, international SQL challenges were held and winner becomes Knight of the Order of the Wooden Pretzel.

May the quickest, most entertaining, most educational, most creative, and somewhat readable solution prevail!

In recognition that SQL is not the only language in which enterprising developers can create pretzels on hallucinogens, the challenge is also open to NoSQL solutions. Well, it seems this has been put on hold for a while but maybe I was not googling long enough.


Oracle strongly prohibits cheating

You sign an agreement before exam (example – including

Bad news:

  • you can accidentally stumble into brain dumps forums/topics (and you may not even know they are…) – just be aware
  • practice tests you buy might appear attractive wrapped dumps

Good news:

  • authorised practice tests available for most exams (but not for all, for example, Big Data exam does not have).

Some characteristics of the exams in SQL and PLSQL:


Exam day

  • Shortly: pay for exam and book time via PearsonVUE in BDA
  • Read confirmation email what you need to bring with you
  • Note, you must have TWO person documents with you:
    • passport and driver licence, for example
  • Arrive ~20 mins earlier (there is coffee and cookies in BDA)
  • You will be photographed AND face will be compared to previous exams if any (so might be several photos taken until matches)
  • You will place all belongings in a lockable wardrobe
  • They’ll give
    • a special pen like marker
    • 1-2 sheets of laminated A4 paper (depends on exam); you may not erase your notes
    • ear plugs
  • You will be guided to a computer room and set until you see [Start test]
  • You will be videomonitored all the time
  • You may leave the room to WC, time is not stopped

Exam software

Before starting exam you have option to choose intro.

Interface changes between versions but the idea stays the same:

  • Question content (may be with [Exhibit] or [View] buttons for popups)
  • Answer options – radio buttons or checkboxes
  • Checkbox [Mark] for later review
  • Buttons [Previous] and [Next] – you always can browse
  • Button [Review] (sub-options – marked ones or all)
  • Button [End exam] (you may press it any time; re-asks before quit)
  • Time counter showing how much time left like (00:46:32)
  • Question counter like ‘Question 17 of 63’

Do not leave marked unanswered question – points are not negated for qwrong answer and guessing comes for free – because exam time flies very fast.

English language, no dictionary available, no time discount.

Question types

  • May contain [Exhibit] or [View] buttons for popups, usually showing table descriptions, rarely questioned target report template
  • Radio buttons (one from four options usually)
  • Checkboxes, usually 4 to 6
    • Choose two (or three)
    • Choose all that apply
  • May contain options ‘all of above’ and/or ‘none of the above’
  • May be combined question statements and options like
    1. A-4, B-1, C-3, D-1, E-1, F-3
    2. A-3, B-4, C-1, D-1, E-1, F-3
    3. A-1, B-2, C-4, D-2, E-2, F-1
    4. A-1, B-3, C-3, D-2, E-2, F-2
    5. E. A-1, B-1, C-1, D-2, E-1, F-3

After exam

    1. You will be given a printout that your results will be available online within in about 30 min
    2. Usually there are within 10 minutes (if you have issue with CertView account you can some hours later call Oracle support and by your testing ID they will tell results by phone)


  • Earlier they used to send printed Certificates and cards but now are saving environment and does not send
  • You will receive emails. Approx 10 minutes after exam –  about availability of results, about 24 hours later about e-certificate and about 48 hours later about badge in Acclaim.


Associate level certification


Professional level certification



Learning – what?

    • I found Gints Plivna blog to be very useful, especially NULL:
    • Stay real and stay calm. Don’t try to memorize REGEXP
    • Enjoy the process and have fun
    • I subscribed to various a la ‘Daily SQL challenge’
      • in LinkedIN
      • via email
    • To SQL, PL/SQL groups in LinkedIN and Facebook

Learning – how?

    • Despite experience it takes time, if you target to add value for yourself
      • depends on experience. Eg, OCA about a month daily 1-2 hours
      • read topic by topic in manuals
      • play a lot in Oracle
      • google pros, cons, examples
      • drill practice test
    • Set mindset to do exercises and daily tasks correct at once
    • Consider buying practice tests and drilling daily by portions
    • Remember: passing score is never 100%! You MAY afford to have mistakes
      • Helpful: fast recognising of obvious errors to lessen options
      • Questions containing functions may serve as hints for others (like syntax of NVL2 or TO_CHAR or SUBSTR)
    • Internet full of incorrect examples and wrong answers, so it is crucial to distinguish right from wrong and test, test, test
    • if this is not Oracle page and there is a question and answer like ‘Correct: B’ without explanation it sounds stolen dump – be aware
    • often accompanied with note ‘any fool can see that correct is B’Many people enjoy writing blogs about preparation, I was reading them also.







Practice tests – see

Oracle Database 11g: SQL Fundamentals I 1Z0-051

(it was easier then real exam)

30 days online access

99 $



Oracle Database 11g: Program with PL/SQL 1Z0-144 30 days online access

99 $



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

(again – it was easier then exam)

30 days online access

99 $



There are plenty of practice tests if you google. Remember: You sign agreement to not use unauthorised materials

– Practice Exam:Oracle authorized practice exam from Transcender

Examples of challenges




  • There is always a chance something you do not know appears
  • There might be questions like ‘what is the 3rd parameter of DBMS_RLS.REFRESH_GROUPED_POLICY’
  • And, eg, which of the answers can be produced by a specific built in package (DBMS_REDEFINITION, DBMS_HPROF, DBMS_LOB, UTL_COLL etc)
  • Don’t panic – your background is good enough to do a good guess. Remember, you may afford to have mistakes

And, after all, even failing is not the end or world. Very many people fail. One of my friends passed PLSQL exam in 4th try.

Good skills and good luck!



PL SQL developer sertifikācijas atmiņu stāsts

Programmēšanas eksāmenos velns ir detaļās (vs lomu sertifikācijās velns ir jēdzienos). Lai iegūtu Oracle PL/SQL Developer Certified Associate sertifikātu, nokārtoju divus eksāmenus –

  • Oracle Database: SQL Fundamentals I
  • Program with PL/SQL.

Abi ir darba ikdienas sastāvdaļa, jo lielos valsts mēroga projektos sistēmanalītiķis nereti ir cilvēks – orķestris, kam tabulu, vaicājumu, apdeitu, skatu, procedūru un trigeru diriģēšana ir ikdiena – Informix, Oracle, MS SQL, Sybase… It kā jau SQL arī Āfrikā ir SQL, tomēr apaudzēts ar fīčām un nav viegli tā uzreiz atcerēties, kurā DBVS varchar2 noteikti jānorāda minimālais garums, kurā pēc noklusēšanas ir 1 un kurā tāda varchar2 vispār nav.

Tātad, lai dokumentāli apliecinātu zināšanas, raitā tempā jādemonstrē kompilatora prasme. ~1.5 min uz jautājumu – jāizlasa, jāsaprot, parasti satur 4 atbilžu variantus, kas katra ir citāda funkciju virkne. Līdz ar to dot slēdzienu par vienu virkni ir ~20 sekundes laika. Jo ātrāk mācēsi noskanēt virkni uz tipiskām kļūdu ķeramvietām, jo vairāk laika paliks pētīt aizdomīgi pareizās:

  • Vai strādās select lower(replace(trim(‘son’ from cust_last_name),’An’,’O’)) from customers?
  • Kas notiek, kad izpilda select initcap(cust_first_name||’’||upper(substr(cust_city,-length(cust_city),2))) from customers?
  • Vai to_number(to_number(prod_price,’$99,999.99’)*.25,’$99,999.00’) atgriezīs $6,509.75?
  • Vai select lpad(substr(cust_name,instr.(cust_name,’’)),length(cust_name)-instr(cust_name,’’),’*’) from customers where instr.(cust_name,’’,-1,2)<>0 atlasīs tikai tādus klientus, kuriem ir 3 vārdi?
  • Vai no sysdate var atņemt pī?

Un tā tālāk. Nācās atsvaidzināt teoriju. Iesaku Ginta Plivnas blogu – izcili mācību materiāli!!!, lasīju, uzslēdzu smadzenes uz mazkompilatora režīmu (nost ar zinātniskās bakstīšanas metodi “hmm, nez, šitāds kompilēsies?” un “tad jau piečibinās, ja šitas neatgriež”), un izvirzīju mērķi uzreiz uzrakstīt pareizi, zīmēju Venna diagrammas uz papīra.

Tehniskie izaicinājumi

Ja lieta ir gadiem lietota, tad lielos tramvajos jau aiziešana līdz eksāmena telpai ir 3/4 no uzvaras. Tomēr izgulēties uz lauriem eksāmenos nevar:

  • Pieraksta izvirtības uz LV neierastiem formātiem – sākot ar tiem $1,234.67, turpinot October 25th DD-MON-RR un visbeidzot AM un PM. Nācās mācīties tos $99G99D00, $9,999V99, fxDay, fmDdspth…
  • Ne visas funkcijas no eksāmena tvēruma ikdienā lietoju, tāpēc mācījos (kā, piemēram, coalesce), ko dara, argumentu skaits, obligātie, defaultie, kādi
  • Argumentu vērtības un uzvedība. Piemēram, ja instr norāda negatīvu virknes garumu, tad izkritīs ar kļūdu, nevis uzskatīs par null, 0 vai 1. Atceroties šo, bija viegli ātri izslēgt pa kādam atbilžu variantam
  • Funkciju virknes – vai, kā eksāmenā tādas mīl… nvl2(coalesce(decode(substr(instr(length(…. un variācijas, piemēram, – vai var joinot uz A = length(nvl(substr(coalesce(B,instr(A,1,2)),B),’null’))? uh, kādā tempā  eksāmenā nācās galvā šitos kompilēt :)
  • Tipu konversijas, kur ir, kādas un kur nav pieļaujamas – decode, nvl, nvl2, kā arī ar tiem (to_number(to_date(to_char(… – ārpus eksāmena ar tiem palīdz F1 un gūgle
  • Kas notiek ar NULLiem dažādās funkcijās (paldies, Gint!), vai var taisīt substr no null un vai nullif (1,null) ir tāds pats, kā nullif(null,1)? Ja concat pieliek null, vai viss rezultāts kļūst null?
  • Aliasi kolonnām, joiniem, groupiem, orderiem un havingiem – kur var lietot, kur nevar, kur vajag, kur vienalga
  • Ķer uz līdzīgām funkcijām – klasika ir months_between, days_between, years_between
  • Daudz JOINu ar visa veida pierakstiem (+), OUTER, ķer uz USING un ON atšķirībām, uz aliasu izmantošanas niansēm

Mācību procesa izaicinājumi

  • Internetā atrodami eksāmena jautājumiem līdzīgi piemēri ar nepareizām atbildēm, tāpēc vai nu jāpārbauda visas atbildes, vai svarīgi vismaz čuja līmenī mācēt nojaust, ka tā varētu nebūt pareiza. Es gandrīz visus piemēra jautājumus izspēlēju savā rotaļlaukumā – pētīju gan pareizo, gan nepareizos
  • Sagūglētiem piemēriem nav atbilžu skaidrojuma. Pateikts, ka pareizā ir “B”, bet kāpēc? Variācija: rakstīts, ka pareizas ir vairākas (A,C,D…), lai gan eksāmenā pareizā var būt tikai viena. Nu tad jāsaprot, kur āķis. Forumos var atrast – lūdzu, paskaidrojiet, kāpēc… Bet ir daudz atbilžu no sērijas “kurš tad ir tāds idiots, ka neredz, ka pareizais protams ir B”
  • Drilltests vietām ir pretrunā gan ar mācību materiāliem, gan praksi. Jau minētā varchar2 gadījumā – drillā pareizā atbilde skaitījās, ka tipa definīcijā var nenorādīt garumu, kamēr materiālos un praktiskajā Oracle krita ar kļūdu, ja nenorāda varchar2 garumu
  • Nebija pieejama precīzi tāda vide, par kādu ir eksaminācija. Tajā, kuru es lietoju, piemēram, varēja kolonnu secību alterēt (līdz ar to prasti jāiegaumē, ka eksāmenā uz “vai var esošai tabulai nomainīt kolonnu secību?”, jāatbild “nē”)
  • Drilltests, kuru dod BDA, ir vieglāks nekā reālais eksāmens. Par to parunāju arī ar BDA cilvēkiem, bet viņi jau neko ietekmēt nevar, tāds tas ir un alles. Tāpēc brīdinu – uz drillu vien nepaļaujieties

Sākumā gāja grūti ar kompilēšanu galvā – nepierasti tomēr. Centība rezultējās ar 91% (pietiktu ar 60% – slieksnis pazems, jo eksāmens IR ķēpīgs).

%d bloggers like this: