Oracle plsql and d2 k interview questions

Oracle PLSQL and D2K InterviewQuestions 1. What isNOCOPY? By defaultthe IN parameterispassedbyreference andthe OUT andIN OUT parameters are passedbyvalue. NOCOPY: isa compilerhintthatcan be usedwithOUT and IN OUT parameterto requesttopass by reference.Thisimprovesthe performance withOUTandINOUT parameters. 2. REPORT TYPES A) Tabular Report B) Group LeftReport C) GroupAbove Report D) Formlike report E) Matrix Report F) Multi MediaReport G) MailingLabel Report H) OLE Report. 3. ANCHOR Anchorsare usedto determinethe vertical andhorizontalpositioningof achildobjectrelative toits parentobject.The endof the anchor shouldbe attachedtothe parentobject. A) ParentObject B) ChildObject Since the size of the some layoutobjectsmaychange whenthe reportruns (Whenthe datais actually fetched),anchorsneedtobe definedtomake the appearance of the object An Anchordefinesthe relativepositionof anobjecttothe objectto whichif thisanchored. * Symbol is

By pressingthe shiftkeyAnchorcanbe moved. AnchorProperties: A) ChildEdge percentonchildedge type B) ChildObjectName C) Collapse Horizontally D) Collapse vertically E) Comments F) Name * Twochildobjectscan be relatedtoone parentobjectby an Anchor. 4. User Exits: An userexitis:- Program that can be writtenandlinkedintothe report builderexecutable oruserexit DLL files. User exitsare buildwheneverthe control needtobe passedfromthe reportbuildertoaprogram, whichperformssome functionandthencontrol returnstothe ReportBuilder. Typesof user exits:- A) Oracle Pre-Complieruserexits. B) OCI(Oracle Call Interface userexits) C) Non– Oracle user exits. User exitscanperformthe followingtasks. Performcomplex datamanipulation. Passdata to report builderfromOStextfiles. SupportPL/SQLblocks. Control real time devicesbe printerorrobot. 5. Typesof Triggersin Reports:-

* ReportTriggers * Data Triggers * LayoutTriggers ReportTriggers:- AfterParameterformTrigger. AfterReportTrigger Before parameterformTrigger Before ReportTrigger. BetweenPagesTrigger + FiringSequence * Before ParameterformTrigger - Firesbefore the runtime parameterformisdisplayed. - The parametervaluescanbe accessedand changed. * AfterParameterformTrigger. - firesafterthe runtime parameterformisdisplayed. - The parameterscan be accessedandtheirvaluescanbe checked. * Before ReportTrigger - Firesbefore the reportisexecutedbutafterthe queriesare passedanddataisfetched. * Between PagesTrigger. - Firesbetweeneachpage of the reportis formatted,exceptthe veryfirstpages. - Thisisusedfor customizedpage formatting. * AfterReportTrigger - Firesafterexitingfromthe runtime premierorafterreportoutputis sentto a specified destination.(File,Printer,Mai lidetc….) - Thisisusedto cleanup anyinitial processingthatwasdone suchas deletingthe tables. - ThisTriggeralwaysfiresirrespectiveof successorfailure of the report.

DATA TRIGGERS: * Ref Cursor Query. - ThisusesPL/SQLto fetchdata for the report. - Inthisa PL/SQLfunctionneedtobe specifiedtoreturnacursor value froma cursor variable. * GroupFilter: Thisis PL/SQLfunctionthatdetermineswhichrecordstobe includedinagroup inthe property isPL/SQL. - The functionmustreturna BOOLEAN value. True …….. Includesthe currentrecordinthe report. False ……. Excludesthe currentrecordfromthe report. * Formula - These are Pl/SQLfunctionsthatpopulate formulaorplace holdercolumns. * ValidationTrigger - These are alsoPL/SQL functionsthatare executedwhenparametervaluesare specifiedonthe commandline and whenthe runtime parameterformisaccepted. - Are alsousedto validate the initial value propertyof the parameter. * LayoutTriggers * FormatTrigger. - These are PL/SQLfunctionsexecutedbeforethe objectisformatted. - Usedto dynamicallychange the formattingattributesof the object. * ActionTrigger - These are Pl/SQLproceduresexecutedwhenabuttonisselectedinthe runtime previener.

- Thiscan be usedtodynamicallycall anotherreportorexecute anyotherPL/SQL. 6 FormulaColumn It performsa user-definedcomputationonanothercolumnsdata,includingPlace- holdercolumns. Formulasare PL/SQL functionsthatpopulate formulaorplace holdercolumns. Cannotbe usedtopopulate parametervalues. 7 SummaryColumn - Performsacomputationonanothercolumnsdatalike sum, average,count,minimum,maximum, %,total. - For groupreports,the reportwizardand data wizardcreate ‘n’summaryfieldsinthe datamodel for eachsummarycolumnthat isdefined. --- One ateach group level above the columnbeingsummarized. - One at the reportlevel. 8 Place HolderColumn - A Place holdercolumnisa columnforwhich,the data type and value canbe setdynamically (Programmatically) * The value can be set fora place holdercolumninthe followingplaces. - Before reporttriggerif the place holderisareport level column. - Reportlevel formulacolumn,if the place holderisa reportlevel column. - A formulainthe place holdersgroupbelow it(The value issetonce foreachrecordof the group) 9 RepeatingFrame - Repeatingframe surroundsall of the fieldsthatare createdfora groups columns.

- Repeatingframe printsonce foreachrecordof the group. - For framesandrepeatingframes,the propertyelasticitydefineswhetherthe size of the frame or repeatingframe shouldwiththe objectsinside of itatruntime. 10 Frame - Surroundsthe objectsandprotectthemfrombeingoverwrittenorpushedbyotherobjects. 11 SystemParametersinReports * Background * Copies * Currency *Decimal * Desformat * Desname * Destype * Mode * Orientation * PrintJob * Thousands. 12 Data Link - Data linksrelate the resultsof multiplequeries. - A data link(Parent – ChildRelationShip) causesthe childquerytobe executedonce foreach instance of itsparentgroup. 13 In whichtablesFFare stored? A) FND – ID – FLEXS B) FND-ID-FLEX-STRUCTURES 14 Advantagesof storedfunctionsandprocedures

* Applicationscanbe modularized. * Easy maintenance. - Rowtinescanbe modifiedonline withoutinterferingother users. - One routine canbe modifiedtoeffectmultiple applications. * Improveddatasecurityandintegrity. - Indirectaccessto database objectscanbe controlledfromnonprivilegeduserswithsecurity privileges. * Improvedperformance. - Reparsingformultipleuserscanbe avoidedbyexploitingthe sharedSQLarea. - PL/SQLparsingat run-time canbe avoidedbypursingatcompile time. - Numberof callsto the database can be reducedandnetworktrafficdecreasedbybundling commands. * Improvedcode clarity. - The clarityof code increasesbyusingappropriate identifiernamestodescribe the actionof the routineswhichreducesthe needforcomments. 15 Difference betweenafunctionandaprocedure Functions Procedures * Invoke asa part of an expression. Execute asa PL/SQLstatement. * Must containa RETURN clause inthe header. Do not containa RETURN Clause inthe header. * Must returna single value. Can returnnone,one or many values. * Must containat fast one RETURN Can containa RETURN

Statement. Statement. * Do notcontainOUT and INOUT Can containIN,Out and IN OUT parameters. IN OUT Parameters. 16 AboutCursors - Oracle serverusessome private workareastoexecute SQLstatementsandtostore processing information. * By usingPL/SQLcursors these private SQLareascan be namedandthe stored informationcanbe accessed. Two Types: * ImplicitCursors. - Implicitcursorsare declaredbyPL/SQLimplicitlyforall DML and PL/SQLselectstatements, includingqueriesthatreturnonlyone row. - Oracle Serverimplicitlyopensacursorto processeach SQL statementnotassociatedwithon explicitlydeclaredcursor. - The most recentimplicitcursorcanbe returnedas the SQL cursor. * ExplicitCursors - For queriesthatreturnmore thanone row,explicitcursorsare declaredandnamedbythe programmesandmanipulatedthroughspecificstatementsinthe block’sexecutable actions. - Explicitcursorsare usedto individuallyprocesseachrow returnedbyamultiple-row SELECT statement. - The setof rowsreturnedbya multiple–row queryiscalledasactive set. Declare Open Fetch Empty? Close Cursor Attributes:-

Attribute Type Description % isopen Boolean EvaluatestoTRUE if the cursor isopen. % notfound Boolean EvaluatestoTRUE if the mostrecentfetch doesn’treturna row. % found Boolean Evaluate toTRUE if the mostrecentfetch returnsa row. Complementof %notfound. % RowCount Number Evaluatesthe total numberof rowsreturnedso far. ParameterizedCursors:- - Parameterscanbe passedto the cursor in a cursor forloop. - It allowtoopenandclose an explicitcursorseveral timesinablock,returningadifferentactive seton each occasionforeach execution,the previouscursorisclosedandreopenedwithanew setof parameters. - Sizesshouldnotbe mentionedforthe datatypesof parametersthe parametersnamesare for referencesinthe queryexpressionof the cursor. 17 ConfinedMode:- - If it ison, childobjectscannotbe movedoutside theirenclosingparentobjects. - If it isoff childobjectscan be movedoutsidestheirenclosingparentobjects. Flex Mode:- - If it ison, parent bordersstretchwhenchildobjectsare movedagainstthem. - If it isoff,parentbordersremain fixedwhenchildobjectsare movedagainstthem. 18 Parameters

- A parameterisa variable whose valuecanbe setat runtime (fromthe run time parameter of the commandline). - User parametersare createdbythe user andsystemparametersare createdbyReportBuilder. - Systemparameterscannotbe renamedordeleted. BindParameters(Variables) - Bindreferences (orVariables)are usedtoreplace a single value inSQLorPL/SQL,such as a character string,numberordate. - Bindreferencesmaybe usedtoreplace expressionsinSELECT,WHERE, GROUP BY, ORDER BY, HAVING,CONNECTBY and START WITH clausesof queries. - Bindreferencescannotbe referencedinFROMclauses. - Bindvariables canbe referencedbyenteringacolon(:) followedimmediatelybythe columnor parametername. - If the parameter/ columnisnot createdbefore makingabindreference,reportbuilderwill create a parameter. Lexical Parameters(Variables) - Lexical referencesare place holdersfortextthatisembeddedinaSELECT statement. - Lexical Variablescanreplace the clausesappearingafterSLECT,FROM, WHERE, GROUP BY, ORDER BY, HAVING,CONNECTBY, and START WITH. What is% Row type - % Rowtypesisusedto declare a recordbasedon a collectionof columnsinadatabase table or view. - The fieldsinthe recordtake theirnamesanddata typesfromthe columnsof the table orview. - The record can alsostore an entire row of data fetchedfromacursor or cursor variable. - %Row type shouldbe prefixedwiththe database table. Ex: Declare Emp_record employee5% row type.

