Semester 1 Final Exam Oracle Pl SQL (3)

Test:Semester1FinalExam 1. You Youh hav ave ecr crea eate ted da afu func ncti tion onn nam amed edN NEW EWFU FUNC NC. .Yo You uno now wch chan ange ges som ome eof oft the hef f unctioncode,andtrytorecreatethefunctionbyexecuting: CREATEORREPLACEFUNCTIONnewfunc....; Whathappens? MarkforReview (1)Points Thecommandfailsbecausethefunctionalreadyexists. Thefunctionisautomaticallydroppedandthenrecreated.(*) Thecommandfailsbecauseyoushouldexecute: Thecommandfailsbeca useyoushouldexecute:CREATEANDREPLACE....; CREATEANDREPLACE....; AsecondfunctionnamedNEWFUNC_2iscreated. Thefunctionisdroppedbutnotrecreated.

Correct 2. Afu Afunc ncti tion onn nam amed edM MYF YFUN UNC Cha has sbe been enc cre reat ated ed. .Th This isf fun unct ctio ion n acceptsoneINparameterofdatatypeVARCHAR2 acceptsoneINparamet erofdatatypeVARCHAR2andreturnsaNUMBER.You andreturnsaNUMBER.Youwantto wantto invokethefunctionwithinthefollowinganonymousblock: DECLARE v_var1NUMBER(6,2); BEGIN --LineA END; WhatcouldbecodedatLiineA? MarkforReview (1)Points myfunc('Crocodile'):=v_var1; myfunc(v_var1):='Crocodile'; myfunc(v_var1,'Crocodile'); v_var1:=myfunc('Crocodile');(*) myfunc('Crocodile',v_var1);

Correct 3. CREATEFUNCTIONADD_EM (aNUMBER:=1, bNUMBER:=2) RETURNNUMBER ISBEGIN RETURN(a+b); ENDADD_EM;

Co n s i d e r t h e f o l l o w i ng f u n c t i o n :

WhichoneofthefollowingblockswillNOTworkcorrectly? MarkforReview (1)Points DECLARE xNUMBER; BEGIN x:=add_em(b=4); END; (*)

DECLARE xNUMBER; BEGIN x:=add_em(4); END; DECLARE xNUMBER; BEGIN x:=add_em(4,5); END; DECLARE xNUMBER; BEGIN x:=add_em; END; Noneofthemwillwork.

Correct 4. You Yout try ryt to ocr crea eate tea af fun unct ctio ion nna name med dMY MYFU FUNC NC. .Th The efu func ncti tion on doesnotcompilecorrectlybecausethereareerrorsinyourcode.Whic doesnotcompilecorrectlybecausethereare errorsinyourcode.WhichDiction hDiction aryvi aryview ewcan canyo youq uquer ueryt ytos osee eethe theer error rors? s? Markf Markfor orRev Review iew (1)Points

USER_SOURCE USER_ERRORS(*) USER_OBJECTS USER_DEPENDENCIES USER_COMPILES

Correct 5. ure urean and da afu func ncti tion on? ? (1)Points

Whi Which cho of fth the efo foll llow owin ing gis isa ad dif iffe fere renc nce ebe betw twee een na apr proc oced ed Mark Markf for orR Rev evie iew w

AprocedurecanincludeDMLstatements,butafunctioncannot. AfunctionmusthaveatleastoneINparameter,whileparametersareop AfunctionmusthaveatleastoneINparameter ,whileparametersareopt t ionalforaprocedure. AprocedurecanreturnaBOOLEANdatatype,whi Aprocedurecanreturn aBOOLEANdatatype,whileafunctioncannot. leafunctioncannot. AfunctioncanbeusedinsideaSQLstatement, Afunctioncanbeused insideaSQLstatement,whileaprocedurecannot whileaprocedurecannot. . (*) AprocedurecanincludeanEXCEPTIONsection, Aprocedurecaninclud eanEXCEPTIONsection,whileafunctioncannot. whileafunctioncannot.

Incorrect.RefertoSection9Lesson1. 6. Whi Which cho of fth the efo foll llow owin ing gbe best std des escr crib ibes esa as sto tore red dfu func ncti tion on ? Ma r k f o r R e v i ew (1)Points Asubprogramthatmustreturnexactlyonevalue.(*) AsubprogramthatmusthaveatleastoneINparameter. AsubprogramthathasnoOUTorINOUTparameters.

