Downloaded 15 times
![SET AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS] - As with the explain plan command,to obtain an execution plan the PLAN-TABLE mustbe created in the user’s schema prior to Auto Tracing. SQL> Set Auto trace trace only explain SQL> Select * from dual; - To enable viewing of STATISTICS data, the auto tracing user musthave access to dynamic performance tables. - To achieve this, grantPLUS TRACE role to the user. PLUS TRACE role is created by the plus trace.Sql script $ ORACLE_HOME / sql plus admin SYS user mustrun this script. DBA can them grantthe role to the users who wish to use the AUTOTRACE. TK PROF - Tk prof facility accepts as inputa SQL Trace File and produces a formatted output file. - Tk Prof Filename_source filename_outputEXPLAIN = [user name / password]sys = [yes/no] TABLE = [Table Name] A) How do you add trace to a report? - By usingthe package SRW.TRACE_ADD_OPTION B) How do you execute a specified DDL in a report? - BY using the package PW.DO_SQL C) How do you generate message in reports? - By using the packages PW.MESSAGE (Reg Num, ); D) Explain BLOBS of CLOBS? LOB A LOB is a data type that is used to store large,unstructured data such as text, graphic images,video, clippings etc. * Four large objectdata types. BLOB : Represents a binarylarge object CLOB : Represents a character large object NCLOB: Represents a multibyte character object. BFILE: Represents a binaryfile store of in an os binary file outside the data base. LOB’S Internal LOBS (CLOB, BLOB, NCLOB) external Files (BFILE) - Depending on the storage aspects and their interpretation by Oracle server. * LONG_TO_LOB API is used to migrate LONG columns to LOB columns. LOB’S LOB Locator - A table can have multiple LOB columns - The maximum size of a LOB can be 4 GB - LOB’S return the locator - LOB’S store a locator in the table end data in a differentsegmentunless the data is less than 4000 bytes. LOB LOB value (real data) LOB locator (pointer to the location of the LOB value) - A LOB column doesn’tcontain the data and it contains the located of the LOB value. - When a table is created with LOB column,the default storage is ENABLE STORAGE IN ROW. - If DISABLE storage in Row option is used the LOB value is not stored in the ROW even if the size is less then 4000 bytes. Internal LOB’S - Stored inside the Oracle server. - BLOB, NCLOB, CLOB. BFILE - BFILE’S are external LOB’S.](https://image.slidesharecdn.com/oracleplsqlandd2kinterviewquestion1-160701114214/75/Oracle-plsql-and-d2-k-interview-question1-12-2048.jpg)
Uploaded byArunkumar Gurav
Oracle plsql and d2 k interview question1
Oracle PLSQL and D2K Interview Questions document contains information about various PLSQL concepts like NOCOPY, report types, anchors, user exits, triggers, parameters, cursors, exceptions, dynamic SQL, autonomous transactions, bulk binding and materialized views. The key topics covered are NOCOPY for improving performance with OUT and INOUT parameters, the different types of reports, using anchors to define object positioning, user exits for complex data manipulation, different types of triggers and their firing sequence, parameters and bind variables, implicit and explicit cursors, handling exceptions, writing dynamic SQL, autonomous transactions for independent processes, bulk binding for improved performance, and materialized views as replicas of master data.
Oracle plsql and d2 k interview question1
- 1. Oracle PLSQL andD2K Interview Questions 1. What is NOCOPY? By defaultthe IN parameter is passed byreference and the OUT and IN OUT parameters are passed byvalue. NOCOPY : is a compiler hintthat can be used with OUT and IN OUT parameter to requestto pass by reference.This improves the performance with OUT and INOUT parameters. 2. REPORT TYPES A) Tabular Report B) Group Left Report C) Group Above Report D) Form like report E) Matrix Report F) Multi Media Report G) Mailing Label Report H) OLE Report. 3. ANCHOR Anchors are used to determine the vertical and horizontal positioning ofa child objectrelative to its parentobject. The end of the anchor should be attached to the parentobject. A) Parent Object B) Child Object Since the size of the some layout objects maychange when the reportruns (When the data is actually fetched), anchors need to be defined to make the appearance ofthe object An Anchor defines the relative position ofan object to the objectto which if this anchored. * Symbol is By pressing the shiftkey Anchor can be moved. Anchor Properties : A) Child Edge percent on child edge type B) Child Object Name C) Collapse Horizontally D) Collapse vertically E) Comments F) Name * Two child objects can be related to one parentobjectby an Anchor. 4. User Exits : An user exit is :- Program that can be written and linked into the report builder executable or user exit DLL files. User exits are build when ever the control need to be passed from the reportbuilder to a program,which performs some function and then control returns to the ReportBuilder. Types of user exits :- A) Oracle Pre-Complier user exits. B) OCI (Oracle Call Interface user exits) C) Non – Oracle user exits. User exits can perform the following tasks. Perform complexdata manipulation. Pass data to report builder from OS text files. SupportPL/SQL blocks. Control real time devices be printer or robot. 5. Types of Triggers in Reports :- * ReportTriggers * Data Triggers * Layout Triggers Report Triggers :- After Parameter form Trigger. After ReportTrigger Before parameter form Trigger Before ReportTrigger. Between Pages Trigger
- 2. + Firing Sequence *Before Parameter form Trigger - Fires before the runtime parameter form is displayed. - The parameter values can be accessed and changed. * After Parameter form Trigger. - fires after the runtime parameter form is displayed. - The parameters can be accessed and their values can be checked. * Before Report Trigger - Fires before the reportis executed but after the queries are passed and data is fetched. * Between Pages Trigger. - Fires between each page of the report is formatted,except the very firstpages. - This is used for customized page formatting. * After Report Trigger - Fires after exiting from the run time premier or after report output is sentto a specified destination.(File,Printer, Mai lid etc….) - This is used to clean up any initial processing thatwas done such as deleting the tables. - This Trigger always fires irrespective of success or failure ofthe report. DATA TRIGGERS: * Ref Cursor Query. - This uses PL/SQL to fetch data for the report. - In this a PL/SQL function need to be specified to return a cursor value from a cursor variable. * Group Filter: This is PL/SQL function that determines which records to be included in a group in the property is PL/SQL. - The function mustreturn a BOOLEAN value. True …….. Includes the current record in the report. False ……. Excludes the current record from the report. * Formula - These are Pl/SQL functions that populate formula or place holder columns. * Validation Trigger - These are also PL/SQL functions that are executed when parameter values are specified on the command line and when the runtime parameter form is accepted. - Are also used to validate the initial value property of the parameter. * Layout Triggers * FormatTrigger. - These are PL/SQL functions executed before the objectis formatted. - Used to dynamicallychange the formatting attributes of the object. * Action Trigger - These are Pl/SQL procedures executed when a button is selected in the run time previener. - This can be used to dynamically call another report or execute any other PL/SQL. 6 Formula Column It performs a user-defined computation on another columns data,including Place- holder columns. Formulas are PL/SQL functions that populate formula or place holder columns. Cannotbe used to populate parameter values. 7 Summary Column - Performs a computation on another columns data like sum,average,count,minimum,maximum,%,total. - For group reports,the reportwizard and data wizard create ‘n’ summaryfields in the data model for each summary column thatis defined. --- One at each group level above the column being summarized. - One at the report level. 8 Place Holder Column - A Place holder column is a column for which, the data type and value can be setdynamically (Programmatically) * The value can be set for a place holder column in the following places.
- 3. - Before reporttrigger if the place holder is a report level column. - Reportlevel formula column,ifthe place holder is a report level column. - A formula in the place holders group below it(The value is setonce for each record of the group) 9 Repeating Frame - Repeating frame surrounds all ofthe fields that are created for a groups columns. - Repeating frame prints once for each record of the group. - For frames and repeating frames,the property elasticity defines whether the size of the frame or repeating frame should with the objects inside ofit at runtime. 10 Frame - Surrounds the objects and protect them from being over written or pushed by other objects. 11 System Parameters in Reports * Background * Copies * Currency *Decimal * Desformat * Desname * Destype * Mode * Orientation * Print Job * Thousands. 12 Data Link - Data links relate the results ofmultiple queries. - A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance ofits parent group. 13 In which tables FF are stored? A) FND – ID – FLEXS B) FND-ID-FLEX-STRUCTURES 14 Advantages of stored functions and procedures * Applications can be modularized. * Easy maintenance. - Rowtines can be modified online withoutinterfering other users. - One routine can be modified to effect multiple applications. * Improved data security and integrity. - Indirect access to database objects can be controlled from non privileged users with securityprivileges. * Improved performance. - Reparsing for multiple users can be avoided by exploiting the shared SQL area. - PL/SQL parsing atrun-time can be avoided by pursing atcompile time. - Number of calls to the database can be reduced and network traffic decreased bybundling commands. * Improved code clarity. - The clarity of code increases by using appropriate identifier names to describe the action of the routines which reduces the need for comments. 15 Difference between a function and a procedure Functions Procedures * Invoke as a part of an expression. Execute as a PL/SQL statement. * Must contain a RETURN clause in the header. Do not contain a RETURN
- 4. Clause in theheader. * Must return a single value. Can return none,one or many values. Can contain a RETURN Statement. Can contain IN, Out and IN OUT parameters. IN OUT Parameters. 16 About Cursors - Oracle server uses some private work areas to execute SQL statements and to store processing information. * By using PL/SQL cursors these private SQL areas can be named and the stored information can be accessed. Two Types: * ImplicitCursors. - Implicitcursors are declared by PL/SQL implicitlyfor all DML and PL/SQL selectstatements,including queries that return only one row. - Oracle Server implicitlyopens a cursor to process each SQL statementnotassociated with on explicitly declared cursor. - The mostrecentimplicitcursor can be returned as the SQL cursor. * Explicit Cursors - For queries thatreturn more than one row, explicit cursors are declared and named bythe programmes and manipulated through specific statements in the block’s executable actions. - Explicit cursors are used to individuallyprocess each row returned by a multiple-row SELECTstatement. - The set of rows returned by a multiple – row query is called as active set. Declare Open Fetch Empty? Close Cursor Attributes:- Attribute Type Description % is open Boolean Evaluates to TRUE if the cursor is open. % not found Boolean Evaluates to TRUE if the mostrecentfetch doesn’treturn a row. % found Boolean Evaluate to TRUE if the mostrecentfetch returns a row. Complementof% not found. % Row Count Number Evaluates the total number of rows returned so far. Parameterized Cursors:- - Parameters can be passed to the cursor in a cursor for loop. - It allow to open and close an explicit cursor several times in a block, returning a differentactive set on each occasion for each execution, the previous cursor is closed and reopened with a new setof parameters. - Sizes should notbe mentioned for the data types of parameters the parameters names are for references in the query expression ofthe cursor. 17 Confined Mode:- - If it is on, child objects cannotbe moved outside their enclosing parentobjects. - If it is off child objects can be moved out sides their enclosing parentobjects. Flex Mode:- - If it is on, parent borders stretch when child objects are moved againstthem. - If it is off, parentborders remain fixed when child objects are moved againstthem.
- 5. 18 Parameters - Aparameter is a variable whose value can be setat runtime (from the run time parameter of the command line). - User parameters are created by the user and system parameters are created by ReportBuilder. - System parameters cannotbe renamed or deleted. Bind Parameters (Variables) - Bind references (or Variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number or date. - Bind references maybe used to replace expressions in SELECT,WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses ofqueries. - Bind references cannotbe referenced in FROM clauses. - Bind variables can be referenced by entering a colon (:) followed immediatelyby the column or parameter name. - If the parameter / column is notcreated before making a bind reference,report builder will create a parameter. Lexical Parameters (Variables) - Lexical references are place holders for text that is embedded in a SELECT statement. - Lexical Variables can replace the clauses appearing after SLECT,FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECTBY, and START WITH. What is % Row type - % Row types is used to declare a record based on a collection ofcolumns in a database table or view. - The fields in the record take their names and data types from the columns ofthe table or view. - The record can also store an entire row of data fetched from a cursor or cursor variable. - % Row type should be prefixed with the database table. Ex: Declare Emp_record employee 5% row type. Then emp_record will have a structure consisting ofall the fields each representing a column in the employees table. What is a Ref Cursor? - Oracle server uses unnamed memoryspaces to store data used in implicitcursors. - Ref cursors are used to define a cursor variable,which will pointto that memoryspace and can be used like pointers in SQL ‘S’. About Exceptions - An exception is an identifier in PL/SQL that is raised during the execution of a black that terminates its main bodyof actions. - A block always terminates when PL/SQL raises an exception so that an exception handler should be specified to perform final actions. * Exception can be raised in two ways exception is raised automatically. 1. Ex:- when no rows are retrieved from the database in a SELECT statement,then error ORA-01403 occurs and the exception NO-DATA-FOUNDis raised by PL/SQL. 2. Ex:- Exception can be raised explicitly by issuing the RAISE statementwith in the block. - The exception being raised maybe either. User-Defined or Pre Defined Trapping an exception:-
- 6. - If theexception is raised in executable section of the block, processing branches to the corresponding exception handler in the exception section of the block. - If PL/SQL successfullyhandles the exception,then the exception doesn’tpropagate to the enclosing block or calling environment. - The PL/SQL block terminates successfully. Propagating an exception:- - If the exception is raised in the executable section of the block and there is no corresponding excepti on handler,the PL/SQL block terminates with failure and the exception will be propagated to the calling environment. Types of exceptions:- A) Pre-Defined Oracle Server Exceptions. - ImplicitlyRaised. B) Non-Pre defined Oracle server exceptions. - ImplicitlyRaised. C) User-defined exceptions -ExplicitlyRaised. Pre-Defined Oracle Server Exceptions:- - These are the error(20) that occur mostoften in PL/SQL code. - These exceptions need not be declared and raised implicitly by Oracle Server, NO-DATA-FOUND, LOGIN_DENIED, ZERO_DIVIDE. Non-Pre-Defined Oracle Server Exceptions:- - These are the other standard Oracle Server errors. - These exceptions need to be declared ion the declarative section and raised by Oracle server implicitly. User Defined Exceptions:- - These are the conditions thatthe developer determines as abnormal. - These need to be declared and raised explicitly. PRAGMA EXCEPTION_INIT Statementis used to associate a declared exception with the standard Oracle Server error number. Syntax:-PRAGMA EXCEPTION_INIT (exception, error number) * SQLCODE, SQL ERRM are two functions which can be used to identify the associated error code or error message when exception occurs. - SQLCODE function returns the numeric value for the error code. - SQLERRM function returns the character data containing the message associated with the error number. - SQLCODE f SQLERRM cannotbe used directly in SQL statements. What is Dynamic SQL? - Dynamic SQL is a SQL statementthat contains variables thatcan change during runtime. - It is a SQL statementwith place holders and is stored as a character string. - Dynamic SQL enables DDL,DCL or session control statements to be written and executed (by) from PL/SQL. * Dynamic SQL can be written in two ways. A) DBMS_SQL. -8i B) Native Dynamic SQL. - 8i - BasicallyDynamic SQL means creating the SQL statements dynamicallyat runtime by using variables. Ex:- Dynamic SQL can be used to create a procedure that operates on a table whose name is notknown until runtime or to execute DDL/DCL/SCS statements. ---- In Pl/SQL such statements cannotbe executed statically. -- EXECUTE IMMEDIATE Statementcan perform dynamic single row queries. Declare D_str varchar2 (200); Val varchar2 (20); Begin D_str= insertinto table1 values (;val); Val= ‘ Bye’ Execute Immediate str using val;end;
- 7. What are AutonomousTransactions? - Autonomous transactions are the processes run independentlyof its parent. - By means ofAutonomous Transaction,the currenttransaction can be temporarilysuspended and another operation can be begun. - The basic idea behind this is to have some operation take place independentlyof the currenttransaction. Ex:- to allow error messages written to table to be committed butto rollback everything else thathas taken place prior to the error. - The autonomous or child transaction can commitor rollback as applicable with the execution of the parent transaction being resumed upon its completion. - The parent may then perform further operations ofany operations performed with in the child transaction. - By using Autonomous Transactions,modular and reusable components can be developed more easily. - In fact Oracle already uses similar functionalityinternally,known as recu transactions to handle the updating of system resources. Ex:- When one application selects ‘nextval’ from a non eached sequence,the value is in the database. - Thus a second application will always getthe incremented application has committed or rolled back. - Autonomous Transaction should be defined in PL/SQL in the following manner. PRAGMA AUTONOMOUS_TRANSACTION; - Autonomous transaction also can be nested. - The parent transaction remains active while any statements specified in the declare section ofthe autonomous unit are executed. - As the code unit exits and control returns to the parentthe main (parent) transaction is resumed and the transaction context is switched back to the parent. What is Bulk binding of Bilk collect? Bulkbind:- - The assignmentofvalues to PL/SQL variables in SQL statements is called binding. - The binding of an entire collection at once is refilled to as bulk binding. - Bulk bind improves performance byminimizing the number ofcontext switches between PL/SQL and SQL engines while they pass an entire collection ofelements (varay, nested tables,index-by table or host array) as bind variables back and forth. - Prior to Oracle 81, the execution of every SQL statements required a switch between the Pl/SQL and SQL engines, where as bulk binds use only one context switch. * Bulk binding includes the following A) Input collections;use the FORALL statement. B) Output collections,use the BULK COLLECT clause. Input Collections:- - Input collections are data passed from Pl/SQL engine to the SQL engine to execute INSERT, UPDATE and DELETE statements. Syntax:- FORALL index in lower_bound..upper_bound sql_statement; Output Collections:- - Output collections are the data passed from the SQL engine to the PL/SQL engine as a resultof SELECT or FETCH statements. - The keyword BULK COLLECT can be used with SLECT INTO, FETCH INTO and RETURNING INTO clauses. Syntax:- BULK COLLECT into collection_name,…… What are Materialized Views and Snapshots? Materialized View:- - A Materialized view is a replica of a target master from a single pointin time.
- 8. - In Oracle7, it is terned as SNAPSHOT - Oracle 7.1.6 -- Uptable Snapshots - Oracle 7.3 - Primary Key Snapshots - Oracle 8 - Materialized view - Oracle 9 - Multifier Materialized View. - Materialized views can be used both for creating summaries to be utilized in data warehouse environments. * Replicating data in distributed environments. Target Master -The table(s) which is (are) referenced by the MVIEW query. BestTable -The tables are that is (are) created by MVIEW create statementand that stores data that satisfythe MVIEW query. Syntax:- Create materialized view <name> Refresh fast Start with sysdate Next sysdate +1 as Select *from <master table>; - Since this is a fastrefreshed MVIEW the master table should have a log (Master log) to record the changes on itthat can be created by running. Create materialized view log on master_table; - this statementcreats the following objects - a table called MLOG$_Master_table - an internal trigger on Master_table that populates the log table. * Master Log tables (MLOG$) are used by fast refresh procedure. Refreshing Materialized Views:- - Initially a materialized view contains the same data as in the master table. - After the MVIEW is created, changes can be made to the master table and possiblyalso to the MVIEW. - To keep a MVIEW data relatively currentwith the data in the master table,the MVIEW mustbe periodically refreshed. * Refresh can be accomplished by one of the following procedures. Dbms_mview.refresh (<mview list>,<Refresh types>) Dbms _ refresh.refresh (<Refresh Groups>) Refresh Types -Complete Refresh,FastRefresh,Force Refresh * Complete Refresh is performed bydeleting the rows from the snapshotand inserting the rows satisfying the MVIEW query. * In Fast refresh only the rows updated since lastrefresh are pulled from the master table to insertinto MVIEW. * This requires a log table called as MVIEW Log to be created on the Master Table. * Force refresh firsttries to run a Fast refresh if possible. * If fast refresh is notpossible,itperforms complete refresh. Refresh Groups - These are used to refresh multiple snapshots in a transitionallyconsistentmanner. - When a refresh group is refreshed all MVIEWS in that group are populated with data from a consistentpointin time. - Refresh groups are managed byusing the procedures in the package DBMS – REFRESH. - DBMS-REFRESH, MAKE of DBMS-REFRESH.ADD are used to create a refresh group and add new snap shots to an existing group. Types of Materialized Views:- 1 Read-onlymaterialized views - DML cannotbe performed on the snapshots in this category. 2 Up datable materialized views - These MVIEWS eliminates the restriction ofDML’s on snapshots. 3 Sub query materialized views - These are the MVIEW’S that are created with sub queries in the WHERE clause ofa MVIEW query. 4 Row id Vs Primary Key materialized views - MVIEW’S that use Row id for refresh are called Row id MVIEW’s (Oracle 7). - MVIEW’S that use primarykey for refresh are called primarykey MVIE’S
- 9. (Oracle 8) . *Fast refresh requires some association (mapping) Between rows at snapshotand master tables. 5 Multifier materialized views (Oracle 9) - In this type MVIEW, its master table itselfis a MVIEW. - This feature enables fastrefresh ofMVIEW’S that have MVIEW’S as their masters. - Many companies are structured on at leastthree levels A) International B) National C) Local - Many nodes atboth the national and local levels are required - The bestpossible solution in such cases is to use multifier MVIEW’S. 6 Simple Vs ComplexMVIEW’S. - MVIEW’S being simple or complexdetermines whether itcan be fast refreshed or not. - A MVIEW is fastrefreshable ifit is simple. - A MVIEW is not fastrefreshable ifit is complex. - A MVIEW can be considered CONNECTBY, INTERSECT, MINUS or UNION or UNION ALL clauses in its detining query. * The following data types are not supported in MVIEW replication. A) LONG B) LONG RAW C) BFILE D) UROWID - MVIEW’S are typically used in data ware house or decision supportsystems. Snapshots - Snapshots are mirror or replace of tables in a single pointoftime. - A Snapshotis a local copy of a table data that originates from one or more remote master tables. - To keep a snapshots data currentwith the data of its master the Oracle server mustperiodicallyrefresh the snapshot. VIEWS - Views are built using the columns from one or more tables. - The single table view can be updated,but the view with multiple tables cannotbe updated. * A snapshotcontains a complete or partial copy of a target master table from a single pointin time. - A snapshotmaybe read only or up datable. 26. How duplicate rows are deleted? - Duplicate rows are deleted by using ROWID Syntax delete from <Table> Where ROWID not in (Select max (ROWID) from <Table> Group by <Column_name>); 27. How do you call function and procedure in PL/SQL as well as in SQL prompt? isql*plus EXECUTE < Function/Procedure name > ; (SQL prompt) PL/SQL < Procedure Name / Function Name>; (from another procedure) DevelopmentTools <Procedure name>; 28. Difference between IN and OUT parameters. Three types of parameters 1. IN 2. OUT 3.IN OUT IN parameter: - This parameter passes a value from the calling environmentinto the procedure. - This is the default mode - A formal parameter ofIn mode cannotbe assigned a value (we IN parameter cannotbe modified in the body of the procedure ) - IN parameters can be assigned a defaultvalue in the parameter list. - IN parameters are passed byreference.
- 10. OUT parameters: - OUTparameter mustbe assigned a value before returning to the calling environment. - OUT parameter passes a value from the procedure to the calling environment - OUT parameter can not be assigned a defaultvalue in the parameter list. IN OUT parameter: - this type of parameter pass a value from the calling environmentinto the procedure and a possiblydifferentvalue from the procedure back to calling environmentusing the same parameter. - IN OUT parameter cannotbe assigned a defaultvalue. * By defaultOUT & IN OUT parameters are passed byvalue. - These can be passed byreference by using NOCOPY. 29. Triggers: - A trigger is a PL/SQL block or a PL/SQL procedure associated with a table view schema or the database. - The code in the trigger executes implicitlywhenever a particular event occurs. Two types of triggers: Application trigger - fires whenever an event occurs with in a particular application Database Trigger - Fires whenever a data event (Such as DML ) or system event (such as log on or shutdown) occurs on a schema or database. - Executes implicitlywhen a data event. Such as DML on a table (insert,delete or Update),an INSTEAD OF trigger on a VIEW or DDL statements are issued no matter which user is connected or which application is used. - Also executes implicitlywhen some user or data base system actions occur. - Ex. When user logs on to the system. When DBA shuts down the data base. - Date base triggers can be defined on tables and on views. - If a DML operations as issued on a view, the INSTEAD OF trigger defines whataction takes place, if these actions include any DML operations on tables,then any triggers on the base tables are fired. - Data base triggers can be system triggers on a data base or a schema. - With a data base,triggers fire for each event for all users,with a schema,triggers fire for each event for the specific user. Recursive trigger : - This is a trigger that contains a DML operation changing the very same table. Cascading Trigger: - The action of one trigger cascades to another trigger,causing this second trigger to fire. - Oracle server allows up to 32 triggers to cascade at any one time. - This number can be changed by changing the value of the OPEN - CORSORS. Data bases initialization parameter. ( default value is 50 ). - * A triggering statementshould contain 1 Trigger Timing Before, After (For Table) Instead of (For View) - Determines when the trigger needs to be fired in relation to the triggering event. 2 Triggering Event Insert, Update,Delete - Determines which on the table or view causes the trigger to fire. 3 Trigger Type Statement,Row - Determines how manytimes the trigger body executes 4 Table name Table, View 5 Trigger body PL/SQL – block - Determines whatactions the trigger should perform. * INSTEAD of triggers are used to provide a transparentway to modifying views that cannotbe modified directlythrough SQL, DML statements because the view is not modifiable. - INSTEAD of triggers provide writing of Insert, Update and Delete statements againstthe view. - The INSTEAD if trigger works invisiblyin the background performing the action coded in the trigger body directly on the underlying tables. - INSTEAD of trigger execute the trigger body instead ofthe triggering statement. Statement Triggers - In this type of triggers,the trigger body executes once for the triggering event.
- 11. - This isthe default. - Statementtrigger fires once,even if no rows are affected at all. Row Trigger - In this type, the trigger body executes once for each row affected by the triggering event. - Row trigger is not executed if the triggering event affects no rows. * A view cannotbe modified by normal DML if the view query contains setoperators, group functions,group by, connect By, start with clauses or joins. Mutating Table - A Mutating table is a table that is currently being modified byan UPDATE, DELETE OR INSERT statement,or a table that mightneed to be updated by the effects of a declarative DELETE CASCADE referential integrity action. - A table is not considered mutating for statementtriggers. - A mutating table cannotbe changed because the resulting DML could change data that is in consistentstate. What is SQL Trace? - SQL Trace is the main method for collecting SQL execution information in Oracle collecting a wide range of information and statistics thatcan be used to tune SQL operations. - The SQL – Trace facility can be enabled / disabled for an individual session or atthe instance level. - If the initialization parameter SQL-TRACE is setto TRUE in the init.ora of an instance,then all sessions will be traced. - SQL-TRACE can be set at the instance level by using the initialization parameter SQL-TRACE. - SQL-TRACE can also be enabled / disabled atthe system/session level by using. Alter system/session setSQL-TRACE = TRUE/FALSE. Explain Plan - Explain plan command generates information thatdetails the execution plan that will be used on a particular query. - A uses a pre created table (PLAN_TABLE) in the current schema to store information aboutthe execution plan chosen by the optimizer. Creating the plan table - Plan table is created by using the script utl x plan,sql (Oracle Home / RDBMS / admin / uti x plan.sql) Unix $ ORACLE_HOME / rdbms /admin - This scriptcreates an outputtable, called PLAN-TABLE for holding the output of the explain command. Populating the PLAN TABLE - PLAN TABLE is populated using the explain plan. SQL> Explain Plan for select* from emp where emp no = 1000; - This command inserts the execution plan of the SQL statementinto the plan table. - A name tag can be added to explain information by using the set statement_id clause. Displaying the Execution Plan - Once the table has been populated,the explain info needs to be retrieved and formatted. - Number of scripts are available to formatthe plan table data. $ ORACLE_HOME / rdbms /admin / utlxpls. Sql – to formatserial explain plans. $ ORACLE_HOME/ rdbms/admin/utlxpil.Sql – to formatparallel explain plans. * AUTOTRACE - The AUTOTRACE facility in SQL* plus allows analysts to view the execution pan d some useful statistics for a SQL statementwithin a SQL*plus session. - AUTOTRACE needs to be initiated in the SQL*Plus session prior to executing the statement.
- 12. SET AUTOTRACE [OPTIONS][EXPLAIN] [STATISTICS] - As with the explain plan command,to obtain an execution plan the PLAN-TABLE mustbe created in the user’s schema prior to Auto Tracing. SQL> Set Auto trace trace only explain SQL> Select * from dual; - To enable viewing of STATISTICS data, the auto tracing user musthave access to dynamic performance tables. - To achieve this, grantPLUS TRACE role to the user. PLUS TRACE role is created by the plus trace.Sql script $ ORACLE_HOME / sql plus admin SYS user mustrun this script. DBA can them grantthe role to the users who wish to use the AUTOTRACE. TK PROF - Tk prof facility accepts as inputa SQL Trace File and produces a formatted output file. - Tk Prof Filename_source filename_outputEXPLAIN = [user name / password]sys = [yes/no] TABLE = [Table Name] A) How do you add trace to a report? - By usingthe package SRW.TRACE_ADD_OPTION B) How do you execute a specified DDL in a report? - BY using the package PW.DO_SQL C) How do you generate message in reports? - By using the packages PW.MESSAGE (Reg Num, ); D) Explain BLOBS of CLOBS? LOB A LOB is a data type that is used to store large,unstructured data such as text, graphic images,video, clippings etc. * Four large objectdata types. BLOB : Represents a binarylarge object CLOB : Represents a character large object NCLOB: Represents a multibyte character object. BFILE: Represents a binaryfile store of in an os binary file outside the data base. LOB’S Internal LOBS (CLOB, BLOB, NCLOB) external Files (BFILE) - Depending on the storage aspects and their interpretation by Oracle server. * LONG_TO_LOB API is used to migrate LONG columns to LOB columns. LOB’S LOB Locator - A table can have multiple LOB columns - The maximum size of a LOB can be 4 GB - LOB’S return the locator - LOB’S store a locator in the table end data in a differentsegmentunless the data is less than 4000 bytes. LOB LOB value (real data) LOB locator (pointer to the location of the LOB value) - A LOB column doesn’tcontain the data and it contains the located of the LOB value. - When a table is created with LOB column,the default storage is ENABLE STORAGE IN ROW. - If DISABLE storage in Row option is used the LOB value is not stored in the ROW even if the size is less then 4000 bytes. Internal LOB’S - Stored inside the Oracle server. - BLOB, NCLOB, CLOB. BFILE - BFILE’S are external LOB’S.
- 13. - These arestored in OS files out side the database table spaces. - The data type is BFILE. - BFILE data file stores a locator to the physical file. - BFILE can be GIF, JPEG, MPEG, text or other formats. *DBMS_LO.READ of DBMS_lob.WRITE are used to manipulate LOBS. Oracle Applications Architecture - Internet computing Architecture is a frame work for 3-tired,distributed computing thatsupports Oracle Applications products. - The Three tiers are 1 Data Base Tier 2 Application Tier 3 Desk Top Tier - Database tier manages Oracle 8i database. - Application tier manages Oracle Applications and other tools. - Desktop tier provides the user interface displace. - With internetcomputing architecture,only the presentation layer of Oracle Applications is on the Desk Top tier in the form of a plug-in to a standard internetbrowser. TEMPLATE FORM - The TEMPLATE form is the required starting pointfor all developmentofnew forms. - The developmentof a new form is started by copying the TEMPLATE.fmb file, located in $ AV_top / forms/us,to a local directory and renaming itas appropriate. TEMPLATE FORM CONTAINS THE FOLLOWING:- * platform-independentreferences to objectgroups in the APPSTAND form (STANDARD _PCAND_VA, STANDARD_TOOLBAR, STANDARD_CALENDAR) *platform – independentattachments ofseveral libraries FND SQF APPCORE APPDAYPK *several form level triggers with requited code *program units thatinclude a specification and a body for the package APP_CUSTOM, which contains defaultbehaviors for window opening and closing events. - In general this code should be modified for the specific form under development. * The application’s color pallet,containing the two colors required by the referenced visual attributes. * Many referenced objects thatsupportthe calendar,the toolbar, alternative regions and the menu. * Template form contains simple objects thatshow typical items and layout cosmetics. - These are deleted after developing the form Blocks : Block Name,Detail Block Window : Block Name Canvas _view :Block Name * Template form includes platform-independentattachments ofseveral libraries. - Some of the libraries are attached directly to the TEMPLATE (FNDSOF, APPCORE, APPDAYPK) while the others are attached to these libraries. APPCORE, APPDAYPK, FNDSOF, CUSTOM, GLOBE, VERT, JA, JE, JL APPCORE - Contains the packages and procedures thatare required ofall forms to supportthe Menu, Toolbar and other required standard behaviors. - Procedures and functions in APPCORE have names beginning with APP. APPDAYPK
- 14. - Contains thepackages thatcontrol the Oracle applications calendar feature. FINDSQF - Contains packages and procedures for message dictionary,florfields,profiles and concurrentprocessing. - It also has various other utilities for navigation,MRG, WHO etc. - Procedures and functions have names beginning with FND. CUSTOM - Custom libraryallows extension ofOracle Applications forms withoutmodification ofOracle applications code. - Custom librarycan be used for customizations such as ZOOM (Moving to another form from one form and querying up specific records),enforcing business rules. Ex:- Supplier name mustbe in upper case) and disabling fields thatare not required for a particular site. - All logic mustbranch based on the form and block for which it is run. - Oracle applications send events to the Custom library. - Custom code can take effect based on the events. What is sub query and correlated sub query? Sub Query :- - A Sub Query is a SELECT statementthat is embedded in a clause ofanother SQL statements called the parent statement. - Sub query (Inner Query) returns a value that is used by the outer query. - Scalar sub query is a sub query that returns exactly one column value from one row. Correlated Sub Query - Correlated sub query are used for row – by –row processing. - Each sub query is executed once for every row of the outer query. - A correlated sub queryis one way of reading (data) every row in a table and comparing values in each row against related data. - Oracle server performs correlated sub querywhen the sub query references a column from a table in the parent query. - The inner query is driven by the outer query in correlated sub queries. - A correlated sub queryis evaluated once for each row processed by the parentstatement. GET Candidate row from the outer query EXECUTE Inner query using the candidate row value USE Values from inner query to qualify / disqualifycandidate row Ex:- Selectlast_name,salary,department_id from employers OUTER where salary> (selectAVG (Salary) from employees where departmentid = outer.department_id); Types of Joins 1 Equi Join 2 Non-Equi Join
- 15. 3 Outer Join 4selfJoin Equi Join:- - Is also called simple or inner join. - An equi join is a join condition that contains equalitysign. Non Equi Join:- - It is a join condition that contains something other than the equality operator. Outer Join:- - Outer joins are used to also see rows thatdo not meetthe join condition. - Outer join operator is (+). - The missing rows can be returned if an outer join operator is used in the join condition. - The operator (+) is placed on the side of the join that is deficientin information. - This operator has the effect of creating one or more null rows to each one or more rows from the (join condition) non deficienttable can be joined. Self Join - Self join is used to join a table to itself. Ex:- to find the name ofeach employer’s manager. Which trigger will fire when cursor moves from one block to another block? WHEN_NEW_BLOCK_INSTANCE What are the triggers used in CUSTOM.Pll? CUSTOM.Pll contains (CUSTOM package) the following functions and procedures. CUSTOM.ZOOM_AVAILABLE FUNCTION CUSTOM.STYLEFUNCTION CUSTOM.EVENTPROCEDURE. * Triggers in Custom.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 SPECIAL 1-45 9 KEY_Fn (n is a number between 1 and 8) What is the difference between pre-query and post -query? * Pre-query executes only once for the statementwhere as post-queryexecutes for each 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
- 16. How to getsecond parameter value based on first parameter? $fle x $ value setname. What is Ref Cursor Ref cursor is a data type and executed at server side and with ref cursor multiple selectstatements can be executed. By increase the binary size, that number ofrecords that are committed can be increased by using control file. Forms can be development in APPS in two ways A) Customization by extension (using template.fond) B) Customization by modification (using custom.pll) What are the advantages of packages? Packages bundle related Pl/SQL types, items and sub-programs in to one container. A package should have its specification and body, stored separatelyin the database. Specification is the interface to the applications. A declares the types, variables,constants,exceptions,cursors and sub programmes available for use.The body fully defines the cursors and sub programmes and so implements the specification. Once written and compiled the contents can be shared by manyapplications. When a packaged PL/SQL construct is called for the firsttime the whole package is loaded in to memory,thus later calls to constructs in the same package require no disk I/O. Public package constructs can be referenced from any Oracle server environments. Private package constructs can be referenced only by other constructs which are partof the same package. Advantages 1 Modularity 2 Easier Application Design 3 Hiding Information by using public and private. 4 Added functionality. 5 Better performance. 6 Over loading. Procedures and functions can be over loaded i.e.creating multiple sub programmes with the same name in the same package each taking parameters ofdifferent number or data type. How to call WHO columns into the form By using FND_STANDARD API’S 1. FND_STANDARD.FORM_INFO Provides information aboutthe form. Should be called form when_new_form – instance – instance trigger. 2. FND_standard.set_who loads WHO columns with proper user information. Should be called from PRE_UPDTEand PRE_INSERT Triggers for each block with WHO fields If this is used FND-GLOBAL need not be called. (FND_GLOBAL.WHO) 3. FND_STANDARD.SYSTEM_DATE This is a function which returns date. Behave exactly like SYSDATE built-in. 4. FNID_STANDARD.USER This is a function which returns varchar2 Behaves exactly like builtin USER. APPCORE API’S APP_COMBO APP_DATE
- 17. APP_EXCEPTION APP_FIELD APP_FIND APP_ITEM APP_ITEM_PROPERTY APP_NAVIGATE APP_RECORD APP_REGION APP_STANDARD APP_WINDOW FNDSQF API’S FND_CURRENCY FND_DATE FND_GLOBAL FND_ORG FND_STANDARD FND_UTILITIES.OPEN_URL FND_UTILITIES. PARAM_EXISTS Howto call flex fields in the form? By using FND_FLEX.EVENT (EVENT varchar 2) How to register an executable and define a concurrentprogram through backend? By using concurrentprocessing API’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
- 18. .SUBMIT_SET * FND_PROGRAM.EXECUTABLE - isused to define a concument program executable - it takes 8 parameters ( all are IN mode ) syntax procedure FND_PROGRAM.EXECUTABLE (executable IN varchar2, (Full name) application IN varchar2, (executable shortname) shortname IN varchar2, description IN varchar2 default null execution_method IN varchar2, execution_file_name IN varchar2 defaultnull, (only fol spauned & Immedaite) subroutine_name IN varchar2 defaultnull, icon_name IN varchar2 default null, language_code IN varchar2 default(VS) (for Java Conc Program) execution_file_path IN varchar2 defaultnull, * FND_PROGRAM.REGISTER - this procedure no used to define a concumentprogram. - It has 30 IN paranmeters.Out of which 9 are mandatory,the remaining are 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_application IN varchar2, mls_function_shelt_name IN varchar2, mls_function_application IN varchar2, inerementor IN varhcar2); 56. How to register a table and columns through back end? * by using AD_DD package - for registering a table AD_DD.REGISTER_TABLE - for registering columns AD_DD.REGISTER_COLUMN. - AD_DD BPI doesn’tcheck for the existence of the registered table or column in the data base schema,butonly updates the required SQL tables. - It should be ensured that,all the tables and columns registered existactuallyand have the same formatas that defined using AD_DD API. - Views need not be registered. 57. How to write to a file through concurrentprogram. * By using FND_FILE package and it can be used only for log and output files. FND_FILE package contains procedures to write text to log and outputfiles. FND_FILE supports a maximum buffer line size of 32k for both and outputfiles . 1. FND_FILE.PUT - this is used to write text to a file with out a new line character - Multilane calls to FND_FILE.PUT will produce consummated text. Procedure FND_FILE.PUT (which IN Number, Buff IN varchar2); Which log output file - can be FND_FILE.LOG or FND_FILE.OUTPUT. 2. FND_FILE.PUT_LINE - this procedure as used to write a line of text to a file followed by a new line character. Procedure FND_FILE.PUT_LINE (which IN number, buff IN varchar2); EX:- FND_FILE.PUT_LINE( FND_FILE.LOG, find_message_get); 3. FND_FILE.NEW_LINE - this procedure is used to write line terminators to a file procedure FND_FILE.NEW_LINE (which IN number LINES IN NATURAL:=1); Ex:- to write two newline characters to a log file Fnd_file.new_line (fnd_file.log,2); 4. FND_FILE.PUT_NAMES
- 19. - this procedureas used to set the temporarylog file and outputfilenames and the temporarydirectory to the user specified values. - This should be called before calling my other FND_FILE procedure and only once per a session. Function FND_REQUEST.SUBMIT_REQUEST ( application in varchar2 defaultnull, program in varchar2 defaultnull, description in varchar2 defaultnull, start-time in varchar2 defaultnull, sub_request in bookan defaultFalse, argument1, arguemnt2, argument100) return number; * If this is submitted from oracle forms,all the arguments ( 1 to 100 ) mustbe specified. 59. How to display Request ID in the reports? * By using the parameter P_CONC_REQUEST_ID which needs to be defined always in the reports. 60. How to get username /user id in reports? - By using the API FND_PROFILE THE OPTION values can be manipulated in client/ server profile caches - FND_PROFILE.GET, FND_GLOBAL.USER_ID FND_GLOBAL.USER_NAME - this procedure is located in FNDSQF library. - This procedure is to located to get the currentvalue of the specifed user profile option or null if the profile does not exist. - The server_side PL/SQL package FND_GLOBAL returns the values which need to setwho columns for inserts and updates from stored procedures. - Procedure FND_PROFILE.GET ( name in varchar2, Value out varchar2); FND_PROFILE.GET (‘USER_ID’,user_id); * FND_PROFILE.VALUE - this function exactly works like GET, except it returns the values of the specified profile option as a function result Function FND_PROGILE.VALUE (name in varchar2 Return varchar2; *FND_PROFILE.PUT - this is used to put a value to the specified profile option - - if the option doesn’texist,it can also be created with PUT. Procedure FND_GET (‘USERNAME’, user name); 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 user profile options thatcan be accessed via user profile option routines . - the values of these profile options can be retrieved in forms,reports and program - these values cannot be changed except Gone_Print_Outputand Gone_Print_Style. - SA’s and end users also cannotsee the values or change these values. In which directory log and output files are stored? * APPLCSF is the top level directory in which the concurrentmanager puts logs and outputfiles. * APPLLOG and APPLOUT are the subdirectories in which the concurrentmanager puts log and outputfiles. *APPLCSF variable need to be setin the prod.env ( environmental variable ), so that all product log files $ APPLCSF/ $ APPLLOG log files $APPLCSF/ $ APPLOUT out files * concurrentmanager log and out put files should go to $ FND_TOP/ $ APPLOG and $ FND _TOP / $APPLOUT if $ APPLCSF is not set.
- 20. 62. How tosubmitconcurrentprograms through OS? - From the operating system the utility .CONCSUB is used to submitis concurrentpropgram. - This is basicallyused to test a concurrentprogram . - By using the WAIT token. The utility checks the requeststatus every 60 seconds and returns the OS promptupon completion ofthe request. - Concurrentmanager doesnotabort,shutdown or startup until the concurrentrequestcompletes. * If the concurrentprogram is compatible with itself,it can be checked for data integrity and dead locks by submitting it many times so thatit runs concurrentlywith it self. *PL/SQL procedures can submita requestto run a program as a concurrentprocess by calling. FND_REQUEST. SUBMIT_REQUEST. * Before submitting a request,the following functions also s hould be called optionally. FND_REQUEST.SET_OPTIONS FND_REQUEST.SET_REPEAT_OPTIONS FND_REQUEST.SET_PRINT_OPTIONS FND_REQUEST.SET_MODE 63. How to checks the requeststates? - A PL/SQL procedure can check the status of a concurrentrequestby calling. FND_CONCURENT.GET_REQUEST_STATUS FND_CONCURRENT.WAIT_FOR_REQUEST - FND_CONCURRENT.GET_REQUEST_STATUS - This function returns the status of a concurrentrequest - If the requestis alreadycomputed,it also returns the completion message. - This function returns both user friendly (translatable) phase and status values as well as developer phase and status vales that can drive program logic. Syntax Function FND_CONCURRENT.GET_REQUEST_STATUS ( request_id in outnumber, application in varchar2 default null, program in varchar2 defaultnull, phase outvarchar2, status outvarchar, dev_phase outvarchar2, dev_status outvarchar2, message outvarchar2) return BOOLEAN; - when application and program are specified,the request_id ofthe lastrequestfor the specified program should be returned to request_id. - Phase,and status values should be taken from 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 - This function waits for requestcompletion,then returns the requestphase/status and completion message to the caller. - Goes to sleep between checks for requestcompletion. Syntax FND_CONCURRENT.WAIT_FOR_REQUEST ( request_id in number defaultnull, interval in number default60, max_waitin numbe default0, phase outvarchar2, status outvarchar2, dev_phase outvarchar2, dev_status outvarchar2, message outvarchar2) return BOOLEN; * FND_CONCURRENT.SET_COMPLETION_STATUS - this function should be called from a concurrent program to setits completion states. - This function returns TRUE on success,other wise FALSE. Syntax function FND_CONCURRENT.SET_COMPLETION_STATUS ( status in varchar2, message in varchar2) return BOOLEAN;
- 21. normal status warning messageanymessage Error 64. How to generate fmx at OS level? - Forms can be generated on the forms server as the APPLMGR user. - Generating the form on the Form server, $ FORM60_PATH Should be set and the current directory should be $AV_TOP/forms?us. Syntax $F60 gen userid=apps/appsp module=<form_name>.Fmb Output_file=<schema_top>/forms?<language>/<form_name>.fmx Module_type=form bath =no compile_all=special 65. How to convert a form from 65. How to convert a form from 65. How to convert a form from 4.5 to 6.0? - to upgrade forms,the form can be directly compiled in the next release. - Form can be compiled byusing ifcmp 60.exe - FLINT 60 bath executable can be used to check whether the form is compatible to Apps or not. 66. How to call a form from another form? - to invoke another form with in a form the function security routines should be used which are available in FND_DUNCTION package. * for this purpose,CALL_FORM built-in can not be used since the Oracle Applications libraries do notsupportit. *FND_FUNCTION.EXECUTE should be used to open a new session ofa form (CALL_FORM/ OPEN_FORM do not be used) *APP_NAVIGATE.EXECUTE procedure also can be used to open a form where an instance of the same form is reused,that has alreadybeen opened. *APP_NAVIOGATE.EXECUTE is similar to FND_FUNCTION.EXECUTE, except that is allow a form to be restarted if it is invoked a second time. *FND_FUNCITON.EXECUTE always starts a new instance ofa form. Syntax procedure APP_NAVIGATE.EXECUTE (Function_name in varchar2, open_flag in varchar2 default‘y’ sesson_flag in varchar2 default ‘SESSION’ other_params in varchar2 default null, activate_flag in varchar2 default ‘ACTIVATE’ pinned in boolean defaultFALSE); syntax Procedure FND_FUNCTION.EXECUTE (function name in varchar2, open flag in varhcar2 default‘y’ session_flag in varhcar2 default’session’ other_params in varchar2 default null, activate in varchar2 default ‘Active’ browser_targetin varchar2 default null); 67. What is the reason for not getting any data when a multi org view is quired? - to get the data correctly, the xxx-ALL mustbe referenced and the ORG_ID value should be specified to extract portioned data. - Multiorg views are partitioned by using ORG_ID. - So access through multiorg views will notreturn any roes,as the CLIENT_INFO Value is not set - Use HR_OPERATING UNITS to identify the organization _id of the OU on which query is based. - Use FND_CLIENT_INFO package to setthe value in CLIENT INPO using set_org_contest. - Execute fnd_client_info.Set_org_context(‘<org_id>’); - Now qurying of multiorg views can be done. 68. How do you find that muliorg is installed? - multi organization architecture is meantto allow muliple companies or subsidiaries to store their records with in a single data base. - Multiple organization Architecture allows this by partitioning data through views in APPS schema. - Implementation ofMulti org generally includes more than one business group. * To know whether multiorg is existing or not selectmulti_org_flag form fnd_product_groups) - if the resultis ‘Y’ means the database is group for multiorg 69. what are the triggers that fire on item? 1. Pre_Text_Item
- 22. 2. when_New_Item_Instance 3. post_text_Item 4.post_Change 5. When_validate_Item 6. key_Next_Item execute fnd_client_info.set_org_contest(‘Org_Id’) execute dbms_application_info.set_client_info (‘Org_Id’) 70. Transactional triggers in forms - Transactional triggers are the triggers that are related to accessing a data soruce. - These triggers fire for each record that is marked for inset,updata or table when forms whould typically insert. Update of delete statements. - Internally forms would be calling its internal insert_record,update_Record and Delete_Record built_ins as appropriate to perform the default processing . * Importantatn Transaction triggers are 1. ON_LOCK 2. ON_UPDATE 3. ON_INSERT 4. ON_DELETE 71. which triggers will fire when censor moves from one block to another block? Trigger Firing Order 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 is the difference between PRE_COMMIT and POST_COMMIT triggers? * ‘POST_FORMS_COMMIT triggers is the new name for the POST_COMMIT triggers. *When a form is being committed the following triggers are fired (i) PRE_COMMIT (ii) ON_COMMIT (iii) POST_COMMIT Pre – Commit Trigger This trigger fires once during the Postand Committransaction process.Before form builder processes any(changes) records to change.
- 23. Specificallyit fires afterform builder determines thatthere are inserts,updates or deletes in the form to postor commit,butbefore it commits the changes. This trigger doesn’tfire when there is an attemptto commit,butvalidation determines thatthere are no changed records in the form. This is a form level trigger. Enter query mode should be setas ‘No’ This can be used to perform an action,such as setting up special locking requirements,atany time a database commitis going to occur. If this trigger fails,the postand commitprocesses fail,no records are written to the database and focus remains in the current item. If a DML is performed in a pre-committrigger and it fails,ten manual rollback mustbe performed,because form builder doesn’tperform an automatic roll back. This trigger fires in postand committransactions. Post – Commit Trigger This is also known as post-committrigger. Post-committrigger fires once during the postand committransactions. If there are records in the form that have been marked as inserts,updates or deletes,the post-forms-committrigger fires after these changes have been written to the database butbefore form builder issues the database committo finalize the transaction. If the operation or application initiates a commitwhen there are no records in the form have been marked as inserts, updates or deletes,form builder fires post-forms-committrigger immediately,withoutposting changes to the database. This is a form level trigger. Enter query mode should be setto ‘No’ Post-forms-committrigger should be used to perform an action, such as updating an audittrial any time a data base commitis aboutto occur. If this trigger fails,postand commitprocessing aborts and form builder issues a ROLLBACK and decrements the internal save point counter. This trigger fires in Postand Committransactions. What is Recode function? *Decode function decodes an expression in a way similar to the IF_THEN_ELSE logic used in various languages. Decode function decodes expression after comparing itto each search condition. If the expression is the same as search,resultis returned. If the defaultvalue is committed,a null value is returned where a search value does notmatch any of the result values. DECODE function facilitates conditional inquiries bydoing the work of 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_SALARY from employees; How to call a Report in two applications? *SRW Package is a collection of PL/SQL constructs that contain manyfunctions,procedures and exceptions thatcan be referenced in Reports. 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
- 24. SRW.Run_Report SRW.Run_Report(command_line,char); Executes specified R25RUN Command SRW.RUN_REPORT_FAILURE; Stops report execution when failure of SRW.Run_Reportoccurs. By using SRW.Run_Report, another reportcan be called to the screan from a button with in one report. If this is used from a ReportTrigger, BATCH=YES mustbe passed. * DESTYPE can only be FILE, PRINTER or MAIL. Ex:- Function F1 return Boolean is Begin SRW.RUN_REPORT(‘Report=Rep_A P_Param1=20’); -- calls ReportRep-A and displays to screen -- Passes a parameter 20 to the param_1 Exception When SRW.RUN_REPORT_FAILURE Then SRW. Message (100,‘Error Calling Report’); Raise SRW.Program_Abort; Return True; End; * SRW.DO_SQL (sql statementchar); Executes specified SQL statement * SRW.DO_SQL_FAILURE; Stops report execution upon SRW.Do_SQL failure. * SRW.Message (msg_number number,msg_textchar); Displays a specified message and message number * SRW.Program_Abort; Stops execution of report when raised. * SRW.Set_Altr Applies attribute settings,such as font, color to lay out objects. This procedure applies formatting attributes to the currentframe, repeating frame,field or boiler plate object. * SRW.Set_Altr (object_id number,altr SRW.Generic_Altr); object_id is always zero. Altr is SRW.Altr (that is, the attributes to change) * SRW.set_Field The procedures in this package are very useful in formattriggers. They are used to change data that will be displayed ion a particular item based on a specific condition. SRW.Set_Field_char (Object_id,text char); SRW.Set_Field_Date (Object_id,date date); SRW.Set_Field_ Num (Object_id,number number); Can a Report contain more than one template? Templates define common characteristics and objects thatcan be applied to multiple reports. For example template can be defined that include the companylogo and sets fontcolors for selected areas ofa report. When a reportis created through the ReportWizard, there is an option of applying a template (.tdf file) to the report. When a template is chosen,objects in the margin area ofa template are imported into the same locations in the current reportsection,over writing any objects ifexists. The characteristics ofthe objects in the body area of the template are applied to objects in the body area of the template are applied to objects in the body area of the currentreport section. Any template properties,parameters,reporttriggers,programme units and attached libraries are also applied. Different (Multiple) templates can be applied to each section of the report. If another template is applied later to a reportthe existing template objects will be deleted in the current report section. How to add a template to the predefined templates list? i) In a text editor open the Preferences File ii) scroll down to the template descriptions identified byReports.Xxx – Template_Reso (who xxx specified a Report style) (Tabular,Break Above) iii) For each Reportstyle for which the template is defined
- 25. - to theReports.xxx – Template_Disc list,add the description thatshould be appeared on the template page ofthe report wizard. - To the corresponding Reports.XXX_template_file list,add the file name of the template in the same position as the addition that is made to the description list. iv) copy the templae file (file name.tdf) to ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US Preferences file:- Windows ORACLE_HOMECAUPREFS.ORA (user preferences) ORACLE_HOMECAGPREFS.ORA (Global Preferences) Unix HOME_DIRECOTRY/Prefs.ora (User preferences) $ORACLE_HOME/tools/admin/prefs.ora( Global Preferences) 78. How to pass a parameter in a requestsetfor three concurrentprograms which are having same parameter? i) For the first report in the Reportset, click on the parameters button and ender the parameters thatare to the shared by all reports in the Request(Report) set. ii) Go to the nestreport and click the parameters button and listthe same shared parameters. iii) Do the above step for each and every report in the requestset iv)’Modify’ check box can be used to allow the users to change the values of the parameters in the ‘lower ’reports at submission time.. v) ‘Display) check box can be used to allow the users to see the parameter values atsubmission time. * Requestsetwizard can be used to quickly create a new Requestsetin which all of the requestrun sequentiallyor all of the requestrun in parallel. *sequentiallyOne after another * parallel All at once. - the action can be setwhether to continue processing or abortprocessing.Ifa requestends with the statues ‘Error’. 79. What are Global variables in Reports? *Global variables are the variables that ca n be assigned to parameters in reports and those parameters can be used in reports create_parameter_list(------) add_parameter (----:Global_var); run_product(….); 80. what are Handlers? * Handler is a group of packaged procedures which is used byOracle Applications to organize . PL/SQL code in forms. - Handlers provide a way to centralize the code so that it becomes easier to develop,maintain and debug. - The packaged procedures available in a handler are called form the triggers by passing the name ofthe trigger as an argumentfor the procedure to process. * Handlers are types :- 1) Item Handlers 2) Event Handlers 3) Table Handlers 4) Business Rules - Handlers reside in program units in the form or in stored packed in the database. Adding Table handler Logic Coding logic for window and alternative region control. Adding fin-windows and/or ROW-LOV’S and enable query-find. Coding logic for item relations such as dependentfields. Coding messages to use message dictionary. Adding FF logic if required. Adding choices to the special mence and logic to modify choices the defaultmenu and tool bar behavior is necessary. Coding any other logic. Creating a form function for the developed form and registering anysub functions. Testing the form by it self. Registering the form with AOL. Adding the form function to a menu or creating custom mence. Assigning the menu to the responsibilityand assigning the responsibilityto the user. Testing the form within Oracle Applications. Registering ofApplication,form and a concurrentprogram through Application developer Responsibility Application:- ResponsibilityApplication Developer
- 26. <Application / Register> Form:- <Application / Form> <Application / Function> Menu:- <Application / Menu> Messages:- <Application / Messages> Table:- <Database /Table> Sequence:- <Database /View> ConcurrentProgramme:- <Concurrent/ Executable> <Concurrent/ Program> Application Developer (Responsibility) *Flexfield +Key +Descriptive -Test *Concurrent -Program -Executable -Library *Application -Register -Form -Function -Menu -Messages +Database +Lookups +Validation *Profile *Attachments - DocumentEntities - DocumentCategories - Attachment Functions *Other *Requests - Run -Set -Profile -Concurrent -Change Organization -Running Jobs
- 27. +Key +Descriptive -Register -Register -Segments -Segments -Aliases-Values -Cross Validation -Values +Lookups -Groups -Application Object Library -Accounts -Common +Database +Validation -Table -Set -View -Values -Sequence Lexical references cannotbe made in Pl/SQL statements. Bind references can be done in a PL/SQL statements. Lexical parameters can be referenced by entering an ampusand ( ) followed immediatelyby the column name or parameter. Before creating the query, a column or parameter in the data model should be created for each lexical reference in the query. For lexical parameters,initial value mustbe defined so thatreport builder uses this value to validate the query with a lexical reference. Token If Oracle reports are executed by a concurrentprogram,(for Oracle Reports Program),then a keyword or a parameter with the same name as in the reportbuilder,should be defined which for each param eter,which is known as taken. This is used to pass the parameters to the reports from the application (SRS Window) RequestSet Requestsetis the group of requests,thatcan be submitted regularlyusing a single transaction. Incompatibility These are the listof programs thatcan be defined as incompatible with a pertain program. If any program is defined as incompatible to a particular program,then that program should notrun simultaneously with the concurrentprogram,because theymightinterfere with its execution. Application Developer Responsibility Various Screens Different Executable Methods
- 28. 1 Host 2 Immediate 3Java Stored Procedure 4 Java ConcurrentProgramme 5 Multi Language Function 6 Oracle Reports 7 PL/SQL stored Procedure 8 Requestsetstage function 9 Spawned 10 SQL*Loader 11 SQL*Plus <Concurrent/Library> ConcurrentLibrary Library types Transaction Library <Lookups> User Access Levels Extensible System <Validation/Set> Listof values Listtypes Long Listof Values Poplist No security Security type Hireaxhial Security Non-hireaxhial Security Char Formattype Date Date time Number Standard date Standard date time Time Validation types Respondent Independent None Pair Special Table Translatable Independent Translatable Dependent <Attachments / Attachment Functions> function type form report 85. What is a Data Group? - A data group is a group of oracle applications and the Oracle ID’s of each application - Oracle ID grants access privileges to tables in an Oracle Database - Data group determines which Oracle Data base accounts a responsibilities forms,concurrentprograms and reports connectto. 86. What is a Responsibility? - Responsibilitydefines Applications Privileges - A responsibilityis a level of authority in Oracle Applications thatlets users onlythose Oracle Applications functions and data appropriate to their roles in an organization. - Each user has at listone or more responsibilities and several users can share the same responsibility * Each responsibilityallows access to - a specific application or a set of applications. - A setof books - A restricted listof windows thatan user can navigate - Reports in a specific application. 87. What are security Attributes?
- 29. - Security Attributesare used by Oracle selfservice web Applications to allow rows of data to be visible to specified users responsibilities based on the specific data contained in the row. 88. What is a Profile Option? - profile options are the setof changeable options thataffects how the application looks and behaves. - By setting profile options,the applications can be made to react in different ways for different users depending on the specific user attributes. 89. What are steps involved in developing a flex field? - designing the table structure - creating fields on the form (Visible/Hidden) - calling appropriate routines - registration ofthe flex field. - Definition of the flex field. <Flex fields / key/ Register> <Flex fields/Descriptions /Register> 90. What is an application /Module? - Application is a collection of forms,function and menus 91. What are Alerts? - Alert is a mechanism thatchecks the database for a specific exception condition. - An alert is characterized by the SQL selectstatements itcontains. - A SQL selectstatementfells the application whatdatabase exception to identify as well as what output to produce for that exception. 92. what are composite Data types? * Composite Data types are of two types 1. PL/SQL Records 2. PL/SQL Collections Index By Table Nested Table VARRAY * Composite data types are also known as collections - they are RECORD,TABLE,NESTED TABLE and VARRAY RECOD data type:- - A RECORD is a group of related data items stored as fields each with its own name and data type. - PL/SQL Records are similar to structures in 3GL’s - A RECORD is not the same as Row in a database table - RECORD treats a collection of fields as a logical unit. - These are (RECORD type) convenient for fetching a row of data from a table for processing - RECORDS also can be declared. Syntax TYPE type_name is RECORD (filed declaration,…..); identifier type_name; Ex:- TYPE emp_record_type is RECORD last_name varchar2(50), job_id varchar2(10), salarynumber*8,2)); emp_record emp_ record_type; - fields declared as NOT NULL mustbe initialized. INDEX BY Table data types:- * This data type contains two components . 1. Primary key of data type BINARY_INTEGER 2. column of scalar or record data type. * Objects of the TABLE type are called INDEX BY Tables - they are modeled as (butnot the same as ) data base tables. - INDEX BY Table are a primarykey to provide the user with array-like access to rows. - INDEX BY table is similar to an ARRAY. - It can be increased in size dynamicallybecause theyare un constrained. * there are two steps involved in creating a INDEX BY table. 1. Declare a TABLE data type. 2. Declare a variable of that type. - the size of the INDEX BY Table is un constrained increase dynamicallyso thatINDEX BY Table an increase dynamically,so that INDEX BY Table grows as new rows are added. - INDEX BY Tables can have one column and a unique identifier to that one column neither of which can be named.
- 30. - The columncan belong to any scalar or record data type, but the primary key mustbe ling to type BINARY_INTEGER - INDEX BY Tables cannotbe initialized at the time of its declaration and also itcannotbe populated at the time of declaration. - An exploit executable statementis required to initialize (populate) the INDEX BY TABLE. INDEX BY TABLE STRUCTURE Unique identifier Column ……. 1 2 …… BINARY_INTEGER SCALOU Syntax TYPE ename_table_type IS TABLE OF Employees.last_name%TYPE INDEX BY BINARY_INTEGER; -this can be reterened by INDEX Bytable_name (primary_key_value); - The Following methods are used with INDEX BY Tables. 1. EXISTS 2. OUNT 3. FIRST AND LAST 4. PRIOR 5. NEXT 6. TRIM 7. DELETE INDEX BY Table of Records:_ - At a given point of time.INDEX BY Table can store only the details of any one of the columns ofa database table - To store al the columns retried by a query, INDEX BY Table of Records are used. - Because only the table definition is needed to hold information aboutall ofthe fields of a data base table,the table of records greatly increases the functionalityif INDEX BY Table. Syntax TYPE dept_table_type IS TABLE OF Departments % ROWTYPE INDEX BY BINARY_INTEGER; Dept_table dept_table_type; *% ROW TYPE attribute can be used to declare a record that represents a row in a database table. *The difference between the % ROWTYPE attribute and the composite data type RECORD is that RECORD allows to specify the data types of fields in the record or to declare new fields with new data types. Nested Tables * Nested Table is an ordered group of items oftype TABLE. Nested Table contain multiple columns and can be used as variables,parameters,results,attributes and columns. They can be thought of as one column data base tables. Rows of a nested table are not stored in any particular order. The size of a nested table can be increased dynamicallyi.e. nested tables are unbounded. … Gopi Raj ….
- 31. Elements in atable initially have consecutive subscripts,butas elements are deleted,theycan have non -consecutive subscripts. The range of values for nested table subscripts is 1 ..2147483647. To extend a nested table,the built-in procedure EXTEND mustbe used. To delete elements,the built-in procedure DELETE mustbe used. An un initialized nested table is automaticallynull,so the IS NULL comparison operator can be used to ses if nested table is null. The operators CAST, THE and MULTISET are used or manipulating nested tables. 1. Creation of a Nested Table Defining an objecttype. SQL> Create type ELEMENTS AS OBJECT (ELEM_ID Number (6), PRICE Number (7,2)); / 2. Create a table type ELEMENTS_TAB which stores ELEMENTS objects. SQL> Create TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS / 3. Create a data base table STORAGE having type ELEMENTS_TAB as one of its columns. SQL> Create Table STORAGE (Saleman number(4), Elem_id number(6), Ordered Date, Items Elements_Tab) NESTED TABLE ITEMS STORE AS ITEMS_TAB; VARRAYS:- VARRAYS are ordered group of items oftype VARRAY. VARRAYS can be used to associate a single identifier with an entire collection. This allows manipulation ofthe collection as a whole and easy reference of individual elements. The maximum size ofVARRAY needs to be specified in its type definition. The range of values for the index of a VARRAY is from 1 to the maximum specified in its type definition. If no elements are in the (table) ARRAY, then the ARRAY is automaticallynull. The main use ofVARRAY is to group small ofuniform-sized collection ofobjects. Elements ofa VARRAY cannot be accessed individuallySQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript. The type of the elementof a VARRAY can be any PL/SQL type except the following. BOOLEAN, TABLE, VARRAY etc. VARRAYS can be used to retrieve an entire collection as a value. VARRAY data is stored in-line,in the table space as the other data in its row. When a VARRAY is declared,a constructor with the same name as the VARRAY is implicitlydefined. The constructor creates a VARRAY from the elements passed to it. A VARRAY can be assigned to another VARRAY, provided the data types are the exact same type. TYPE my_VARRAY1 IS VARRAY (10) OF MY_Type; Is NULL comparison operator can be used to see if a VARRAY is null. VAARAYS cannotbe compared for equality or in equality.
- 32. Creating a VARRAY:- 1.Defining objecttype ELEMENTS SQL> Create TYPE MEDICINES AS OBJECT (MED_ID NUMBER (6), MED_NAME Varchar2 (14), MANF_DATE DATE); / 2. Define a VARRAY type MEDICINE_ARR which stores MEDICINES. objects SQL> Create TYPE MEDICINE_ARR AS VARRAY (40) OF MEDICIES; / 3. Creating a relational table MED_STORE which has MEDICINE_ARR as a column type SQL> Create table MED_STORE( Location varchar2 (15), Store_Size number (7), Employees number (6), Med_Items Medicine_Arr); Differences between nested tables and Varrays *Nested Tables are unbounded,where as Varrays have a maximum size. *Individual elements can be deleted from a nested table,but not from a Varray. Therefore nestedtables can be spares,where as Varrays always are dense. Varrays are stored by Oracle in-line (in the same table space),where as nested table data is out-of-line in a store table, which is a system generated data base table associated with the nested table. When stored in the data base,nested tables do notretain their ordering and subscripts, where as Varrays do. Nested tables supportindexes while VARRAYS do not. Differences between conversions and Interfaces:- Conversion Interface 1. Conversion is one-time process 1. Interface is the postproduction which is performed once before process. production go on live. (Pre-production process) 2. Data comes in to Oracle Applications 2. Interface is the integration of only (One way process) two systems. 3. Interative Process 3. Scheduled and repetive process. Oracle Reports – Trouble Shooting 1. ConcurrentRequestLogs:- The first step of reports debugging should be to examine the log of concurrent request. 2. Running from the operating system:- If the problem is notresolved with log, then the reportshould be run through from the operating system. Along with the standard reportarguments,the report should be run along with the arguments passed bythe concurrentmanager. If it is run successfullythen the problem is with the environmentfrom with the concurrentmanager was started. 3. Using r25run in place of ar25run:- For this debugging step,AOL provides a report $FND_TOP/ SRW/FNDNOEXT.rdf (Unix path name) which has no user exits. If this step also fails,then the problem could be with Oracle Applications Installation.
- 33. 4. Running thePrint EnvironmentVariable Values Report:- The concurrentmanager inherits its environmentvariables from the shell from which itwas started and then runs reportusing this environment. This environmentcould be different from that a user sees logging in to the Applications because the concurrent manager mayhave been started by a different user with different environmentsettings. Due to this difference,it is sometimes difficultto determine the cause oferror in running reports. To examine the values of few variables,prints environmentvariable values,Reportto printout the variable as seen by the concurrentmanager to see if is correct. Very common and often problems such as a problem in compilation or the concurrentmanagers inabilityto locate a library happen due to incorrect REPORTS 60_PATH. 5. Emulate ConcurrentManager Environment:- For UNIX plat forms,to assistin determining where the problem lies,Oracle Applications AOL ships a program called $ FND_TOP / Srw / ar60run.oc. This program helps to emulate the concurrentmanager environmentwhen testing reports from the OS command line. This program writes all the environmentvariables and arguments passed to it in a log file ar60run.log.(Located b y defaultin the $FND_TOP / $APPLLOG directory). Save the ar60run.oc source code to a file named ar60run.oc compile itand rename the executable as ar60run.(new). Save $FND_TOP / bin / ar60run in to some other file and place the new ar60run into $FND_top / bin. Compiling and relinking has been incorporated in to fnd.mk which will, by defaultbuild an executable $FND_TOP / bin /ar60rund,which can be renamed to ar60run (new). Submitthe reportfrom concurrentmanager and look at ar60run.log. Then run the report from OS with the same arguments as shown byar60run.log shows using old ar60run. Bitmapped Reports Printer drivers should be provided with printstyle (Landscape) to determine how to print text files. Bitmapped reports are nottext files and these are output as postscriptfiles. The postscriptfile is assetofinstructions telling the printer exactly a landscape report,the postscriptfile mustbe generated as landscape. Frequently asked questions in Reports 1. Why does myreport only fail from the concurrentmanager? This is because the environmentfrom which the concurrentmanager launches a reportis differentfrom the one when running the report from OS command line. 2. Why does myreport show differentdata? If the report shows different data when it is run as a stand alone report,sometimes the data in the output may be different for different situations. This is usuallydue to different / no profile options or other values being passed to the report by the concurrent manager. Check the calls to SRWINIT and SRWEXIT, it those are found disabled,theyshould be re-enables,before the report is run through concurrentmanager. 3. Why do I get the error REP_0713 when I run my report? Oracle Reports uses a text file called uiprint.txt to hold printer names. If the current printer name is notin this file, then the error REP_0713 error. 4. Why do I get manypages of nonsense when Iprint my Report? Postscriptcode should be recognized by the printer driver. ‘ enscript’ program cannotbe used for printing. 5. What does the ‘ SEP-0065 ’; virtual memorysystem error? * This error could be due to the following reasons. * By default Oracle Reports uses /tmp directory to write temporaryfiles, which may be getting full. These files could be directed to another directory using the environmentvariable TMPDIR. * Are the failing reports using page N or M? This can consume a lotof Oracle Reports virtual memory. If possible,Reports should be run againsta smaller database.
- 34. FND_PROGRAM Package FND_PROGRAM.Executable:- Procedure FND_PROGRAM.Executable IS (executable in Varchar2, application in varchar2, (full name) short_name in varchar2,(executable shortname) description in varchar2 default NULL, execution_method in varchar2, execution_file_name in varchar2 default null, Subrowline_name in varchar2 default null,(only for spawned immediate) Icon_name in varchar2 default null, Language_code in varchar2 default ‘US’, Execution_file_path in varchar2 defaultnull); For Java ConcurrentProgram. FND. PROGRAM. REGISTER:- Procedure FND_PROGRAM.Register IS (Program in varchar2, application in varchar2, enabled in varchar2, short_name in varchar2, description in varchar2,default null, executable_short_name in varchar2, executable_application in varchar2, execution_options in varchar2,default null, priority in number defaultnull, save_outputin varchar2 default ‘Y’, print in varchar2 dafault ‘Y’, cols in varchar2 default null, rows in varchar2, defaultnull, style in varchar2, defaultnull, style_required in varchar2, default‘N’, printer in varchar2, default null, Requets_Type in varchar2, default null, Request_type_Application in varchar2 defaultnull, Use_in_Srs in varchar2,default ‘N’, Allow_disabled_valuer in varchar2 default‘N’, Run_alone in varchar2 default ‘N’, Output_type in varchar2 default ‘TEXT’, Enable_trace in varchar2 default‘N’, Restartin varchar2 default‘Y’, nls_complaintin varchar2 default ‘Y’, icon_name in varchar2 defaultnull, language_code in varchar2, default‘US’, mls_function_short_name in varchar2 defaultnull, mls_function_application in varchar2 defaultnull, incrementor in varchar2 default null); Property Classes A property class is a named objectthatcontains a listof properties and their settings. Once a property class is created,it can be assigned to any object. An objectbased on a property class can inheritthe settings ofany property in that property class. There can be number ofproperties in a property class,and the properties in a class can apply to different objects. When an object is based on a property class,all the properties which are inherited from the property class can be controlled locallyalso.
- 35. Property class areseparate objects and can be copied between modules ifrequired. A property class can be sub classed in onlynumber ofmodules. Visual Attributes Visual attributes are the font, color and pattern properties thatcan be setfor form and menu modules which are appeared in application’s interface. Font Properties:font name,font size, font style, font width, font height. Color and pattern properties: Foreground color,Back ground color,fill pattern, char mode, logical attribute,White on Black. Every interface object has its visual attribute group property that determines how the objects individual visual attribute settings are derived. The visual property group property can be set to default, NULL,or the name of a named visual attribute defined in the same module. An object’s named visual attribute setting can be changed programmaticallyto change the font, color and pattern of the objectat runtime.