Thenemp_recordwill have astructure consistingof all the fieldseach representingacolumninthe employeestable. What isa Ref Cursor? - Oracle serverusesunnamedmemoryspacestostore datausedinimplicitcursors. - Ref cursorsare usedto define acursorvariable,whichwill pointtothatmemoryspace and can be usedlike pointersinSQL‘S’. AboutExceptions - AnexceptionisanidentifierinPL/SQLthatis raisedduringthe executionof ablack that terminatesitsmainbodyof actions. - A blockalwaysterminateswhenPL/SQLraisesanexceptionsothatan exceptionhandlershould be specifiedtoperformfinal actions. * Exceptioncanbe raisedintwo waysexceptionisraisedautomatically. Ex:- whenno rowsare retrievedfromthe database inaSELECT statement,thenerror ORA-01403 occurs and the exceptionNO-DATA-FOUNDisraisedbyPL/SQL. Ex:- Exceptioncanbe raisedexplicitlybyissuingthe RAISEstatementwithinthe block. - The exceptionbeingraisedmaybe either. User-DefinedorPre Defined Trappingan exception:- - If the exceptionisraisedinexecutablesectionof the block,processingbranchestothe correspondingexceptionhandlerinthe exceptionsectionof the block. - If PL/SQL successfullyhandlesthe exception,thenthe exceptiondoesn’tpropagate tothe enclosingblockorcallingenvironment. - The PL/SQL blockterminatessuccessfully.

Propagatinganexception:- - If the exceptionisraisedinthe executable sectionof the blockandthere isnocorrespondingexception handler,the PL/SQLblockterminateswithfailure andthe exceptionwillbe propagatedtothe calling environment. Typesof exceptions:- A) Pre-DefinedOracle ServerExceptions. - ImplicitlyRaised. B) Non-Pre definedOracle serverexceptions. - ImplicitlyRaised. C) User-definedexceptions -ExplicitlyRaised. Pre-DefinedOracle ServerExceptions:- - These are the error(20) that occur mostofteninPL/SQL code. - These exceptionsneednotbe declaredandraisedimplicitlybyOracle Server,NO-DATA-FOUND, LOGIN_DENIED,ZERO_DIVIDE. Non-Pre-DefinedOracle ServerExceptions:- - These are the otherstandard Oracle Servererrors. - These exceptionsneedtobe declaredionthe declarativesectionandraisedbyOracle server implicitly. User DefinedExceptions:- - These are the conditionsthatthe developerdeterminesasabnormal. - These needtobe declaredandraisedexplicitly. PRAGMA EXCEPTION_INIT Statementisusedtoassociate a declaredexceptionwiththe standardOracle Servererrornumber. Syntax:-PRAGMA EXCEPTION_INIT(exception,errornumber) * SQLCODE,SQL ERRM are twofunctionswhichcanbe usedtoidentifythe associatederrorcode or error message whenexceptionoccurs. - SQLCODE functionreturnsthe numericvalue forthe errorcode.

- SQLERRMfunctionreturnsthe character data containingthe message associatedwiththe error number. - SQLCODE f SQLERRM cannot be useddirectlyinSQLstatements. What isDynamicSQL? - DynamicSQL isa SQL statementthatcontainsvariablesthatcanchange duringruntime. - It isa SQL statementwithplace holdersandisstoredasa characterstring. - DynamicSQL enablesDDL,DCL or sessioncontrol statementstobe writtenandexecuted(by) fromPL/SQL. * DynamicSQL can be writtenintwoways. A) DBMS_SQL. -8i B) Native DynamicSQL. - 8i - BasicallyDynamicSQLmeanscreatingthe SQL statementsdynamicallyatruntime byusing variables. Ex:- DynamicSQL can be usedto create a procedure thatoperatesona table whose name isnotknown until runtime orto execute DDL/DCL/SCSstatements. ---- InPl/SQLsuchstatementscannotbe executedstatically. -- EXECUTE IMMEDIATE Statementcanperformdynamicsingle row queries. Declare D_str varchar2 (200); Val varchar2 (20); Begin D_str= insertintotable1values(;val); Val=‘ Bye’Execute Immediate strusingval;end;

What are AutonomousTransactions? - Autonomoustransactionsare the processesrunindependentlyof itsparent. - By meansof AutonomousTransaction,the currenttransactioncanbe temporarilysuspendedand anotheroperationcanbe begun. - The basic ideabehindthisistohave some operationtake place independentlyof the current transaction. Ex:- to allowerrormessageswrittentotable tobe committedbutto rollbackeverything else thathastakenplace prior to the error. - The autonomousorchildtransactioncan commitor rollbackas applicable withthe executionof the parenttransactionbeingresumeduponitscompletion. - The parentmay thenperformfurtheroperationsof anyoperationsperformedwithinthe child transaction. - By usingAutonomousTransactions,modularandreusablecomponentscanbe developedmore easily. - Infact Oracle alreadyusessimilarfunctionalityinternally,knownas recu transactionsto handle the updatingof systemresources. Ex:- Whenone applicationselects‘nextval’fromanoneachedsequence,the value isin the database. - Thusa secondapplicationwill alwaysgetthe incrementedapplicationhascommittedorrolled back. - AutonomousTransactionshouldbe definedin PL/SQLinthe followingmanner. PRAGMA AUTONOMOUS_TRANSACTION; - Autonomoustransactionalsocanbe nested. - The parenttransactionremainsactive while anystatementsspecifiedinthe declare sectionof the autonomousunitare executed. - Asthe code unitexitsandcontrol returnsto the parentthe main(parent) transactionisresumed and the transactioncontextisswitchedbacktothe parent.

What isBulkbindingof Bilkcollect? Bulkbind:- - The assignmentof valuestoPL/SQLvariablesinSQLstatementsiscalledbinding. - The bindingof an entire collectionatonce isrefilledtoasbulkbinding. - Bulkbindimprovesperformance byminimizingthe numberof contextswitchesbetweenPL/SQL and SQL engineswhile theypassanentire collectionof elements(varay,nestedtables,index-bytable or hostarray) as bindvariablesbackandforth. - Priorto Oracle 81, the executionof everySQLstatementsrequiredaswitchbetweenthe Pl/SQL and SQL engines,where asbulkbindsuse onlyone contextswitch. * Bulkbindingincludesthe following A) Inputcollections;use the FORALLstatement. B) Outputcollections,use the BULKCOLLECT clause. InputCollections:- - Inputcollectionsare datapassedfromPl/SQLengine tothe SQLengine toexecute INSERT, UPDATE and DELETE statements. Syntax:- FORALLindex inlower_bound..upper_boundsql_statement; OutputCollections:- - Outputcollectionsare the datapassedfromthe SQL engine tothe PL/SQLengine asa resultof SELECT or FETCH statements. - The keywordBULK COLLECT can be usedwithSLECT INTO,FETCH INTOand RETURNING INTO clauses. Syntax:- BULK COLLECT intocollection_name,…… What are MaterializedViewsand Snapshots? MaterializedView:- - A Materializedview isareplicaof a targetmasterfrom a single pointintime.

- InOracle 7, itis ternedasSNAPSHOT - Oracle 7.1.6 -- Uptable Snapshots - Oracle 7.3 - PrimaryKey Snapshots - Oracle 8 - Materializedview - Oracle 9 - MultifierMaterializedView. - Materializedviewscanbe usedbothforcreatingsummariestobe utilizedindatawarehouse environments. * Replicatingdataindistributed environments. Target Master -The table(s)whichis(are) referencedbythe MVIEWquery. BestTable -The tablesare that is(are) createdbyMVIEW create statementandthat storesdata that satisfythe MVIEW query. Syntax:- Create materializedview <name> Refreshfast Start withsysdate Nextsysdate +1 as Select*from<mastertable>; - Since thisisa fast refreshedMVIEWthe mastertable shouldhave alog(Masterlog) to recordthe changeson itthat can be createdbyrunning. Create materializedviewlogonmaster_table; - thisstatementcreatsthe followingobjects - a table calledMLOG$_Master_table - an internal triggeronMaster_table thatpopulatesthe logtable. * Master Log tables(MLOG$) are usedby fastrefreshprocedure. RefreshingMaterializedViews:- - Initiallyamaterializedviewcontainsthe same dataasin the mastertable.

- Afterthe MVIEW iscreated,changescan be made to the master table andpossiblyalsotothe MVIEW. - To keepa MVIEW data relativelycurrentwiththe datainthe mastertable,the MVIEW mustbe periodicallyrefreshed. * Refreshcanbe accomplishedbyone of the followingprocedures. Dbms_mview.refresh(<mviewlist>,<Refreshtypes>) Dbms _ refresh.refresh (<RefreshGroups>) RefreshTypes -Complete Refresh,FastRefresh,Force Refresh * Complete Refreshisperformedbydeletingthe rowsfromthe snapshotandinsertingthe rowssatisfyingthe MVIEWquery. * InFast refreshonlythe rowsupdatedsince lastrefreshare pulledfromthe mastertable to insertintoMVIEW. * Thisrequiresalogtable calledasMVIEW Log tobe createdonthe Master Table. * Force refreshfirsttriestorun a Fast refreshif possible. * If fast refreshisnotpossible,it performscomplete refresh. RefreshGroups - These are usedto refreshmultiple snapshotsinatransitionallyconsistentmanner. - Whena refreshgroupisrefreshedall MVIEWSinthatgroup are populatedwithdatafroma consistentpoint intime. - Refreshgroupsare managedbyusingthe proceduresinthe package DBMS – REFRESH. - DBMS-REFRESH,MAKE of DBMS-REFRESH.ADDare usedto create a refreshgroupand addnew snap shotsto an existinggroup. Typesof MaterializedViews:- 1 Read-onlymaterializedviews

- DML cannotbe performedonthe snapshotsinthiscategory. 2 Up datable materializedviews - These MVIEWSeliminatesthe restrictionof DML’son snapshots. 3 Subquerymaterializedviews - These are the MVIEW’S that are createdwithsub queriesinthe WHERE clause of a MVIEW query. 4 Row idVsPrimaryKeymaterializedviews - MVIEW’Sthat use Row idfor refreshare calledRow idMVIEW’s(Oracle 7). - MVIEW’S that use primarykeyfor refreshare calledprimarykeyMVIE’S (Oracle 8) . * Fastrefreshrequiressome association(mapping) Betweenrowsatsnapshotandmastertables. 5 Multifiermaterializedviews(Oracle9) - Inthistype MVIEW, itsmaster table itself isaMVIEW. - Thisfeature enablesfastrefreshof MVIEW’Sthathave MVIEW’S as theirmasters. - Many companiesare structuredonat leastthree levels A) International B) National C) Local - Many nodesat boththe national andlocal levelsare required - The bestpossible solutioninsuchcasesisto use multifierMVIEW’S. 6 Simple VsComplexMVIEW’S. - MVIEW’Sbeingsimple orcomplex determineswhetheritcanbe fast refreshedornot. - A MVIEW isfastrefreshable if itissimple. - A MVIEW isnot fastrefreshable if itiscomplex. - A MVIEW can be consideredCONNECTBY,INTERSECT,MINUS or UNION or UNION ALL clausesin itsdetiningquery.

