Semester 2 Final Exam PL SQL (2)

False(*) False(*)

C

Coorrrreecct t CCoorrrreecctt

2.

2. IdIdenentitifyfyeexaxampmplelessofofbbenenefefititssofofuusisingngPPLSLSQLQL_O_OPTPTIMIMIZIZE_E_LELE V

VELEL..(C(Chohoososeeththreree)e) MaMarkrkffororRRevevieieww (1)Points

(1)Points

(Chooseallcorrectanswers) (Chooseallcorrectanswers)

Modifysourcecodeto

Modifysourcecodetooptimizefrequently-usedoptimizefrequently-usedelementsatthetop.elementsatthetop.

ControlwhatPL/SQLdoeswithuselesscode.(*) ControlwhatPL/SQLdoeswithuselesscode.(*)

Backwardcompatiblewi

BackwardcompatiblewithpreviousversionsofthpreviousversionsoftheOracledatabase.(*)theOracledatabase.(*)

Separatingcompiledco

Separatingcompiledcodesothatseparateunitdesothatseparateunitsmayberepeatedasneedsmayberepeatedasneedee d.

d.

Copycompiledcodefro

Copycompiledcodefromonesubprogramintoanmonesubprogramintoanothersubprogram.(*)othersubprogram.(*)

C

Coorrrreecct t CCoorrrreecctt

3.

3. NaNatitivevemmacachihineneccododeePLPL/S/SQLQLwwililllalalwawaysyseexexecucuteteffasasteterrthth anbytecodePL/SQLbec

anbytecodePL/SQLbecauseitneednotbeinteauseitneednotbeinterpretedatruntime.Truerpretedatruntime.TrueorFalseorFalse ? ? MMaarrkk ffoorr RReevviieeww (1)Points (1)Points True(*) True(*) False False C Coorrrreecct t CCoorrrreecctt

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime.

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

4.

4. IdIdenentitifyfyssomomeebebenenefifitstsooffususininggcocondndititioionanallcocompmpililatatioion.n. (

(ChChoooosesettwowo)) MaMarkrkffororRRevevieieww (1)Points

(1)Points

(Chooseallcorrectanswers) (Chooseallcorrectanswers)

Usenewfeatureswith

Usenewfeatureswiththelatestdatabaserelethelatestdatabasereleaseanddisablethemwithaseanddisablethemwith olderdatabaseversions(*)

olderdatabaseversions(*)

SpeedupthecompilationtimeofalengthyPL/SQLsubprogram. SpeedupthecompilationtimeofalengthyPL/SQLsubprogram.

Determineinitializati

Determineinitializationvaluesduringstartuonvaluesduringstartupofadatabasesession.pofadatabasesession.

Activatedebuggingor

Activatedebuggingortracingstatementsinthtracingstatementsinthedevelopmentenvironmentedevelopmentenvironment (*)

(*)

C

Coorrrreecct t CCoorrrreecctt

5.

5. InInttheheffolollolowiwingngeexaxampmplele,,whwhatatsstatatetemementntbbelelonongsgsiinnLiLinene A? A? ALTERSESSIONSETPLSQL_CCFLAGS='debug:true'; ALTERSESSIONSETPLSQL_CCFLAGS='debug:true'; CREATEORREPLACEPROCEDUREtestprocISBEGIN CREATEORREPLACEPROCEDUREtestprocISBEGIN ... ... $IF$$debug$THEN $IF$$debug$THEN DBMS_OUTPUT.PUT_LINE('Thiscodewasexecuted'); DBMS_OUTPUT.PUT_LINE('Thiscodewasexecuted'); --LineA --LineA ... ... ENDtestproc; ENDtestproc; ALTERSESSIONSETPLSQL_CCFLAGS='debug:false'; ALTERSESSIONSETPLSQL_CCFLAGS='debug:false'; MarkforReview MarkforReview (1)Points (1)Points $ENDIF $ENDIF $$END; $$END; $END(*) $END(*) $ELSIF $ELSIF

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

C

Coorrrreecct t CCoorrrreecctt

6.

6. ToToiincncluludedesselelecectitiononssofofccododeefoforrcocompmpililatatioionnbabaseseddononuu ser-d

ser-defineefinedvaldvalues,ues,usetusethePLhePLSQL_CSQL_CCFLAGCFLAGSparSparameteameters.Trs.TrueorueorFalrFalse?se? MarkMarkforfor Review Review (1)Points (1)Points True(*) True(*) False False C Coorrrreecct t CCoorrrreecctt 7.

7. ObObfufuscscatatioionndodoesesnnototaallllowowaanynyononeetotosseeeetthehessouourcrceecocodede ,inc

