1. Examine the following following code: DECLARE CURSOR region_cur S SELEC! " #RO$ wf_world_region%& '_region_rec region_cur(RO)!*+E& region_cur(RO)!*+E& CURSOR countr,_cur -_region_id /U$0ER S SELEC! " #RO$ wf_countrie% )2ERE region_id 3 _region_id& '_countr,_rec '_countr,_rec countr,_cur(RO)!*+E& countr,_cur(RO)!*+E& 0E4/ O+E/ region_cur& LOO+ #E!C2 region_cur /!O '_region_rec& '_region_rec& E5! )2E/ region_cur(/O!#OU/D& region_cur(/O!#OU/D& D0$S_OU!+U!.+U!_L/E -'_region_rec.region_name& 66 Line A 66 LOO+ #E!C2 countr,_cur /!O '_countr,_rec& '_countr,_rec& E5! )2E/ countr,_cur(/O!#OU/D& ...... )hat would ,ou code at Line A7 O+E/ countr,_cur -'_region_rec.region_id& -'_region_rec.region_id& -" O+E/ countr,_cur& O+E/ countr,_cur -region_cur.region_id& O+E/ countr,_cur -_region_id& -_region_id& O+E/ countr,_cur -wf_world_region%.region_id& -wf_world_region%.region_id&
8. )hich )hich of the the follow following ing i% a good rea%on rea%on to to u%e two two cur%or% cur%or% in in a %ingle %ingle +L9SL +L9SL ;loc<7 ;loc<7 t i% the onl, wa, to declare a cur%or with a arameter. )hen two ta;le% are related to each other -o=en ;, a foreign
le'el reort u%ing data from ;oth ta;le%. -" !o %eed u the execu>on of the +L9SL ;loc<. !o allow one cur%or to ;e oened twice at the %ame >me. !o allow row% to ;e loc
?. )hat i% one of the ad'antage% of u%ing arameter% with a cur%or7 *ou can u%e a %ingle cur%or to fetch a di@erent %et of row% each >me the cur%or i% oened. -" t will execute much fa%ter than a cur%or without arameter%. arameter%. *ou can declare the cur%or #OR U+DA!E. *ou can u%e a cur%or #OR loo. *ou do not need to DECLARE the cur%or at all.
. )hat i% mi%%ing from the following cur%or declara>on7 CURSOR em_cur% S SELEC! " #RO$ deartment% )2ERE loca>on_id 3 _loc_id& /othing i% wrong& the cur%or declara>on i% correct. !he declara>on i% in'alid. *ou cannot reference reference a cur%or arameter in a )2ERE clau%e. A arameter i% mi%%ing. !he arameter %hould ;e coded a%: -_loc_id / /U$0ER A arameter i% mi%%ing. !he arameter %hould ;e coded a%: -_loc_id /U$0ER -"
B. )hat i% wrong with the following iece of code7
0E4/ #OR em_record / em_cur%or LOO+ D0$S_OU!+U!.+U!_L/E-em_record.la%t_name& E/D LOO+& # em_record.la%t_name 3 +atel !2E/ ... t %hould read: D0$S_OU!+U!.+U!_L/E-em_cur%or.la%t_name& /othing i% wrong& the code will execute correctl,. !he cur%or ha% not ;een O+E/ed. *ou cannot reference E$+_RECORD out%ide the loo. -" E$+_RECORD ha% not ;een exlicitl, declared.
. )hat i% the DSad'antage of u%ing a cur%or #OR loo with a %u;uer,7 *ou cannot declare the cur%or in the declara>on %ec>on. *ou cannot reference cur%or aFri;ute% %uch a% (/O!#OU/D. -" *ou cannot u%e the cur%or to Goin two or more ta;le%. !he execu>on %eed i% %lower. !here are no di%ad'antage%.
H. *ou ha'e declared a cur%or a% follow%: CURSOR loc_cur% S SELEC! " #RO$ loca>on%& 2ow %hould ,ou code a #OR loo to u%e thi% cur%or7 #OR loc_cur% / loc_rec LOO+ ... )2LE loc_rec / loc_cur% LOO+ ... #OR loc_rec / loc_cur% LOO+ ... -" # loc_rec / loc_cur% LOO+ ... #OR loc_rec / 1 .. loc_cur%(RO)COU/! LOO+ ...
I. Examine the following code: DECLARE CURSOR det_cur% S SELEC! deartment_name #RO$ deartment%& '_det_name deartment%.deartment_name(!*+E& 0E4/ O+E/ det_cur%& LOO+ #E!C2 det_cur% /!O '_det_name& D0$S_OU!+U!.+U!_L/E-'_det_name& E5! )2E/ det_cur%(/O!#OU/D& E/D LOO+& CLOSE det_cur%& E/D& !here are 1J row% in the DE+AR!$E/!S ta;le. )hat will haen when thi% code i% executed7 A /O_DA!A_#OU/D exce>on will ;e rai%ed. 1J row% will ;e di%la,ed. !he la%t row will ;e di%la,ed twice. -" !he loo will execute fore'er& the %ame 1J row% will ;e di%la,ed o'er and o'er again. 1J row% will ;e di%la,edK followed ;, a row of /ULL 'alue%.
. )hat will haen when the following code i% executed7 DECLARE CURSOR em_cur% S
SELEC! %alar, #RO$ emlo,ee%& '_%alar, emlo,ee%.%alar,(!*+E& 0E4/ O+E/ em_cur%& #E!C2 em_cur% /!O '_%alar,& CLOSE em_cur%& #E!C2 em_cur% /!O '_%alar,& E/D& !he ;loc< will fail and a !OO_$A/*_RO)S exce>on will ;e rai%ed. !he Mr%t emlo,ee row will ;e fetched twice. !he ;loc< will fail and an /NALD_CURSOR exce>on will ;e rai%ed. -" !he Mr%t two emlo,ee row% will ;e fetched.
1J. )hat i% wrong with the following code7 DECLARE CURSOR em_cur% S SELEC! la%t_nameK %alar, #RO$ emlo,ee%& '_la%t_name emlo,ee%.la%t_name(!*+E& '_%alar, emlo,ee%.%alar,(!*+E& 0E4/ #E!C2 em_cur% /!O '_la%t_nameK '_%alar,& O+E/ em_cur%& #E!C2 em_cur% /!O '_la%t_nameK '_%alar,& CLOSE em_cur%& E/D& !he Mr%t row i% #E!C2ed ;efore the cur%or i% O+E/ed. -" )hen #E!C2ing more than one rowK ,ou $US! u%e a loo. !he cur%or declara>on doe% not include a )2ERE condi>on. !he cur%or declara>on doe% not include an /!O clau%e.
11. )hen mu%t ,ou declare and u%e an exlicit cur%or7 *ou want to ;e a;le to ROLL0AC a tran%ac>on if needed. *ou need to SELEC! more than one row from a ta;le. -" *ou want to u%e a $ER4E %tatement. *ou need to U+DA!E more than one row in a ta;le.
18. )hen can we u%e the )2ERE CURRE/! O# clau%e7 Onl, with a DELE!EK ;ut not with an U+DA!E )hen the cur%or ha% not ;een O+E/ed )hen the cur%or i% ;a%ed on a %ingle ta;le -not on a Goin )hen the cur%or i% declared a% SELEC! ... #OR U+DA!E ...& -" Onl, with an U+DA!EK ;ut not with a DELE!E
1?. Examine the following code: DECLARE CURSOR c S SELEC! " #RO$ emlo,ee% #OR U+DA!E&
c_rec c(RO)!*+E& 0E4/ O+E/ c& #OR i / 1..8J LOO+ #E!C2 c /!O c_rec& # i 3 !2E/ U+DA!E emlo,ee% SE! Mr%t_name 3 Poe )2ERE CURRE/! O# c& E/D #& E/D LOO+& CLOSE c& E/D& )hich emlo,ee row or row% will ;e udated when t hi% ;loc< i% executed7 !he th fetched row will ;e udated. -" !he ;loc< will not comile ;ecau%e the cur%or %hould ha'e ;een declared .... #OR U+DA!E )A! B&
/one of the%e. !he Mr%t fetched row% will ;e udated. /o row% will ;e udated ;ecau%e ,ou loc
1. *ou ha'e declared the following cur%or: CURSOR countr,_cur% S SELEC! " #RO$ wf_countrie% ORDER 0* countr,_name& !here are o'er 8JJ row% in the )#_COU/!RES ta;leK ;ut ,ou want to fetch and di%la, onl, the Mr%t 8B row%. 2ow would ,ou exit from the #E!C2 loo7 E5! )2E/ countr,_cur%(#OU/D-8B& E5! )2E/ RO)COU/! Q 8B& )2E/ countr,_cur% Q 8B !2E/ E5!& E/D #& E5! )2E/ countr,_cur%(RO)COU/! Q 8B& -"
1B. A%%ume that ,ou ha'e declared a cur%or called C_E$+. )hich of the following %tatement% a;out C_E$+ i% correct7 -Choo%e two. *ou can u%e c_em(/O!#OU/D to exit a loo. -" *ou can fetch row% when c_em(SO+E/ e'aluate% to #ALSE. *ou can u%e c_em(#OU/D a=er the cur%or i% clo%ed. *ou can u%e c_em(RO)COU/! to return the num;er of row% returned ;, the cur%or %o far. -"
1. *ou can reference exlicit cur%or aFri;ute% directl, in a SL %tatement. !rue or #al%e7 !rue #al%e -"
1H. )hich of the following %tatement% a;out u%er6deMned +L9SL record% i% /O! true7 t can ;e u%ed a% an OU! arameter in a ac
1I. n an /DE5 0* ta;le of record% the record can ;e _______________________. (RO)!*+E -"
Either one. a u%er6deMned record
1. )hat will ;e di%la,ed when the following code i% executed7 outerQQ DECLARE '_m,'ar /U$0ER& 0E4/ '_m,'ar :3 1J& DECLARE '_m,'ar /U$0ER :3 8JJ& 0E4/ outer.'_m,'ar :3 8J& '_m,'ar :3 '_m,'ar 9 J& 66 thi% rai%e% a ERO_DNDE error outer.'_m,'ar :3 ?J& E/D& '_m,'ar :3 J& E5CE+!O/ )2E/ ERO_DNDE !2E/ D0$S_OU!+U!.+U!_L/E-'_m,'ar& E/D& 8J -" J 8JJ 1J ?J
8J. !he following code doe% not 'iolate an, con%traint% and will not rai%e an ORA6J888 error. )hat will haen when the code i% executed7 0E4/ DECLARE e_con%traint_'iola>on E5CE+!O/& +RA4$A E5CE+!O/_/!-e_con%traint_'iola>onK 6888& 0E4/ D0$S_OU!+U!.+U!_L/E-nner ;loc< me%%age& E/D& E5CE+!O/ )2E/ e_con%traint_'iola>on !2E/ D0$S_OU!+U!.+U!_L/E-Outer ;loc< me%%age& E/D& nner ;loc< me%%age will ;e di%la,ed. !he code will fail ;ecau%e line %hould read: +RA4$A E5CE+!O/_/!-6888K e_con%traint_'iola>on&
!he code will fail ;ecau%e the exce>on i% declared in the inner ;loc< ;ut i% referenced in the outer ;loc<. -" Outer ;loc< me%%age will ;e di%la,ed.
81. )hich of the%e exce>on% can ;e handled ;, an E5CE+!O/ %ec>on in a +L9SL ;loc<7 An aFemt i% made to di'ide ;, Tero An, other on that can occur within the ;loc< A SELEC! %tatement return% no row% /one of the%e. All of the%e. -"
88. )hile a +L9SL ;loc< i% execu>ngK more than one exce>on can occur at the %ame >me. !rue or #al%e7
!RUE #ALSE -"
8?. )hich of the following are good rac>ce guideline% for exce>on handling7 -Choo%e three. !e%t ,our code with di@erent com;ina>on% of data to %ee what oten>al error% can haen. -" 2andle %eciMc named exce>on% where o%%i;leK in%tead of rel,ing on )2E/ O!2ERS. -" Allow exce>on% to roagate ;ac< to t he calling en'ironment. U%e an exce>on handler whene'er there i% an, o%%i;ilit, of an error occurring. -" nclude a )2E/ O!2ERS handler a% the Mr%t handler in the exce>on %ec>on.
8. !he following line of code i% correct. !rue or #al%e7 RASE_A++LCA!O/_ERROR-681JJ1K$, error me%%age& !rue #al%e -"
8B. )hat i% wrong with the following code7 0E4/ U+DA!E emlo,ee% SE! %alar, 3 8JJJJ )2ERE Go;_id 3 CLER& # SL(RO)COU/! 3 J !2E/ RASE /O_DA!A_#OU/D& 66 Line A E/D #& E5CE+!O/ )2E/ /O_DA!A_#OU/D !2E/ D0$S_OU!+U!.+U!_L/E-/o emlo,ee wa% udated& E/D& *ou cannot u%e SL(RO)COU/! in condi>onal control %tatement% %uch a% # or CASE. *ou cannot exlicitl, rai%e redeMned Oracle Ser'er error% %uch a% /O_DA!A_#OU/D. /O_DA!A_#OU/D ha% not ;een DECLAREd. /othing i% wrong& the code will execute correctl,. -" Line A %hould ;e: 2A/DLE /O_DA!A_#OU/D
8. )hat i% a u%er6deMned exce>on7 An exce>on which i% not rai%ed automa>call, ;, the Oracle %er'erK ;ut mu%t ;e declared and rai%ed exlicitl, ;, the +L9SL rogrammer. -" An exce>on handler which the u%er -the rogrammer include% in the E5CE+!O/ %ec>on. An exce>on which ha% a redeMned Oracle error num;er ;ut no redeMned name. A redeMned Oracle %er'er exce>on %uch a% /O_DA!A_#OU/D.
8H. An aFemt to in%ert a null 'alue into a /O! /ULL ta;le column rai%e% an ORA6J1JJ exce>on. 2ow can ,ou code an exce>on handler to tra thi% exce>on7 Declare a 'aria;le e_null_exce of t,e E5CE+!O/K a%%ociate it with ORA6J1JJ u%ing a +RA4$A direc>'eK and te%t for )2E/ e_null_exce in the exce>on %ec>on. -" Declare a 'aria;le e_null_exce of t,e NARC2AR8K a%%ociate it with ORA6J1JJ u%ing a +RA4$A direc>'eK and te%t for )2E/ e_null_exce in the exce>on %ec>on. Declare a 'aria;le a% follow%: e_null_exce E5CE+!O/ :3 6J1JJ& !hen te%t for )2E/ e_null_exce in the exce>on %ec>on. !e%t for )2E/ ORA61JJ in the exce>on %ec>on.
8I. A +L9SL ;loc< execute% and an Oracle Ser'er exce>on i% rai%ed. )hich of the following contain% the text me%%age a%%ociated with the exce>on7 SL_$ESSA4E_!E5!
SL($ESSA4E SLERR$ -" SLCODE
8. )hich of the following i% /O! a redeMned Oracle Ser'er error7 e_%al_too_high E5CE+!O/& -" /O_DA!A_#OU/D DU+_NAL_O/_/DE5 ERO_DNDE !OO_$A/*_RO)S
?J. )hich of the following can /O! ;e u%ed a% the datat,e of a rocedure arameter7 A +LSL record deMned u%ing (RO)!*+E A non6SL datat,e %uch a% 0OOLEA/ A large o;Gect datat,e %uch a% CLO0 !he name of another rocedure -"
?1. +arameter% are a %ecial form of a 'aria;leK who%e inut 'alue% are ini>aliTed ;, the calling en'ironment when the %u;rogram i% calledK and who%e outut 'alue% are returned to the calling en'ironment when the %u;rogram return% control to the caller. !rue -" #al%e
?8. )hat will haen when the following rocedure i% called a% format_hone -IJJBBB18?7 CREA!E OR RE+LACE +ROCEDURE format_hone -_hone_no / OU! NARC2AR8 S 0E4/ _hone_no :3 SU0S!R-_hone_noK1K? . SU0S!R-_hone_noKK? . SU0S!R-_hone_noKH& E/D format_hone& !he rocedure doe% not execute ;ecau%e the inut 'aria;le i% not roerl, declared. !he hone num;er IJJ.BBB.18? i% laced into the _hone_no 'aria;le. -" !he hone num;er -IJJ BBB618? i% rinted to the %creen. !he hone num;er IJJ.BBB.18? i% rinted to the %creen.
??. +rocedure /U$+ROC ha% ;een created a%: CREA!E +ROCEDURE numroc -x /U$0ERK , /U$0ER :3 1JJK T /U$0ER S 0E4/ .... *ou want to call the rocedureK a%%ing argument% of 1J for 5 and 8J for . )hich one of the following call% i% correct7 numroc-x31JKT38J& numroc-1JKT3Q8J& -" numroc-x3Q1JK8J& numroc-1JKK8J&
?. )hat i% another name for a ne%ted %u;rogram7 Local %u;rogram -" Limited %u;rogram 2o%ted %u;rogram
?B. )h, will the following rocedure fail7 CREA!E OR RE+LACE +ROCEDURE mainroc
... S +ROCEDURE %u;roc -... S 0E4/ ... 0E4/ ... %u;roc -...& ... E/D& +rocedure main roc mu%t u%e the
?. )hich of the following are ;eneMt% of u%ing +L9SL %u;rogram% rather than anon,mou% ;loc<%7 -Choo%e three.
Do not need to declare 'aria;le% Ea%ier to write 0eFer data %ecurit, -" #a%ter erformance -" Ea%ier code maintenance -"
?H. Examine the following code: CREA!E OR RE+LACE #U/C!O/ add_func -_aram1 /U$0ERK _aram8 /U$0ER RE!UR/ /U$0ER S 0E4/ RE!UR/ -_aram1 V _aram8& E/D& )hat will ;e di%la,ed when the following SL %tatement i% executed7 SELEC! add_func-K add_func-?KI #RO$ dual& An error me%%age will ;e di%la,ed ;ecau%e ,ou cannot ne%t u%er6deMned func>on%. 8? 11 1H -"
?I. A func>on mu%t ha'e at lea%t one / arameterK and mu%t return exactl, one 'alue. !rue #al%e -"
?. CREA!E #U/C!O/ get_%al -_id emlo,ee%.emlo,ee_id(!*+E RE!UR/ num;er S '_%al emlo,ee%.%alar,(!*+E :3 J& 0E4/
SELEC! %alar, /!O '_%al #RO$ emlo,ee% )2ERE emlo,ee_id 3 _id& RE!UR/ '_%al& E/D get_%al& )hich 'aria;le i% a%%ed to the func>on and which 'aria;le i% returned from the func>on7 +_D i% a%%ed and N_SAL i% returned. -" SALAR* i% a%%ed and +_D i% returned. 4E!_SAL i% a%%ed and N_SAL i% returned. E$+LO*EE_D i% a%%ed and SALAR* i% returned.
J. *ou need to remo'e rocedure 0AD+ROC from ,our %chema. )hat i% the correct %,ntax to do thi%7 DELE!E +ROCEDURE ;adroc& DRO+ +ROCEDURE ;adroc& -" AL!ER +ROCEDURE ;adroc DSA0LE& DRO+ +RO4RA$ ;adroc&
1. !he data;a%e admini%trator ha% granted the DRO+ A/* +ROCEDURE ri'ilege to u%er $. !hi% allow% im to remo'e other u%er% rocedure% and func>on% from the data;a%e. 2ow would im now dro func>on 4E!_E$+K which i% owned ;, u%er $E2$E!7 DRO+ +RO4RA$ mehmet.get_em DRO+ #U/C!O/ mehmet.get_em -" DRO+ #U/C!O/ get_em #RO$ mehmet DRO+ +ROCEDURE mehmet.get_em /one of the%e
8. )hich o;Gect ri'ilege can ;e granted on a %ingle column of a ta;le7 U+DA!E -" DRO+ SELEC! CREA!E DELE!E
?. *our %chema contain% two rocedure% named C2LD1 and C2LD8. *ou now create a third rocedure ;, execu>ng: CREA!E OR RE+LACE +ROCEDURE arent S 0E4/ child1& child8& E/D& *ou now want u%er POE to ;e a;le to in'o
. )hich of the following i% a ;eneMt of u%er6deMned func>on%7 -Choo%e ? !he, can add ;u%ine%% rule% to the data;a%e and can ;e reu%ed man, >me%. -" !he, can ;e u%ed in a )2ERE clau%e to Mlter data and there;, increa%e eWcienc,. -"
!he, can o=en ;e u%ed in%ide SL %tatement%. -" !he, can do the %ame Go; a% ;uilt6in %,%tem func>on% %uch a% U++ER and ROU/D.
B. )hich of the following i% a legal loca>on for a func>on call in a SL %tatement7 -Choo%e ? !he ORDER 0* and 4ROU+ 0* clau%e% of a uer, -" NALUES clau%e of an /SER! %tatement -" )2ERE clau%e in a DELE!E %tatement -" CREA!E !A0LE %tatement
. )hich of the following i% /O! a ;eneMt of u%er6deMned func>on%7 !he, can add ;u%ine%% rule% to the data;a%e and can ;e reu%ed man, >me%. !he, can ;e u%ed in a )2ERE clau%e to Mlter data. !he, can do the %ame Go; a% ;uilt6in %,%tem func>on% %uch a% U++ER and ROU/D. -" !he, can o=en ;e u%ed in%ide SL %tatement%.
H. A u%er execute% the following %tatement: CREA!E /DE5 fn_index O/ emlo,ee%-Mr%t_name& )hat outut will the following %tatement now di%la,: SELEC! index_name #RO$ u%er_indexe% )2ERE index_name LE fn(& /o outut will ;e di%la,ed -" #/_/DE5 fn_index #/_/DE5 fn_index
I. *ou want to di%la, the name% of all ta;le% in ,our %chemaK ;ut ,ou ha'e forgoFen which Dic>onar, 'iew to uer,. )hich of the following will remind ,ou of the name of the correct Dic>onar, 'iew7 SELEC! " #RO$ DC!O/AR* )2ERE ta;le_name LE u%er(ta;le(& 2EL+ DC!O/AR* SELEC! " #RO$ DC!O/AR* )2ERE ta;le_name LE USER(!A0(& -" SELEC! " #RO$ DC!O/AR* )2ERE ta;le_name LE D0A(!A0LE(& SELEC! " #RO$ USER_O0PEC!S )2ERE ta;le_name LE (!A0LE(&
. +rocedure 4E!_E$+S include% a SELEC!X#RO$ E$+LO*EES. !he rocedure wa% created u%ing n'o
!he creator of the rocedure need% SELEC! ri'ilege on E$+LO*EES. -"
BJ. 2ow do ,ou %ecif, that ,ou want a rocedure $*+ROCA to u%e YDeMner% Right%Y7 CREA!E OR RE+LACE +ROCEDURE m,roca AU!2D CURRE/!_USER S... DeMner% Right% are the defaultK therefore no extra code or command% are needed. -" AL!ER +ROCEDURE m,roca !O DE#/ER& CREA!E OR RE+LACE +ROCEDURE m,roca AU!2D O)/ER S... 4RA/! DE#/ER !O m,rocA&