* The followingdatatypesare notsupportedinMVIEWreplication. A) LONG B) LONG RAW C) BFILE D) UROWID - MVIEW’Sare typicallyusedindataware house or decisionsupportsystems. Snapshots - Snapshotsare mirroror replace of tablesina single pointof time. - A Snapshotis a local copyof a table data that originatesfromone ormore remote mastertables. - To keepa snapshotsdatacurrentwiththe data of itsmasterthe Oracle servermustperiodically refreshthe snapshot. VIEWS - Viewsare built usingthe columnsfromone ormore tables. - The single table view canbe updated,butthe view withmultiple tablescannotbe updated. * A snapshotcontainsa complete orpartial copyof a target mastertable froma single pointintime. - A snapshotmaybe read onlyor updatable. 26. How duplicate rowsare deleted? - Duplicate rowsare deletedbyusingROWID Syntax  delete from<Table> Where ROWID notin (Selectmax (ROWID) from <Table> Group by <Column_name>); 27. How doyou call functionandprocedure inPL/SQLas well asin SQL prompt? isql*plus EXECUTE< Function/Procedure name >; (SQLprompt)

PL/SQL< Procedure Name /FunctionName>; (fromanotherprocedure) DevelopmentTools <Procedure name>; 28. DifferencebetweenIN andOUTparameters. Three typesof parameters 1. IN 2. OUT 3.IN OUT IN parameter: - Thisparameterpassesavalue fromthe callingenvironmentintothe procedure. - Thisisthe defaultmode - A formal parameterof In mode cannotbe assignedavalue (we IN parametercannotbe modified inthe bodyof the procedure ) - IN parameterscan be assignedadefaultvalue inthe parameterlist. - IN parametersare passedbyreference. OUT parameters: - OUT parametermustbe assignedavalue before returningtothe callingenvironment. - OUT parameterpassesa value fromthe procedure tothe callingenvironment - OUT parametercan not be assignedadefaultvalue inthe parameterlist. IN OUT parameter: - thistype of parameterpassa value fromthe callingenvironmentintothe procedure anda possiblydifferentvaluefromthe procedure backtocallingenvironmentusingthe same parameter. - IN OUT parametercannotbe assignedadefaultvalue.*By defaultOUT& IN OUT parametersare passedbyvalue. - These canbe passedbyreference byusingNOCOPY. 29. Triggers:

- A triggeris a PL/SQL blockor a PL/SQL procedure associatedwithatable view schemaorthe database. - The code in the triggerexecutesimplicitlywheneveraparticulareventoccurs. Two typesof triggers: Applicationtrigger - fireswheneveranevent occurswithina particularapplication Database Trigger - Fireswheneveradataevent(Suchas DML ) or systemevent(suchaslogon or shutdown) occurs on a schemaor database. - Executesimplicitlywhenadataevent.Suchas DML ona table (insert,deleteorUpdate),an INSTEADOF triggeron a VIEWor DDL statementsare issuednomatterwhichuserisconnectedorwhich applicationisused. - Alsoexecutesimplicitlywhensome userordata base systemactionsoccur. - Ex.Whenuserlogs onto the system. WhenDBA shutsdownthe data base. - Date base triggerscan be definedontablesandonviews. - If a DML operationsasissuedona view,the INSTEADOFtriggerdefineswhatactiontakesplace, if these actionsinclude anyDML operationsontables,thenanytriggersonthe base tablesare fired. - Data base triggerscan be systemtriggersona data base or a schema. - Witha data base,triggersfire foreach eventforall users,withaschema,triggersfire foreach eventforthe specificuser. Recursive trigger: - Thisisa triggerthat containsa DML operationchangingthe verysame table. CascadingTrigger: - The actionof one triggercascadesto anothertrigger,causingthissecondtriggertofire. - Oracle serverallowsupto32 triggersto cascade at any one time.

- Thisnumbercan be changedby changingthe value of the OPEN - CORSORS.Data bases initializationparameter.( defaultvalue is50). - * A triggeringstatementshouldcontain 1 TriggerTiming Before,After (ForTable) Insteadof (ForView) - Determineswhenthe triggerneedstobe firedinrelationtothe triggeringevent. 2 TriggeringEvent Insert,Update,Delete - Determineswhichonthe table orview causesthe triggertofire. 3 TriggerType Statement,Row - Determineshowmanytimesthe triggerbodyexecutes 4 Table name Table,View 5 Triggerbody PL/SQL– block - Determineswhatactionsthe triggershouldperform. * INSTEADof triggersare usedtoprovide a transparentwayto modifyingviewsthat cannot be modifieddirectlythroughSQL,DML statementsbecausethe view isnot modifiable. - INSTEADof triggersprovide writingof Insert,Update andDelete statementsagainstthe view. - The INSTEADif triggerworksinvisiblyinthe backgroundperformingthe actioncodedinthe triggerbodydirectlyonthe underlyingtables. - INSTEADof triggerexecute the triggerbodyinsteadof the triggeringstatement. StatementTriggers - Inthistype of triggers,the triggerbodyexecutesonce forthe triggeringevent. - Thisisthe default. - Statementtriggerfiresonce,evenif norowsare affectedatall.

Row Trigger - Inthistype,the triggerbodyexecutesonce foreachrow affectedbythe triggeringevent. - Rowtriggeris not executedif the triggeringeventaffectsnorows. * A viewcannotbe modifiedbynormal DML if the view querycontainssetoperators, groupfunctions,groupby,connectBy, start withclausesorjoins. MutatingTable - A Mutating table isa table that iscurrentlybeingmodifiedbyanUPDATE,DELETE OR INSERT statement,ora table that mightneedtobe updatedbythe effectsof a declarative DELETE CASCADE referentialintegrityaction. - A table is notconsideredmutatingforstatementtriggers. - A mutatingtable cannotbe changedbecause the resultingDML couldchange data that isin consistentstate. What isSQL Trace? - SQL Trace isthe mainmethodforcollectingSQLexecutioninformationinOracle collectingawide range of informationandstatisticsthatcanbe used totune SQL operations. - The SQL – Trace facilitycanbe enabled/disabledforanindividual sessionoratthe instance level. - If the initializationparameterSQL-TRACEissettoTRUE in the init.oraof an instance,thenall sessions willbe traced. - SQL-TRACE can be setat the instance level byusingthe initializationparameterSQL-TRACE. - SQL-TRACEcan alsobe enabled/disabledatthe system/sessionlevelbyusing. Altersystem/sessionsetSQL-TRACE=TRUE/FALSE. ExplainPlan - Explainplancommandgeneratesinformationthatdetailsthe executionplanthatwill be usedon a particularquery.

- A usesa pre createdtable (PLAN_TABLE) inthe currentschemato store informationaboutthe execution planchosenbythe optimizer.  Creatingthe plantable - Plantable iscreatedbyusingthe script utl x plan,sql (Oracle Home / RDBMS / admin/ uti x plan.sql) Unix  $ ORACLE_HOME / rdbms/ admin - Thisscriptcreatesan outputtable,calledPLAN-TABLEforholdingthe outputof the explain command.  Populatingthe PLAN TABLE - PLAN TABLE ispopulatedusingthe explainplan. SQL> ExplainPlanforselect* fromempwhere empno= 1000; - Thiscommandinsertsthe executionplanof the SQLstatementintothe plantable. - A name tag can be addedto explaininformationbyusingthe setstatement_idclause. Displayingthe ExecutionPlan - Once the table has beenpopulated,the explaininfoneedstobe retrievedandformatted. - Numberof scriptsare available toformatthe plantable data. $ ORACLE_HOME / rdbms / admin/utlxpls.Sql –to format serial explainplans. $ ORACLE_HOME/ rdbms/admin/utlxpil.Sql –toformat parallel explainplans. * AUTOTRACE - The AUTOTRACE facilityinSQL*plusallowsanalyststoview the executionpandsome useful statisticsfora SQL statementwithinaSQL*plussession. - AUTOTRACEneedstobe initiatedinthe SQL*Plussessionpriorto executingthe statement. SET AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS]

- Aswiththe explainplancommand,toobtainanexecutionplanthe PLAN-TABLEmustbe created inthe user’sschemapriorto AutoTracing. SQL> Set Autotrace trace onlyexplain SQL> Select* from dual; - To enable viewingof STATISTICSdata,the auto tracingusermust have accessto dynamic performance tables. - To achieve this,grantPLUS TRACE role tothe user. PLUS TRACE role iscreatedby the plustrace. Sql script $ ORACLE_HOME / sql plusadmin SYS usermust runthisscript. DBA can themgrant the role to the userswho wishtouse the AUTOTRACE. TK PROF - Tk prof facilityacceptsasinputa SQL Trace File andproducesaformattedoutputfile. - Tk Prof Filename_source filename_outputEXPLAIN =[username /password] sys= [yes/no] TABLE = [Table Name] A) How do youadd trace to a report? - By usingthe package SRW.TRACE_ADD_OPTION B) Howdo you execute aspecifiedDDLina report? - BY usingthe package PW.DO_SQL C) Howdo yougenerate message inreports? - By usingthe packagesPW.MESSAGE (RegNum, ); D) ExplainBLOBSof CLOBS? LOB A LOB isa data type thatis usedto store large,unstructureddata suchas text,graphicimages, video,clippingsetc. * Fourlarge objectdata types. BLOB : Representsabinarylarge object

CLOB : Representsacharacter large object NCLOB:Representsamultibyte characterobject. BFILE: Representsabinaryfile store of inanos binaryfile outside the database. LOB’S Internal LOBS(CLOB,BLOB,NCLOB) external Files(BFILE) - Dependingonthe storage aspectsandtheirinterpretationbyOracle server. * LONG_TO_LOB APIisusedto migrate LONG columnstoLOB columns. LOB’S LOB Locator - A table can have multiple LOBcolumns - The maximumsize of aLOB can be 4 GB - LOB’Sreturnthe locator - LOB’Sstore a locatorin the table enddatain a differentsegmentunlessthe dataislessthan4000 bytes. LOB LOB value (real data) LOB locator (pointertothe locationof the LOB value) - A LOB columndoesn’tcontainthe dataand itcontainsthe locatedof the LOB value. - Whena table iscreatedwithLOB column,the defaultstorage isENABLESTORAGE IN ROW. - If DISABLE storage inRow optionisusedthe LOB value isnotstoredin the ROW evenif the size is lessthen4000 bytes. Internal LOB’S - Storedinside the Oracle server.