Asubprogramthatexecutesautomaticallywhen Asubprogramthatexec utesautomaticallywhenaDMLstatementisexecut aDMLstatementisexecute e donatable. Asubprogramwhichinvokesanothersubprogram.

Correct 7. ctio ctions ns? ?(C (Cho hoos ose e3) 3) (1)Points

Whi Which cho of fth the efo foll llow owin ing gis isa ab ben enef efit ito of fus user er-d -def efin ined edf fun un Mark Markf for orR Rev evie iew w (Chooseallcorrectanswers)

Theycanaddbusinessrulestothedatabasean Theycanaddbusiness rulestothedatabaseandcanbereusedmanytime dcanbereusedmanytimes s .(*) TheycanbeusedinaWHEREclausetofilterd Theycanbeusedina WHEREclausetofilterdataandtherebyincrease ataandtherebyincreasee e fficiency.(*) Theycandothesamejobasbuilt-insystemfu Theycandothesamej obasbuilt-insystemfunctionssuchasUPPERand nctionssuchasUPPERand ROUND. TheycanoftenbeusedinsideSQLstatements.(*)

Correct 8. Why Whywi will llt the hef fol ollo lowi wing ngs sta tate teme ment ntf fai ail? l? SELECTemployee_id,tax(p_value=>salary) FROMemployees; MarkforReview (1)Points User-definedfunctionsarenotallowedintheSELECTclause Namenotationisnotallowed(*) Thedatatypeforthetaxvariabledoesnotma Thedatatypeforthe taxvariabledoesnotmatchthedatatypeforsal tchthedatatypeforsala a ry Thestatementwillexecuteandnotfail

Correct 9. The Thef fun unct ctio ion nav avg_ g_an ann_ n_sa sal lre retu turn rns sth the eav aver erag age ean annu nual als sal al aryforaparticulardepartment.Theexampleb aryforaparticulard epartment.Theexamplebelowisavaliduseofof elowisavaliduseofofthisfun thisfun ction.TrueorFalse? SELECTfirst_name,last_name FROMemployees WHEREavg_ann_sal(20)>15000; MarkforReview (1)Points True(*) False

Correct 10. 10. Whi Which chD Dat ata aDi Dict ctio iona nary ryv vie iew wca can nbe beu use sed dto tod dis ispl play ayt the hed d etaile etailedc dcode odeof ofa apro proced cedure urein inyo your ursch schema ema? ? Markf Markfor orRev Review iew (1)Points USER_PROCEDURES USER_OBJECTS USER_SOURCE(*) USER_SUBPROGRAMS Noneoftheabove.

Correct 11. Youw Youwant antto tose seet ethe henam names, es,mo modes desan andd ddata ataty types pesof ofth thef eform ormal alpar parame ameter ter soffunctionMY_FUNCinyourschema.Howcan soffunctionMY_FUNC inyourschema.Howcanyoudothis?(Choosetwo) youdothis?(Choosetwo) Markfor Review (1)Points (Chooseallcorrectanswers) QueryUSER_PARAMETERS QueryUSER_SOURCE(*)

QueryUSER_FUNCTIONS SHOWPARAMETERmy_func; DESCRIBEmy_func;(*)

Incorrect.RefertoSection9Lesson4. 12. 12.

Exa Exami mine net the hef fol ollo lowi wing ngc cod ode: e:C CRE REAT ATE EPR PROC OCED EDUR URE Epa pare rent nt

ISBEGIN child1; child2; EXCEPTION WHENNO_DATA_FOUNDTHENNULL; ENDparent; NeitherCHILD1norCHILD2hasanexceptionhandler. WhenPARENTisinvoked,CHILD1raisesaNO_DAT WhenPARENTisinvoked ,CHILD1raisesaNO_DATA_FOUNDexception.Whath A_FOUNDexception.Whathappensne appensne xt? MarkforReview (1)Points PARENThandlestheexception,thenCHILD1continuestoexecute. CHILD1endsabruptly.PARENThandlestheexcep CHILD1endsabruptly. PARENThandlestheexceptionandthenends.CHILD tionandthenends.CHILD2 2 doesnotexecute.(*) CHILD1endsabruptly,PARENThandlestheexcep CHILD1endsabruptly, PARENThandlestheexception,thenCHILD2execute tion,thenCHILD2executes s . CHILD1endsabruptly,PARENTalsoendsabruptl CHILD1endsabruptly, PARENTalsoendsabruptlyandreturnsanunhandle yandreturnsanunhandled d exception. PARENTdoesnotcompilebecauseyoucannotuse PARENTdoesnotcompil ebecauseyoucannotuseNULL;inanexceptionha NULL;inanexceptionhan n dler.