,includinludingthegtheowneowner.Trr.TrueorueorFalsFalse?e? MarkMarkforRforRevieweview (1)Points (1)Points True(*) True(*) False False C Coorrrreecct t CCoorrrreecctt 8.

8. WhWhicichhisisNNOTOTaabbenenefefititooffobobfufuscscatatioion?n? MaMarkrkffororRRevevieieww (1)Points (1)Points Sourcecodeisnotloadedinthedatadictionary. Sourcecodeisnotloadedinthedatadictionary. Sourcecodeishiddenfromallusers. Sourcecodeishiddenfromallusers. Sourcecodeisvisibletotheowner.(*) Sourcecodeisvisibletotheowner.(*) Protectionforintellectualpropertyisprovided. Protectionforintellectualpropertyisprovided. C Coorrrreecct t CCoorrrreecctt

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

True True False(*) False(*) C Coorrrreecct t CCoorrrreecctt 10

10.. WhWhatatddoeoessththeefofollllowowininggststatatememenenttdodo?? DBMS_WARNING.ADD_WARNI

DBMS_WARNING.ADD_WARNING_SETTING_CAT('PERFORMANG_SETTING_CAT('PERFORMANCE','ENABLE','SESSION');NCE','ENABLE','SESSION'); MarkforReview

MarkforReview (1)Points

(1)Points

EnablesthePERFORMANC

EnablesthePERFORMANCEwarningcategory,settEwarningcategory,settingothercategorysettiningothercategorysettingg stodisabled.

stodisabled.

EnablesthePERFORMANC

EnablesthePERFORMANCEwarningcategory,leavEwarningcategory,leavingothercategorysettiningothercategorysettingg sunchanged.

sunchanged.

AddthePERFORMANCEwarningcategoryintoaPL/SQLvariable. AddthePERFORMANCEwarningcategoryintoaPL/SQLvariable.

Disablesallwarningc

Disablesallwarningcategories,thenenablesategories,thenenablesthePERFORMANCEcategory.thePERFORMANCEcategory.

EnablesthePERFORMANC

EnablesthePERFORMANCEwarningcategory,leavEwarningcategory,leavingothercategorysettiningothercategorysettingg sunchanged,forthecurrentsession.(*)

sunchanged,forthecurrentsession.(*)

C

Coorrrreecct t CCoorrrreecctt

11.

11. InInthetheUSUSER_ER_ERRERRORSORSdadatatadicdictiotionarnaryvyviewiew,i,ifafanenerrorroririspsprefrefixeixedwdwithith"W"W arning,"thecommandc

arning,"thecommandcompletesbuthasasideompletesbuthasasideeffecttheuserneedstoeffecttheuserneedstoknowabouknowabou t.Forallothererror

t.Forallothererrors,thecommandterminates,thecommandterminatesabnormally.TrueorFalsabnormally.TrueorFalse?se? MarkforReview MarkforReview (1)Points (1)Points True(*) True(*) False False C Coorrrreecct t CCoorrrreecctt

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

and and ALTERSESSION ALTERSESSION SETPLSQL_WARNINGS='ENABLE:SEVERE'; SETPLSQL_WARNINGS='ENABLE:SEVERE'; MarkforReview MarkforReview (1)Points (1)Points True True False(*) False(*) C Coorrrreecct t CCoorrrreecctt Section14 Section14 (Answerallquestionsinthissection) (Answerallquestionsinthissection) 13

13.. InIntthihissscscenenararioio,,ththeefofollllowowininggststatatususiissgigivevennfoforreaeachch procedure:

procedure:

ProcedureAislocalandhasatimestampof10AM ProcedureAislocalandhasatimestampof10AM ProcedureBisrem

ProcedureBisremoteandhasalocalandoteandhasalocalandremotetimestampof10:3remotetimestampof10:30AM0AM InTimestampMode,Pro

InTimestampMode,ProcedureA,whichisdepencedureA,whichisdependentonProcedureB,willdentonProcedureB,willexecuteexecute successfullyat11AM.TrueorFalse? successfullyat11AM.TrueorFalse? MarkforReview MarkforReview (1)Points (1)Points True(*) True(*) False False C Coorrrreecct t CCoorrrreecctt 14

14.. IfIfttwoworrelelatatededoobjbjecectstsaarereiinndidiffffererenenttdadatatababaseses,s,tthehedd ependencybetweenthem

ependencybetweenthemisautomaticallyrecordisautomaticallyrecordedonthedependentobjecedonthedependentobject'sdatat'sdata d