- BLOB,NCLOB, CLOB. BFILE - BFILE’Sare external LOB’S. - These are storedinOS filesoutside the database table spaces. - The data type isBFILE. - BFILE data file storesalocatorto the physical file. - BFILE can be GIF, JPEG, MPEG, textor otherformats. *DBMS_LO.READ of DBMS_lob.WRITEare usedto manipulate LOBS. Oracle ApplicationsArchitecture - InternetcomputingArchitecture isaframe workfor3-tired,distributedcomputingthatsupports Oracle Applicationsproducts. - The Three tiersare 1 Data Base Tier 2 ApplicationTier 3 DeskTop Tier - Database tiermanagesOracle 8i database. - ApplicationtiermanagesOracle Applicationsandothertools. - Desktoptierprovidesthe userinterface displace. - Withinternetcomputingarchitecture,onlythe presentationlayerof Oracle Applicationsisonthe DeskTop tierinthe formof a plug-intoa standardinternetbrowser. TEMPLATE FORM - The TEMPLATE formis the requiredstartingpointforall developmentof new forms. - The developmentof anewformisstartedby copyingthe TEMPLATE.fmbfile,locatedin$AV_top / forms/us,to a local directoryandrenamingitas appropriate. TEMPLATE FORMCONTAINSTHE FOLLOWING:-

* platform-independentreferencestoobjectgroupsinthe APPSTANDform (STANDARD_PCAND_VA, STANDARD_TOOLBAR, STANDARD_CALENDAR) *platform– independentattachmentsof several libraries FND SQF APPCORE APPDAYPK *several formlevel triggerswithrequitedcode *program unitsthat include aspecificationandabodyfor the package APP_CUSTOM, whichcontainsdefaultbehaviorsforwindow openingandclosingevents. - Ingeneral thiscode shouldbe modifiedforthe specificformunderdevelopment. * The application’scolorpallet,containingthe twocolorsrequiredbythe referencedvisual attributes. * Many referencedobjectsthatsupportthe calendar,the toolbar,alternative regionsandthe menu. * Template formcontainssimple objectsthatshow typical itemsandlayoutcosmetics. - These are deletedafterdevelopingthe form Blocks: Block Name,Detail Block Window: BlockName Canvas_view:BlockName * Template formincludesplatform-independentattachmentsof several libraries. - Some of the librariesare attacheddirectlytothe TEMPLATE (FNDSOF,APPCORE,APPDAYPK) while the othersare attachedto these libraries. APPCORE,APPDAYPK,FNDSOF,CUSTOM,GLOBE, VERT,JA, JE,JL

APPCORE - Containsthe packagesandproceduresthatare requiredof all formstosupportthe Menu,Toolbar and otherrequiredstandardbehaviors. - ProceduresandfunctionsinAPPCOREhave namesbeginningwithAPP. APPDAYPK - Containsthe packagesthatcontrol the Oracle applicationscalendarfeature. FINDSQF - Containspackagesandproceduresformessage dictionary,florfields,profilesandconcurrent processing. - It alsohas variousotherutilitiesfornavigation,MRG,WHO etc. - Proceduresandfunctionshave namesbeginningwithFND. CUSTOM - Customlibraryallowsextensionof Oracle Applicationsformswithoutmodificationof Oracle applicationscode. - Customlibrarycan be usedforcustomizationssuchasZOOM(Movingto anotherformfromone formand queryingupspecificrecords),enforcingbusinessrules. Ex:- Suppliername mustbe inuppercase) and disabling fieldsthatare notrequiredfora particularsite. - All logicmustbranchbasedon the formand blockfor whichitis run. - Oracle applicationssendeventstothe Customlibrary. - Customcode can take effectbasedonthe events. What issub queryandcorrelatedsubquery? SubQuery:-

- A Sub Queryisa SELECT statementthatisembeddedinaclause of anotherSQL statementscalled the parentstatement. - Subquery(InnerQuery) returnsavalue thatis usedbythe outerquery. - Scalarsub queryisa subquerythat returnsexactlyone columnvalue fromone row. CorrelatedSubQuery - Correlatedsubqueryare usedforrow – by –row processing. - Each sub queryisexecutedonce foreveryrow of the outerquery. - A correlatedsubqueryisone way of reading(data) everyrow ina table and comparingvaluesin each rowagainstrelateddata. - Oracle serverperformscorrelatedsubquerywhenthe subqueryreferencesacolumnfroma table inthe parentquery. - The innerqueryisdrivenbythe outerqueryincorrelatedsubqueries. - A correlatedsubqueryisevaluatedonce foreachrow processedbythe parentstatement. GET Candidate row fromthe outerquery EXECUTE Innerqueryusingthe candidate row value USE Valuesfrominnerquerytoqualify/disqualifycandidate row

Ex:- Selectlast_name,salary,department_idfromemployersOUTER where salary>(selectAVG(Salary) fromemployees where departmentid=outer.department_id); Typesof Joins 1 Equi Join 2 Non-Equi Join 3 OuterJoin 4 self Join Equi Join:- - Isalso calledsimpleorinnerjoin. - Anequi joinisa joinconditionthatcontainsequalitysign. NonEqui Join:- - It isa joinconditionthatcontainssomethingotherthanthe equalityoperator. OuterJoin:- - Outerjoinsare usedto alsosee rowsthat do notmeetthe joincondition. - Outerjoinoperatoris(+). - The missingrowscan be returnedif anouter joinoperatorisusedinthe joincondition. - The operator(+) is placedonthe side of the jointhatis deficientininformation. - Thisoperatorhas the effectof creatingone or more null rowsto eachone or more rowsfromthe (joincondition)nondeficienttable canbe joined.

Self Join - Self joinisusedtojoina table toitself. Ex:- to findthe name of eachemployer’smanager. Whichtriggerwill fire whencursormovesfromone blocktoanotherblock? WHEN_NEW_BLOCK_INSTANCE What are the triggersusedinCUSTOM.Pll? CUSTOM.Pll contains(CUSTOM package) the followingfunctionsandprocedures. CUSTOM.ZOOM_AVAILABLEFUNCTION CUSTOM.STYLEFUNCTION CUSTOM.EVENTPROCEDURE. * TriggersinCustom.Pll:- 1 WHEN _NEW_FORM_INSTANCE 2 WHEN_FORM_NAVIGATE 3 WHEN_NEW_BLOCK_INSTANCE 4 WHEN_NEW_RECORD_INSTANCE 5 WHEN_NEW_ITEM_INSTANCE 6 ZOOM 7 EXPORT 8 SPECIAL1-45 9 KEY_Fn (nis a numberbetween1and8) What isthe difference betweenpre-queryandpost-query? * Pre-queryexecutesonlyonce forthe statementwhere aspost-queryexecutesforeach record.

List of some API’S FND_PROGRAM.EXECUTABLE FND_PROGRAM.REGISTER FND_PROGRAM.PARAMETER FND_PROGRAM.ADD_TO_GROUP FND_REQUEST.SUBMIT_REQUEST FND_PROFILE.VALUE FND_PROFILE.GET How to getsecondparametervalue basedonfirstparameter? $fle x $ value setname. What isRef Cursor Ref cursor isa data type andexecutedatserverside andwithref cursormultiple selectstatementscan be executed. By increase the binarysize,thatnumberof recordsthatare committedcanbe increasedbyusing control file. Formscan be developmentinAPPSintwoways A) Customizationbyextension(usingtemplate.fond) B) Customizationbymodification(usingcustom.pll) What are the advantagesof packages? Packagesbundle relatedPl/SQLtypes,itemsandsub-programsintoone container. A package shouldhave itsspecificationandbody,storedseparatelyinthe database. Specificationisthe interface tothe applications.

A declaresthe types,variables,constants,exceptions,cursorsandsubprogrammesavailableforuse. The body fullydefinesthe cursorsandsub programmesandso implementsthe specification. Once writtenandcompiledthe contentscanbe sharedby manyapplications. Whena packagedPL/SQL constructis calledforthe firsttime the whole package is loadedintomemory, thuslatercallsto constructs inthe same package require nodiskI/O. Publicpackage constructscan be referencedfromanyOracle serverenvironments. Private package constructscan be referencedonlybyotherconstructswhichare part of the same package. Advantages 1 Modularity 2 EasierApplicationDesign 3 HidingInformationbyusingpublicandprivate. 4 Addedfunctionality. 5 Betterperformance. 6 Overloading. Proceduresandfunctionscanbe overloadedi.e.creatingmultiplesubprogrammeswiththe same name inthe same package eachtakingparametersof differentnumberordata type. How to call WHO columnsintothe form By usingFND_STANDARDAPI’S 1. FND_STANDARD.FORM_INFO Providesinformationaboutthe form. Shouldbe calledformwhen_new_form –instance – instance trigger. 2. FND_standard.set_who loadsWHO columnswithproperuserinformation. Shouldbe calledfromPRE_UPDTEandPRE_INSERT

Triggersfor eachblockwithWHO fields If this isusedFND-GLOBALneednotbe called. (FND_GLOBAL.WHO) 3. FND_STANDARD.SYSTEM_DATE Thisis a functionwhichreturnsdate. Behave exactlylikeSYSDATEbuilt-in. 4. FNID_STANDARD.USER Thisis a functionwhichreturnsvarchar2 BehavesexactlylikebuiltinUSER. APPCOREAPI’S APP_COMBO APP_DATE APP_EXCEPTION APP_FIELD APP_FIND APP_ITEM APP_ITEM_PROPERTY APP_NAVIGATE APP_RECORD APP_REGION APP_STANDARD APP_WINDOW FNDSQFAPI’S

FND_CURRENCY FND_DATE FND_GLOBAL FND_ORG FND_STANDARD FND_UTILITIES.OPEN_URL FND_UTILITIES.PARAM_EXISTS How to call flex fieldsinthe form? By usingFND_FLEX.EVENT(EVENTvarchar2) How to registeranexecutableanddefineaconcurrentprogram throughbackend? By usingconcurrentprocessingAPI’S 1. FND_CONC_GLOBAL.REQUES_DATA .SET_REQUEST_GLOBALS 2. FND_CONCURRENT.AF_COMMIT .AF_ROLLBACK .GET_REQUEST_STATUS .WAIT_FOR_REQUEST .SET_COMPLETION_STATUS 3. FND_FILE . PUT . PUT_LINE .NEW_NAME .PUT_NAMES .CLOSE

4. FND-PROGRAM . MESSAGE . EXECUTABLE . REGISTER . PARAMETER . IN COMPATIBILITY . EXECUTABLE_EXISTS 5. FND_REQUEST . SET-OPTIONS .SET_REPEAT_OPTIONS .SET_PRINT_OPTIONS .SUBMIT_REQUEST .SET_MODE 6. FND_REQUEST_INFO . GET_PARAM_NUMBER . GET_PARAM_INFO . GET_PROGRAM . GET_PARAMETER 7. FND_SET . MESSAGE .ADD_PROGRAM .ADD_STAGE .IN COMPATIBILITY 8. FND_SUBMIT. SET_MODE .SET_REQUEST_STATUS .SUBMIT_PROGRAM .SUBMIT_SET * FND_PROGRAM.EXECUTABLE - isusedto define aconcument programexecutable

