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

Recommended

More Related Content

What's hot (20)

Similar to Oracle plsql and d2 k interview questions (20)

Recently uploaded (20)

Oracle plsql and d2 k interview questions