The most important DDL statements in SQL are: CREATE TABLE - creates a new database table ALTER TABLE - alters (changes a database table DR!" TABLE - deletes a database table TR#$CATE - cleans all data RE$A%E- renames a table name &' !perators sed in SELECT statements' ) E*al +, or ) $ot e*al , .reater than +,) .reater than or e*al +) Less than or e*al BET/EE$ Between an inclsi0e range L12E Search 3or a pattern 4' SELECT statements: SELECT colmn5name(s 6R!% table5name SELECT D1ST1$CT colmn5name(s 6R!% table5name SELECT colmn 6R!% table /7ERE colmn operator 0ale SELECT colmn 6R!% table /7ERE colmn L12E pattern SELECT colmn8S#%(colmn 6R!% table .R!#" B9 colmn SELECT colmn8S#%(colmn 6R!% table .R!#" B9 colmn 7A1$. S#%(colmn condition 0ale $ote that single *otes arond te;t 0ales and nmeric 0ales shold not be enclosed in *otes' Doble *otes ma< be acceptable in some databases' =' The SELECT 1$T! Statement is most o3ten sed to create bac>p copies o3 tables or 3or archi0ing records' SELECT colmn5name(s 1$T! newtable ?1$ e;ternaldatabase@ 6R!% sorce SELECT colmn5name(s 1$T! newtable ?1$ e;ternaldatabase@ 6R!% sorce /7ERE colmn5name operator 0ale ' The 1$SERT 1$T! Statements: 1$SERT 1$T! table5name AL#ES (0ale8 0ale&8'''' 1$SERT 1$T! table5name (colmn8 colmn&8''' AL#ES (0ale8 0ale&8'''' ' The #pdate Statement: #"DATE table5name SET colmn5name ) new50ale /7ERE colmn5name ) some50ale ' The Delete Statements: DELETE 6R!% table5name /7ERE colmn5name ) some50ale Delete All Rows: DELETE 6R!% table5name or DELETE 6R!% table5name F' Sort the Rows: SELECT colmn8 colmn&8 ''' 6R!% table5name !RDER B9 colmnG8 colmn98 ''
SELECT colmn8 colmn&8 ''' 6R!% table5name !RDER B9 colmnG DESC SELECT colmn8 colmn&8 ''' 6R!% table5name !RDER B9 colmnG DESC8 colmn9 ASC H' The 1$ operator ma< be sed i3
now the e;act 0ale
SELECT colmn8 colmn&8 ''' 6R!% table5name !RDER B9 colmnG DESC SELECT colmn8 colmn&8 ''' 6R!% table5name !RDER B9 colmnG DESC8 colmn9 ASC H' The 1$ operator ma< be sed i3 now the e;act 0ale
START or ' &' /hat is the 0ale o3 comm and sal a3ter e;ecting the 3ollowing *er< i3 the initial 0ale o3 sal is IIII #"DATE E%" SET SAL ) SAL O III8 C!%% ) SALI'J sal ) III8 comm ) III' &&' /hich command displa the pri0ileges o33ered b< the .RA$T commandJ RE!2E' &=' /hat will be the otpt o3 the 3ollowing *ers whether a gi0en string is a nmerical digit' &' /hich date 3nction is sed to 3ind the di33erence between two datesJ %!$T7S5BET/EE$' &' /hat operator per3orms pattern matchingJ L12E operator' &F' /hat is the se o3 the DR!" option in the ALTER TABLE commandJ 1t is sed to drop constraints speci3ied on the table' &H' /hat operator tests colmn 3or the absence o3 dataJ 1S $#LL operator' 4I' /hat are the pri0ileges that can be granted on a table b< a ser to othersJ 1nsert8 pdate8 delete8 select8 re3erences8 inde;8 e;ecte8 alter8 all' 4' /hich 3nction is sed to 3ind the largest integer less than or e*al to a speci3ic 0aleJ 6L!!R' 4&' /hich is the sbset o3 SQL commands sed to maniplate !racle Database strctres8 inclding tablesJ Data De3inition Langage (DDL' 44' /hat is the se o3 DESC in SQLJ DESC has two prposes' 1t is sed to describe a schema as well as to retrie0e rows 3rom table in descending order' E;planation : The *er< SELECT 6R!% E%" !RDER B9 E$A%E DESC will displa< the otpt sorted on E$A%E in descending order'
4=' /hat command is sed to create a table b< cop' Database triggers do not 3ire on TR#$CATE DELETE allows the 3iltered deletion' Deleted records can be rolled bac> or committed' Database triggers 3ire on DELETE' =' /hat is a UoinJ E;plain the di33erent t
Correlated sb-*er< is a sb-*er<8 which has re3erence to the main *er<' ==' E;plain C!$$ECT B9 "R1!RJ Retrie0es rows in hierarchical order eg' select empno8 ename 3rom emp where' =' Di33erence between S#BSTR and 1$STRJ 1$STR (String8 String& (n8 (m8 1$STR retrns the position o3 the m-th occrrence o3 the string & in string' The search begins 3rom nth position o3 string' S#BSTR (String n8 m S#BSTR retrns a character string o3 siWe m in string8 starting 3rom n-th position o3 string' =' E;plain #$1!$8 %1$#S8 #$1!$ ALL and 1$TERSECTJ 1$TERSECT - retrns all distinct rows selected b< both *eries' %1$#S - retrns all distinct rows selected b< the 3irst *er< bt not b< the second' #$1!$ - retrns all distinct rows selected b< either *er< #$1!$ ALL - retrns all rows selected b< either *er<8 inclding all dplicates' =' /hat is R!/1DJ R!/1D is a psedo colmn attached to each row o3 a table' 1t is F characters long8 bloc>no8 rownmber are the components o3 R!/1D' =F' /hat is the 3astest wa< o3 accessing a row in a tableJ #sing R!/1D' C!$STRA1$TS =H' /hat is an integrit< constraintJ 1ntegrit< constraint is a rle that restricts 0ales to a colmn in a table' I' /hat is re3erential integrit< constraintJ %aintaining data integrit< throgh a set o3 rles that restrict the 0ales o3 one or more colmns o3 the tables based on the 0ales o3 primar< >e< or ni*e >e< o3 the re3erenced table' ' /hat is the sage o3 SAE"!1$TSJ SAE"!1$TS are sed to sbdi0ide a transaction into smaller parts' 1t enables rolling bac> part o3 a transaction' %a;imm o3 3i0e sa0e points are allowed' &' /hat is !$ DELETE CASCADEJ /hen !$ DELETE CASCADE is speci3ied !racle maintains re3erential integrit< b< atomaticall< remo0ing dependent 3oreign >e< 0ales i3 a re3erenced primar< or ni*e >e< 0ale is remo0ed' 4' /hat are the data t
=' /hat is di33erence between C7AR and ARC7AR&J /hat is the ma;imm S1XE allowed 3or each t spaces to the ma;imm length' ARC7AR& does not pad blan> spaces' 6or C7AR the ma;imm length is & and &III 3or ARC7AR&' ' 7ow man< L!$. colmns are allowed in a tableJ 1s it possible to se L!$. colmns in /7ERE clase or !RDER B9J !nl< one L!$. colmn is allowed' 1t is not possible to se L!$. colmn in /7ERE or !RDER B9 clase' ' /hat are the pre-re*isites to modi3< datate< constraint on DATE colmn is created8 will it 0alidate the rows that are inserted with S9SDATEJ 1t wonMt8 Becase S9SDATE 3ormat contains time attached with it' I' /hat is a database lin>J Database lin> is a named path throgh which a remote database can be accessed' ' 7ow to access the crrent 0ale and ne;t 0ale 3rom a se*enceJ 1s it possible to access the crrent 0ale in a session be3ore accessing ne;t 0aleJ Se*ence name C#RRAL8 se*ence name $EGTAL' 1t is not possible' !nl< i3
- To hide comple;it< o3 a *er<' - To hide comple;it< o3 calclations' =' Can a 0iew be pdatedPinsertedPdeletedJ 13 9es - nder what conditionsJ A iew can be pdatedPdeletedPinserted i3 it has onl< one base table i3 the 0iew is based on colmns 3rom one or more tables then insert8 pdate and delete is not possible' ' 13 a 0iew on a single base table is maniplated will the changes be re3lected on the base tableJ 13 changes are made to the tables and these tables are the base tables o3 a 0iew8 then the changes will be re3erence on the 0iew' ' /hich o3 the 3ollowing statements is tre abot implicit crsorsJ ' 1mplicit crsors are sed 3or SQL statements that are not named' &' De0elopers shold se implicit crsors with great care' 4' 1mplicit crsors are sed in crsor 3or loops to handle data processing' =' 1mplicit crsors are no longer a 3eatre in !racle' ' /hich o3 the 3ollowing is not a 3eatre o3 a crsor 6!R loopJ ' Record te to se re3erential datat p datat
' crsor action5crsor is &' select name8 rate8 action 4' into action5record =' 3rom action5table ' There are no errors in this statement' I' The command sed to open a C#RS!R 6!R loop is ' open &' 3etch 4' parse =' $one8 crsor 3or loops handle crsor opening implicitl<' ' /hat happens when rows are 3ond sing a 6ETC7 statement ' 1t cases the crsor to close &' 1t cases the crsor to open 4' 1t loads the crrent row 0ales into 0ariables =' 1t creates the 0ariables to hold the crrent row 0ales &' Read the 3ollowing code: I' CREATE !R RE"LACE "R!CED#RE 3ind5cpt ' (05mo0ie5id YArgment %odeZ $#%BER8 05cost5per5tic>et Yargment modeZ $#%BER &' 1S 4' BE.1$ =' 16 05cost5per5tic>et , F' T7E$ ' SELECT cost5per5tic>et ' 1$T! 05cost5per5tic>et ' 6R!% gross5receipt F' /7ERE mo0ie5id ) 05mo0ie5id H' E$D 16 &I' E$D /hich mode shold be sed 3or 5C!ST5"ER5T1C2ETJ ' 1$ &' !#T 4' RET#R$ =' 1$ !#T 4' Read the 3ollowing code: &&' CREATE !R RE"LACE TR1..ER pdate5show5gross &4' Ytrigger in3ormationZ &=' BE.1$ &' Yadditional codeZ &' E$D The trigger code shold onl< e;ecte when the colmn8 C!ST5"ER5T1C2ET8 is greater than [4' /hich trigger in3ormation will et , 4' &' /7E$ (:new'cost5per5tic>et , 4'
4' /7ERE (new'cost5per5tic>et , 4' =' /7ERE (:new'cost5per5tic>et , 4' =' /hat is the ma;imm nmber o3 handlers processed be3ore the "LPSQL bloc> is e;ited when an e;ception occrsJ ' !nl< one &' All that appl< 4' All re3erenced =' $one ' 6or which trigger timing can e this 3nction within SQL"lsJ ' AR1ABLE g5ing this procedre8
&' 7andle the error in EGCE"T1!$ section b< re3erencing the error code directl<' 4' 7andle the error in the EGCE"T1!$ section b< re3erencing the #$1Q#E5ERR!R prede3ined e;ception' =' Chec> 3or sccess b< chec>ing the 0ale o3 SQL6!#$D immediatel< a3ter the #"DATE statement' FI' Read the 3ollowing code: =I' CREATE !R RE"LACE "R!CED#RE calclate5bdget 1S =' 05bdget stdio'ed in0alid and mst be recompiled be3ore the ne;t e;ection' &' The SET5B#D.ET 3nction will be mar>ed in0alid and mst be recompiled be3ore the ne;t e;ection' 4' !nl< the CALC#LATE5B#D.ET procedre needs to be recompiled' =' All three procedres are mar>ed in0alid and mst be recompiled' F' /hich procedre can be sed to create a cstomiWed error messageJ ' RA1SE5ERR!R &' SQLERR% 4' RA1SE5A""L1CAT1!$5ERR!R =' RA1SE5SERER5ERR!R F&' The C7EC25T7EATER trigger o3 the T7EATER table has been disabled' /hich command can 5theater E$ABLE &' E$ABLE TR1..ER chec>5theater 4' ALTER TABLE chec>5theater E$ABLE chec>5theater =' E$ABLE chec>5theater F4' E;amine this database trigger &' CREATE !R RE"LACE TR1..ER pre0ent5gross5modi3ication 4' Yadditional trigger in3ormationZ =' BE.1$ ' 16 T!5C7AR(s
F' E$D 16 H' E$D This trigger mst 3ire be3ore each DELETE o3 the .R!SS5RECE1"T table' 1t shold 3ire onl< once 3or the entire DELETE statement' /hat additional in3ormation mst eeeeage bod< a3ter recompiling the pac>age speci3icationJ ' Altering the argment list o3 one o3 the pac>age constrcts &' An< change made to one o3 the pac>age constrcts 4' An< SQL statement change made to one o3 the pac>age constrcts =' Remo0ing a local 0ariable 3rom the DECLARE section o3 one o3 the pac>age constrcts F' "rocedre and 6nctions are e;plicitl< e;ected' This is di33erent 3rom a database trigger' /hen is a database trigger e;ectedJ ' /hen the transaction is committed &' Dring the data maniplation statement 4' /hen an !racle spplied pac>age re3erences the trigger =' Dring a data maniplation statement and when the transaction is committed F' /hich !racle spplied pac>age can
bac>' &' An< D%L statements issed b< the constrct are committed 4' #nless a .!T! statement is sed to contine processing within the BE.1$ section8the constrct terminates' =' The constrct rolls bac> an< D%L statements issed and retrns the nhandled e;ception to the calling en0ironment' FH' E;amine this code ' BE.1$ &' theater5pc>'05total5seats5sold5o0erall :) theater5pc>'get5total53or5age' &' !nl< the .ET5T!TAL56!R59EAR 0ariable mst e;ist in the speci3ication o3 the T7EATER5"C2 pac>age' 4' !nl< the 5T!TAL5SEATS5S!LD5!ERALL 0ariable mst e;ist in the speci3ication o3 the T7EATER5"C2 pac>age' =' Both the 5T!TAL5SEATS5S!LD5!ERALL 0ariable and the .ET5T!TAL56!R59EAR 3nction mst e;ist in the speci3ication o3 the T7EATER5"C2 pac>age' HI' A stored 3nction mst retrn a 0ale based on conditions that are determined at rntime' There3ore8 the SELECT statement cannot be hard-coded and mst be created dage will enable this 3eatreJ ' DB%S5DDL &' DB%S5D%L 4' DB%S5S9$ =' DB%S5SQL H 7ow to implement 1S$#%ER1C 3nction in SQL "ls J %ethod : Select length (translate(trim (colmn5name8MO-'I&4=FHM8MM3rom dal /ill gi0e lmnop*rst0w;
!rder b< a'repno /hat are steps re*ired tning this *er< to impro0e its per3ormanceJ -7a0e an inde; on TER5%ASTER'RE"$! and one on ER%AST'RE"$! -Be sre to get 3amiliar with EG"LA1$ "LA$' This can help es' 13 ing re3erential Constraints (to see i3 there are dependent child records and 3iring an< DELETE triggers' 1n the order s 3or the e;istence (and stats o3 another 3oreign >e< "ointing to the table' 13 one e;ists and is enabled8 then eee< constraints in the child tables be3ore issing the TR#$CATE command8 then re-enable them a3terwards' ' Describe the di33erence between a procedre8 3nction and anon' Le0el: Low E;pected answer : Candidate shold mention se o3 DECLARE statement8 a 3nction mst retrn a 0ale while a procedre doesnJt ha0e to' &' /hat is a mtating table error and how can
DB%S5TRA$SACT1!$8 DB%S5L!C28 DB%S5ALERT8 DB%S5!#T"#T8 DB%S5V!B8 DB%S5#T1L1T98 DB%S5DDL8 #TL561LE' 13 the< can mention a 3ew o3 these and describe how the< sed them8 e0en better' 13 the< inclde the SQL rotines pro0ided b< !racle8 great8 bt not reall< what was as>ed' ' Describe the se o3 "LPSQL tables E;pected answer: "LPSQL tables are scalar arras sch as with stand alone8 non-stored "LPSQL procedres' 1t mst come 3irst in a "LPSQL stand alone 3ile i3 it is sed' ' 1n what order shold a openP3etchPloop set o3 commands in a "LPSQL bloc> be implemented i3 8 i3 a crsor is openJ E;pected answer: #se the 1S!"E$ crsor stats 0ariable' I' 7ow can age' Another possible method is to Ust se the S7!/ ERR!R command8 bt this onl< shows errors' The DB%S5!#T"#T pac>age can be sed to show intermediate reslts 3rom loops and the stats o3 0ariables as the procedre is e;ected' The new pac>age #TL561LE can also be sed' ' /hat are the t
combinations o3 the BE6!RE8 A6TER8 R!/8 TABLE8 1$SERT8 #"DATE8 DELETE and ALL >e< words: BE6!RE ALL R!/ 1$SERT A6TER ALL R!/ 1$SERT BE6!RE 1$SERT A6TER 1$SERT etc' 2. 7ow can 0ariables be passed to a SQL rotineJ E;pected answer: B< se o3 the K s o33 0eri3< o33 spool drop5all's*l select Jdrop ser JsernameJ cascadeJ 3rom dba5sers where sername not in (\S9SJ8JS9STE%J spool o33 Essentiall< ing to see that the< >now to inclde a command (in this case DR!" #SER'''CASCADE and that
%a;(sm5o35cost8 min(sm5o35cost8 cont(item5no8 item5no E;pected answer: The onl< colmn that can be groped on is the \item5no\ colmn8 the rest ha0e aggregate 3nctions associated with them' F' /hat special !racle 3eatre allows e< ' 6or e;ample: select rowid 3rom emp e where e'rowid , (select min(;'rowid 3rom emp ; where ;'emp5no ) e'emp5no 1n the sitation where mltiple colmns ma>e p the proposed >e<8 the< mst all be sed in the where clase' I' /hat is a Cartesian prodctJ E;pected answer: A Cartesian prodct is the reslt o3 an nrestricted Uoin o3 two or more tables' The reslt set o3 a three table Cartesian prodct will ha0e ; < W nmber o3 rows where ;8 <8 W correspond to the nmber o3 rows in each table in0ol0ed in the Uoin' ' 9o are Uoining a local and a remote table8 the networ> manager complains abot the tra33ic in0ol0ed8 how can tra33icJ Le0el: 7igh E;pected answer: "sh the processing o3 the remote data to the remote instance b< sing a 0iew to pre-select the in3ormation 3or the Uoin' This will reslt in onl< the data re*ired 3or the Uoin being sent across' &' /hat is the de3alt ordering o3 an !RDER B9 clase in a SELECT statementJ E;pected answer: Ascending 4' /hat is t>pro3 and how is it sedJ Le0el: 1ntermediate to high E;pected answer: The t>pro3 tool is a tning tool sed to determine cp and e;ection times 3or SQL statements' 9o se it b< 3irst setting timed5statistics to tre in the initialiWation 3ile and then trning on tracing 3or either the entire database 0ia the s*l5trace parameter or 3or the session sing the ALTER SESS1!$ command' !nce the trace 3ile is generated pro3 tool against the trace 3ile and then loo> at the otpt 3rom the t>pro3 tool' This can also be sed to generate e;plain plan otpt' =' /hat is e;plain plan and how is it sedJ Le0el: 1ntermediate to high E;pected answer: The EG"LA1$ "LA$ command is a tool to tne SQL statements' To se it
are rnning the e;plain plan 3or' This is created sing the tl;plan's*l script' !nce the e;plain plan table e;ists pro3' ' 7ow do
/. Write a query to display 1 row selected while there is o data i the table. !ELECT C$0T)"+ #R$% emp( . Write a query which display emp_id emp_ame alo3 with a e4tra 5eld " All Employees li6e this. Employee Code Employee ame " All Employees E771 !usheel E772
8ai !ELECT emp_id 9Employee Code9* emp_ame 9Employee ame9 #R$% emp 0:$ !ELECT ;"; 9Employee Code9* ;All Employees; 9Employee ame9 #R$% emp $R,ER B< 1( =. Write a query to display alterate odd or e>e rows. !ELECT " #R$% emp W&ERE )R$W:,* 7+ $T : )!ELECT R$W:,* %$,)R$W0%* 2+ #R$% emp+( ?. ,isplay the ame o@ employees ad their maa3ers @rom emp table by usi3 sel@ oi !ELECT e.emp_ame* m.emp_ame #R$% emp e* emp m W&ERE e.maa3er_id ' m.emp_id $R,ER B< e.emp_id( . Write a query to display the departmets ad o. o@ employees wor6i3 i those departmets !ELECT dept_id* C$0T)emp_id+ #R$% emp R$0- B< dept_id( D. Write a query which displays those etries which are ot duplicate. !ELECT " #R$% emp W&ERE R$W:, $T :)!ELECT %:)R$W:,+ #R$% emp R$0B< emp_id+( . Write a query which display emp_id ad mi ma4 salary i same colum li6e this. Employee !alary E771 1*777 mi)salary+ E77= 17*777 ma4)salary+ !ELECT emp_id 9Employee9* salary 9!alary9 #R$% emp W&ERE salary ' )!ELECT %:)salary+ #R$% emp+ 0:$ !ELECT emp_id 9Employee9* salary 9!alary9 #R$% emp W&ERE salary ' )!ELECT %AF)salary+ #R$% emp+( !ELECT emp_id* salary #R$% emp W&ERE salary G' all )!ELECT salary #R$% emp+ 0:$ !ELECT emp_id* salary #R$% emp W&ERE salary H' all )!ELECT salary #R$% emp+( 17. Write a query to display earest mi ad earest ma4 @rom a>era3e salary.
!ELECT emp_id* salary #R$% emp W&ERE salary G' )!ELECT %AF)salary+ #R$% emp W&ERE salary ' )!ELECT AI)salary+ #R$% emp++ 0:$ !ELECT emp_id* salary #R$% emp W&ERE salary H' )!ELECT %:)salary+ #R$% emp W&ERE salary ' )!ELECT AI)salary+ #R$% emp++( 11. ,isplay the list o@ top employees 3etti3 hi3hest salary !ELECT empo* sal #R$% )!ELECT empo* sal #R$% emp $R,ER B< sal ,E!C+ W&ERE rowum G ' ( 12. #id %a4imum salary ,epartmet ad Employee wise !ELECT a.empo* a.depto* b.sal #R$% emp a* )!ELECT " #R$% )!ELECT empo* depto* sal* dese_ra6)+ o>er)partitio by depto order by sal desc+ r #R$% emp++ b W&ERE a.depto ' b.depto)J+ A, b.empo)J+ ' a.empo A, b.r)J+ ' 1( 1/. ,isplay the list o@ employee 3etti3 th hi3hest salary. !ELECT empo* sal #R$% )!ELECT " #R$% emp $R,ER B< sal ,E!C+ W&ERE R$W0% G' %:0! !ELECT empo* sal #R$% )!ELECT " #R$% emp $R,ER B< sal ,E!C+ W&ERE R$W0% G' K1( $R* !ELECT r* empo* sal #R$% )!ELECT rowum r* empo* sal #R$% )!ELECT empo* sal #R$% emp $R,ER B< sal ,E!C++ W&ERE r ' ( $R* !ELECT ,:!T:CT a.empo* a.sal #R$% emp a W&ERE ' )!ELECT C$0T),:!T:CT b.sal+ #R$% emp b W&ERE a.sal G' b.sal+( $R* !ELECT " #R$% )!ELECT empo*sal*ra6)+ o>er )$R,ER B< sal ,E!C+ r6 #R$% emp+ W&ERE r6 G' ( $R* !ELECT le>el* ma4)salary+ #R$% emp W&ERE le>el ' C$ECT B< -R:$R salary H salary R$0- B< le>el( 1. ,elete duplicate etries @rom a table. ,ELETE #R$% emp W&ERE R$W:, $T : )!ELECT %:)R$W:,+ #R$% emp R$0- B< emp_id+( 1=. ,elete the etries @rom emp table which are ot matchi3 @rom emp_history table by usi3 correlated subquery. ,ELETE #R$% emp e W&ERE )e.emp_id* e.maa3er_id* e.salary+ $T : )!ELECT h.emp_id* h.maa3er_id* h.salary #R$% emp_history h
W&ERE e.emp_id ' h.emp_id A, e.maa3er_id ' h.maa3er_id A, e.salary ' h.salary+( 1?. !elect last ame @rom emp table o@ a employee while ame is 3i>e @ull. !ELECT LTR:%)!0B!TR)ame*:!TR)ame*; ;*1++*; ;+ #R$% emp( 1D. Write a query which shows all data i@ the 0LL is also e4ists while passi3 0LL. !ELECT t."* t.rowid #R$% emp t W&ERE IL)t.empo*7+ ' IL)emp*>l)t.empo*7++( 1. 0pdate the ame o@ a employee i swapi3 maer @or e3. :@ ame is amit the reha ad reha the amit by si3le update. 0-,ATE emp !ET empame ' ,EC$,E)empame* ;Amit;* ;Reha;* ;Reha;* ;Amit;+( 27. Write a query to sort all colums idepedetly select tab_4.a* tab_y.b @rom )select tab_a.a* rowum r1 @rom )select a @rom a_1 order by 1+ tab_a+ tab_4* )select tab_b.b* rowum r2 @rom )select b @rom a_1 order by 1+ tab_b+ tab_y where tab_4.r1 ' tab_y.r2( 21. Write a query to prit distict data i a si3le row select a* b* sum)4+* ;A; T1* sum)y+* ;B; T2 @rom )select a a* b b* c 4* 7 y @rom a_dummy where d ' ;A; uio select a a* b b* 7 4* C y @rom a_dummy where d ' ;B;+ 3roup by a* b( 22. Write a query to select all tables i database does ot cotai ay row. select table_ame* sum)um_ulls J um_distict+ table_rows @rom user_tab_colums 3roup by table_ame ha>i3 sum)um_ulls J um_distict+ ' 7( $R* select table_ame* um_rows table_rows @rom user_tables where a.um_rows ' 7( 2/. Write a query to cha3e the amout i dieret @ormats. !ELECT ltrim)to_char)-_A%$0T* ;,;*
;L!_0%ER:C_C&ARACTER! ' ;;.;;;;;+* ; ;+ amt_co>erter #R$% dual( !ELECT ltrim)to_char)-_A%$0T* ;,%:;* ;L!_0%ER:C_C&ARACTER! ' ;;*.;;;+* ; ;+ amt_co>erter #R$% dual( 2. Write a query to co>ert the amout i words !ELECT to_char)to_date)-_A%$0T_LE!!_T&A_=//D* ;8;+* ;8sp;+ MM ; $ly; amout_i_word #R$% dual( 2=. Write a query to 5d out umber o@ particular character i a stri3 !ELECT C$0T)$_$#_C&AR+ $_$#_C&AR #R$% )!ELECT 0--ER)!0B!TR);str;* R$W0%* 1++ $_$#_C&AR #R$% 0!ER_$B8ECT! W&ERE R$W0% G' LET&);str;++ W&ERE $_$#_C&AR ' 0--ER);char;+( 2D. Cout umbers* 0pperKLower Characters* ad !ymbols KK 8ust #or umbers select LET&);!TR:;+ LEN* LET&)TRA!LATE);!TR:;* ;A12/=?D7;* ;A;++ 9KK oKumeric Charcters KK9 @rom dual( LEN KK oKumeric Charcters KK KKKKKKKKKK KKKKKKKKKKKKKKKKKKKKKKKKKKK /? // KK #or umbers* 0pperKLower Characters* $thers select le3th);!TR:;+ K LET&)TRA!LATE);!TR:;* ;A12/=?D7;* ;A;++ 9KK umeric KK9* LET&)TRA!LATE);!TR:;* ;A12/=?D7;* ;A;++ 9KK oKumeric Charcters KK9* !0%)le3th);!TR:;+ K LET&)RE-LACE);!TR:;* !i3le_0pperChar+++ 0pperN* !0%)le3th);!TR:;+ K LET&)RE-LACE);!TR:;* !i3le_LowerChar+++ LowerN @rom )select C&R)? J R$W0%+ !i3le_0pperChar* C&R)? J R$W0%+ !i3le_LowerChar @rom all_syoyms where rowum G 2+(
KK umeric KK KK oKumeric Charcters KK 0--ERN L$WERN KKKKKKKKKKKKK KKKKKKKKKKKKKKKKKKKKKKKKKKK KKKKKK KKKKKKKK / // 1D 2. Write a query to display total >owels ad cosoats i a stri3 !ELECT LET&)REEF-_RE-LACE);!TR;* ;)OaeiouAE:$0P+M.;* ;Q1;++ tot_>owels* LET&)REEF-_RE-LACE);!TR;* ;)Obcd@3h6lmpqrst>w4yBC,#&8SL%-R!TIWF
ursor or loop implicitly declares )R%-#*+E as loop index, opens a cursor, etches ro.s o values rom active set into ields in the record and closes .hen all the records have been processed' What is the difference beteen implicit and e(plicit c$rsors? An explicit cursor is declared opened and etched rom in the pro/ram bloc2 .here as an implicit cursor is automatically /enerated or S6$ statements that process a sin/le ro. only' What are the different types of )oins available in "racle? E4ui 9oin0 -hen primary and orei/n 2ey relationship exists bet.een the tables that are /oin/ to be :oined' Sel 9oin0 1 comparison comes in a sin/le table artesian 9oin0 -hen tables are :oined .ithout /ivin/ any :oin condition' 1nner 9oin0 #he resultant set includes all the ro.s that satisy the :oin condition' %uter 9oin0 #he resultant set includes the ro.s .hich doesn3t satisy the :oin condition' #he outer :oin operator +lus si/n ;<= .ill be included in the :oin condition' What are SQL&"' and SQL!!* and hy are they important for PL/SQL developers? S6$%DE returns the value o the error number or the last error encountered' #he S6$ERRM returns the actual error messa/e or the last error encountered' #hey can be used in exception handlin/ to report, or, store in an error lo/ table, the error that occurred in the code' #hese are especially useul or the -!EN %#!ERS exception' What is an a$tonomo$s transaction? An autonomous transaction is an independent transaction that is initiated by another transaction ;the parent transaction=' An autonomous transaction can modiy data and commit or rollbac2 independent o the state o the parent transaction' What is the difference beteen +ie and *ateriali,ed vie? Materiali>ed vie. .ill not be rereshed every time you 4uery the vie.' So to have /ood perormance .hen data is not chan/ed so rapidly, .e use Materiali>ed vie.s rather than normal vie.s .hich al.ays etches data rom tables every time you run a 4uery on it' What is dynamic SQL? Dynamic S6$ allo.s you to construct a 4uery, a DE$E#E statement, a REA#E #AB$E statement, or even a +$5S6$ bloc2 as a strin/ and then execute it at runtime' &an yo$ $se &"**-T in a tri##er? *es but by deinin/ an autonomous transaction' What is the difference beteen anonymo$s blocks and stored proced$res? Anonymous bloc2 is compiled only .hen called' Stored procedure is compiled and stored in database .ith the dependency inormation as .ell' 8ormer is +$5S6$ code directly called rom an application' $atter is stored in database' 8ormer has declare statement' $atter doesn3t' What is a packa#e spec and packa#e body? Why the separation? Spec declares public constructs' Body deines public constructs, additionally declares and deines +rivate constructs' Separation helps ma2e development easier' Dependency is simpliied' *ou can modiy body .ithout invalidatin/ dependent ob:ects'
What is &orrelated S$b.$ery? orrelated Sub4uery is a sub4uery that is evaluated once or each ro. processed by the parent statement' +arent statement can be Select, Update or Delete' What is Se.$ence? Se4uences are used or /eneratin/ se4uence numbers .ithout any overhead o loc2in/' Dra.bac2 is that ater /eneratin/ a se4uence number i the transaction is rolled bac2, then that se4uence number is lost' What is SQL 'eadlock? Deadloc2 is a uni4ue situation in a multi user system that causes t.o or more users to .ait indeinitely or a loc2ed resource' 8irst user needs a resource loc2ed by the second user and the second user needs a resource loc2ed by the irst user' #o avoid dead loc2s, avoid usin/ exclusive table loc2 and i usin/, use it in the same se4uence and use ommit re4uently to release loc2s' What is SQLLoader? S6$?$oader is a product or movin/ data in external iles into tables in an %racle database' #o load data rom external iles into an %racle database, t.o types o input must be provided to S6$?$oader0 the data itsel and the control ile' What is the $se of &0S&0' &"1ST!0-1TS? -hen this clause is used .ith the DR%+ command, a parent table can be dropped even .hen a child table exists' (plain forard declaration $sed in f$nctions? A or.ard declaration means that modules ;procedures and unctions= are declared in advance o their actual body deinition' #his declaration ma2es that module available to be called by other modules even beore the pro/ram3s body is deined' A or.ard declaration consists simply o the module header, .hich is :ust the name o the module ollo.ed by the parameter list ;and a RE#URN clause in case the module is a unction=, no more no less' 8or.ard declarations are re4uired in one speciic situation0 mutual recursion' What are SQL&"' and SQL!!* and hy are they important for PL/SQL developers? S6$%DE returns the value o the error number or the last error encountered' #he S6$ERRM returns the actual error messa/e or the last error encountered' #hey can be used in exception handlin/ to report, or, store in an error lo/ table, the error that occurred in the code' #hese are especially useul or the -!EN %#!ERS exception' What is the difference beteen Tr$ncate and 'elete &ommands? #RUNA#E is a DD$ command .hereas DE$E#E is a DM$ command' !ence DE$E#E operation can be rolled bac2, but #RUNA#E operation cannot be rolled bac2' -!ERE clause can be used .ith DE$E#E and not .ith #RUNA#E' What is the P$rpose of 20+-13 &la$se? #he !AV1N& clause is used in combination .ith the &R%U+ B* clause' 1t can be used in a SE$E# statement to ilter the records that a &R%U+ B* returns' What is -1L-1 +ie in SQL?
#he inline vie. is a construct in %racle S6$ .here you can place a 4uery in the S6$ 8R%M, clause, :ust as i the 4uery .as a table name' While creatin# a se.$ence4 hat does cache and nocache options mean? -ith respect to a se4uence, the cache option speciies ho. many se4uence values .ill be stored in memory or aster access' 'oes the vie e(ist if the table is dropped from the database? *es, in %racle, the vie. continues to exist even ater one o the tables ;that the vie. is based on= is dropped rom the database' !o.ever, i you try to 4uery the vie. ater the table has been dropped, you .ill receive a messa/e indicatin/ that the vie. has errors' What is an -nde(? An index is a perormance@tunin/ method o allo.in/ aster retrieval o records' An index creates an entry or each value that appears in the indexed columns' By deault, %racle creates B@tree indexes' What types of inde( data str$ct$res can yo$ have? An index helps to aster search values in tables' #he three most commonly used index@types are0 B@#ree0 builds a tree o possible values .ith a list o ro. 1Ds that have the lea value' Needs a lot o space and is the deault index type or most databases' Bitmap0 strin/ o bits or each possible value o the column' Each bit strin/ has one bit or each ro.' Needs only little space and is very ast' ;!o.ever, domain o value cannot be lar/e, e'/' SE;m,= de/ree;BS,MS,+!D= !ash0 A hashin/ al/orithm is used to assi/n a set o characters to represent a text strin/ such as a composite o 2eys or partial 2eys, and compresses the underlyin/ data' #a2es lon/er to build and is supported by relatively e. databases' •
•
•
What is the difference beteen a 5here6 cla$se and a 5havin#6 cla$se? C-here is a 2ind o restriction statement' *ou use .here clause to restrict all the data rom DB' -here clause is used beore result retrievin/' But !avin/ clause is usin/ ater retrievin/ the data' !avin/ clause is a 2ind o ilterin/ command' &an a vie be $pdated/inserted/deleted? -f Yes 7 $nder hat conditions? A Vie. can be updated5deleted5inserted i it has only one base table i the vie. is based on columns rom one or more tables then insert, update and delete is not possible' What is tkprof and ho is it $sed? #he t2pro tool is a tunin/ tool used to determine cpu and execution times or S6$ statements' *ou use it by irst settin/ timedstatistics to true in the initiali>ation ile and then turnin/ on tracin/ or either the entire database via the s4ltrace parameter or or the session usin/ the A$#ER SESS1%N command' %nce the trace ile is /enerated you run the t2pro tool a/ainst the trace ile and then loo2 at the output rom the t2pro tool' #his can also be used to /enerate explain plan output' What is e(plain plan and ho is it $sed? #he E+$A1N +$AN command is a tool to tune S6$ statements' #o use it you must have an explaintable /enerated in the user you are runnin/ the explain plan or' #his is created usin/ the utlxplan's4l script' %nce the explain plan table exists you run the explain plan command
/ivin/ as its ar/ument the S6$ statement to be explained' #he explainplan table is then 4ueried to see the execution plan o the statement' Explain plans can also be run usin/ t2pro' What are the Lock types? Share Lock0 1t allo.s the other users or only readin/ not to insert or update or delete' (cl$sive Lock8 %nly one user can have the privile/es o insert or update and delete o particular ob:ect4 others can only read' 9pdate Lock8 Multiple user can read, update delete ' What is Pra#ma :&PT-"1;-1-T? (plain the $sa#e? #he +RA&MA EE+#1%N1N1# tells the complier to associate an exception .ith an oracle error' E'/' +RA&MA EE+#1%N1N1# ;exception name, oracle error number= What is !aise;application;error? Raiseapplicationerror is a procedure o pac2a/e DBMSS#ANDARD .hich allo.s to issue a userdeined error messa/es rom stored sub@pro/ram or database tri//er' What are the modes for passin# parameters to "racle? #here are three modes or passin/ parameters to subpro/rams 1N 7 An 1n@parameter lets you pass values to the subpro/ram bein/ called' 1n the subpro/ram it acts li2e a constant and cannot be assi/ned a value' %U# 7 An out@parameter lets you return values to the caller o the subpro/ram' 1t acts li2e an initiali>ed variable its value cannot be assi/ned to another variable or to itsel' 1N%U# 7 An in@out parameter lets you pass initial values to the subpro/ram bein/ called and returns updated values to the caller' What is the difference beteen Packa#e4 Proced$re and <$nctions? A pac2a/e is a database ob:ects that lo/ically /roups related +$5S6$ types, ob:ects, and Subpro/rams' +rocedure is a sub pro/ram .ritten to perorm a set o actions and can return multiple values' 8unction is a subpro/ram .ritten to perorm certain computations and return a sin/le value' Unli2e subpro/rams pac2a/es cannot be called, passed parameters or nested' 2o do yo$ make a <$nction and Proced$re as a Private? 8unctions and +rocedures can be made private to a pac2a/e by not mentionin/ their declaration in the pac2a/e speciication and by :ust mentionin/ them in the pac2a/e body' What is &ommit4 !ollback and Save point? ommit 7 Ma2es chan/es to the current transaction permanent' 1t erases the savepoints and releases the transaction loc2s' Savepoint 7Savepoints allo. to arbitrarily hold .or2 at any point o time .ith option o later committin/' #hey are used to divide transactions into smaller portions' Rollbac2 7 #his statement is used to undo .or2' What is the difference beteen ''L4 '*L and '&L str$ct$res? DD$ statements are used or deinin/ data' Ex0 reate, Alter, Drop, #runcate, Rename' DM$ statements are used or manipulatin/ data' Ex0 1nsert, update, truncate' D$ statements are used or to control the access o data' Ex &rant, Revo2e' #$ statements are used or data savin/' Ex ommit, Rollbac2, Savepoint'
What is the difference beteen the snapshot and synonym? A snapshot reers to read@only copies o a master table or tables located on a remote node' A snapshot can be 4ueried, but not updated only the master table can be updated' A snapshot is periodically rereshed to relect chan/es made to the master table' 1n this sense, a snapshot is really a vie. .ith periodicity' A synonym is an alias or table, vie., se4uence or pro/ram unit' #hey are o t.o types private and public' What is the difference beteen data types char and varchar? har reserves the number o memory locations mentioned in the variable declarations, even thou/h not used ;it can store a maximum o "FF bytes=' -here as Varchar does not reserve any memory locations .hen the variable is declared, it stores the values only ater they are assi/ned ;it can store a maximum o G"HIH bytes=' &an one call ''L statements from PL/SQL? %ne can call DD$ statements li2e REA#E, DR%+, #RUNA#E, etc' rom +$5S6$ by usin/ the CEEU#E 1MMEDA#E statement' Tell some ne feat$res in PL/SQL in =>#? @Re/ular expression unctions RE&E+$1JE, RE&E+1NS#R, RE&E+RE+$AE, and RE&E+SUBS#R @ompile time .arnin/s @ onditional compilation @ 1mprovement to native compilation @ B1NAR*1N#E&ER made similar to +$S1N#E&ER @ 1mplicit conversion bet.een $%B and N$%B @ 1mproved %verloadin/ @ Ne. datatypes B1NAR*8$%A#, B1NAR*D%UB$E @ &lobal optimi>ation enabled @ +$S1N#E&ER ran/e increased to G"bit @ D*NAM1 -RA+ usin/ DBMSDD$ What is "verloadin# in PL/SQL? %verloadin/ is an oops concept ;%b:ect %riented +ro/rammin/=' By usin/ the same name .e can .rite any number o +rocedures or unctions in a pac2a/e but either number o parameters in the procedure5unction must vary or parameter datatype must vary' What is a m$tatin# and constrainin# table? CMutatin/ means Cchan/in/' A mutatin/ table is a table that is currently bein/ modiied by an update, delete, or insert statement' -hen a tri//er tries to reerence a table that is in state o lux ;bein/ chan/ed=, it is considered Cmutatin/ and raises an error since %racle should not return data that has not yet reached its inal state' Another .ay this error can occur is i the tri//er has statements to chan/e the primary, orei/n or uni4ue 2ey columns o the table o .hich it ires' 1 you must have tri//ers on tables that have reerential constraints, the .or2around is to enorce the reerential inte/rity throu/h tri//ers as .ell' What is 1ested Table?
A nested table is a table .ithin a table' A nested table is a collection o ro.s, represented as a column .ithin the main table' 8or each record .ithin main table, the nested table may contain multiple ro.s' 1n a sense, it3s a .ay o storin/ a one@to many relationship .ithin one table' What is +aryin# 0rray? A varyin/ array is a set o ob:ects, each .ith the same data types' #he si>e o the array is limited .hen it is created' ;-hen the table is created .ith a varyin/ array, the array is a nested table .ith a limited set o ro.s=' Varyin/ arrays also 2no.n as VARRA*S, allo.s storin/ repeated attributes in tables' 3ive some most often $sed predefined e(ceptions? a= N%DA#A8%UND ;Select Statement returns no ro.s= b= #%%MAN*R%-S ;Sin/le ro. Select statement returns more than K ro.= c= 1NVA$1DURS%R ;1lle/al cursor operations occurred= d= URS%RA$READ*%+EN ;1 cursor is opened ( .e are tryin/ to reopen it= e= 1NVA$1DNUMBER ;onversion o haracter to number ails= = LER%D1V1DE /= DU+VA$%N1NDE ;Attempted to insert a duplicate value= 3ive some important "racle s$pplied packa#es? DBMSS6$0 1t is used to .rite +rocedures ( Anonymous bloc2s that use Dynamic S6$' DBMS9%B0 Usin/ it, .e can submit +$5S6$ pro/rams or execution, execute +$5S6$ pro/rams on a schedule, identiy .hen pro/rams should run, remove pro/rams rom the schedule ( suspend pro/rams rom runnin/' DBMS%U#+U#0 #his pac2a/e outputs values ( messa/es rom any +$5S6$ bloc2' U#$81$E0 -ith this pac2a/e, you can read rom ( .rite to %peratin/ system iles U#$!##+0 #his pac2a/e allo.s to ma2e !##+ Re4uests directly rom the database' What is -nstead "f Tri##er? #his tri//er is used to perorm DM$ operation directly on the underlyin/ tables, because a vie. cannot be modiied by normal DM$ Statements i it contains :oins or &roup 8unctions' #hese tri//ers are %nly Ro. $evel #ri//ers' #he !EJ option or vie.s is not enorced .hen DM$ to the vie. are perormed by 1nstead o #ri//er' What is the Se.$ence of
Varraya= #his are Dense b= -e cannot Delete its Elements c= #his are 8ixed Si>e ( al.ays need to speciy the si>e d= #hese are Stored in #ablespaces What are the vario$s SQL Statements? a= Data Retrieval0 Select b= DM$0 1nsert, Update, Delete c= DD$0 reate, Alter, Drop, Rename, #runcate d= #ransaction ontrol0 ommit, Rollbac2, Savepoint e= D$0 &rant, Revo2e = Session ontrol0 Alter Session, Set Role /= System ontrol0 Alter System h= Embedded S6$ Statements0 %pen, lose, 8etch ( Execute' What is !oid? 1t is a !exadecimal Representation o a Ro. in a #able' Ro.id can only be han/ed i .e Enable Ro. Movement3 on a +artitioned #able' Ro.id3s o Deleted Ro.s can be Reused i #ransaction is ommitted' What is Partitionin#? 1t Enables #ables ( 1ndexes or 1ndex@%r/ani>ed tables to be subdivided into smaller mana/eable +ieces ( these each small +iece is called +artition' #hey are o ollo.in/ #ypes0 a= Ran/e +artitionin/ b= !ash +artitionin/ c= $ist +artitionin/ d= omposite Ran/e@!ash +artitionin/ What is a &l$ster? A cluster provides an optional method o storin/ table data' A cluster is comprised o a /roup o tables that share the same data bloc2s, .hich are /rouped to/ether because they share common columns and are oten used to/ether' 8or example, the EM+ and DE+# table share the DE+#N% column' -hen you cluster the EM+ and DE+#, %racle physically stores all ro.s or each department rom both the EM+ and DE+# tables in the same data bloc2s' *ou should not use lusters or tables that are re4uently accessed individually' What is the 'ifference beteen 1ested S$b.$ery &orrelated S$b.$ery? Nested Sub4uerya= 1nner 6uery runs irst and executes once, returnin/ values .hich are to be used by the Main 4uery or outer 4uery b= %uter 4uery is driver by 1nner 6uery orrelated Sub4uerya= A orrelated Sub4uery or 1nner 6uery execute once or each candidate ro. considered by outer 4uery b= 1nner 6uery is Driven by %uter 6uery What is the 'ifference beteen Translate !eplace? #ranslate unction converts each character in Strin/ .ith speciied one .hereas Replace unction replaces part o the strin/ in continuity by another sub@strin/' +$5S6$ K' -hats a +$5S6$ table 1ts purpose and Advanta/es A' A +$5S6$ table is one dimensional, indexed, unbounded sparsed collection o homo/eneous
Data' +$S6$ tables are used to move data into and out o the database and bet.een client side applications and stored sub@pro/rams' #hey have attributes such as exits, prior, irst, last, delete ,next ' #hese attributes ma2e +$S6$ tables easier to use and applications easier to maintain' Advanta/es0 +$OS6$ tables /ive you the ability to hold multiple values in a structure in memory so that a +$OS6$ bloc2 does not have to /o to the database every time it needs to retrieve one o these values @ it can retrieve it directly rom the +$OS6$ table in memory' &lobal temporary tables act as perormance enhancers .hen compared to standard tables as they /reatly reduce the dis2 1%' #hey also oer the ease@o@use o standard tables, since standard S6$ can be used .ith them no special array@processin/ syntax is re4uired' "' -hat is a ursor !o. many types o ursor are there A= ursor is an identiier5name to a .or2 area that .e can interact .ith to access its inormation' A cursor points to the current ro. in the result set etched' #here are three types o cursors' #hey are 1mplicit cursors created automatically by +$5S6$ or all S6$@DM$ statements such as 1nsert Update, delete and Select Explicit cursors reated explicitly' #hey create a stora/e area .here the set o ro.s Returned by a 4uery are placed' Dynamic ursors Re ursors; used or the runtime modiication o the select 4uerry=' Declarin/ the cursor, %penin/ the cursor, 8etchin/ data , losin/ the cursor;Releasin/ the .or2 area= are the steps involved .hen usin/ explicit cursors' G' -hat is the dierence bet.een 8unction and +rocedure +rocedure is a sub pro/ram .ritten to perorm a set o actions and returns multiple values Usin/ out parameters or return no value at all' 8unction is a subpro/ram .ritten to perorm certain computations and return a sin/le value' P' -hat are the modes or passin/ parameters to %racle A= #here are three modes or passin/ parameters to subpro/rams 1N @ An 1n@parameter lets you pass values to the subpro/ram bein/ called' 1n the subpro/ram it acts li2e a constant and cannot be assi/ned a value' %U# An out@parameter lets you return values to the caller o the subpro/ram' 1t acts li2e an initiali>ed variable its value cannot be assi/ned to another variable or to itsel' 1N%U# An in@out parameter lets you pass initial values to the subpro/ram bein/ called and returns updated values to the caller' F' -hat is the dierence bet.een #runcate and Delete Statement #runcate Data truncated by usin/ truncate statement is lost permanently and cannot be retrieved even by rollbac2' #runcate command does not use rollbac2 se/ment durin/ its execution, hence it is ast' Delete Data deleted by usin/ the delete statement can be retrieved bac2 by Rollbac2' Delete statement does not ree up the table ob:ect allocated space' I' -hat are Exceptions !o. many types o Exceptions are there A= Exceptions are conditions that cause the termination o a bloc2' #here are t.o types o exceptions +re@Deined +redeined by +$5S6$ and are associated .ith speciic error codes' User@Deined Declared by the users and are rose on deliberate re4uest' ;Brea2in/ a condition etc'= Exception handlers are used to handle the exceptions that are raised' #hey prevent exceptions rom propa/atin/ out o the bloc2 and deine actions to be perormed .hen exception is raised'
H' -hat is a +ra/ma Exception1nit Explain its usa/e A= +ra/ma Exception1nit is used to handle undeined exceptions' 1t issues a directive to the compiler as2in/ it to associate an exception to the oracle error' #here by displayin/ a speciic error messa/e pertainin/ to the error occurred' +ra/ma Exception1nit ;exceptionname, oracleerrorname=' Q' -hat is a Raise and Raise Application Error A= Raise statement is used to raise a user deined exception' B= A raise application error is a procedure belon/in/ to dbmsstandard pac2a/e' 1t allo.s to display a user deined error messa/e rom a stored subpro/ram' Q' -hat is the dierence bet.een +ac2a/e, +rocedure and 8unctions A pac2a/e is a database ob:ects that lo/ically /roups related +$5S6$ types, ob:ects, and Subpro/rams' +rocedure is a sub pro/ram .ritten to perorm a set o actions and can return multiple values' 8unction is a subpro/ram .ritten to perorm certain computations and return a sin/le value' Unli2e subpro/rams pac2a/es cannot be called, passed parameters or nested' ' !o. do you ma2e a 8unction and +rocedure as a +rivate A= 8unctions and +rocedures can be made private to a pac2a/e by not mentionin/ their declaration in the pac2a/e speciication and by :ust mentionin/ them in the pac2a/e body' K' -hat is an Anonymous bloc2 A= Anonymous Bloc2 is a bloc2 o instructions in +$5S6$ and S6$ .hich is not saved under a name as an ob:ect in database schema' 1t is also not compiled and saved in server stora/e, so it needs to be parsed and executed each time it is run' !o.ever, this simple orm o pro/ram can use variables, can have lo. o control lo/ic, can return 4uery results into variables and can prompt the user or input usin/ the S6$?+lus T(T eature as any stored procedure' K"' -hat are the t.o basic parameters that .e have to pass .hile re/isterin/ +$5S6$ procedure A= Error code and Error Buer' KK' !o. do you 2ic2 a oncurrent pro/ram rom +$5S6$ A= Usin/ 8NDSUBM1#'SUBM1#RE6UES#' K"' !o. to display messa/es in $o/ ile and %utput ile A= Usin/ 8ND81$E'+U#$1NE KG' -hat is a #ri//er !o. many types o #ri//ers are there A= #ri//er is a procedure that /ets implicitly executed .hen an insert5update5delete statement is issued a/ainst an associated table' #ri//ers can only be deined on tables not on vie.s, ho. ever tri//ers on the base table o a vie. are ired i an insert5update5delete statement is issued a/ainst a vie.' #here are t.o types o tri//ers, Statement level tri//er and Ro. level tri//er' 1nsert Ater 5 8or each ro. #ri//er is ired 5 Update 5 Beore 5 8or Each statement Delete KP' an .e use ommit in a Database #ri//er, i No then .hy A= No' ommittin/ in a tri//er .ill violate the inte/rity o the transaction' KF' -hat is ommit, Rollbac2 and Save point ommit Ma2es chan/es to the current transaction permanent' 1t Erases the savepoints and releases the transaction loc2s' Savepoint Savepoints allo. to arbitrarily hold .or2 at any point o time .ith option o later
committin/' #hey are used to divide transactions into smaller portions' Rollbac2 #his statement is used to undo .or2' KI' -hat is the dierence bet.een DD$, DM$ and D$ structures A= DD$ statements are used or deinin/ data' Ex0 reate, Alter, Drop' DM$ statements are used or manipulatin/ data' Ex0 1nsert, update, truncate, delete, select' D$ statements are used or to control the access o data' Ex &rant, Revo2e' KH' !o. can u create a table in +$5S6$ procedure A= By usin/ execute immediate statement .e can create a table in +$S6$' Be/in Execute immediate create table amit as select ? rom emp End All DD$,DM$,D$ commands can be perormed by usin/ this command' KQ' !o. do .e #une the 6ueries A= 6ueries can be tuned by hec2in/ the lo/ic ;table :oins=, by creatin/ 1ndexes on ob:ects in the .here clause, by avoidin/ ull table scans' 8inally use the trace utility to /enerate the trace ile, use the #J@+ro utility to /enerate a statistical a nalysis about the 4uery usin/ .hich appropriate actions can be ta2en' "K' -hat is Explain +lan !o. do u use Explain +lan in #%AD A= 1t is a utility provided by toad that /ives the statistics about the perormance o the 4uery' 1t /ives inormation such as number o ull table scans occurred, cost, and usa/e o indexes K' -hat is a #J@+R%8 and its usa/e A= #2@+ro is a utility that reads the trace iles and /enerates more readable data that /ives the statistics about the perormance o the 4uery on a line to line basis' "' -hat is %ptimi>ation !o. many types o %ptimi>ation are there A= Rule based %ptimi>ation and ost Based %ptimi>ation' "K' -hat is the deault optimi>ation chosen by %racle A= ost based %ptimi>ation' ""' -hat is the dierence bet.een -hen no data 8ound and cursor attribute ) DA#A 8%UND A= -hen no Data 8ound is a predeined internal exception in +$S6$' -here as ) Data ound is a cursor attribute that returns *ES .hen >ero ro.s are retrieved and returns N% .hen at least one ro. is retrieved' "G' -hat is the dierence bet.een the snapshot and synonym A snapshot reers to read@only copies o a master table or tables located on a remote node' A snapshot can be 4ueried, but not updated only the master table can be updated' A snapshot is periodically rereshed to relect chan/es made to the master table' 1n this sense, a snapshot is really a vie. .ith periodicity' A synonym is an alias or table, vie., se4uence or pro/ram unit' #hey are o t.o types private and public' "F' -hat is the dierence bet.een data types char and varchar A= har reserves the number o memory locations mentioned in the variable declarations, even thou/h not used ;it can store a maximum o "FF bytes=' -here as Varchar does not reserve any memory locations .hen the variable is declared, it stores the values only ater they are assi/ned ;it can store a maximum o G"HIH bytes=' "I' !o. can .e place index to a second column in the table i'e' there is already one index and 1 .ant to place another index or the column on the table "H' 1tems are imported rom the le/acy system usin/ the item import interace usin/ the SRS' !o. are items imported usin/ the UN1 5+$S6$ commands .ith out usin/ SRS A= 8rom the operatin/ system, use %NSUB to submit a concurrent pro/ram' 1tTs an easiest
.ay to test a concurrent pro/ram' Normally, %NSUB submits a concurrent re4uest and returns control to the %S prompt5shell script .ithout .aitin/ or the re4uest to complete' #he %NSUB -A1# parameter can be used to ma2e %NSUB .ait until the re4uest has completed beore returnin/ control to the %S prompt5shell script By usin/ the -A1# to2en, the utility chec2s the re4uest status every I seconds and returns to the operatin/ system prompt upon completion o the re4uest' concurrent mana/er does not abort, shut do.n, or start up until the concurrent re4uest completes' 1 your concurrent pro/ram is compatible .ith itsel, .e can chec2 it or data inte/rity and deadloc2s by submittin/ it many times so that it runs concurrently .ith itsel' Syntax0 %NSUB %RA$E 1D Responsibility Application Short Name Responsibility Name User Name W-A1#X-ait 8la/Y %NURREN# oncurrent +ro/ram Application Short Name oncurrent +ro/ram Name WS#AR#XRe4uested Start DateY WRE+EA#DA*SXRepeat 1ntervalY WRE+EA#ENDXRe4uest Resubmission End DateY oncurrent +ro/ram Ar/uments ''' #o pass null parameters to %NSUB, use TZZT .ithout spaces or each null parameter' 1n .ords0 sin/le 4uote double 4uote double 4uote sin/le 4uote 8ollo.in/ is an example o %NSUB syntax .ith null parameters0 %NSUB oe5oe %E T%rder Entry Super UserT 9-A$S! %NURREN# %E %E+AJ P G TZZT G B= #o 1nvo2e a oncurrent +ro/ram usin/ +$5S6$0 i= 9ust insert a ro. in 8ND%NURREN#RE6UES#S .ith the apropriate parameters and commit' ii= 1nvo2e the SUBM1#RE6UES# procedure in 8NDRE6UES# pac2a/e' 8NDRE6UES#'SUBM1#RE6UES#; TART, TRAM#RT, TT, TT, 8A$SE, TAutoinvoice Master +ro/ramT, sctime, 8A$SE, K, K", TVR+T, TK@9AN@T, chr;=, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT, TT= "Q' -hat is pipelinin/ "= !o. can the duplicate records be rom the table S6$ create table tablename" as select distinct ? rom tablenameK S6$ drop tablenameK S6$ rename tablename" to tablenameK G= -hat is the si/niicance o all tables A= all tables are multi@or/ tables .hich are associated .ith the company as a .hole' Multiple %r/ani>ations is enabled in %racle Applications by partitionin/ some database tables by the %peratin/ Unit' %ther tables are shared across %peratin/ Units ;and thereore across set o boo2s=' Examples o Applications .ith partitioned tables are %racle +ayables, %racle +urchasin/, %racle Receivables, %racle +ro:ects, %racle Sales ( Mar2etin/ etc' #he name o each correspondin/ partitioned table is the vie. name appended by TA$$T GK=-hat are mutatin/ tables And .hat is mutatin/ error A= A mutatin/ table is a table that is currently bein/ modiied by an U+DA#E, DE$E#E, or 1NSER# statement, or it is a table that mi/ht need to be updated by the eects o a declarative DE$E#E ASADE reerential inte/rity constraint'
A mutatin/ error occurs .hen a tri//er .hich ires .hen updation5deletion5insertion is done on a table A perorms insertion5updation5deletion on the same table A' #his error results in an ininite loop .hich is termed as a mutatin/ error' G"= -hat is dierence bet.een oracle H and oracle Q A= %racle H is a simple RDBMS, .here as %racle Q is %RDBMS i'e', RDBMS .ith %b:ect Support' #he main add@ons in version Q are
Abstract Data types Varrays +$5S6$ #ables Nested #ables +artitioned #ables
GG'-hat is Data cleanin/ and testin/' A= Data leanin/0 #ransormation o data in its current state to a pre@deined, standardi>ed ormat usin/ pac2a/ed sot.are or pro/ram modules' Data #estin/0 #he a/reed upon conversion deliverables should be approved by the client representatives .ho are responsible or the success o the conversion' 1n addition, three levels o conversion testin/ have been identiied and described in the prepare conversion test plans deliverables' E/0 or Summary Balances in &$ .e set #est riteria as Record ounts, !ash #otals, Balances, 9ournal Debit and redit' GP' -hile re/isterin/ a report and a pl5s4l bloc2 .e pass some parameters, or any pl5s4l bloc2 .e pass " additional parameters' an u list them A= 1t re4uires t.o 1N parameters or a +$5S6$ procedure thatTs re/istered as a concurrent pro/ram in Apps' #hey are K' Errcode 1N VAR!AR" "' Errbu 1N VAR!AR" GF= .hat is a trace ile A= .hen ever an internal error is detected by a process in oracle it dumps the inormation about the error into a trace ile' Alter session set s4ltraceX#RUE GI = -hen do you use Re ursors -e base a 4uery on a re cursor .hen you .ant to0 i= More easily administer S6$ ii= Avoid the use o lexical parameters in your reports iii= Share data sources .ith other applications, such as 8orm Builder iv= 1ncrease control and security v= Encapsulate lo/ic .ithin a subpro/ram
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Wh? It is not possible! As triggers are "e#ine" #or each table$ i# ou use COMMIT o# ROLLBACK in a trigger$ it a##ects logical transaction processing!
%&!What are t'o (irtual tables a(ailable "uring "atabase trigger e)ecution? The table columns are re#erre" as OLD!column*name an" +,W!column*name! -or triggers relate" to I+S,RT onl +,W!column*name (alues onl a(ailable! -or triggers relate" to ./DAT, onl OLD!column*name +,W!column*name (alues onl a(ailable! -or triggers relate" to D,L,T, onl OLD!column*name (alues onl a(ailable! 01!What happens i# a proce"ure that up"ates a column o# table 2 is calle" in a "atabase trigger o# the same table? Mutation o# table occurs! 0%!Write the or"er o# prece"ence #or (ali"ation o# a column in a table ? I! "one using Database triggers! ii! "one using Integarit Constraints! 00!What is an ,)ception? What are tpes o# ,)ception? ,)ception is the error han"ling part o# /L3S4L bloc5! The tpes are /re"e#ine" an" user*"e#ine"! Some o# /re"e#ine" e)ecptions are! C.RSOR*ALR,AD6*O/,+ D./*7AL*O+*I+D,2 +O*DATA*-O.+D TOO*MA+6*ROWS I+7ALID*C.RSOR I+7ALID*+.MB,R LO8O+*D,+I,D +OT*LO88,D*O+ /RO8RAM9,RROR STORA8,*,RROR TIM,O.T*O+*R,SO.RC, 7AL.,*,RROR :,RO*DI7ID, OT;,RS! 0 0!What is Raise*application*error? Raise*application*error is a proce"ure o# pac5age DBMS*STA+DARD 'hich allo's to issue an user*"e#ine" error messages #rom store" sub9program or "atabase trigger! 0@!What are the return (alues o# #unctions S4LCOD, an" S4L,RRM?
S4LCOD, returns the latest co"e o# the error that has occure"! S4L,RRM returns the rele(ant error message o# the S4LCOD,! 0!Where the /re*"e#ine"*e)ceptions are store"? In the stan"ar" pac5age! /roce"ures$ -unctions /ac5ages 0!What is a store" proce"ure? A store" proce"ure is a seEuence o# statements that per#orm speci#ic #unction! <1!What is "i##erence bet'een a /ROC,D.R, -.+CTIO+? A -.+CTIO+ is al'a returns a (alue using the return statement! A /ROC,D.R, ma return one or more (alues through parameters or ma not return at all! <%!What are a"(antages o# Store" /roce"ures? ,)tensibilit$Mo"ularit$ Reusabilit$ Maintainabilit an" one time compilation! <0!What are the mo"es o# parameters that can be passe" to a proce"ure? I+$O.T$I+9O.T parameters! < is local (ariable "eclarations B,8I+ ,)ecutable statements! ,)ception! e)ception han"lers en" <@!8i(e the structure o# the #unction? -.+CTIO+ name =argument list !!!!!> Return "atatpe is local (ariable "eclarations Begin e)ecutable statements ,)ception e)ecution han"lers ,n" <!,)plain ho' proce"ures an" #unctions are calle" in a /L3S4L bloc5 ?
-unction is calle" as part o# an e)pression! sal FG calculate*sal =Ha00H> proce"ure is calle" as a /L3S4L statement calculate*bonus =HA00H> <!What is O(erloa"ing o# proce"ures? The Same proce"ure name is repeate" 'ith parameters o# "i##erent "atatpes an" parameters in "i##erent positions$ (aring number o# parameters is calle" o(erloa"ing o# proce"ures! e!g! DBMS*O.T/.T put*line <!What is a pac5age? What are the a"(antages o# pac5ages? /ac5age is a "atabase obJect that groups logicall relate" proce"ures! The a"(antages o# pac5ages are Mo"ularit$ ,asier Applicaton Design$ an" In#ormation! ;i"ing$! Reusabilit an" Better /er#ormance! <&!What are t'o parts o# pac5age? The t'o parts o# pac5age are /ACKA8, S/,CI-ICATIO+ /ACKA8, BOD6! /ac5age Speci#ication contains "eclarations that are global to the pac5ages an" local to the schema! /ac5age Bo" contains actual proce"ures an" local "eclaration o# the proce"ures an" cursor "eclarations! 1!What is "i##erence bet'een a Cursor "eclare" in a proce"ure an" Cursor "eclare" in a pac5age speci#ication? A cursor "eclare" in a pac5age speci#ication is global an" can be accesse" b other proce"ures or proce"ures in a pac5age! A cursor "eclare" in a proce"ure is local to the proce"ure that can not be accesse" b other proce"ures! %!;o' pac5age" proce"ures an" #unctions are calle" #rom the #ollo'ing ? a! Store" proce"ure or anonmous bloc5 b! an application program such a /RC C$ /RO COBOL c! S4L /L.S a! /ACKA8, +AM,!/ROC,D.R, +AM, =parameters> (ariable FG /ACKA8, +AM,!-.+CTIO+ +AM, =arguments> ,2,C S4L ,2,C.T, b! B,8I+ /ACKA8, +AM,!/ROC,D.R, +AM, =parameters> (ariable FG /ACKA8, +AM,!-.+CTIO+ +AM, =arguments> ,+D ,+D ,2,C c! ,2,C.T, /ACKA8, +AM,!/ROC,D.R, i# the proce"ures "oes not ha(e an out3in9out parameters! A #unction can not be calle"!
0!+ame the tables 'here characteristics o# /ac5age$ proce"ure an" #unctions are store"? .ser*obJects$ .ser*Source an" .ser*error /. What is $>erloadi3 o@ proceduresV The Same proced$re name is repeated ith parameters of different datatypes and parameters in different positions4 varyin# n$mber of parameters is called overloadin# of proced$res@ e@#@ 'B*S;"9TP9T p$t;line What is a packa#e ? What are the advanta#es of packa#es ? -hat is +$5S6$ and .hat is it used or S6$ is a declarative lan/ua/e that allo.s database pro/rammers to .rite a S6$ declaration and hand it to the database or execution' As such, S6$ cannot be used to execute procedural code .ith conditional, iterative and se4uential statements' #o overcome this limitation, +$5S6$ .as created' +$5S6$ is %racleTs +rocedural $an/ua/e extension to S6$' +$5S6$Ts lan/ua/e syntax, structure and data types are similar to that o Ada Ada'' Some o the statements provided by +$5S6$0 &onditional &ontrol Statements8
18 ''' #!EN ''' E$S18 ''' #!EN ''' E$SE ''' END 18
ASE ''' -!EN ''' #!EN ''' E$SE ''' END ASE
-terative Statements8
$%%+ ''' END $%%+
-!1$E ''' $%%+ ''' END $%%+
8%R ''' 1N WREVERSEY ''' $%%+ ''' END $%%+
Se.$ential &ontrol Statements8
&%#% '''
NU$$
#he +$5S6$ lan/ua/e includes ob:ect oriented pro/rammin/ techni4ues such as encapsulation, unction overloadin/, inormation hidin/ ;all but inheritance=' +$5S6$ is commonly used to .rite data@centric pro/rams to manipulate data in an %racle database' (ample PL/SQL blocks8
/* Remember to SET SERVEROUTPUT ON to see the output */ BEGIN DBMS_OUTPUT.PUT_LINE(!e""o DBMS_OUTPUT.PUT_ LINE(!e""o #or"$%&
END& / BEGIN '' PL/S)L ursor +OR ursor, IN (SELE-T * +ROM tb"e,% tb"e,% '' Ths Ths s 0 embe$$e$ S)L stteme0t LOOP DBMS_OUTPUT.PUT_LINE(-o"um0 DBMS_OUTPUT.PUT_L INE(-o"um0 , 1 22 ursor,.o"um0, 22 3 -o"um0 4 1 22 ursor,.o"um04%& ursor,.o"um04%& END LOOP& END& / Wedit editY-hat Y-hat is the dierence bet.een S6$ and +$5S6$ Both S6$ and +$5S6$ are lan/ua/es used to access data .ithin %racle databases' S6$ is a limited lan/ua/e that allo.s you to directly interact .ith the database' *ou can .rite 4ueries ;SE$E#=, manipulate ob:ects ;DD$= and data ;DM$= .ith S6$' !o.ever, S6$ doesnTt include all the thin/s that normal pro/rammin/ lan/ua/es have, such as loops and 18'''#!EN'''E$SE statements' +$5S6$ is a normal pro/rammin/ lan/ua/e that includes all the eatures o most other pro/rammin/ lan/ua/es' But, it has one thin/ that other pro/rammin/ lan/ua/es donTt have0 the ability to easily inte/rate .ith S6$' Some o the dierences0
S6$ is executed one statement at a time' +$5S6$ is executed as a bloc2 o code' S6$ tells the database .hat to do ;declarative=, not ho. to do it' 1n contrast, +$5S6$ tell the database ho. to do thin/s ;procedural=' S6$ is used to code 4ueries, DM$ and DD$ statements' +$5S6$ is used to code pro/ram bloc2s, tri//ers, unctions, procedures and pac2a/es'
*ou can embed S6$ in a +$5S6$ pro/ram, but you cannot embed +$5S6$ .ithin a S6$ statement' Wedit editYShould YShould one use +$5S6$ or 9ava to code procedures and tri//ers
Both +$5S6$ and 9ava can be used to create %racle stored procedures and tri//ers' #his oten leads to 4uestions li2e Z-hich o the t.o is the bestZ and Z-ill %racle ever desupport +$5S6$ in avour o 9avaZ' Many %racle applications are based on +$5S6$ and it .ould be diicult o %racle to ever desupport +$5S6$' 1n act, all indications are that +$5S6$ still has a bri/ht uture ahead o it' Many enhancements are still bein/ made to +$5S6$' 8or example, %racle i supports native compilation o +$5S6$ code to binaries' Not to mention the numerous +$5S6$ enhancements made in %racle K/ and KK/'
+$5S6$ and 9ava appeal to dierent people in dierent :ob roles' #he ollo.in/ table briely describes the similarities and dierence bet.een these t.o lan/ua/e environments0 PL/SQL8
an be used to create %racle pac2a/es, procedures and tri//ers
Data centric and ti/htly inte/rated into the database
+roprietary to %racle and diicult to port to other database systems
Data manipulation is sli/htly aster in +$5S6$ than in 9ava
+$5S6$ is a traditional procedural pro/rammin/ lan/ua/e
Aava8
an be used to create %racle pac2a/es, procedures and tri//ers
%pen standard, not proprietary to %racle
1ncurs some data conversion overhead bet.een the Database and 9ava type
9ava is an %b:ect %rientated lan/ua/e, and modules are structured into classes
9ava can be used to produce complete applications
+S0 Startin/ .ith %racle K/, 'NE# procedures can also be stored .ithin the database ;-indo.s only=' Nevertheless, unlike PL/SQL and JAVA, .NET code is not usable on nonindo!s s"ste#s. +S0 1n earlier releases o %racle it .as better to put as much code as possible in procedures rather than tri//ers' At that sta/e procedures executed aster than tri//ers as tri//ers had to be re@compiled every time beore executed ;unless cached=' 1n more recent releases both tri//ers and procedures are compiled .hen created ;stored p@code= and one can add as much code as one li2es in either procedures or tri//ers' !o.ever, it is still considered a best practice to put as much o your pro/ram lo/ic as possible into pac2a/es, rather than tri//ers' Wedit editY!o. Y!o. can one see i somebody modiied any code #he source code or stored procedures, unctions and pac2a/es are stored in the %racle Data Dictionary' %ne can detect code chan/es by loo2in/ at the #1MES#AM+ and $AS#DD$#1ME column in the USER%B9E#S dictionary vie.' Example0
SELE-T OB5E-T_NME3 TO_-!R(-RETED3 DD'Mo0'RR !!467MI% -RETE_TIME3 TO_-!R(LST_DDL_TIME3 TO_-!R(LST_DDL _TIME3 DD'Mo0'RR !!467MI% MOD_TIME3 STTUS +ROM USER_OB5E-TS
#!ERE
LST_DDL_TIME 8 9-!E-:_+ROM_DTE&
Note0 1 you recompile an ob:ect, the $AS#DD$#1ME column is updated, but the #1MES#AM+ column is not updated' 1 you modiied the code, both the #1MES#AM+ and $AS#DD$#1ME columns are updated' WeditY!o. can one search +$5S6$ code or a strin/5 2ey value #he ollo.in/ 4uery is handy i you .ant to 2no. .here certain tables, columns and expressions are reerenced in your +$5S6$ source code'
SELE-T t;pe3 0me3 "0e +ROM user_soure #!ERE UPPER(te#ORD=%& 1 you run the above 4uery rom S6$?+lus, enter the strin/ you are searchin/ or .hen prompted or JE*-%RD' 1 not, replace (JE*-%RD .ith the strin/ you are searchin/ or' WeditY!o. does one 2eep a history o +$5S6$ code chan/es %ne can build a history o +$5S6$ code chan/es by settin/ up an A8#ER REA#E schema ;or database= level tri//er ;available rom %racle Q'K'H=' #his .ill allo. you to easily revert to previous code should someone ma2e any catastrophic chan/es' $oo2 at this example0
-RETE TBLE SOUR-E_!IST '' -rete hstor; tb"e S SELE-T S>SDTE -!NGE_DTE3 LL_SOUR-E.* +ROM LL_SOUR-E #!ERE ,14& -RETE OR REPL-E TRIGGER h0?e_hst '' Store o$e 0 hst tb"e +TER -RETE ON S-OTT.S-!EM '' -h0?e S-OTT to ;our shem 0me DE-LRE BEGIN I+ OR_DI-T_OB5_T>PE 0 (PRO-EDURE3 +UN-TION3 P-:GE3 P-:GE BOD>3 T>PE3 T>PE BOD>% T!EN '' Store o"$ o$e 0 SOUR-E_!IST tb"e INSERT INTO SOUR-E_!IST SELE-T s;s$te3 ""_soure.* +ROM LL_SOUR-E #!ERE T>PE 1 OR_DI-T_OB5_T>PE '' DI-TIONR>_OB5_T>PE IN @ ND NME 1 OR_DI-T_OB5_NME& '' DI-TIONR>_OB5_NME IN @ END I+& EA-EPTION #!EN OT!ERS T!EN
rse_pp"to0_error('43 S)LERRM%& END& / shoC errors A better approach is to create an external VS or SVN repository or the scripts that install the +$5S6$ code' #he canonical version o .hatTs in the database must match the latest VS5SVN version or else someone .ould be cheatin/' WeditY!o. can 1 protect my +$5S6$ source code %racle provides a binary .rapper utility that can be used to scramble +$5S6$ source code' #his utility .as introduced in %racleH'" ;+$5S6$ V"'"= and is located in the %RA$E!%ME5bin directory' #he utility use human@readable +$5S6$ source code as input, and .rites out portable binary ob:ect code ;some.hat lar/er than the ori/inal=' #he binary code can be distributed .ithout ear o exposin/ your proprietary al/orithms and methods' %racle .ill still understand and 2no. ho. to execute the code' 9ust be careul, there is no ZdecodeZ command available' So, donTt lose your source[ #he syntax is0
Crp 0me1m;srpt.p"s o0me1<<<<.p"b +lease note0 there is no le/al .ay to un.rap a ?'plb binary ile' *ou are supposed to bac2up and 2eep your ?'pls source iles ater .rappin/ them' !o.ever it is possible or s2illed hac2ers to $nrapyour .rapped %racle +$5S6$ code' WeditY!o. can 1 2no. .hich stored +$5S6$ code is .rapped #he ollo.in/ 4uery /ives the list o all .rapped +$5S6$ code0
se"et oC0er3 0me3 t;pe rom $b_soure Chere "0e 1 , 0$ 0str(te
set sereroutput o0 be?0
$bms_output.put_"0e(LooH M3 I 0 pr0t rom PL/S)L%& e0$& / DBMS%U#+U# is useul or debu//in/ +$5S6$ pro/rams' !o.ever, i you print too much, the output buer .ill overlo.' 1n that case, set the buer si>e to a lar/er value, e/'0 set serveroutput on si>e " 1 you or/et to set serveroutput on type SET SE$VE$%&TP&T %N once you remember, and then E(E) N&LL'' 1 you havenTt cleared the DBMS%U#+U# buer .ith the disable or enable procedure, S6$?+lus .ill display the entire contents o the buer .hen it executes this dummy +$5S6$ bloc2' #o display an empty line, it is better to use ne!*line procedure than +ut*line .ith an empty strin/' WeditYan one read5.rite iles rom +$5S6$ #he U#$81$E database pac2a/e can be used to read and .rite operatin/ system iles' A DBA user needs to /rant you access to read rom5 .rite to a speciic directory beore usin/ this pac2a/e' !ere is an example0
-ONNE-T / S S>SDB -RETE OR REPL-E DIRE-TOR> m;$r S /tmp& GRNT re$3 Crte ON DIRE-TOR> m;$r TO sott& +rovide user access to the U#$81$E pac2a/e ;created by catproc's4l=0
GRNT EAE-UTE ON UTL_+ILE TO sott& opy and paste these examples to /et you started0 Write
DE-LRE !0$"er UTL_+ILE.+ILE_T>PE& BEGIN !0$"er 71 UTL_+ILE.+OPEN(M>DIR3 m;"e3 C%& UTL_+ILE.PUT+(!0$"er3 LooH m3 Im writing to a file!!!\n'); UTL_+ILE.+-LOSE(!0$"er%& EA-EPTION #!EN ut"_"e.0"$_pth T!EN rse_pp"to0_error('43 I0"$ pth. -rete $retor; or set UTL_+ILE_DIR.%& END& / !ead