- ittakes8 parameters( all are IN mode ) syntax procedure FND_PROGRAM.EXECUTABLE (executable IN varchar2, (Full name)  applicationIN varchar2, (executable shortname)  shortname IN varchar2, descriptionIN varchar2defaultnull execution_methodIN varchar2, execution_file_name IN varchar2defaultnull, (onlyfol spauned&Immedaite)  subroutine_nameIN varchar2defaultnull, icon_name IN varchar2 defaultnull, language_code IN varchar2 default(VS) (forJava Conc Program)  execution_file_pathIN varchar2defaultnull, * FND_PROGRAM.REGISTER - thisprocedure nousedto define a concumentprogram. - It has 30 IN paranmeters.Outof which9 are mandatory,the remainingare default. Syntax procedure FND_PROGRAM.REGISTER (program IN varchar2, application IN varchar2, enabled IN varchar2, short_name IN varchar2, description IN varchar2, defaultnull, ( executable_short_name) executable_name IN varchar2 executable_applicationIN varchar2, mls_function_shelt_name IN varchar2, mls_function_applicationIN varchar2,

inerementorIN varhcar2); 56. How to registeratable andcolumnsthroughback end? * byusingAD_DD package - forregisteringatable  AD_DD.REGISTER_TABLE - forregisteringcolumns  AD_DD.REGISTER_COLUMN. - AD_DD BPIdoesn’tcheckfor the existenceof the registeredtable orcolumninthe database schema,butonlyupdatesthe requiredSQLtables. - It shouldbe ensuredthat,all the tablesandcolumnsregisteredexistactuallyandhave the same formatas that definedusingAD_DDAPI. - Viewsneednotbe registered. 57. How to write toa file throughconcurrentprogram. * By usingFND_FILEpackage and itcan be usedonlyforlog andoutputfiles. FND_FILEpackage containsprocedurestowrite texttologand outputfiles. FND_FILEsupports a maximumbufferlinesize of 32kfor bothand outputfiles. 1. FND_FILE.PUT - thisisusedto write texttoa file withoutanew line character - Multilane callstoFND_FILE.PUTwill produce consummatedtext. Procedure FND_FILE.PUT(whichIN Number, Buff IN varchar2); Which logoutputfile - can be FND_FILE.LOG or FND_FILE.OUTPUT. 2. FND_FILE.PUT_LINE - thisprocedure asusedto write a line of texttoa file followedbyanew line character. Procedure FND_FILE.PUT_LINE(whichIN number, buff IN varchar2); EX:- FND_FILE.PUT_LINE( FND_FILE.LOG,find_message_get);

3. FND_FILE.NEW_LINE - thisprocedure isusedtowrite line terminatorstoafile procedure FND_FILE.NEW_LINE(whichIN number LINES IN NATURAL:=1); Ex:- to write twonewline characterstoa logfile Fnd_file.new_line (fnd_file.log,2); 4. FND_FILE.PUT_NAMES - thisprocedure asusedto setthe temporary logfile andoutputfilenamesandthe temporary directorytothe userspecifiedvalues. - Thisshouldbe calledbefore callingmyotherFND_FILEprocedure andonlyonce pera session. FunctionFND_REQUEST.SUBMIT_REQUEST ( application invarchar2 defaultnull, program invarchar2 defaultnull, description invarchar2 defaultnull, start-time invarchar2 defaultnull, sub_request inbookan defaultFalse, argument1, arguemnt2, argument100) returnnumber; * If thisis submittedfrom oracle forms,all the arguments( 1 to100 ) mustbe specified. 59. How to displayRequestIDinthe reports? * By usingthe parameterP_CONC_REQUEST_ID whichneedstobe definedalwaysinthe reports. 60. How to getusername /userid inreports?

- By usingthe APIFND_PROFILETHE OPTION values can be manipulatedinclient/serverprofilecaches - FND_PROFILE.GET,FND_GLOBAL.USER_ID FND_GLOBAL.USER_NAME - thisprocedure islocatedinFNDSQFlibrary. - Thisprocedure isto locatedtoget the currentvalue of the specifeduserprofile optionornull if the profile doesnotexist. - The server_side PL/SQLpackage FND_GLOBALreturnsthe valueswhichneedtosetwhocolumns for insertsandupdatesfrom storedprocedures. - Procedure FND_PROFILE.GET( name invarchar2, Value outvarchar2); FND_PROFILE.GET(‘USER_ID’,user_id); * FND_PROFILE.VALUE - thisfunctionexactlyworkslikeGET,exceptitreturnsthe valuesof the specifiedprofile optionasa functionresult FunctionFND_PROGILE.VALUE(name invarchar2 Returnvarchar2; *FND_PROFILE.PUT - thisisusedto put a value tothe specifiedprofileoption - - if the optiondoesn’texist,itcanalsobe createdwithPUT. Procedure FND_GET (‘USERNAME’,username); FND_PROFILE.GET(‘USER_ID’,user_id); - username,user-id,vsp_id,Appl_shrt_Name,Resp_Appl_Id,Form_Name,Form_id, Form_Appl_Name,Form_Appl_Id,Logon_Date,Last_Lagon_Date,login_id,Gone_Request_Id, Gone_Program_Id,Gone_program_Application_Id,Gone_Login_Id,Gone_Print_Output,

Gone_printstyle_these are the userprofile optionsthatcanbe accessedviauserprofile optionroutines . - the valuesof these profileoptionscanbe retrievedinforms,reportsandprogram - these valuescannotbe changedexceptGone_Print_OutputandGone_Print_Style. - SA’sandendusersalso cannotsee the valuesorchange these values. In whichdirectorylogandoutputfilesare stored? * APPLCSF isthe top level directoryinwhichthe concurrentmanagerputslogsandoutputfiles. * APPLLOG and APPLOUTare the subdirectoriesinwhichthe concurrentmanagerputslogandoutput files. *APPLCSFvariable needtobe setinthe prod.env( environmental variable ),sothatall productlog files $ APPLCSF/$ APPLLOGlogfiles $APPLCSF/$ APPLOUT outfiles * concurrentmanagerlogand out putfilesshouldgoto$ FND_TOP/$ APPLOGand$ FND _TOP / $APPLOUT if $ APPLCSFisnot set. 62. How to submitconcurrentprogramsthroughOS? - Fromthe operatingsystemthe utility.CONCSUB isusedtosubmitisconcurrentpropgram. - Thisisbasicallyusedtotesta concurrentprogram . - By usingthe WAITtoken. The utilitychecksthe requeststatusevery60secondsandreturnsthe OS promptuponcompletionof the request. - Concurrentmanagerdoesnotabort,shutdownorstartup until the concurrentrequestcompletes. * If the concurrentprogram iscompatible withitself,itcanbe checkedfordata integrityanddeadlocks by submittingitmanytimessothatit runsconcurrentlywithitself. *PL/SQL procedurescansubmita requesttorun a program as a concurrentprocessby calling. FND_REQUEST. SUBMIT_REQUEST. * Before submittingarequest,the followingfunctionsalsoshouldbe calledoptionally. FND_REQUEST.SET_OPTIONS

FND_REQUEST.SET_REPEAT_OPTIONS FND_REQUEST.SET_PRINT_OPTIONS FND_REQUEST.SET_MODE 63. How to checksthe requeststates? - A PL/SQL procedure cancheck the statusof a concurrentrequestbycalling. FND_CONCURENT.GET_REQUEST_STATUS FND_CONCURRENT.WAIT_FOR_REQUEST - FND_CONCURRENT.GET_REQUEST_STATUS - Thisfunctionreturnsthe statusof a concurrentrequest - If the requestisalreadycomputed,italsoreturnsthe completionmessage. - Thisfunctionreturnsbothuserfriendly(translatable) phase andstatusvaluesaswell asdeveloper phase and statusvalesthatcan drive program logic. Syntax  FunctionFND_CONCURRENT.GET_REQUEST_STATUS ( request_idinoutnumber, applicationinvarchar2defaultnull, program invarchar2 defaultnull, phase outvarchar2, statusout varchar, dev_phase outvarchar2, dev_statusoutvarchar2, message outvarchar2) returnBOOLEAN; - whenapplicationandprogramare specified,the request_idof the lastrequestforthe specified program shouldbe returnedtorequest_id. - Phase,andstatusvaluesshouldbe takenfrom FND_LOOKUPS dev_phase dev_status

pending normal,standby,scheduled,paused running normal,waiting,resuming,terminating. Complete normal,Error, warning,cancelled,terminated Inactive disabled,on-hold,No-manager,supended - FND_REQUEST.WAIT_FOR_REQUEST - Thisfunctionwaitsforrequestcompletion,thenreturnsthe requestphase/statusandcompletion message tothe caller. - Goesto sleepbetweenchecksforrequestcompletion. Syntax FND_CONCURRENT.WAIT_FOR_REQUEST ( request_idinnumberdefaultnull, interval innumberdefault60, max_waitinnumbe default0, phase outvarchar2, statusout varchar2, dev_phase outvarchar2, dev_statusoutvarchar2, message outvarchar2) returnBOOLEN; * FND_CONCURRENT.SET_COMPLETION_STATUS - thisfunctionshouldbe calledfromaconcurrentprogramto setits completionstates. - ThisfunctionreturnsTRUE on success,otherwise FALSE. Syntax  functionFND_CONCURRENT.SET_COMPLETION_STATUS ( statusin varchar2, message invarchar2) return BOOLEAN; normal status warning message anymessage

Error 64. How to generate fmx atOS level? - Formscan be generatedonthe formsserverasthe APPLMGR user. - Generatingthe formonthe Form server, $ FORM60_PATH Shouldbe setand the currentdirectoryshouldbe $AV_TOP/forms?us. Syntax  $F60 genuserid=apps/appsp module=<form_name>.Fmb Output_file=<schema_top>/forms?<language>/<form_name>.fmx Module_type=formbath=nocompile_all=special 65. How to converta formfrom 65. How to converta formfrom 65. How to converta formfrom 4.5 to 6.0? - to upgrade forms,the formcan be directlycompiledinthe nextrelease. - Formcan be compiledbyusingifcmp60.exe - FLINT60 bath executable canbe usedtocheckwhetherthe formiscompatible toAppsor not. 66. How to call a form fromanotherform? - to invoke anotherformwithinaformthe functionsecurityroutinesshouldbe usedwhichare available inFND_DUNCTION package. * forthispurpose,CALL_FORMbuilt-incannotbe usedsince the Oracle Applicationslibrariesdonot supportit. *FND_FUNCTION.EXECUTE shouldbe usedto opena new sessionof aform(CALL_FORM/ OPEN_FORM do notbe used) *APP_NAVIGATE.EXECUTEprocedure alsocanbe usedto opena formwhere aninstance of the same formis reused,thathasalreadybeenopened. *APP_NAVIOGATE.EXECUTEissimilartoFND_FUNCTION.EXECUTE,exceptthatisallow aform to be restartedif itis invokedasecondtime. *FND_FUNCITON.EXECUTEalwaysstartsa new instance of a form. Syntax  procedure APP_NAVIGATE.EXECUTE (Function_name invarchar2,

open_flaginvarchar2 default‘y’ sesson_flaginvarchar2default‘SESSION’ other_paramsinvarchar2 defaultnull, activate_flaginvarchar2 default‘ACTIVATE’ pinnedinboolean defaultFALSE); syntax Procedure FND_FUNCTION.EXECUTE (functionname invarchar2, openflagin varhcar2 default‘y’ session_flaginvarhcar2default’session’ other_paramsinvarchar2 defaultnull, activate invarchar2 default‘Active’ browser_targetinvarchar2 defaultnull); 67. What isthe reasonfornot gettinganydata whena multi orgview isquired? - to getthe data correctly,the xxx-ALLmustbe referencedandthe ORG_ID value shouldbe specifiedtoextractportioneddata. - Multiorgviewsare partitionedbyusingORG_ID. - Soaccess throughmultiorgviewswill notreturnanyroes,as the CLIENT_INFOValue isnotset - Use HR_OPERATING UNITSto identifythe organization_idof the OU on whichqueryisbased. - Use FND_CLIENT_INFOpackage to setthe value inCLIENT INPOusingset_org_contest. - Execute fnd_client_info.Set_org_context(‘<org_id>’); - Nowquryingof multiorgviewscanbe done. 68. How doyou findthatmuliorgisinstalled? - multi organizationarchitecture ismeanttoallow muliple companiesorsubsidiariestostore their recordswithina single database. - Multiple organization Architecture allowsthisbypartitioningdatathroughviewsinAPPSschema. - Implementationof Multi orggenerallyincludesmore thanone businessgroup.

* To knowwhethermultiorgisexistingornot selectmulti_org_flag formfnd_product_groups) - if the resultis‘Y’ meansthe database isgroup formultiorg 69. whatare the triggersthat fire onitem? Pre_Text_Item when_New_Item_Instance post_text_Item post_Change When_validate_Item key_Next_Item execute fnd_client_info.set_org_contest (‘Org_Id’) execute dbms_application_info.set_client_info(‘Org_Id’) 70. Transactional triggersinforms - Transactional triggersare the triggersthatare relatedtoaccessinga data soruce. - These triggersfire foreachrecordthat is markedfor inset,updataor table whenformswhould typicallyinsert.Update of delete statements. - Internallyformswouldbe callingitsinternal insert_record,update_RecordandDelete_Record built_insasappropriate toperformthe defaultprocessing. * ImportantatnTransactiontriggersare 1. ON_LOCK 2. ON_UPDATE 3. ON_INSERT 4. ON_DELETE 71. whichtriggerswill fire whencensormovesfromone blocktoanotherblock?

TriggerFiringOrder Level 1. Post_Test_Item Item 2. Post_Record Block 3. Post_Block Block 4.When_Create_Record Block 5. Pre_ Block Block 6. Pre_Record Block 7. Pre_Text.Item Block 8.When_New_Block_Instance Block 9.When_New_Recrd_Instance Block 10.When_new_Item_Instance Form 72. What isthe difference betweenPRE_COMMITandPOST_COMMIT triggers? * ‘POST_FORMS_COMMIT triggersisthe new name for the POST_COMMIT triggers. *Whena formis beingcommittedthe followingtriggersare fired (i) PRE_COMMIT (ii) ON_COMMIT (iii) POST_COMMIT

Pre – CommitTrigger Thistriggerfiresonce duringthe Postand Committransactionprocess.Before formbuilderprocesses any (changes) recordstochange. Specificallyitfiresafterformbuilderdeterminesthat there are inserts,updatesordeletesinthe formto postor commit,butbefore itcommitsthe changes. Thistriggerdoesn’tfire whenthere isanattempttocommit,but validationdeterminesthatthere are no changedrecordsin the form. Thisis a formlevel trigger. Enter querymode shouldbe setas‘No’ Thiscan be usedto performanaction,such as settingupspecial lockingrequirements,atanytime a database commitisgoingto occur. If this triggerfails,the postandcommitprocessesfail,norecordsare writtentothe database and focus remainsinthe currentitem. If a DML isperformedina pre-committriggeranditfails,tenmanual rollbackmustbe performed, because formbuilderdoesn’tperformanautomaticroll back. Thistriggerfiresinpost and committransactions.

Post– CommitTrigger Thisis alsoknownas post-committrigger. Post-committriggerfiresonce duringthe postandcommittransactions. If there are recordsin the formthat have beenmarkedasinserts,updatesordeletes,the post-forms- committriggerfiresafterthese changeshave beenwrittentothe database butbefore formbuilder issuesthe database committofinalize the transaction. If the operationorapplicationinitiatesacommitwhenthere are norecords inthe form have been markedas inserts,updatesordeletes,formbuilderfirespost-forms-committriggerimmediately, withoutpostingchangestothe database. Thisis a formlevel trigger. Enter querymode shouldbe setto‘No’ Post-forms-committriggershouldbe used toperformanaction,suchas updatingan audittrial any time a database commitis aboutto occur. If this triggerfails,postandcommitprocessingabortsandformbuilderissuesaROLLBACKand decrementsthe internalsave pointcounter. ThistriggerfiresinPostand Committransactions. What isRecode function? *Decode functiondecodesanexpressioninawaysimilartothe IF_THEN_ELSE logicusedinvarious languages. Decode functiondecodesexpressionaftercomparingittoeachsearch condition. If the expressionisthe same assearch,resultisreturned. If the defaultvalue iscommitted,anull value isreturnedwhere asearchvalue doesnotmatchany of the resultvalues. DECODE functionfacilitatesconditional inquiriesbydoingthe workof a CASE or IF_THEN_ELSE statement. DECODE (column,expression,search1,result1,search2,result2,…..); Ex:- Selectlast_name,job,alary, DECODE ( ‘IT_PROG’,1.10*SALARY,

‘ST_CLERK’,1.15*SALARY, ‘ST_REP’,1.20*SALARY, SALARY) REVISED_SALARYfromemployees; How to call a Reportin twoapplications? *SRW Package is a collectionof PL/SQLconstructsthat containmanyfunctions,proceduresand exceptionsthatcanbe referencedinReports. 1 SRW.Break 2. SRW.Context_Failure 3 SRW.Do_SQL 4. SRW.Do_SQL_Failure 5 SRW.Get_Page_Num 6 SRW.Message 7 SRW.Program_Abort 8 SRW.Refrence 9 SRW.Run_Report 10 SRW.Run_report_Failure 11 SRW.Set_Altr 12 SRW.Integer_Error 13 SRW.Set_Field_char 14 SRW.Set_Field_Num 15 SRW.Set_Maxrow 16 SRW.Trace_Add_Option 17 SRW.Trace_End 18 SRW.Trace_Start 19 SRW.User_Exit 20 SRW.User_Exit_Failure SRW.Run_Report SRW.Run_Report(command_line,char); ExecutesspecifiedR25 RUN Command SRW.RUN_REPORT_FAILURE; Stopsreportexecutionwhenfailure of SRW.Run_Reportoccurs. By usingSRW.Run_Report,anotherreportcanbe calledtothe screanfrom a buttonwithinone report. If this isusedfroma Report Trigger,BATCH=YES mustbe passed. * DESTYPE can onlybe FILE, PRINTERor MAIL.

Ex:- FunctionF1 returnBooleanis Begin SRW.RUN_REPORT(‘Report=Rep_A P_Param1=20’); -- callsReportRep-A anddisplaystoscreen -- Passesa parameter20 to the param_1 Exception WhenSRW.RUN_REPORT_FAILURE Then SRW. Message (100, ‘Error CallingReport’); Raise SRW.Program_Abort; ReturnTrue; End; * SRW.DO_SQL(sql statementchar); ExecutesspecifiedSQLstatement * SRW.DO_SQL_FAILURE; StopsreportexecutionuponSRW.Do_SQLfailure. * SRW.Message (msg_numbernumber,msg_textchar); Displaysaspecified message andmessage number * SRW.Program_Abort; Stopsexecutionof reportwhenraised. * SRW.Set_Altr Appliesattributesettings,suchasfont,colorto layout objects. Thisprocedure appliesformattingattributestothe currentframe,repeatingframe,fieldorboilerplate object. * SRW.Set_Altr(object_idnumber,altrSRW.Generic_Altr); object_idis alwayszero. Altris SRW.Altr(thatis,the attributestochange)

* SRW.set_Field The proceduresinthispackage are veryuseful informattriggers. Theyare usedto change data that will be displayedionaparticularitembasedona specificcondition. SRW.Set_Field_char(Object_id,textchar); SRW.Set_Field_Date (Object_id,date date); SRW.Set_Field_Num(Object_id,numbernumber); Can a Reportcontainmore than one template? Templatesdefine commoncharacteristicsandobjectsthatcan be appliedtomultiple reports. For example template canbe definedthatinclude the companylogoandsetsfontcolorsforselected areas of a report. Whena report iscreatedthroughthe ReportWizard,there is an optionof applyingatemplate (.tdf file) to the report. Whena template ischosen,objectsinthe marginareaof a template are importedintothe same locationsinthe currentreportsection,overwritinganyobjectsif exists. The characteristicsof the objectsinthe bodyarea of the template are appliedtoobjectsinthe body area of the template are appliedtoobjectsinthe bodyareaof the current reportsection. Anytemplate properties,parameters,reporttriggers,programme unitsandattachedlibrariesare also applied. Different(Multiple) templatescanbe appliedtoeachsectionof the report. If anothertemplate isappliedlatertoa reportthe existingtemplateobjectswill be deletedinthe currentreport section. How to adda template tothe predefinedtemplateslist? i) In a texteditoropenthe PreferencesFile ii) scroll downtothe template descriptionsidentifiedbyReports.Xxx –Template_Reso(whoxxx specifiedaReportstyle) (Tabular,BreakAbove) iii) ForeachReportstyle forwhichthe template isdefined - to the Reports.xxx–Template_Disclist,addthe descriptionthatshouldbe appearedonthe template page of the reportwizard.

