Semester 1 Final Exam Oracle Pl SQL (2)

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. 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DMLstatementisexecut Asubprogramthatexecutesautomaticallywhen aDMLstatementisexecute e donatable. Asubprogramwhichinvokesanothersubprogram.

Correct 3. Af Afun unct ctio ion nmu must sth hav ave eat atl lea east sto one neI IN Npa para rame mete ter, r,a and ndm mus us tretu treturn rnexa exactl ctlyo yone neval value. ue. Markf Markfor orRev Review iew (1)Points

True False(*)

Correct 4. 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.

Correct 5. 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 doesnotcompilecorr ectlybecausethereareerrorsinyourcode.Whic 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

6. 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

Functionscannotbenested;procedurescanbe Functionscannotbene sted;procedurescanbenestedtoatleast8leve nestedtoatleast8level l s. Aprocedurecanhavedefaultvaluesforparame Aprocedurecanhaved efaultvaluesforparameters,whileafunctionca ters,whileafunctioncan n not. Anexplicitcursorcanbedeclaredinaproced Anexplicitcursorcan bedeclaredinaprocedure,butnotinafunctio ure,butnotinafunction n . AfunctioncannotbeusedwithinaSQLstatement;aprocedurecanbeus AfunctioncannotbeusedwithinaSQLstateme nt;aprocedurecanbeuse e dwithinSQL. Afunctionmustreturnavalue,aproceduremayormaynot.(*)

Incorrect.RefertoSection9Lesson1. 7. Whe When ncr crea eati ting nga au use serr-de defi fine ned dfu func ncti tion on, ,th the esi size zeo of fth the e returnedvaluesmaybeuptothesizeofanyP returnedvaluesmaybe uptothesizeofanyPL/SQLdatatype.Trueor L/SQLdatatype.TrueorFalse? False? MarkforReview (1)Points True False(*)

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.

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 11.

Youw Youwant antto tore remov movet ethe hepro proced cedure ureNO NO_NE _NEED EDfro fromy myour oursc schem hema. a.You Youex execu ecute: te:

DROPPROCEDUREno_need; WhichDataDictionaryviewsareupdatedautomatically? MarkforReview (1)Points USER_PROCEDURES USER_OBJECTS USER_SOURCE Alloftheabove.(*) Noneoftheabove.

Correct 12. 12.