dicictitiononarary.y.TTrurueeororFFaalslse?e? MaMarkrkffororRRevevieieww (1)Points

(1)Points

True True

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

15

15.. PrPrococededurureeBBhahassititsslolocacallvavaririabableleeempmp_n_numumbeberrchchanangegeddtoto emp_name.Thedataty

emp_name.Thedatatypeofemp_idischangedpeofemp_idischangedfromnumbertointeger.Ifromnumbertointeger.Itiscomptiscomp iledsuccessfully.In

iledsuccessfully.InSignatureMode,ProcedurSignatureMode,ProcedureA,whichisdependentoeA,whichisdependentonremotenremote Pr

PrococededurureeB,B,wwililllcocompmpilileeananddexexececututeesusuccccesessfsfulullyly..TrTrueueoorrFaFalslse?e? MaMarkrkfforor Review Review (1)Points (1)Points True(*) True(*) False False C Coorrrreecct t CCoorrrreecctt 16

16.. AAchchanangegeiinnaareremomoteterrefefererenenceceddsusubpbprorogrgramamiissauautotomamatiticaca llyrecordedasinvali

llyrecordedasinvalidifitsbaseobjectchadifitsbaseobjectchangesandthatnewstatusngesandthatnewstatusisrelayeisrelaye dtothedependentobj

dtothedependentobject'sstatusandautomatect'sstatusandautomaticallymarkedasinvalid.icallymarkedasinvalid.TrueorTrueor Fal

False?se? MarMarkfkfororRevReviewiew (1)Points (1)Points True True False(*) False(*) C Coorrrreecct t CCoorrrreecctt 17

17.. PrPrococededurureeBBhahassaanenewwININOOUTUTppararamametetereraaddddededttooitit..ItItcc ompilessuccessfully.

ompilessuccessfully.InSignaturemode,ProceInSignaturemode,ProcedureA,whichisdependendureA,whichisdependentonremotonremo te

tePProrocecedudurereBB,,wiwillllffaiail.l.TTrurueeororFFalalsese?? MaMarkrkffororRRevevieieww (1)Points (1)Points True(*) True(*) False False I Innccoorrrreecct t IInnccoorrrreecctt.. RReeffeerr ttoo SSeeccttiioonn 1144 LLeessssoonn 22.. 1 188 EExxaammiinnee tthhee ffoolllloowwiinngg ccooddee::

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime.

Whichofthefollowing

Whichofthefollowingstatementsaboutdependstatementsaboutdependenciesaretrue?(Chooseenciesaretrue?(Choosetwo.)two.) MarkforReview MarkforReview (1)Points (1)Points (Chooseallcorrectanswers) (Chooseallcorrectanswers) ED_PROCisindirectlydependentonDEPARTMENTS(*) ED_PROCisindirectlydependentonDEPARTMENTS(*) EMPLOYEESisreferencedbyED_VIEW(*) EMPLOYEESisreferencedbyED_VIEW(*) ED_CURSisdirectlydependentonED_VIEW ED_CURSisdirectlydependentonED_VIEW ED_PROCisreferencedbyED_VIEW ED_PROCisreferencedbyED_VIEW ED_PROCisdirectlydependentonEMPLOYEES ED_PROCisdirectlydependentonEMPLOYEES C Coorrrreecct t CCoorrrreecctt 19

19.. UsUsererBBOBOBwwanantststtooknknowowwwhihichchoobjbjecectstsrrefefererenencecehhisisDDEPEPARAR TMENTStable.Whichof

TMENTStable.WhichofthefollowingmustheethefollowingmustheexecutetopopulatetheDExecutetopopulatetheDEPTREE_TEMPTREE_TEM P PTTAABB ttaabbllee?? MMaarrkk ffoorr RReevviieeww (1)Points (1)Points BEGIN BEGIN utldtree('DEPARTMENTS'); utldtree('DEPARTMENTS'); END; END; BEGIN BEGIN deptree_fill('TABLE','BOB','DEPARTMENTS'); deptree_fill('TABLE','BOB','DEPARTMENTS'); END; END; (*) (*)

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime.

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime.

20

20.. WhWhicichhofofttheheffolollolowiwingngiissNONOTTcrcreaeateteddwhwhenenttheheuutltldtdtreree.e. s sqqll ssccrriipptt iiss rruunn?? MMaarrkk ffoorr RReevviieeww (1)Points (1)Points TheDEPTREEview TheDEPTREEview TheDEPTREE_FILLprocedure TheDEPTREE_FILLprocedure TheUSER_DEPENDENCIESview(*) TheUSER_DEPENDENCIESview(*) TheDEPTREE_TEMPTABtable TheDEPTREE_TEMPTABtable C Coorrrreecct t CCoorrrreecctt 21.