- To the correspondingReports.XXX_template_file list,addthe file name of the templateinthe same positionasthe additionthatismade to the descriptionlist. iv) copythe templae file (filename.tdf) to ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US Preferencesfile:- Windows ORACLE_HOMECAUPREFS.ORA (userpreferences) ORACLE_HOMECAGPREFS.ORA (Global Preferences) Unix  HOME_DIRECOTRY/Prefs.ora(Userpreferences) $ORACLE_HOME/tools/admin/prefs.ora( Global Preferences) 78. How to passa parameterina requestsetforthree concurrentprogramswhichare havingsame parameter? i) For the first reportinthe Reportset,click onthe parametersbuttonandenderthe parametersthat are to the sharedby all reportsinthe Request(Report) set. ii) Go to the nestreportand clickthe parametersbuttonandlistthe same sharedparameters. iii) Dothe above stepfor eachand everyreportinthe requestset iv)’Modify’checkbox canbe usedtoallow the usersto change the valuesof the parametersinthe ‘lower’reportsatsubmissiontime.. v) ‘Display) checkbox canbe usedto allow the userstosee the parametervaluesatsubmissiontime. * Requestsetwizardcanbe usedto quicklycreate a new Requestsetinwhichall of the requestrun sequentiallyorall of the requestruninparallel. *sequentially Oneafteranother * parallel All atonce. - the actioncan be setwhethertocontinue processingorabortprocessing.If arequestends withthe statues‘Error’. 79. What are Global variablesinReports? *Global variablesare the variablesthatca n be assignedtoparametersinreportsandthose parameters can be usedinreports create_parameter_list(------)