Exa Exami mine net the hef fol ollo lowi wing ngc cod ode e(t (the hec cod ode eof ofC CHI HILD LD2 2is isn not ots s

hown): CREATEPROCEDUREchild1 ISv_salaryemployees.salary%TYPE; BEGIN SELECTsalaryINTOv_salaryFROMemployees WHEREemployee_id=9999; EXCEPTION WHENNO_DATA_FOUNDTHENNULL; ENDchild1; CREATEPROCEDUREparent ISBEGIN child1; child2; EXCEPTION WHENNO_DATA_FOUNDTHENNULL; ENDparent; Employee_id9999doesnotexist.Whathappens Employee_id9999does notexist.WhathappenswhenPARENTisexecuted? whenPARENTisexecuted? MarkforReview (1)Points

CHILD1handlestheexceptionsuccessfullyandends.PARENTcontinuesto CHILD1handlestheexceptionsuccessfullyand ends.PARENTcontinuesto executeandinvokesCHILD2.(*) CHILD1endsabruptly,PARENThandlestheexcep CHILD1endsabruptly, PARENThandlestheexceptionsuccessfullyandend tionsuccessfullyandends s .CHILD2doesnotexecute. CHILD1endsabruptly,thenPARENTalsoendsabruptlywithanunhandled CHILD1endsabruptly,thenPARENTalsoendsab ruptlywithanunhandlede e xception. PARENThandlestheexception,thenCHILD1resumesexecution. PARENTfailstocompilebecauseyoucannothavethesameexceptionhand PARENTfailstocompilebecauseyoucannothav ethesameexceptionhandl l erintwoseparatesubprograms.

Correct 13. 13. 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 14. 14. How Howd do oyo you usp spec ecif ify yth that aty you ouw wan ant ta apr proc oced edur ure eMY MYPR PROC OCA Ato to use use" "De Defi fine ner' r's sRi Righ ghts ts"? "? Mark Markf for orR Rev evie iew w (1)Points CREATEORREPLACEPROCEDUREmyproca AUTHIDCURRENT_USERIS... CREATEORREPLACEPROCEDUREmyproca AUTHIDOWNERIS... GRANTDEFINERTOmyprocA;

ALTERPROCEDUREmyprocaTODEFINER; Definer'sRightsarethedefault,thereforenoextracodeorcommandsa Definer'sRightsarethedefault,thereforeno extracodeorcommandsar r eneeded.(*)

Correct 15. 15. useInvok useInvoker's er'sRight Rights? s? (1)Points

How Howd do oyo you usp spec ecif ify yth that aty you ouw wan ant ta apr proc oced edur ure eMY MYPR PROC OCA Ato to MarkforR MarkforReview eview

CREATEORREPLACEPROCEDUREmyproca AUTHIDCURRENT_USERIS... (*)

Invoker'sRightsarethedefault,thereforeno Invoker'sRightsaret hedefault,thereforenoextracodeisneeded. extracodeisneeded. GRANTINVOKERTOmyprocA; ALTERPROCEDUREmyprocaTOINVOKER; CREATEORREPLACEPROCEDUREmyproca AUTHIDOWNERIS...

Correct

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

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_excepcannotbereference Thecodewillfailtocompilebecausee_inner_ 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_ Thecodewillfailto compilebecausee_inner_excepwasdeclaredbutne excepwasdeclaredbutnev v erRAISEd.

Correct 17. 17. 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 18. 18. 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 19. 19. 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_excep1EXCEPTION; e_excep2EXCEPTION; BEGIN RAISEe_excep1; EXCEPTION WHENe_excep1THENBEGIN RAISEe_excep2;END; END; MarkforReview (1)Points Itwillfailtocompilebecauseyoucannothav Itwillfailtocompil ebecauseyoucannothaveasubblockinsideanex easubblockinsideanexc c eptionsection. Itwillfailtocompilebecausee_excep1isou Itwillfailtocompil ebecausee_excep1isoutofscopeinthesubbloc tofscopeinthesubblock k . Itwillfailtocompilebecauseyoucannotdec Itwillfailtocompil ebecauseyoucannotdeclaremorethanoneexcept laremorethanoneexcepti i oninthesameblock. Itwillcompilesuccessfullyandreturnanunhandlede_excep2totheca Itwillcompilesuccessfullyandreturnanunh andlede_excep2tothecal l lingenvironment.(*)

Correct 20. 20. leSe eServ rver ere err rror or? ? (1)Points

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p pre rede defi fine ned dOr Orac ac Mark Markf for orR Rev evie iew w

HasastandardOracleerrornumberbutmustbe HasastandardOracle errornumberbutmustbenamedbythePL/SQLprog namedbythePL/SQLprogr r ammer Isnotraisedautomaticallybutmustbedeclar Isnotraisedautomati callybutmustbedeclaredandraisedexplicitly edandraisedexplicitlyb b ythePL/SQLprogrammer HasastandardOracleerrornumberandastandardnamewhichcanberef HasastandardOracleerrornumberandastand ardnamewhichcanberefe e rencedintheEXCEPTIONsection(*) IsassociatedwithanOracleerrornumberusin Isassociatedwithan OracleerrornumberusingPRAGMAEXCEPTION_INIT gPRAGMAEXCEPTION_INIT

Correct 21. Which Whichof ofth thef efoll ollowi owing ngare areex examp amples lesof ofpr prede edefin fined edOra Oracle cleSe Serve rvere rerro rrors? rs? (Cho (Choos ose eth thre ree. e.) ) Mark Markf for orR Rev evie iew w (1)Points (Chooseallcorrectanswers) TOO_MANY_ROWS(*) NO_DATA_FOUND(*) OTHERS ZERO_DIVIDE(*) E_INSERT_EXCEP

Correct 22. 22. Exa Exami mine net the hef fol ollo lowi wing ngc cod ode. e.W Wha hat tme mess ssag age eor orm mes essa sage ges swi wi llbedisplayedwhenthiscodeisexecuted? DECLARE v_last_nameemployees.last_name%TYPE; v_numberNUMBER:=27; BEGIN v_number:=v_number/0; SELECTlast_nameINTOv_last_nameFROMemployees WHEREemployee_id=999; EXCEPTION WHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('Norowswerefound'); WHENZERO_DIVIDETHEN DBMS_OUTPUT.PUT_LINE('Attempttodividebyzero'); WHENOTHERSTHEN

DBMS_OUTPUT.PUT_LINE('Anerroroccurred'); END; MarkforReview (1)Points Norowswerefound Attempttodividebyzero(*) AttempttodividebyzeroNorowswerefound Anerroroccurred Nomessagewillbedisplayed

Correct 23. 23. 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 columnraisesanORA01400exception.Howcanyoucodeanexceptionha 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EXCEPTION:=-01400;Thent Declareavariableasfollows:e_null_excepEX CEPTION:=-01400;Thente e stforWHENe_null_excepintheexceptionsection.

Correct 24. 24. Exa Exami mine net the hef fol ollo lowi wing ngc cod ode efr frag agme ment nt. .At AtL Lin ine eA, A,y you ouw wan an ttoraiseanexceptionifthefetchedsalary ttoraiseanexceptio nifthefetchedsalaryvalueisgreaterthan300 valueisgreaterthan30000.Howc 00.Howc anyou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 25. 25. How Howc can any you our ret etri riev eve eth the eer erro ror rco code dea and nde err rror orm mes essa sage geo o fanyOrac fanyOracleSe leServer rverexcep exception? tion? MarkforR MarkforReview eview (1)Points ByusingthefunctionsSQLCODEandSQLERRM(*) ByusingthefunctionsSQLCODEandSQLERR ByusingRAISE_APPLICATION_ERROR BydefininganEXCEPTIONvariableandusingPR BydefininganEXCEPTI ONvariableandusingPRAGMAEXCEPTION_INIT AGMAEXCEPTION_INIT

Correct 26. 26. Au Ause serr-de defi fine ned dex exce cept ptio ion nmu must stb be ede decl clar ared eda as sa ava vari riab able le ofdatatypeEXCEPTION.TrueorFalse? ofdatatypeEXCEPTIO N.TrueorFalse? MarkforReview (1)Points True(*) False

Correct

27. 27. 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 28. 28. 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 29. 29. The There rea are ren no oem empl ploy oyee ees sin ind dep epar artm tmen ent_ t_id id9 99. 9.W Wha hat tou outp tput ut willbedisplayedwhenthefollowingcodeisexecuted? DECLARE v_countNUMBER; BEGIN SELECTCOUNT(*)INTOv_count FROMemployeesWHEREdepartment_id=99; IFv_count=0THEN RAISENO_DATA_FOUND;

DBMS_OUTPUT.PUT_LINE('Noemployeesfound'); ENDIF; EXCEPTION WHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('Department99isempty'); END; MarkforReview (1)Points Noemployeesfound NoemployeesfoundDepartment99isempty Department99isempty(*) TheblockwillfailbecauseyoucannotexplicitlyRAISEapredefinedOr Theblockwillfailbecauseyoucannotexplici tlyRAISEapredefinedOra a cleServererrorsuchasNO_DATA_FOUND

Correct 30. 30. Whi Which cho of fth the efo foll llow owin ing gar are ego good odp pra ract ctic ice egu guid idel elin ines esf for or exception exceptionhand handling? ling?(Cho (Chooset osethree. hree.) ) MarkforR MarkforReview eview (1)Points (Chooseallcorrectanswers) Testyourcodewithdifferentcombinationsof Testyourcodewithdi fferentcombinationsofdatatoseewhatpotentia datatoseewhatpotential l errorscanhappen.(*) Useanexceptionhandlerwheneverthereisanypossibilityofanerror Useanexceptionhandlerwheneverthereisany possibilityofanerroro o ccurring.(*) IncludeaWHENOTHERShandlerasthefirsthan IncludeaWHENOTHERS handlerasthefirsthandlerintheexceptionsec dlerintheexceptionsect t ion. Allowexceptionstopropagatebacktothecallingenvironment. Handlespecificnamedexceptionswherepossible,insteadofrelyingon Handlespecificnamedexceptionswherepossibl e,insteadofrelyingonW W HENOTHERS.(*)

Correct 31. Exami Examine nethe thefo follo llowin wingc gcode ode.W .Why hydoe doest sthe heexc except eption ionha handl ndler ernot notfo follo llowg wg ood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.

Correct 32. 32. 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 33. 33. Whi While lea aP PL/ L/SQ SQL Lbl bloc ock kis ise exe xecu cuti ting ng, ,mo more ret tha han non one eex exce cept pt ioncanoccuratthesametime.TrueorFalse? MarkforReview ioncanoccuratthesametime.TrueorFalse? (1)Points True False(*)

Correct

Section8 (Answerallquestionsinthissection) 34. 34. 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 35. 35. 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 36. 36.

Wha What tis isa ano noth ther ern nam ame efo for ra ane nest sted eds sub ubpr prog ogra ram? m?

Mark Markfo for r

Review (1)Points Hostedsubprogram Localsubprogram(*) Limitedsubprogram

Correct 37. 37. AP APL/ L/SQ SQL Lst stor ored edp pro roce cedu dure rec can ana acc ccep ept ton one eor orm mor ore ein inpu put t parametersandcanreturnoneormoreoutputvaluestothecallingenvi parametersandcanreturnoneormoreoutputv aluestothecallingenvironment. ronment. TrueorFa TrueorFalse? lse? MarkforR MarkforReview eview (1)Points True(*) False

Correct 38. 38. As Asto tore red dPL PL/S /SQL QLp pro roce cedu dure rec can anb be ein invo voke ked dfr from omw whi hich cho of f thefollowing? APL/SQLanonymousblock AnotherPL/SQLprocedure Acallingapplication MarkforReview (1)Points Aonly AandB AandC

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

Correct 39. 39. Whi Which cho of fth the efo foll llow owin ing gke keyw ywor ords dsM MUS UST Tbe bei inc nclu lude ded din ine eve ve ryPL/SQLproceduredefinition?(Choosethree. ryPL/SQLprocedurede finition?(Choosethree.) ) MarkforReview (1)Points (Chooseallcorrectanswers) REPLACE BEGIN(*) ISorAS(*) DECLARE END(*)

Correct 40. 40. You Youh hav ave ecr crea eate ted dpr proc oced edur ure eMY MYPR PROC OCw wit ith ha asi sing ngle lep par aram amet et erPARM1NUMBER.Nowyouwanttoaddasecond erPARM1NUMBER.Nowy ouwanttoaddasecondparametertotheprocedur parametertotheprocedure.Which e.Which ofthe ofthefo follo llowin wingw gwill illch chang anget ethe hepro proced cedure uresu succe ccessf ssfull ully? y? Markf Markfor orRev Review iew (1)Points ALTERPROCEDUREmyprocADD(parm2NUMBER); Theprocedurecannotbemodified.Onceaproce Theprocedurecannotb emodified.Onceaprocedurehasbeencreated,th durehasbeencreated,the e numberofparameterscannotbechanged. CREATEORREPLACEPROCEDUREsomeproc (parm1NUMBER,parm2NUMBER); (Youdonotneedtorepeatthedetailedcodeo (Youdonotneedtore peatthedetailedcodeoftheprocedure,onlythe ftheprocedure,onlytheheader) header) REPLACEPROCEDUREsomeproc (parm1NUMBER,parm2NUMBER) IS BEGIN...

CREATEORREPLACEPROCEDUREsomeproc (parm1NUMBER,parm2NUMBER) IS BEGIN...(*)

Correct 41. Youh Youhave avecr creat eated edap aproc rocedu edure renam named edMYP MYPROC ROCth that atacc accept eptst sthre hreeI eINp Npara aramet met ersA,B,andC(allnumbers).Whichofthefo ersA,B,andC(alln umbers).WhichofthefollowingcallstoMYPROCi llowingcallstoMYPROCisNOTcor sNOTcor rect rect? ? Mark Markf for orR Rev evie iew w (1)Points myproc(5,10,20); myproc(a=>5,b=>10,20)(*) myproc(a=>5,b=>10,c=>20) myproc(5,10,c=>20)

Correct

param aramet eter er. . (1)Points

42. 42. 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 43. 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 44. 44. 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 45. 45.

Wha What tar are eth the ety type peo of fpa para rame mete ter rmo mode des? s?

(1)Points CHARACTER,NUMBER,DATE,BOOLEAN CONSTANT,VARIABLE,DEFAULT

Mark Markfo for rRe Revi view ew

LOCAL,GLOBAL,BOTH IN,OUT,INOUT(*)

Correct 46. 46.

Whi Which chp par aram amet eter erm mod ode eis ist the hed def efau ault lt? ?

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

(1)Points IN(*) OUT NUMBER VARIABLE CONSTANT

Correct 47. 47. 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

48. 48. 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Dreferenced? MarkforReview (1)Points Positionally Named(*) Acombinationofpositionallyandnamed Acombinationofnamedanddefaulted Defaulted

Incorrect.RefertoSection8Lesson3.

Section6 (Answerallquestionsinthissection) 49. 49.

You Youc can anu use se% %RO ROWT WTYP YPE Ewi with tht tab able les san and dvi view ews. s.

Mark Markf for or

Review (1)Points True(*) False

Correct 50. Review (1)Points True(*) False

An AnIN INDE DEX XBY BYT TAB ABLE LEm mus ust tha have vea ap pri rima mary ryk key ey

Mar Markfo kfor r

Correct