Correct 13. 13. User UserR REY EYHA HAN Ncr crea eate tes sth the efo foll llow owin ing gpr proc oced edur ure: e: CREATEPROCEDUREproc1AUTHIDCURRENT_USER IS v_countNUMBER; BEGIN SELECTCOUNT(*)INTOv_count FROMtom.employees; END;

UserBILLwantstoexecutethisprocedure.Wha UserBILLwantstoexe cutethisprocedure.WhatprivilegeswillBILLne tprivilegeswillBILLneed? ed? MarkforReview (1)Points EXECUTEonREYHAN.PROC1andSELECTonTOM.EMPLOYEES(*) EXECUTEonREYHAN.PROC1 SELECTonTOM.EMPLOYEES BILLneedsnoprivileges Noneoftheabove.TheprocedurewillfailtocompilebecauseREYHANdo Noneoftheabove.Theprocedurewillfailto compilebecauseREYHANdoe e snothaveSELECTprivilegeonTOM.EMPLOYEES.

Correct 14. 14. Whe When nmu must stA AUT UTHI HID DCU CURR RREN ENT_ T_US USER ERb be ein incl clud uded edi in nan ana aut uton onom om oustr oustrans ansact action ionsu subpr bprogr ogram? am? Markf Markfor orRev Review iew (1)Points WhendeclaringDefiner'srights WhendeclaringInvoker'srights(*) WhenusingCOMMITorROLLBACK WhenusingGRANTonthesubprogram

Correct 15. 15. User UserB BOB OBc cre reat ates esp pro roce cedu dure reM MYP YPRO ROC Cus usin ing gth the ede defa faul ult tDe Defi fi ner'sRights.BOBthenexecutes: GRANTEXECUTEONbob.myprocTOted; WhenTEDinvokesBOB.MYPROC,whoseprivilegesarechecked? MarkforReview (1)Points TED'sprivileges

PUBLIC'sprivileges SYSTEM'sprivileges BOB'sprivileges(*) ORACLE'sprivileges

Correct

Section8 (Answerallquestionsinthissection) 16. 16. Whi Which cho of fth the efo foll llow owin ing gar are ech char arac acte teri rist stic ics sof ofP PL/ L/SQ SQL Lsu su bprograms bprogramsbutn butnotof otofanon anonymous ymousPL/S PL/SQLbl QLblocks? ocks?(Cho (Chooset osethree. hree.) ) MarkforR MarkforReview eview (1)Points (Chooseallcorrectanswers) Cantakeparameters(*) Arestoredinthedatabase(*) CanbeginwiththekeywordDECLARE Arenamed(*) Arecompiledeverytimetheyareexecuted

Correct 17. 17. Ap Apro rogr gram amme mer rwa want nts sto toc cre reat ate ea aPL PL/S /SQL QLp pro roce cedu dure ren nam amed edE E MP_PROC.Whatwillhappenwhenthefollowingcodeisexecuted? CREATEORREPLACEPROCEDUREemp_procIS v_salaryemployees.salary%TYPE; BEGIN SELECTsalaryINTOv_salaryFROMemployees WHEREemployee_id=999; DBMS_OUTPUT.PUT_LINE('Thesalaryis:'||v_salary); END; MarkforReview (1)Points

ThestatementwillraiseaNO_DATA_FOUNDexcep Thestatementwillrai seaNO_DATA_FOUNDexceptionbecauseemployee_id tionbecauseemployee_id9 9 99doesnotexist. Thestatementwillfailbecausethelastline Thestatementwillfai lbecausethelastlineofcodeshouldbeENDemp ofcodeshouldbeENDemp_  _  proc; Thestatementwillfailbecauseyoucannotdec Thestatementwillfai lbecauseyoucannotdeclarevariablessuchasv_ larevariablessuchasv_s s alaryinsideaprocedure. Theprocedurewillbecreatedsuccessfully.(*) Thestatementwillfailbecausetheprocedure Thestatementwillfai lbecausetheproceduredoesnothaveanyparamet doesnothaveanyparamete e rs.