21. AsAsingingleleproprocedcedureurecacanbnbebebothothaarefrefereerencencedodobjebjectctandandaadepdependendententobobjecjec t t.. TTrruuee oorr FFaallssee?? MMaarrkk ffoorr RReevviieeww (1)Points (1)Points True(*) True(*) False False C Coorrrreecct t CCoorrrreecctt 22

22.. FuFuncnctitiononFFETETCHCH_E_EMPMPrrefefererenencecessththeeEMEMPLPLOYOYEEEESStatablble.e.TThehett ableismodifiedby:

ableismodifiedby:

ALTERTABLEemployeesADD(resumeCLOB); ALTERTABLEemployeesADD(resumeCLOB); WhenwilltheORACLEs

WhenwilltheORACLEservertrytorecompileFervertrytorecompileFETCH_EMPautomatically?ETCH_EMPautomatically? MarkforReview

MarkforReview (1)Points

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime. 2 233.. EExxaammiinnee tthhee ffoolllloowwiinngg ccooddee:: CREATEFUNCTIONdeptfunc CREATEFUNCTIONdeptfunc RETURNNUMBERIS RETURNNUMBERIS v_countNUMBER(6); v_countNUMBER(6); BEGIN BEGIN SELECTCOUNT(*)INTOv_countFROMdepartments; SELECTCOUNT(*)INTOv_countFROMdepartments; RETURNv_count; RETURNv_count; END; END; Whichofthefollowing

WhichofthefollowingwilldisplaythedependwilldisplaythedependencybetweenDEPTFUNCandencybetweenDEPTFUNCandDEPARTMEDEPARTME NTS? NTS? MarkforReview MarkforReview (1)Points (1)Points SELECTname,type SELECTname,type FROMuser_dependencies FROMuser_dependencies WHEREnameIN('DEPTFUNC','DEPARTMENTS'); WHEREnameIN('DEPTFUNC','DEPARTMENTS'); SELECTname,type,referenced_name,referenced_type SELECTname,type,referenced_name,referenced_type FROMuser_dependencies FROMuser_dependencies WHEREreferenced_name='DEPARTMENTS' WHEREreferenced_name='DEPARTMENTS' ANDreferenced_type='TABLE'; ANDreferenced_type='TABLE'; (*) (*) SELECTname,type,referenced_name,referenced_type SELECTname,type,referenced_name,referenced_type FROMuser_dependencies FROMuser_dependencies WHEREname='DEPARTMENTS' WHEREname='DEPARTMENTS' ANDtype='TABLE'; ANDtype='TABLE'; SELECTobject_name,object_type SELECTobject_name,object_type FROMuser_objects FROMuser_objects WHEREobject_nameIN('DEPARTMENTS','DEPTFUNC') WHEREobject_nameIN('DEPARTMENTS','DEPTFUNC') ANDreferenced='YES'; ANDreferenced='YES';

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime.

Trusted by over 1 million members

Try Scribd

FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime.

Thespecificationand

ThespecificationandbodyofEMPPACKareinvabodyofEMPPACKareinvalidated,butCALL_EMPremlidated,butCALL_EMPremaa insvalid.

insvalid.

ThebodyofEMPPACKis

ThebodyofEMPPACKisinvalidated,butthespinvalidated,butthespecificationremainsvalidecificationremainsvalid.. (*)

(*)

EMPPACK.GET_EMPisinv

EMPPACK.GET_EMPisinvalidated,butotherprocalidated,butotherproceduresinEMPPACKremaineduresinEMPPACKremainvv alid.

alid.

Nothingisinvalidated

NothingisinvalidatedbecausethePL/SQLcodebecausethePL/SQLcodedoesnotreferencetheGdoesnotreferencetheGEE NDERcolumn.

NDERcolumn.

C

Coorrrreecct t CCoorrrreecctt

25

25.. AASESELELECTCTffrorommththeeDEDEPTPTREREEEtatablbleedidispsplalaysysttabableleLLOCOCATATIOIONSNS atnestedlevel0and

atnestedlevel0andprocedureLOCPROCatneprocedureLOCPROCatnestedlevel2.Thisshowsstedlevel2.ThisshowsthatLOCPthatLOCP ROCisdirectlydepend

ROCisdirectlydependentonLOCATIONS.TrueoentonLOCATIONS.TrueorFalse?rFalse? MarkforReviewMarkforReview (1)Points (1)Points True True False(*) False(*) C Coorrrreecct t CCoorrrreecctt