add_parameter(----:Global_var); run_product(….); 80. whatare Handlers? * Handlerisa group of packagedprocedureswhichisusedbyOracle Applicationstoorganize .PL/SQL code in forms. - Handlersprovide awayto centralize the code sothatit becomeseasiertodevelop,maintainand debug. - The packagedproceduresavailableinahandlerare calledformthe triggersbypassingthe name of the triggeras an argumentforthe procedure to process. * Handlersare types :- 1) ItemHandlers 2) EventHandlers 3) Table Handlers 4) BusinessRules - Handlersreside inprogramunitsinthe formor instoredpackedin the database. AddingTable handlerLogic Codinglogicforwindowandalternative regioncontrol. Addingfin-windowsand/orROW-LOV’Sandenable query-find. Codinglogicforitemrelationssuchasdependentfields. Codingmessagestouse message dictionary. AddingFFlogicif required. Addingchoicestothe special mence andlogictomodifychoicesthe defaultmenuandtool barbehavior isnecessary. Codinganyotherlogic. Creatinga formfunctionforthe developedformandregisteringanysubfunctions. Testingthe formby itself. Registeringthe formwithAOL.

Addingthe formfunctiontoa menuor creatingcustommence. Assigningthe menutothe responsibilityandassigningthe responsibilitytothe user. Testingthe formwithinOracle Applications. Registeringof Application,formanda concurrentprogramthroughApplicationdeveloperResponsibility Application:- Responsibility ApplicationDeveloper <Application/Register> Form:- <Application/Form> <Application/Function> Menu:- <Application/ Menu> Messages:- <Application/Messages> Table:- <Database /Table> Sequence:- <Database / View> ConcurrentProgramme:- <Concurrent/ Executable> <Concurrent/ Program> ApplicationDeveloper(Responsibility)

*Flexfield +Key +Descriptive -Test *Concurrent -Program -Executable -Library *Application -Register -Form -Function -Menu -Messages +Database +Lookups +Validation *Profile *Attachments - DocumentEntities

- DocumentCategories - AttachmentFunctions *Other *Requests - Run -Set -Profile -Concurrent -Change Organization -RunningJobs +Key +Descriptive -Register -Register -Segments -Segments -Aliases -Values -CrossValidation

-Values +Lookups -Groups -ApplicationObjectLibrary -Accounts -Common +Database +Validation -Table -Set -View -Values -Sequence Lexical referencescannotbe made inPl/SQLstatements. Bindreferencescanbe done ina PL/SQL statements. Lexical parameterscanbe referencedbyenteringanampusand( ) followedimmediatelybythe columnname or parameter. Before creatingthe query,acolumnor parameterinthe data model shouldbe createdforeachlexical reference inthe query. For lexical parameters,initial value mustbe definedsothatreportbuilderusesthisvaluetovalidate the querywitha lexical reference. Token If Oracle reportsare executedbyaconcurrentprogram, (forOracle ReportsProgram),thena keyword or a parameterwiththe same name asin the reportbuilder,shouldbe definedwhichforeach parameter,whichisknownastaken. Thisis usedtopass the parameterstothe reportsfromthe application(SRSWindow) RequestSet

Requestsetisthe groupof requests,thatcan be submittedregularlyusingasingle transaction. Incompatibility These are the listof programsthat can be definedasincompatible withapertainprogram. If any program isdefinedasincompatible toaparticularprogram, thenthat program shouldnotrun simultaneouslywiththe concurrentprogram, because theymightinterfere withitsexecution. ApplicationDeveloperResponsibility VariousScreens DifferentExecutableMethods 1 Host 2 Immediate 3 JavaStored Procedure 4 JavaConcurrentProgramme 5 Multi Language Function 6 Oracle Reports 7 PL/SQLstoredProcedure 8 Requestsetstage function 9 Spawned 10 SQL*Loader 11 SQL*Plus <Concurrent/Library> ConcurrentLibrary Librarytypes TransactionLibrary <Lookups>

User AccessLevels Extensible System <Validation/Set> List of values List types Long Listof Values Poplist No security Securitytype Hireaxhial Security Non-hireaxhial Security Char Format type Date Date time Number Standarddate Standarddate time Time Validationtypes  Respondent Independent None Pair Special Table Translatable Independent Translatable Dependent

<Attachments/AttachmentFunctions> function type form report 85. What isa Data Group? - A data group isa groupof oracle applicationsand the Oracle ID’sof each application - Oracle ID grantsaccess privilegestotablesinanOracle Database - Data groupdetermineswhichOracle Database accountsa responsibilitiesforms,concurrent programsand reportsconnectto. 86. What isa Responsibility? - ResponsibilitydefinesApplicationsPrivileges - A responsibilityisalevel of authorityinOracle Applicationsthatletsusersonlythose Oracle Applicationsfunctionsanddataappropriate totheirrolesinan organization. - Each userhas at listone or more responsibilitiesandseveral userscanshare the same responsibility * Each responsibilityallowsaccessto - a specificapplicationora setof applications. - A set of books - A restrictedlistof windowsthatanusercan navigate - Reportsina specificapplication. 87. What are securityAttributes? - SecurityAttributesare usedbyOracle self service webApplicationstoallow rowsof data to be visible tospecifiedusersresponsibilitiesbasedonthe specificdatacontainedinthe row. 88. What isa Profile Option? - profile optionsare the setof changeable optionsthataffectshow the applicationlooksand behaves.

- By settingprofileoptions,the applicationscanbe made to react indifferentwaysfordifferent usersdependingonthe specificuserattributes. 89. What are stepsinvolvedindevelopingaflex field? - designingthe table structure - creatingfieldsonthe form(Visible/Hidden) - callingappropriate routines - registrationof the flex field. - Definitionof the flex field. <Flex fields/key/Register> <Flex fields/Descriptions/Register> 90. What isan application/Module? - Applicationisacollectionof forms,functionandmenus 91. What are Alerts? - Alertisa mechanismthatchecksthe database fora specificexceptioncondition. - Analertis characterizedbythe SQL selectstatementsit contains. - A SQL selectstatementfellsthe applicationwhatdatabase exceptiontoidentifyaswell aswhat outputto produce forthat exception. 92. whatare composite Datatypes? * Composite Datatypesare of two types 1. PL/SQL Records 2. PL/SQL Collections  Index ByTable  NestedTable  VARRAY * Composite datatypesare alsoknownascollections - theyare RECORD,TABLE,NESTEDTABLE andVARRAY

RECOD data type:- - A RECORD is a group of relateddataitemsstoredas fields eachwithitsownname anddata type. - PL/SQLRecordsare similartostructuresin3GL’s - A RECORD is not the same as Row ina database table - RECORD treatsa collectionof fieldsasa logical unit. - These are (RECORDtype) convenientforfetchingarow of data froma table for processing - RECORDSalso can be declared. Syntax  TYPE type_name isRECORD (fileddeclaration,…..); identifiertype_name; Ex:- TYPE emp_record_type isRECORD last_name varchar2(50), job_idvarchar2(10), salarynumber*8,2)); emp_recordemp_record_type; - fieldsdeclaredasNOTNULL must be initialized. INDEXBY Table data types:- * Thisdata type containstwocomponents. 1. Primarykeyof data type BINARY_INTEGER 2. columnof scalar or recorddata type. * Objectsof the TABLE type are calledINDEXBY Tables - theyare modeledas(butnotthe same as ) data base tables. - INDEXBY Table are a primarykeyto provide the userwitharray-like access torows. - INDEXBY table issimilartoan ARRAY.

- It can be increasedinsize dynamicallybecausetheyare unconstrained. * there are two stepsinvolvedincreatingaINDEXBY table. 1. Declare a TABLE data type. 2. Declare a variable of that type. - the size of the INDEX BY Table isun constrainedincrease dynamicallysothatINDEXBY Table an increase dynamically,sothatINDEXBY Table growsas new rowsare added. - INDEXBY Tablescan have one columnand a unique identifiertothatone columnneitherof which can be named. - The columncan belongtoany scalar or recorddata type,but the primarykeymustbe lingto type BINARY_INTEGER - INDEXBY Tablescannot be initializedatthe time of itsdeclarationandalsoitcannot be populated at the time of declaration. - Anexploitexecutable statementisrequiredtoinitialize (populate)the INDEXBYTABLE. INDEXBY TABLE STRUCTURE Unique identifier Column ……. 1 2 …… BINARY_INTEGER SCALOU Syntax  TYPE ename_table_type IS TABLE OF Employees.last_name%TYPE INDEXBY BINARY_INTEGER; -thiscan be reterenedby … Gopi Raj ….

INDEXBytable_name (primary_key_value); - The Followingmethodsare used with INDEXBY Tables. 1. EXISTS 2. OUNT 3. FIRST ANDLAST 4. PRIOR 5. NEXT 6. TRIM 7. DELETE INDEXBY Table of Records:_ - At a givenpointof time.INDEXBY Table can store onlythe detailsof anyone of the columnsof a database table - To store al the columnsretriedbya query, INDEXBY Table of Recordsare used. - Because onlythe table definitionisneededtoholdinformationaboutall of the fieldsof adata base table,the table of recordsgreatlyincreasesthe functionalityif INDEXBYTable. Syntax  TYPE dept_table_typeISTABLEOF Departments%ROWTYPE INDEXBY BINARY_INTEGER; Dept_table dept_table_type; *% ROW TYPE attribute canbe usedto declare a recordthat representsarow ina database table.

*The difference betweenthe %ROWTYPEattribute andthe composite datatype RECORDis that RECORD allowstospecifythe datatypesof fieldsinthe recordor to declare new fieldswithnew data types. NestedTables * NestedTable isan orderedgroupof itemsof type TABLE. NestedTable containmultiplecolumnsandcanbe usedas variables,parameters,results,attributesand columns. Theycan be thought of as one columndatabase tables. Rowsof a nestedtable are notstoredinany particularorder. The size of a nestedtable canbe increaseddynamicallyi.e.nestedtablesare unbounded. Elementsinatable initiallyhave consecutivesubscripts,butaselementsare deleted,theycanhave non- consecutive subscripts. The range of valuesfornestedtable subscriptsis1..2147483647. To extendanestedtable,the built-inprocedure EXTENDmustbe used. To delete elements,the built-inprocedure DELETEmust be used. An uninitializednestedtable isautomaticallynull,sothe ISNULL comparisonoperatorcanbe usedto sesif nestedtable isnull. The operatorsCAST,THE and MULTISET are usedor manipulatingnestedtables. 1. Creationof a NestedTable Defininganobjecttype. SQL> Create type ELEMENTS AS OBJECT