Correct 18. 18. The Thef fol ollo lowi wing nga are ret the hes ste teps psi inv nvol olve ved din inc cre reat atin ing, g,a and ndl l atermodifyingandre-creating,aPL/SQLproce atermodifyingandrecreating,aPL/SQLprocedureinApplicationExpre dureinApplicationExpress.Inwh ss.Inwh atsequenceshouldthesestepsbeperformed? Retrievethesavedcodefrom"SavedSQL"inSQLCommands Executethecodetocreatetheprocedure Executethecodetore-createtheprocedure Clickonthe"Save"buttonandsavetheprocedurecode ModifythecodeintheSQLCommandswindow TypetheprocedurecodeintheSQLCommandswindow MarkforReview (1)Points F,C,A,B,E,D F,B,D,A,E,C(*) E,D,F,C,A,B F,B,D,E,A,C F,B,C,D,E,A

Correct 19. 19. An Anes este ted dsu subp bpro rogr gram amc can ano onl nly ybe bei inv nvok oked edf fro rom mth the ema main ins s ubpr ubprog ogra ram. m.T Tru rue eor orF Fal alse se? ? Mark Markf for orR Rev evie iew w (1)Points

True(*) False

Correct 20. 20. Ap Apro rogr gram amme mer rcr crea eate tes sa aPL PL/S /SQL QLs sub ubpr prog ogra ram mwh whic ich his isc com ompi pi ledandstoredinthedatabase.Twoseparateu ledandstoredinthe database.Twoseparateusersthenexecuteanappl sersthenexecuteanapplicationw icationw hichinvokesthissubprogramfourtimes.Howm hichinvokesthissubp rogramfourtimes.Howmanytimesmustthesubpro anytimesmustthesubprogramber gramber ecomp compil iled ed? ? Mark arkf forR orRev evie iew w (1)Points Twice Fourtimes None(*) Eighttimes Once

Correct 21. Which Whichof ofth thef efoll ollowi owing ngare arech chara aracte cteris ristic ticso sofP fPL/S L/SQL QLsto stored redpr proce ocedur dures? es? (Cho (Choos ose eth thre ree. e.) ) Mark Markf for orR Rev evie iew w (1)Points (Chooseallcorrectanswers) TheyarenamedPL/SQLblocks(*) Theymustreturnexactlyonevaluetothecallingenvironment. Theycanhaveanexceptionsection.(*) TheycanbeinvokedfrominsideaSQLstatement. Theycanacceptparameters.(*)

Correct

22. 22. Sup Suppo pose sey you ous set etu up pa apa para rame mete ter rwi with tha an nex expl plic icit itO OUT UTm mod od e.Whatis e.Whatistrue trueabou abouttha tthatpar tparamete ameter? r? MarkforR MarkforReview eview (1)Points ItmusthaveaDEFAULTvalue. ItcannothaveaDEFAULTvalue.(*) Itactslikeaconstant(itsvaluecannotbec Itactslikeaconstan t(itsvaluecannotbechangedinsidethesubprog hangedinsidethesubprogr r am). ItmustbethesametypeasthematchingINparameter. ItinheritsitstypefromthematchingINparameter.

Correct 23. 23. Sup Suppo pose sey you ous set etu up pa apa para rame mete ter rwi with tha an nex expl plic icit itI IN Nmo mode de .Whatis .Whatistrue trueabout aboutthat thatpara parameter meter? ? MarkforR MarkforReview eview (1)Points ItmusthaveaDEFAULTvalue. ItcannothaveaDEFAULTvalue. Itactslikeaconstant(itsvaluecannotbechangedinsidethesubprog Itactslikeaconstant(itsvaluecannotbec hangedinsidethesubprogr r am).(*) ItmustbethesametypeasthematchingOUTparameter. ItinheritsitstypefromthematchingOUTparameter.

Incorrect.RefertoSection8Lesson3. 24. 24. The Thefo foll llow owin ing gpr proc oced edur ure eha has sbe been enc cre reat ated ed: : CREATEORREPLACEPROCEDUREmyproc (AINNUMBER:=20, BINNUMBER, CINNUMBERDEFAULT30) IS..... Whichofthefollowingwillinvoketheprocedurecorrectly?

MarkforReview (1)Points myproc(40); myproc(10,B=>30,50); myproc(C=>25); Alloftheabove Noneoftheabove(*)

Correct 25. 25. Pro Proce cedu dure reS SOM OMEP EPRO ROC Cha has sfi five vep par aram amet eter ers sna name med dA, A,B B, ,C, C,D D ,Einthatorder.Theprocedurewascalledasfollows: SOMEPROC(10,20,D=>50); HowwasparameterBreferenced? MarkforReview (1)Points Positional(*) Named Acombinationofpositionallyandnamed Acombinationofnamedanddefaulted Defaulted

Correct 26. 26. You Youha have vec cre reat ated edt the hef fol ollo lowi wing ngp pro roce cedu dure re: : CREATEORREPLACEPROCEDUREdouble_it (p_paramINOUTNUMBER) IS BEGIN p_param:=p_param*2; END;

Whichofthefollowinganonymousblocksinvoke Whichofthefollowing anonymousblocksinvokesthisproceduresuccessf sthisproceduresuccessfully? ully? MarkforReview (1)Points BEGIN EXECUTEdouble_it(20); END; BEGIN SELECTdouble_it(20) FROMDUAL; END; DECLARE v_resultNUMBER(6); BEGIN v_result:=double_it(20); END; DECLARE v_resultNUMBER(6):=20; BEGIN double_it(v_result); END;(*) BEGIN double_it(20); END;

Correct

param aramet eter er. . (1)Points

27. 27. Ap Apro roce cedu dure rew wil ill lex exec ecut ute efa fast ster eri if fit ith has asa at tle leas ast ton one e Mark arkf forR orRev evie iew w

True False(*)

Correct 28. 28. Whi Which cho of fth the efo foll llow owin ing gbe best std des escr crib ibes esh how owa an nIN INp par aram amet et eraffectsaprocedure? MarkforReview eraffectsaprocedure? (1)Points

Itdescribestheorderinwhichtheprocedure' Itdescribestheorder inwhichtheprocedure'sstatementsshouldbeex sstatementsshouldbeexe e cuted. Itdescribeswhichpartsoftheprocedure'sco Itdescribeswhichpar tsoftheprocedure'scodeareoptionalorcondit deareoptionalorconditi i onal. Itmakestheprocedureexecutefaster. Itpassesavalueintotheprocedurewhenthe Itpassesavalueinto theprocedurewhentheprocedureisinvoked.(*) procedureisinvoked.(*) Itallowscomplexcalculationstobeexecuted Itallowscomplexcalc ulationstobeexecutedinsidetheprocedure. insidetheprocedure.

Correct 29. 29. edure durep par aram amet eter er? ? (1)Points

Whi Which cho of fth the efo foll llow owin ing gis isN NOT OTc cor orre rect ctc cod odin ing gfo for ra apr proc oc Mark Markf for orR Rev evie iew w

(p_paramINVARCHAR2) (p_paramVARCHAR2) (p_paramVARCHAR2(50))(*) (p_paramemployees.last_name%TYPE) (p_paramINOUTVARCHAR2)

Correct 30. Exa Exami mine net the hef fol ollo lowi wing ngp pro roce cedu dure re: : CREATEORREPLACEPROCEDUREsmallproc (p_paramINNUMBER) IS BEGIN.... Theprocedureisinvokedby: DECLARE v_paramNUMBER:=20; BEGIN smallproc(v_param); END;

Whichofthefollowingstatementsistrue? MarkforReview (1)Points p_paramisaparameterandv_paramisanargument p_paramisaformalparameterand20isanactualparameter p_paramisaformalparameterandv_paramisa p_paramisaformalpa rameterandv_paramisanactualparameter(*) nactualparameter(*) p_paramandv_paramarebothformalparameters p_paramandv_paramar ebothformalparameters,while20isanactualp ,while20isanactualpa a rameter p_paramisanactualparameterandv_paramis p_paramisanactualp arameterandv_paramisaformalparameter aformalparameter

Correct 31. AnI AnIND NDEX EXB BY YT TABLE ABLEm mus ust tha have vea ap pri rima mary ryk key ey (1)Points

Mark Markf for orR Rev evie iew w

True(*) False

Correct 32. 32. You Youc can ans sto tore rea aw who hole ler rec ecor ord din ina as sin ingl gle eva vari riab able leu usi sing ng %ROWTYPEorbycreatingyoruownrecordstructureasatypeandthend %ROWTYPEorbycreatingyoruownrecordstruc tureasatypeandthendeclaring eclaring ava avari riab able leo of fth that att typ ype. e. Mark Markf for orR Rev evie iew w (1)Points True(*) False

Incorrect.RefertoSection6Lesson1.

Section7 (Answerallquestionsinthissection)

33. 33. Dep Depar artm tmen entt-id id9 99 9do does esn not ote exi xist st. .Wh What atw wil ill lbe bed dis ispl play ayed ed whenthefollowingcodeisexecuted? DECLARE v_deptnamedepartments.department_name%TYPE; BEGIN SELECTdepartment_nameINTOv_deptname FROMdepartmentsWHEREdepartment_id=99; EXCEPTION WHENNO_DATA_FOUNDTHEN RAISE_APPLICATION_ERROR(-20201,'Departm RAISE_APPLICATI ON_ERROR(-20201,'Departmentdoesnotexist'); entdoesnotexist'); END; MarkforReview (1)Points ORA-01403:NoDataFoundORA-20201:Departmentdoesnotexist ORA-01403:NoDataFound ORA-20201:Departmentdoesnotexist(*) Noneoftheabove

Correct 34. 34. defineder definederrorm rormessag essage? e? (1)Points

Whi Which cho of fth the efo foll llow owin ing gwi will lls suc ucce cess ssfu full lly yre retu turn rna au use serrMarkforR MarkforReview eview

RAISE_APPLICATION_ERROR('ErrorRaised',-22001); RAISE_APPLICATION_ERROR(-20257,'Errorraised');(*) RAISE_APPLICATION_ERROR(-22001,'ErrorRaised'); RAISE_APPLICATION_ERROR('ErrorRaised',-20257);

Correct 35. 35. Au Ause serr-de defi fine ned dex exce cept ptio ion nca can nbe ber rai aise sed: d: A.Inthedeclarationsection B.Intheexecutablesection C.Intheexceptionsection MarkforReview (1)Points

B C AandB BandC(*) AandC

Correct 36. 36. Use Userr-de defi fine ned dex exce cept ptio ions nsm mus ust tbe bed dec ecla lare red dex expl plic icit itly lyb by y theprogrammer,butthenareraisedautomatica theprogrammer,butth enareraisedautomaticallybytheOracleServer. llybytheOracleServer.Trueor Trueor False? False? MarkforR MarkforReview eview (1)Points True False(*)

Incorrect.RefertoSection7Lesson3. 37. 37. Wha What twi will llh hap appe pen nwh when ent the hef fol ollo lowi wing ngc cod ode eis ise exe xecu cute ted? d? DECLARE e_outer_excepEXCEPTION; BEGIN DECLARE e_inner_excepEXCEPTION; BEGIN RAISEe_outer_excep; END; EXCEPTION WHENe_outer_excepTHEN DBMS_OUTPUT.PUT_LINE('Outerraised'); WHENe_inner_excepTHEN DBMS_OUTPUT.PUT_LINE('Innerraised'); END; MarkforReview (1)Points Thecodewillfailtocompilebecausee_inner_ Thecodewillfailto compilebecausee_inner_excepcannotbereference excepcannotbereferenced d intheouterblock.(*)

Thecodewillpropagatethee_outer_excepback Thecodewillpropagat ethee_outer_excepbacktothecallingenvironme tothecallingenvironmen n t. Thecodewillexecutesuccessfullyand'Outer Thecodewillexecute successfullyand'OuterRaised'willbedisplayed Raised'willbedisplayed. . Thecodewillfailtocompilebecausee_inner_excepwasdeclaredbutne Thecodewillfailtocompilebecausee_inner_ excepwasdeclaredbutnev v erRAISEd.

Correct 38. 38. Usi Using ngt two won nes este ted dbl bloc ocks ks, ,a aTO TOO_ O_MA MANY NY_R _ROW OWS Sex exce cept ptio ion nis isr r aisedwithintheinnerblock.Whichofthefol aisedwithintheinner block.Whichofthefollowingexceptionhandlers lowingexceptionhandlerswillsuc willsuc cessfullyhandletheexception? cessfullyhandlethee xception? MarkforReview (1)Points WHENTOO_MANY_ROWSintheinnerblock WHENTOO_MANY_ROWSineitherblock WHENOTHERSineitherblock WHENOTHERSintheinnerblock Alloftheabove(*)

Correct 39. 39. Exc Excep epti tion ons sde decl clar ared edi in na abl bloc ock kar are eco cons nsid ider ered edl loc ocal alt to o thatblock thatblock,and ,andglob globalto altoall allitss itssub-bl ub-blocks. ocks.True TrueorF orFalse? alse? MarkforR MarkforReview eview (1)Points True(*) False

Correct 40. 40. The There rea are ren no oem empl ploy oyee ees sin ind dep epar artm tmen ent t75 75. .Wh What atw wil ill lbe bed d isplayedwhenthiscodeisexecuted? DECLARE v_last_nameemployees.last_name%TYPE; BEGIN

DBMS_OUTPUT.PUT_LINE('A'); BEGIN SELECTlast_nameINTOv_last_name FROMemployeesWHEREdepartment_id=75; DBMS_OUTPUT.PUT_LINE('B'); END; DBMS_OUTPUT.PUT_LINE('C'); EXCEPTION WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('D'); END; MarkforReview (1)Points A C D A D (*)

A A B D Noneoftheabove

Correct 41. Exami Examine nethe thefo follo llowin wingc gcode odefr fragm agment ent.A .AtL tLine ineA, A,yo youw uwant antto tora raise isean anex ex ceptionifthefetchedsalaryvalueisgreater ceptionifthefetched salaryvalueisgreaterthan30000.Howcanyou than30000.Howcanyoudothis? dothis? DECLARE v_salaryemployees.salary%TYPE; BEGIN SELECTsalaryINTOv_salaryFROMemployees WHEREemployee_id=100; IFv_salary>30000THEN --LineA ENDIF; ... MarkforReview (1)Points TestforWHENVALUE_TOO_HIGHintheexceptionsection. UseRAISE_APPLICATION_ERRORtoraiseanexcept UseRAISE_APPLICATION_ ERRORtoraiseanexceptionexplicitly.(*) ionexplicitly.(*)

TestforWHENOTHERSintheexceptionsection, TestforWHENOTHERSi ntheexceptionsection,becauseWHENOTHERStrap becauseWHENOTHERStraps s allexceptions. DefineanEXCEPTIONvariableandassociateitwithanOracleServererr DefineanEXCEPTIONvariableandassociateit withanOracleServererro o rnumberusingPRAGMAEXCEPTION_INIT.

Correct 42. 42. Exa Exami mine net the hef fol ollo lowi wiin ing gco code de. .Wh Whic ich hex exce cept ptio ion nha hand ndle lers rsw w ouldsuccessfullytraptheexceptionwhichwil ouldsuccessfullytrap theexceptionwhichwillberaisedwhenthiscod lberaisedwhenthiscodeisexec eisexec uted?(Choosetwo.) DECLARE CURSORemp_cursISSELECT*FROMemployees; v_emp_recemp_curs%ROWTYPE; BEGIN FETCHemp_cursINTOv_emp_rec; OPENemp_curs; CLOSEemp_curs; EXCEPTION... END; MarkforReview (1)Points (Chooseallcorrectanswers) WHENCURSOR_NOT_OPEN WHENINVALID_CURSOR(*) WHENOTHERS(*) WHENNO_DATA_FOUND WHENINVALID_FETCH

Incorrect.RefertoSection7Lesson2. 43. 43. An Anat atte temp mpt tto toi ins nser ert ta anu null llv val alue uei int nto oa aNO NOT TNU NULL LLt tab able le columnraisesanORA-01400exception.Howcanyoucodeanexceptionha columnraisesanORA-01400exception.Howcan youcodeanexceptionhandlerto ndlerto trapt trapthis hisex excep ceptio tion? n? Markf Markfor orRev Review iew (1)Points TestforWHENORA-1400intheexceptionsection. Declareavariablee_null_excepoftypeEXCEPT Declareavariablee_n ull_excepoftypeEXCEPTION,associateitwithOR ION,associateitwithORA A

-01400usingaPRAGMAdirective,andtestforWHENe_null_excepinthe -01400usingaPRAGMAdirective,andtestfor WHENe_null_excepintheexception exception section.(*) Declareavariablee_null_excepoftypeVARCHA Declareavariablee_n ull_excepoftypeVARCHAR2,associateitwithORA R2,associateitwithORA01400usingaPRAGMAdirective,andtestforWHENe_null_excepinthee 01400usingaPRAGMAdirective,andtestforW HENe_null_excepintheexception xception section. Declareavariableasfollows:e_null_excepEX Declareavariableas follows:e_null_excepEXCEPTION:=-01400;Thent CEPTION:=-01400;Thente e stforWHENe_null_excepintheexceptionsection.

Correct 44. 44. Whi Which chk kin inds dso of fex exce cept ptio ions nsa are rer rai aise sed dim impl plic icit itly ly( (i. i.e. e., , automatica automatically)? lly)?(Cho (Chooset osetwo.) wo.) MarkforR MarkforReview eview (1)Points (Chooseallcorrectanswers) PredefinedOracleServererrorssuchasNO_DATA_FOUND(*) User-definederrors Allerrors Non-predefinedOracleServererrorssuchasORA-01400(*)

Correct 45. 45. Whi Which cho of fth thes ese eex exce cept ptio ions nsw wou ould ldn nee eed dto tob be era rais ised ede exp xpli li citlybythePL/SQLprogrammer? citlybythePL/SQLpr ogrammer? MarkforReview (1)Points OTHERS ASELECTstatementreturnsmorethanonerow. Acheckconstraintisviolated. ASQLUPDATEstatementdoesnotupdateanyrows.(*) ArowisFETCHedfromacursorwhilethecursorisclosed.

Correct 46. 46. An Anat atte temp mpt tto tou upd pdat ate ean ane emp mplo loye yee' e's ssa sala lary ryt to oa ane nega gati tive ve valuewillviolateacheckconstraintandraiseanORA-02290exception valuewillviolateacheckconstraintandrai seanORA-02290exception.Whicho .Whicho fthefollowingisacorrectdefinitionofah fthefollowingisac orrectdefinitionofahandlerforthisexception andlerforthisexception? ? MarkforReview (1)Points DECLARE e_sal_excepEXCEPTION; PRAGMAEXCEPTION_INIT(-02290,e_sal_excep); DECLARE PRAGMAEXCEPTION_INIT(e_sal_excep,-02290); e_sal_excepEXCEPTION; DECLARE e_sal_excepEXCEPTION; PRAGMAEXCEPTION_INIT(e_sal_excep,-02290); (*)

DECLARE e_sal_excepEXCEPTION; PRAGMA_EXCEPTION_INIT(e_sal_exception,-02290); DECLARE e_sal_excepEXCEPTION; PRAGMAEXCEPTION_INIT(e_sal_excep,02290);

Correct 47. 47. The Thef fol ollo lowi wing ngE EXC XCEP EPTI TION ONs sec ecti tion oni is sco cons nstr truc ucte ted dco corr rrec ectl tl y.TrueorFalse? EXCEPTION WHENNO_DATA_FOUNDORTOO_MANY_ROWS THENstatement_1; statement_2; WHENOTHERS THENstatement_3; END; MarkforReview (1)Points True(*) False

Correct 48. 48. Whi Which cho of fth thes ese eex exce cept ptio ions nsc can anb be eha hand ndle led dby bya an nEX EXCE CEPT PTIO IO Nsect Nsection ionin ina aPL/ PL/SQL SQLbl block ock? ? Markf Markfor orRev Review iew (1)Points ASELECTstatementreturnsnorows ASELECTstatementreturnsmorethanonerow Anyotherkindofexceptionthatcanoccurwithintheblock Alloftheabove(*) Noneoftheabove

Correct 49. 49. Whi Which cho of fth the efo foll llow owin ing gis isN NOT OTa an nad adva vant ntag age eof ofi inc nclu ludi ding ng an anex exce cept ptio ion nha hand ndle ler rin ina aP PL/ L/SQ SQL Lbl bloc ock? k? Mark Markf for orR Rev evie iew w (1)Points Protectsthedatabasefromerrors Codeismorereadablebecauseerror-handlingroutinescanbewrittenin Codeismorereadablebecauseerror-handlingr outinescanbewrittenin thesameblockinwhichtheerroroccurred Preventserrorsfromoccurring(*) Avoidscostlyandtime-consumingcorrectionofmistakes

Correct 50. 50. Exa Exami mine net the hef fol ollo lowi wing ngc cod ode. e.W Why hyd doe oes sth the eex exce cept ptio ion nha hand nd lernotfollowgoodpracticeguidelines? DECLARE v_salaryemployees.salary%TYPE; BEGIN SELECTsalaryINTOv_salaryFROMemployees WHEREemployee_id=999; EXCEPTION

WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('Anerroroccurred'); END; MarkforReview (1)Points YoushouldnotuseDBMS_OUTPUT.PUT_LINEinan YoushouldnotuseDBM S_OUTPUT.PUT_LINEinanexceptionhandler. exceptionhandler. employee_id999doesnotexistintheemployeestable. Theexceptionhandlershouldtestforthename Theexceptionhandler shouldtestforthenamedexceptionNO_DATA_FOUND dexceptionNO_DATA_FOUND. . (*) TheexceptionhandlershouldCOMMITthetransaction.

C o r r ec t