(ELEM_ID Number(6), PRICE Number(7,2)); / 2. Create a table type ELEMENTS_TAB whichstoresELEMENTS objects. SQL> Create TYPE ELEMENTS_TAB ASTABLE OFELEMENTS / 3. Create a data base table STORAGEhavingtype ELEMENTS_TAB as one of its columns. SQL> Create Table STORAGE (Salemannumber(4), Elem_idnumber(6), OrderedDate, ItemsElements_Tab) NESTED TABLE ITEMS STORE AS ITEMS_TAB; VARRAYS:- VARRAYSare orderedgroupof itemsof type VARRAY. VARRAYScan be usedto associate a single identifierwith anentire collection. Thisallowsmanipulationof the collectionasawhole andeasyreference of individual elements. The maximumsize of VARRAYneedstobe specifiedinitstype definition. The range of valuesforthe index of a VARRAYisfrom 1 to the maximumspecifiedinitstype definition. If no elementsare inthe (table) ARRAY,thenthe ARRAYisautomaticallynull.

The main use of VARRAYisto group small of uniform-sizedcollectionof objects. Elementsof aVARRAYcannot be accessedindividuallySQL,althoughtheycanbe accessedinPL/SQL, OCI,or Pro*C usingthe array style subscript. The type of the elementof aVARRAYcan be any PL/SQLtype exceptthe following. BOOLEAN,TABLE, VARRAYetc. VARRAYScan be usedto retrieve anentire collectionas a value. VARRAYdata isstoredin-line,inthe table space asthe otherdata in itsrow. Whena VARRAYis declared,aconstructorwiththe same name as the VARRAYisimplicitlydefined. The constructor createsa VARRAYfromthe elementspassedtoit. A VARRAYcan be assignedtoanotherVARRAY,providedthe datatypesare the exactsame type. TYPE my_VARRAY1IS VARRAY(10) OF MY_Type; Is NULL comparisonoperatorcan be usedto see if a VARRAYisnull. VAARAYScannotbe comparedforequalityorinequality. Creatinga VARRAY:- 1. Definingobjecttype ELEMENTS SQL> Create TYPE MEDICINESAS OBJECT (MED_ID NUMBER (6), MED_NAME Varchar2 (14), MANF_DATE DATE); / 2. Define aVARRAYtype MEDICINE_ARR whichstoresMEDICINES.objects SQL> Create TYPE MEDICINE_ARRAS VARRAY(40) OF MEDICIES; /

3. Creatinga relational table MED_STOREwhichhas MEDICINE_ARR as a columntype SQL> Create table MED_STORE( Locationvarchar2 (15), Store_Size number(7), Employeesnumber(6), Med_ItemsMedicine_Arr); DifferencesbetweennestedtablesandVarrays *NestedTablesare unbounded,where asVarrayshave amaximumsize. *Individual elementscanbe deletedfromanestedtable,butnotfroma Varray. Therefore nestedtablescanbe spares,where asVarraysalwaysare dense. Varraysare storedby Oracle in-line(inthe same table space),where asnestedtabledataisout-of-line ina store table,whichisa systemgenerateddatabase table associatedwiththe nestedtable. Whenstoredinthe data base,nestedtablesdonotretaintheirorderingandsubscripts,where as Varraysdo. NestedtablessupportindexeswhileVARRAYSdonot. DifferencesbetweenconversionsandInterfaces:- Conversion Interface 1. Conversionisone-time process 1. Interface isthe post production whichisperformedonce before process. productiongoon live. (Pre-productionprocess) 2. Data comesinto Oracle Applications 2. Interface isthe integrationof

only(One wayprocess) twosystems. 3. Interative Process 3. Scheduledandrepetiveprocess. Oracle Reports – Trouble Shooting 1. ConcurrentRequestLogs:- The firststepof reportsdebuggingshouldbe toexamine the logof concurrent request. 2. Runningfromthe operatingsystem:- If the problemisnotresolvedwithlog,thenthe reportshouldbe runthroughfromthe operatingsystem. Alongwiththe standardreportarguments,the reportshouldbe runalongwiththe argumentspassed by the concurrentmanager. If it is runsuccessfully thenthe problemiswiththe environmentfromwiththe concurrentmanagerwas started. 3. Using r25run inplace of ar25run:- For thisdebuggingstep,AOLprovidesareport$FND_TOP/SRW/FNDNOEXT.rdf (Unix path name) whichhasno userexits. If this step alsofails,thenthe problemcouldbe withOracle ApplicationsInstallation. 4. Runningthe PrintEnvironmentVariable ValuesReport:- The concurrent managerinherits itsenvironmentvariablesfromthe shellfromwhichitwas startedand thenrunsreport usingthisenvironment. Thisenvironmentcouldbe differentfromthatauser seesloggingintothe Applicationsbecause the concurrentmanagermay have beenstartedbya differentuserwithdifferentenvironmentsettings. Due to thisdifference,itissometimesdifficulttodetermine the cause of errorinrunningreports.

To examine the valuesof few variables,printsenvironmentvariablevalues,Reporttoprintoutthe variable asseenbythe concurrentmanagerto see if is correct. Verycommonand oftenproblemssuchasa problemincompilationorthe concurrentmanagers inabilitytolocate alibraryhappendue toincorrectREPORTS 60_PATH. 5. Emulate ConcurrentManager Environment:- For UNIXplat forms,to assistindeterminingwherethe problemlies, Oracle ApplicationsAOLshipsa program called$ FND_TOP/ Srw / ar60run.oc. Thisprogram helpstoemulate the concurrentmanagerenvironmentwhentestingreportsfromthe OS commandline. Thisprogram writesall the environmentvariablesandargumentspassedtoitina logfile ar60run.log. (Locatedby defaultinthe $FND_TOP/ $APPLLOGdirectory). Save the ar60run.oc source code to a file namedar60run.oc compile itandrename the executable as ar60run.(new). Save $FND_TOP / bin/ ar60run into some otherfile andplace the new ar60run into$FND_top / bin. Compilingandrelinkinghasbeenincorporatedintofnd.mkwhichwill,bydefaultbuildanexecutable $FND_TOP / bin/ar60rund, whichcan be renamedtoar60run (new). Submitthe reportfromconcurrentmanagerand lookat ar60run.log. Thenrun the reportfrom OS withthe same argumentsasshownby ar60run.logshowsusingold ar60run. BitmappedReports Printerdriversshouldbe providedwithprintstyle(Landscape) todetermine how toprinttextfiles. Bitmappedreportsare nottextfilesandthese are outputaspost scriptfiles. The post scriptfile isassetof instructionstellingthe printerexactlyalandscape report,the postscript file mustbe generatedaslandscape.

Frequentlyaskedquestions inReports 1. Why doesmy reportonlyfail fromthe concurrentmanager? Thisis because the environmentfromwhichthe concurrentmanagerlaunchesareportisdifferentfrom the one whenrunningthe reportfromOS commandline. 2. Why doesmy reportshow differentdata? If the reportshowsdifferentdatawhenitisrun as a stand alone report,sometimesthe datainthe outputmay be differentfordifferentsituations. Thisis usuallydue todifferent/noprofile optionsorothervaluesbeingpassedtothe reportbythe concurrentmanager. Checkthe callsto SRWINITand SRWEXIT, itthose are founddisabled,theyshouldbe re-enables,before the reportis run throughconcurrentmanager. 3. Why do I getthe errorREP_0713 whenI run my report? Oracle Reports usesa textfile calleduiprint.txttoholdprinternames. If the currentprintername isnot inthisfile,thenthe errorREP_0713 error. Why doI getmanypagesof nonsense whenIprintmyReport? Postscript code shouldbe recognizedbythe printerdriver. ‘ enscript’ programcannot be usedfor printing. 5. What doesthe ‘ SEP-0065 ’;virtual memorysystemerror? * Thiserror couldbe due to the followingreasons. * By defaultOracle Reportsuses/tmpdirectorytowrite temporaryfiles,  whichmaybe gettingfull. These filescouldbe directedtoanotherdirectoryusingthe environmentvariableTMPDIR. * Are the failingreportsusingpage N or M? This can consume a lotof Oracle Reportsvirtual memory. If possible,Reportsshouldbe runagainsta smallerdatabase.

FND_PROGRAMPackage FND_PROGRAM.Executable:- Procedure FND_PROGRAM.Executable IS (executable inVarchar2, applicationinvarchar2,(full name) short_name invarchar2, (executableshortname) descriptioninvarchar2defaultNULL, execution_methodinvarchar2, execution_file_name invarchar2defaultnull, Subrowline_name invarchar2defaultnull,(onlyforspawnedimmediate) Icon_name invarchar2 defaultnull, Language_code invarchar2 default‘US’, Execution_file_pathinvarchar2defaultnull); For Java ConcurrentProgram. FND.PROGRAM. REGISTER:- Procedure FND_PROGRAM.RegisterIS (Programinvarchar2, applicationinvarchar2, enabledinvarchar2, short_name invarchar2, descriptioninvarchar2,defaultnull, executable_short_name invarchar2,

executable_applicationinvarchar2, execution_optionsinvarchar2,defaultnull, priorityinnumberdefaultnull, save_outputinvarchar2 default‘Y’, printin varchar2 dafault‘Y’, colsin varchar2 defaultnull, rowsin varchar2, defaultnull, style invarchar2, defaultnull, style_requiredinvarchar2,default‘N’, printerinvarchar2, defaultnull, Requets_Type invarchar2,defaultnull, Request_type_Applicationinvarchar2defaultnull, Use_in_Srsinvarchar2, default ‘N’, Allow_disabled_valuerinvarchar2default‘N’, Run_alone invarchar2 default‘N’, Output_type invarchar2 default‘TEXT’, Enable_trace invarchar2 default‘N’, Restartin varchar2 default‘Y’, nls_complaintinvarchar2default‘Y’, icon_name invarchar2 defaultnull, language_code invarchar2,default‘US’, mls_function_short_name invarchar2defaultnull, mls_function_applicationinvarchar2defaultnull, incrementorinvarchar2defaultnull);

PropertyClasses A propertyclassisa namedobjectthat containsa listof propertiesandtheirsettings. Once a propertyclassiscreated,itcan be assignedtoanyobject. An objectbasedona propertyclasscan inheritthe settingsof anypropertyinthatpropertyclass. There can be numberof propertiesin apropertyclass,andthe propertiesinaclass can applyto differentobjects. Whenan objectisbasedon a propertyclass,all the propertieswhichare inheritedfromthe property classcan be controlledlocallyalso. Propertyclassare separate objects andcan be copiedbetweenmodulesif required. A propertyclasscan be sub classedinonlynumberof modules. Visual Attributes Visual attributesare the font,colorandpatternpropertiesthatcanbe setfor formand menumodules whichare appearedinapplication’sinterface. FontProperties:fontname,fontsize,fontstyle,fontwidth,fontheight. Colorand patternproperties: Foregroundcolor,Backgroundcolor,fill pattern,charmode, logical attribute,White onBlack. Everyinterface objecthas itsvisual attribute grouppropertythatdetermineshow the objectsindividual visual attribute settingsare derived. The visual propertygrouppropertycan be setto default,NULL,or the name of a namedvisual attribute definedinthe same module. An object’snamedvisual attribute settingcanbe changedprogrammaticallytochange the font,color and patternof the objectat runtime.

Oracle plsql and d2 k interview questions

More Related Content

What's hot

Similar to Oracle plsql and d2 k interview questions

Recently uploaded

Oracle plsql and d2 k interview questions