Oracle Apps Technical Interview Questions And Answers
1. What is NOCOPY?
By default the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value.
NOCOPY: is a compiles hint that can be used with OUT and IN OUT parameter to request to 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 of a child object relative to its parent object. The end of the anchor should be attached to the parent object.
A) Parent Object B) Child Object
Since the size of the some layout objects may change when the report runs (When the data is actually fetched), anchors need to be defined to make the appearance of the object.
An Anchor defines the relative position of an object to the object to which if this anchored.
* Symbol is by pressing the shift key 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 parent object by 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 report builder to a program, which performs some function and then control returns to the Report Builder.
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 complex data manipulation.
Pass data to report builder from OS text files.
Support PL/SQL blocks.
Control real time devices be printer or robot.
5. Types of Triggers in Reports :-
* Report Triggers * Data model Triggers * Layout Triggers
Report Triggers :-
After Parameter form Trigger.
After Report Trigger
Before parameter form Trigger
Before Report Trigger.
Between Pages Trigger
+ 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 report is 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 first pages.
- This is used for customized page formatting.
* After Report Trigger
- Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mai lid etc….)
- This is used to clean up any initial processing that was done such as deleting the tables.
- This Trigger always fires irrespective of success or failure of the 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 must return a BOOLEAN value.
True ……. Includes the current record in the report.
False ……. Excludes the current record from the report.
* Formula Trigger
- 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
* Format Trigger.
- These are PL/SQL functions executed before the object is formatted.
- Used to dynamically change the formatting attributes of the object.
* Action Trigger
- These are Pl/SQL procedures executed when a button is selected in the run time previewer.
- 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 Placeholder columns.
Formulas are PL/SQL functions that populate formula or place holder columns.
Cannot be 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 report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is 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 set dynamically (Programmatically)
* The value can be set for a place holder column in the following places.
- Before report trigger if the place holder is a report level column.
- Report level formula column, if the place holder is a report level column.
- A formula in the place holders group below it (The value is set once for each record of the group)
9 Repeating Frame
- Repeating frame surrounds all of the 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 of it 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 of multiple queries.
- A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its 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.
- Routines can be modified online without interfering 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 security privileges.
* Improved performance.
- Reparsing for multiple users can be avoided by exploiting the shared SQL area.
- PL/SQL parsing at run-time can be avoided by pursing at compile time.
- Number of calls to the database can be reduced and network traffic decreased by bundling 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 PL/SQL statement.
* Must contain a RETURN clause in the header. Do not contain a RETURN
Clause in the header.
* Must return a value. Can return none, one or many
values.
* Must contain at fast one RETURN Can contain a RETURN
Statement. Statement.
* Do not contain OUT and INOUT 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:
* Implicit Cursors.
- Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL select statements, including queries that return only one row.
- Oracle Server implicitly opens a cursor to process each SQL statement not associated with on explicitly declared cursor.
- The most recent implicit cursor can be returned as the SQL cursor.
* Explicit Cursors
- For queries that return more than one row, explicit cursors are declared and named by the programmes and manipulated through specific statements in the block’s executable actions.
- Explicit cursors are used to individually process each row returned by a multiple-row SELECT statement.
- 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 most recent fetch doesn’t return a row.
% Found Boolean Evaluate to TRUE if the most recent fetch returns a row. Complement of % 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 different active set on each occasion for each execution, the previous cursor is closed and reopened with a new set of parameters.
- Sizes should not be mentioned for the data types of parameters the parameters names are for references in the query expression of the cursor.
17 Confined Mode: -
- If it is on, child objects cannot be moved outside their enclosing parent objects.
- If it is off child objects can be moved out sides their enclosing parent objects.
Flex Mode:-
- If it is on, parent borders stretch when child objects are moved against them.
- If it is off, parent borders remain fixed when child objects are moved against them.
18 Parameters
- A parameter is a variable whose value can be set at runtime (from the run time parameter of the command line).
- User parameters are created by the user and system parameters are created by Report Builder.
- System parameters cannot be 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 may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses of queries.
- Bind references cannot be referenced in FROM clauses.
- Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name.
- If the parameter / column is not created 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 SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
What is % Row type
- % Row types is used to declare a record based on a collection of columns in a database table or view.
- The fields in the record take their names and data types from the columns of the 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% row type.
Then emp_record will have a structure consisting of all the fields each representing a column in the employees table.
What is a Ref Cursor?
- Oracle server uses unnamed memory spaces to store data used in implicit cursors.
- Ref cursors are used to define a cursor variable, which will point to that memory space 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 block that terminates its main body of 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.
Ex:- when no rows are retrieved from the database in a SELECT statement, then error ORA-01403 occurs and the exception NO-DATA-FOUND is raised by PL/SQL.
Ex:- Exception can be raised explicitly by issuing the RAISE statement with in the block.
- The exception being raised may be either.
User-Defined or Pre Defined
Trapping an exception:-
- If the exception 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 successfully handles the exception, then the exception doesn’t propagate 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 exception 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. -à Implicitly Raised.
B) Non-Pre defined Oracle server exceptions. -à Implicitly Raised.
C) User-defined exceptions -à Explicitly Raised.
Pre-Defined Oracle Server Exceptions: -
- These are the error (20) that occurs most often 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 that the developer determines as abnormal.
These need to be declared and raised explicitly.
PRAGMA EXCEPTION_INIT
Statement is 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 cannot be used directly in SQL statements.
What is Dynamic SQL?
- Dynamic SQL is a SQL statement that contains variables that can change during runtime.
- It is a SQL statement with placeholders 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
- Basically Dynamic SQL means creating the SQL statements dynamically at runtime by using variables.
Ex:- Dynamic SQL can be used to create a procedure that operates on a table whose name is not known until runtime or to execute DDL/DCL/SCS statements.
----à In Pl/SQL such statements cannot be executed statically.
--à EXECUTE IMMEDIATE Statement can perform dynamic single row queries.
Declare
D_str varchar2 (200);
Val varchar2 (20);
Begin
D_str= insert into table1 values (;val);
Val= ‘ Bye’ Execute Immediate str using val;
end;
What are Autonomous Transactions?
- Autonomous transactions are the processes run independently of its parent.
- By means of Autonomous Transaction, the current transaction can be temporarily suspended and another operation can be begun.
- The basic idea behind this is to have some operation take place independently of the current transaction.
Ex: - to allow error messages written to table to be committed but to rollback everything else that has taken place prior to the error.
- The autonomous or child transaction can commit or rollback as applicable with the execution of the parent transaction being resumed upon its completion.
- The parent may then perform further operations of any 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 functionality internally, 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 get the 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 of the autonomous unit are executed.
- As the code unit exits and control returns to the parent the main (parent) transaction is resumed and the transaction context is switched back to the parent.
What is Bulk binding of Bulk collect?
Bulkbind:-
- The assignment of values 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 by minimizing the number of context switches between PL/SQL and SQL engines while they pass an entire collection of elements (varray, nested tables, index-by table or host array) as bind variables back and forth.
- Prior to Oracle 8i, 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 result of SELECT or FETCH statements.
- The keyword BULK COLLECT can be used with SELECT 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 point in time.
- In Oracle 7, it is termed 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.
Best Table -à The tables are that is (are) created by MVIEW create statement and that stores data that satisfy the MVIEW query.
Syntax:- Create materialized view
Refresh fast
Start with sysdate
Next sysdate +1 as
Select *from ;
- Since this is a fast refreshed MVIEW the master table should have a log (Master log) to record the changes on it that can be created by running.
Create materialized view log on master_table;
-à This statement creates 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 possibly also to the MVIEW.
- To keep a MVIEW data relatively current with the data in the master table, the MVIEW must be periodically refreshed.
* Refresh can be accomplished by one of the following procedures.
Dbms_mview.refresh (, )
Dbms _ refresh.refresh ()
Refresh Types -à Complete Refresh, Fast Refresh, Force Refresh
* Complete Refresh is performed by deleting the rows from the snapshot and inserting the rows satisfying the MVIEW query.
* In Fast refresh only the rows updated since last refresh are pulled from the master table to insert into MVIEW.
* This requires a log table called as MVIEW Log to be created on the Master Table.
* Force refresh first tries to run a Fast refresh if possible.
* If fast refresh is not possible, it performs complete refresh.
Refresh Groups
- These are used to refresh multiple snapshots in a transitionally consistent manner.
- When a refresh group is refreshed all MVIEWS in that group are populated with data from a consistent point in time.
- Refresh groups are managed by using 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-only materialized views
- DML cannot be performed on the snapshots in this category.
2 Up datable materialized views
- These MVIEWS eliminates the restriction of DML’s on snapshots.
3 Sub query materialized views
- These are the MVIEW’S that are created with sub queries in the WHERE clause of a 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 primary key for refresh are called primary key MVIE’S (Oracle 8).
* Fast refresh requires some association (mapping) Between rows at snapshot and master tables.
5 Multifier materialized views (Oracle 9)
- In this type MVIEW, its master table itself is a MVIEW.
- This feature enables fast refresh of MVIEW’S that have MVIEW’S as their masters.
- Many companies are structured on at least three levels
A) International
B) National
C) Local
- Many nodes at both the national and local levels are required
- The best possible solution in such cases is to use multifier MVIEW’S.
6 Simple Vs Complex MVIEW’S.
- MVIEW’S being simple or complex determines whether it can be fast refreshed or not.
- A MVIEW is fast refreshable if it is simple.
- A MVIEW is not fast refreshable if it is complex.
- A MVIEW can be considered CONNECT BY, INTERSECT, MINUS or UNION or UNION ALL clauses in its destining 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 warehouse or decision support systems.
Snapshots
- Snapshots are mirror or replace of tables in a single point of time.
- A Snapshot is a local copy of a table data that originates from one or more remote master tables.
- To keep a snapshots data current with the data of its master the Oracle server must periodically refresh 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 cannot be updated.
* A snapshot contains a complete or partial copy of a target master table from a single point in time.
- A snapshot may be read only or updatable.
26. How duplicate rows are deleted?
- Duplicate rows are deleted by using ROWID
Syntax à delete from Where ROWID not in (Select max (ROWID) from
Group by );
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)
Development Tools à ;
28. Difference between IN and OUT parameters.
Three types of parameters
1. IN 2. OUT 3.IN OUT
IN parameter:
- This parameter passes a values from the calling environment into the procedure.
- This is the default mode.
- A formal parameter of In mode cannot be assigned a value (we IN parameter cannot be modified in the body of the procedure)
- IN parameters can be assigned a default value in the parameter list.
- IN parameters are passed by reference.
OUT parameters:
- OUT parameter must be assigned a value before returning to the calling environment.
- OUT parameter passes a value from the procedure to the calling environment
- OUT parameter cannot be assigned a default value in the parameter list.
IN OUT parameter:
- This type of parameter pass a value from the calling environment into the procedure and a possibly different value from the procedure back to calling environment using the same parameter.
- IN OUT parameter cannot be assigned a default value. * By default OUT & IN OUT parameters are passed by value.
- These can be passed by reference 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 implicitly whenever 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 shut down) occurs on a schema or database.
- Executes implicitly when 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 implicitly when some user or data base system actions occur.
- Ex. When user logs on to the system.
When DBA shuts down the database.
- 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 what action 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 database or a schema.
- With a database, 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 - CURSORS. Data bases initialization parameter. (default value is 50).
- * A triggering statement should 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 on which the table or view causes the trigger to fire.
3 Trigger Type Statement, Row
- Determines how many times the trigger body executes
4 Table name Table, View
5 Trigger body à PL/SQL – block
- Determines what actions the trigger should perform.
* INSTEAD of triggers are used to provide a transparent way to modifying views that cannot be modified directly through SQL, DML statements because the view is not modifiable.
-à INSTEAD of triggers provide writing of Insert, Update and Delete statements against the view.
- The INSTEAD if trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
- INSTEAD of trigger execute the trigger body instead of the triggering statement.
Statement Triggers
- In this type of triggers, the trigger body executes once for the triggering event.
- This is the default.
- Statement trigger 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 cannot be modified by normal DML if the view query contains set operators, group functions, group by, connect By, start with clauses or joins.
Mutating Table
- A Mutating table is a table that is currently being modified by an UPDATE, DELETE OR INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
- A table is not considered mutating for statement triggers.
- A mutating table cannot be changed because the resulting DML could change data that is in consistent state.
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 that can be used to tune SQL operations.
- The SQL – Trace facility can be enabled / disabled for an individual session or at the instance level.
- If the initialization parameter SQL-TRACE is set to 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 at the system/session level by using.
Alter system/session set SQL-TRACE = TRUE/FALSE.
Explain Plan
- Explain plan command generates information that details the execution plan that will be used on a particular query.
- Uses a pre created table (PLAN_TABLE) in the current schema to store information about the 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 script creates an output table, 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 statement into 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 format the plan table data.
$ ORACLE_HOME / rdbms / admin / utlxpls. Sql – to format serial explain plans.
$ ORACLE_HOME/ rdbms/admin/utlxpil. Sql – to format parallel explain plans.
* AUTOTRACE
- The AUTOTRACE facility in SQL* plus allows analysts to view the execution pan d some useful statistics for a SQL statement within a SQL*plus session.
- AUTOTRACE needs to be initiated in the SQL*Plus session prior to executing the statement.
SET AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS]
- As with the explain plan command, to obtain an execution plan the PLAN-TABLE must be 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 must have access to dynamic performance tables.
- To achieve this, grant PLUS TRACE role to the user.
PLUS TRACE role is created by the plus trace. Sql script
$ ORACLE_HOME / sql plus admin SYS user must run this script.
DBA can them grant the role to the users who wish to use the AUTOTRACE.
TK PROF
- Tk prof facility accepts as input a SQL Trace File and produces a formatted output file.
- Tk Prof Filename_source filename_output EXPLAIN = [user name / password] sys = [yes/no] TABLE = [Table Name]
A) How do you add trace to a report?
- By using the 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 object data types.
BLOB: Represents a binary large object
CLOB: Represents a character large object
NCLOB: Represents a multibyte character object.
BFILE: Represents a binary file store of in an os binary file outside the database.
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 different segment unless 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’t contain 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.
- These are stored 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_LOB.READ of DBMS_ LOB.WRITE are used to manipulate LOBS.
Oracle Applications Architecture
- Internet computing Architecture is a framework for 3-tired, distributed computing that supports 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 Internet computing architecture, only the presentation layer of Oracle Applications is on the Desk Top tier in the form of a plug-in to a standard Internet browser.
TEMPLATE FORM
- The TEMPLATE form is the required starting point for all development of new forms.
- The development of a new form is started by copying the TEMPLATE.fmb file, located in $ AU_top / forms/ us, to a local directory and renaming it as appropriate.
TEMPLATE FORM CONTAINS THE FOLLOWING:-
*Platform-independent references to object groups in the APPSTAND form
(STANDARD _PCAND_VA,
STANDARD_TOOLBAR,
STANDARD_CALENDAR)
*Platform–independent attachments of several libraries .
FND SQF
APPCORE
APPDAYPK
*Several form level triggers with required code.
*Program units that include a specification and a body for the package APP_CUSTOM, which contains default behaviors for window opening and closing events.
-In general this code should be modified for the specific form under development.
* The application’s color pallete, containing the two colors required by the referenced visual attributes.
* Many referenced objects that support the calendar, the toolbar, alternative regions and the menu.
* Template form contains simple objects that show 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 plat form-independent attachments of several libraries.
- Some of the libraries are attached directly to the TEMPLATE (FNDSOF, APPCORE, APPDAYPK) while the others are attached to these libraries.
APPCORE, APPDAYPK, FNDSQF, CUSTOM, GLOBE, VERT, JA, JE, JL
APPCORE
- Contains the packages and procedures that are required of all forms to support the Menu, Toolbar and other required standard behaviors.
- Procedures and functions in APPCORE have names beginning with APP.
APPDAYPK
- Contains the packages that control the Oracle applications calendar feature.
FINDSQF
- Contains packages and procedures for message dictionary, flex fields, profiles and concurrent processing.
- It also has various other utilities for navigation, MRG, WHO etc.
- Procedures and functions have names beginning with FND.
CUSTOM
- Custom library allows extension of Oracle Applications forms without modification of Oracle applications code.
- Custom library can 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 must be in upper case) and disabling fields that are not required for a particular site.
- All logic must branch 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 statement that is embedded in a clause of another 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 query is one way of reading (data) every row in a table and comparing values in each row against related data.
- Oracle server performs correlated sub query when 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 query is evaluated once for each row processed by the parent statement.
GET
Candidate row from the outer query
EXECUTE
Inner query using the candidate row value
USE
Values from inner query to qualify / disqualify candidate row
Ex:- Select last_name, salary, department_id from employers OUTER
where salary> (select AVG (Salary) from employees
where department id = outer.department_id);
Types of Joins
1 Equi Join
2 Non-Equi Join
3 Outer Join
4 self Join
Equi Join:-
- Is also called simple or inner join.
- An equi join is a join condition that contains equality sign.
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 that do not meet the 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 deficient in information.
- This operator has the effect of creating one or more null rows to each one or more rows from the (join condition) non deficient table can be joined.
Self Join
Self join is used to join a table to itself.
Ex:- to find the name of each 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.STYLEà FUNCTION
CUSTOM.EVENTà PROCEDURE.
* 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 statement where as post-query executes 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
How to get second parameter value based on first parameter?
$flex
$value setname.
What is Ref Cursor?
Ref cursor is a data type and executed at server side and with ref cursor multiple select statements can be executed.
By increase the binary size, that number of records 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.fmb)
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 separately in 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 many applications.
When a packaged PL/SQL construct is called for the first time 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 part of 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 of different 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 about the 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_UPDATE and 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 built in USER.
APPCORE API’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
FNDSQF API’S
FND_CURRENCY
FND_DATE
FND_GLOBAL
FND_ORG
FND_STANDARD
FND_UTILITIES.OPEN_URL
FND_UTILITIES. PARAM_EXISTS
How to call flex fields in the form?
By using FND_FLEX.EVENT (EVENT varchar2)
How to register an executable and define a concurrent program through backend?
By using concurrent processing API’S
1. FND_CONC_GLOBAL.REQUEST_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
- Is used to define a concurrent program executable
- It takes 8 parameters (all are IN mode)
Syntax procedure FND_PROGRAM.EXECUTABLE
(Executable IN varchar2,
(Full name) à application IN varchar2,
(Executable short name) à short name IN varchar2, Description IN varchar2 default null, execution_method IN varchar2,
execution_file_name IN varchar2 default null,
(only for spawned & Immedaite) à subroutine_name IN varchar2 default null,icon_name IN varchar2 default null,language_code IN varchar2 default (VS)(for Java Conc Program) à execution_file_path IN varchar2 default null,
* FND_PROGRAM.REGISTER
- This procedure no used to define a concument program.
- 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, default null,
(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’t check for the existence of the registered table or column in the data base schema, but only updates the required SQL tables.
- It should be ensured that, all the tables and columns registered exist actually and have the same format as that defined using AD_DD API.
- Views need not be registered.
57. How to write to a file through concurrent program?
* 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 output files.
à FND_FILE supports a maximum buffer line size of 32k for both and output files.
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
- this procedure as used to set the temporary log file and output filenames and the temporary directory 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 default null,
program in varchar2 default null,
description in varchar2 default null,
start-time in varchar2 default null,
sub_request in boolean default False,
argument1,
arguemnt2,
argument 100) return number;
* If this is submitted from oracle forms, all the arguments (1 to 100 ) must be 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 current value of the specified 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 set who 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’t exist, it can also be created with PUT.
Procedure FND_ PROFILE.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 that can 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_Output and Gone_Print_Style.
- SA’s and end users also cannot see the values or change these values.
In which directory log and output files are stored?
* APPLCSF is the top-level directory in which the concurrent manager puts logs and output files.
* APPLLOG and APPLOUT are the subdirectories in which the concurrent manager puts log and output files.
*APPLCSF variable need to be set in the prod.env(environmental variable), so that all product log files
$ APPLCSF/ $ APPLLOG à log files
$APPLCSF/ $ APPLOUT à out files
* concurrent manager log and out put files should go to $ FND_TOP/ $ APPLOG and $ FND _TOP / $APPLOUT if $ APPLCSF is not set.
62. How to submit concurrent programs through OS?
- From the operating system the utility.CONCSUB is used to submit is concurrent program.
- This is basically used to test a concurrent program.
- By using the WAIT token. The utility checks the request status every 60 seconds and returns the OS prompt upon completion of the request.
- Concurrent manager doesn’t abort, shutdown or start up until the concurrent request completes.
* If the concurrent program is compatible with itself, it can be checked for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
*PL/SQL procedures can submit a request to run a program as a concurrent process by calling.
FND_REQUEST.SUBMIT_REQUEST
* Before submitting a request, the following functions also should 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 request states?
- A PL/SQL procedure can check the status of a concurrent request by calling.
FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST
- FND_CONCURRENT.GET_REQUEST_STATUS
- This function returns the status of a concurrent request
- If the request is already computed, it also returns the completion message.
- This function returns both user-friendly (translatable) phase and status values as well as developer phase and status values that can drive program logic.
Syntax à Function FND_CONCURRENT.GET_REQUEST_STATUS
(request_id in out number,
application in varchar2 default null,
program in varchar2 default null,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEAN;
- When application and program are specified, the request_id of the last request for 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 request completion, then returns the request phase/status and completion message to the caller.
- Goes to sleep between checks for request completion.
Syntax àFND_CONCURRENT.WAIT_FOR_REQUEST
( request_id in number default null,
interval in number default 60,
max_wait in numbe default 0,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEN;
* FND_CONCURRENT.SET_COMPLETION_STATUS
- this function should be called from a concurrent program to set its 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;
normal status warning message à any message 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=. Fmb
Output_file=/forms?/.fmx
Module_type=form bath =no compile_all=special
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 by using 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_FUNCTION package.
* for this purpose, CALL_FORM built-in cannot be used since the Oracle Applications libraries do not support it.
*FND_FUNCTION.EXECUTE should be used to open a new session of a 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 already been opened.
*APP_NAVIGATE.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 of a 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 default FALSE);
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_target in varchar2 default null);
67. What is the reason for not getting any data when a multi org view is required?
- To get the data correctly, the xxx-ALL must be 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 not return any rows, 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 set the value in CLIENT INFO using set_org_contest.
- Execute fnd_client_info.Set_org_context (‘’);
- Now querying of multiorg views can be done.
68. How do you find that multiorg is installed?
- multi organization architecture is meant to allow multiple 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 of Multi org generally includes more than one business group.
* To know whether multiorg is existing or not
select multi_org_flag form fnd_product_groups)
- if the result is ‘Y’ means the database is group for multiorg
69. What are the triggers that fire on item?
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 triggers in forms
- Transactional triggers are the triggers that are related to accessing a data source.
- These triggers fire for each record that is marked for insert, update or table when forms would 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 .
* Important 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 Post and Commit transaction process. Before form builder processes any (changes) records to change.
Specifically it fires after form builder determines that there are inserts, updates or deletes in the form to post or commit, but before it commits the changes.
This trigger doesn’t fire when there is an attempt to commit, but validation determines that there are no changed records in the form.
This is a form level trigger.
Enter query mode should be set as ‘No’
This can be used to perform an action, such as setting up special locking requirements, at any time a database commit is going to occur.
If this trigger fails, the post and commit processes fail, no records are written to the database and focus remains in the current item.
If a DML is performed in a pre-commit trigger and it fails, ten manual rollback must be performed, because form builder doesn’t perform an automatic roll back.
This trigger fires in post and commit transactions.
Post – Commit Trigger
This is also known as post-commit trigger.
Post-commit trigger fires once during the post and commit transactions.
If there are records in the form that have been marked as inserts, updates or deletes, the post-forms-commit trigger fires after these changes have been written to the database but before form builder issues the database commit to finalize the transaction.
If the operation or application initiates a commit when there are no records in the form have been marked as inserts, updates or deletes, form builder fires post-forms-commit trigger immediately, without posting changes to the database.
This is a form level trigger.
Enter query mode should be set to ‘No’
Post-forms-commit trigger should be used to perform an action, such as updating an audit trial any time a database commit is about to occur.
If this trigger fails, post and commit processing aborts and form builder issues a ROLLBACK and decrements the internal save point counter.
This trigger fires in Post and Commit transactions.
What is Decode 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 it to each search condition.
If the expression is the same as search, result is returned.
If the default value is committed, a null value is returned where a search value does not match any of the result values.
DECODE function facilitates conditional inquiries by doing the work of a CASE or IF_THEN_ELSE statement.
DECODE (column, expression, search1, result1, search2, result2, …..);
Ex:- Select last_name, job, salary,
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 many functions, procedures and exceptions that can 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
SRW.Run_Report
SRW.Run_Report (command_line, char);
Executes specified R25 RUN Command
SRW.RUN_REPORT_FAILURE;
Stops report execution when failure of SRW.Run_Report occurs.
By using SRW.Run_Report, another report can be called to the screan from a button with in one report.
If this is used from a Report Trigger, BATCH=YES must be 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 Report Rep-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 statement char);
Executes specified SQL statement
* SRW.DO_SQL_FAILURE
Stops report execution upon SRW.Do_SQL failure.
* SRW.Message (msg_number number, msg_text char);
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 current frame, 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 format triggers.
They are used to change data that will be displayed in 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 that can be applied to multiple reports.
For example template can be defined that include the company logo and sets font colors for selected areas of a report.
When a report is created through the Report Wizard, there is an option of applying a template (.tdf file) to the report.
When a template is chosen, objects in the margin area of a template are imported into the same locations in the current report section, over writing any objects if exists.
The characteristics of the 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 current report section.
Any template properties, parameters, report triggers, program 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 report the 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 by Reports. xxx – Template_Reso (who xxx specified a Report style) (Tabular, Break Above)
iii) For each Report style for which the template is defined
- to the Reports.xxx – Template_Disc list, add the description that should be appeared on the template page of the 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 template file (file name.tdf) to
ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US
Preferences file:-
Windows à ORACLE_HOME\CAUPREFS.ORA (user preferences)
ORACLE_HOME\CAGPREFS.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 request set for three concurrent programs, which are having same parameter?
i) For the first report in the Report set, click on the parameters button and ender the parameters that are to the shared by all reports in the Request (Report) set.
ii) Go to the nest report and click the parameters button and list the same shared parameters.
iii) Do the above step for each and every report in the request set
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 at submission time.
* Request set wizard can be used to quickly create a new Request set in which all of the request run sequentially or all of the request run in parallel.
*sequentially à One after another
* parallel à All at once.
- The action can be set whether to continue processing or abort processing. If a request ends with the statues ‘Error’.
79. What are Global variables in Reports?
*Global variables are the variables that can 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 by Oracle 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 of the trigger as an argument for 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 enables query-find.
Coding logic for item relations such as dependent fields.
Coding messages to use message dictionary.
Adding FF logic if required.
Adding choices to the special menu and logic to modify choices the default menu and tool bar behavior is necessary.
Coding any other logic.
Creating a form function for the developed form and registering any sub functions.
Testing the form by itself.
Registering the form with AOL.
Adding the form function to a menu or creating custom menu.
Assigning the menu to the responsibility and assigning the responsibility to the user.
Testing the form within Oracle Applications.
Registering of Application, form and a concurrent program through Application developer Responsibility
Application:-
Responsibility à Application Developer
Form:-
Menu:-
Messages:-
Table:-
Sequence:-
Concurrent Program:-
Application Developer (Responsibility)
*Flex field
+Key
+Descriptive
-Test
*Concurrent
-Program
-Executable
-Library
*Application
-Register
-Form
-Function
-Menu
-Messages
+Database
+Lookups
+Validation
*Profile
*Attachments
- Document Entities
- Document Categories
- Attachment Functions
*Other
*Requests
- Run
-Set
-Profile
-Concurrent
-Change Organization
-Running Jobs
+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 cannot be made in Pl/SQL statements.
Bind references can be done in a PL/SQL statements.
Lexical parameters can be referenced by entering an ampersand () followed immediately by 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 must be defined so that report builder uses this value to validate the query with a lexical reference.
Token
If Oracle reports are executed by a concurrent program, (for Oracle Reports Program), then a keyword or a parameter with the same name as in the report builder, should be defined which for each parameter, which is known as token.
This is used to pass the parameters to the reports from the application (SRS Window)
Request Set:Request set is the group of requests, that can be submitted regularly using a single transaction.
Incompatibility
These are the list of programs that can be defined as incompatible with a certain program.
If any program is defined as incompatible to a particular program, then that program should not run simultaneously with the concurrent program, because they might interfere with its execution.
Application Developer Responsibility
Various Screens
Different Executable Methods
1 Host
2 Immediate
3 Java Stored Procedure
4 Java Concurrent Program
5 Multi Language Function
6 Oracle Reports
7 PL/SQL stored Procedure
8 Request set stage function
9 Spawned
10 SQL*Loader
11 SQL*Plus
Concurrent Library
Library types Transaction Library
User
Access Levels Extensible
System
List of values
List types Long List of Values
Poplist
No security
Security type Hierarchical Security
Non- Hierarchical Security
Char
Format type Date
Date time
Number
Standard date
Standard date time
Time
Validation types à Dependent
Independent
None
Pair
Special
Table
Translatable Independent
Translatable Dependent
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, concurrent programs and reports connect to.
86. What is a Responsibility?
- Responsibility defines Applications Privileges.
- A responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.
- Each user has at least one or more responsibilities and several users can share the same responsibility.
* Each responsibility allows access to
- a specific application or a set of applications.
- A set of books.
- A restricted list of windows that an user can navigate
- Reports in a specific application.
87. What are security Attributes?
- Security Attributes are used by Oracle self-service 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 set of changeable options that affects 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 of the flex field.
- Definition of the flex field.
90. What is an application /Module?
- Application is a collection of forms, function and menus.
91. What are Alerts?
- Alert is a mechanism that checks the database for a specific exception condition.
- An alert is characterized by the SQL select statements it contains.
- A SQL select statement fells the application what database 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
RECORD 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
(field declaration,…..);
identifier type_name;
Ex:- TYPE emp_record_type is RECORD
last_name varchar2(50),
job_id varchar2(10),
salary number(8,2));
emp_record emp_ record_type;
- fields declared as NOT NULL must be 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 (but not the same as) data base tables.
- INDEX BY Table is a primary key to provide the user with array-like access to rows.
- INDEX BY table is similar to an ARRAY.
- It can be increased in size dynamically because they are 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 unconstrained increase dynamically so that INDEX 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.
- The column can belong to any scalar or record data type, but the primary key must be ling to type BINARY_INTEGER.
- INDEX BY Tables cannot be initialized at the time of its declaration and also it cannot be populated at the time of declaration.
- An exploit executable statement is required to initialize (populate) the INDEX BY TABLE.
INDEX BY TABLE STRUCTURE
Unique identifier Column
…….
1
2
……
…
Gopi
Raj
….
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. COUNT
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 of a 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 about all of the fields of a data base table, the table of records greatly increases the functionality of 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 of type TABLE.
Nested Table contains multiple columns and can be used as variables, parameters, results, attributes and columns.
They can be thought of as one column database tables.
Rows of a nested table are not stored in any particular order.
The size of a nested table can be increased dynamically i.e. nested tables are unbounded.
Elements in a table initially have consecutive subscripts, but as elements are deleted, they can 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 must be used.
To delete elements, the built-in procedure DELETE must be used.
An uninitialized nested table is automatically null, so the IS NULL comparison operator can be used to sees if nested table is null.
The operators CAST, THE and MULTISET are used for manipulating nested tables.
1. Creation of a Nested Table
Defining an object type.
SQL> Create type ELEMENTS AS OBJECT
(ELEM_ID Number (6),
PRICE Number (7,2));
/
2. Create a table type ELEMENTS_TAB that 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 of type VARRAY.
VARRAYS can be used to associate a single identifier with an entire collection.
This allows manipulation of the collection as a whole and easy reference of individual elements.
The maximum size of VARRAY 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) VARRAY, then the VARRAY is automatically null.
The main use of VARRAY is to group small of uniform-sized collection of objects.
Elements of a VARRAY cannot be accessed individually SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript.
The type of the element of 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 implicitly defined.
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 cannot be compared for equality or in equality.
Creating a VARRAY:-
1. Defining object type 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 nested tables 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 database, nested tables do not retain their ordering and subscripts, where as Varrays do.
*Nested tables support indexes while VARRAYS do not.
Differences between conversions and Interfaces:-
Conversion Interface
1. Conversion is one-time process 1. Interface is the post production which is performed once before process. Production go on live (Pre-production process)
2. Data comes into Oracle Applications 2. Interface is the two integration of only (One way process) systems process
3. Interative Process 3. Scheduled and repetive process.
Oracle Reports – Trouble Shooting
1. Concurrent Request Logs: -
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 not resolved with log, then the report should be run through from the operating system.
Along with the standard report arguments, the report should be run along with the arguments passed by the concurrent manager.
If it is run successfully then the problem is with the environment from with the concurrent manager 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.
4. Running the Print Environment Variable Values Report: -
The concurrent manager inherits its environment variables from the shell from which it was started and then runs report using this environment.
This environment could be different from that a user sees logging in to the Applications because the concurrent manager may have been started by a different user with different environment settings.
Due to this difference, it is sometimes difficult to determine the cause of error in running reports.
To examine the values of few variables, prints environment variable values, Report to print out the variable as seen by the concurrent manager to see if is correct.
Very common and often problems such as a problem in compilation or the concurrent managers inability to locate a library happen due to incorrect REPORTS 60_PATH.
5. Emulate Concurrent Manager Environment:-
For UNIX platforms, to assist in determining where the problem lies, Oracle Applications AOL ships a program called $ FND_TOP / Srw / ar60run.oc.
This program helps to emulate the concurrent manager environment when testing reports from the OS command line.
This program writes all the environment variables and arguments passed to it in a log file ar60run.log. (Located by default in the $FND_TOP / $APPLLOG directory).
Save the ar60run.oc source code to a file named ar60run.oc compile it and 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 into fnd.mk which will, by default build an executable $FND_TOP / bin /ar60rund, which can be renamed to ar60run (new).
Submit the report from concurrent manager and look at ar60run.log.
Then run the report from OS with the same arguments as shown by ar60run.log shows using old ar60run.
Bitmapped Reports
Printer drivers should be provided with print style (Landscape) to determine how to print text files.
Bitmapped reports are not text files and these are output as postscript files.
The postscript file is asset of instructions telling the printer exactly a landscape report; the postscript file must be generated as landscape.
Frequently asked questions in Reports
1. Why does my report only fail from the concurrent manager?
This is because the environment from which the concurrent manager launches a report is different from the one when running the report from OS command line.
2. Why does my report show different data?
If the report shows different data when it is run as a standalone report, sometimes the data in the output may be different for different situations.
This is usually due 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, they should be re-enables, before the report is run through concurrent manager.
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 not in this file, then the error REP_0713 error.
4.Why do I get many pages of nonsense when I print my Report?
Postscript code should be recognized by the printer driver.
‘ enscript ’ program cannot be used for printing.
5. What does the ‘ SEP-0065 ’; virtual memory system error?
This error could be due to the following reasons.
* By default Oracle Reports uses / tmp directory to write temporary files, which may be getting full.
These files could be directed to another directory using the environment variable TMPDIR.
* Are the failing reports using page N or M? This can consume a lot of Oracle Reports virtual memory.
If possible, Reports should be run against a smaller database.
FND_PROGRAM Package
FND_PROGRAM.Executable:-
Procedure FND_PROGRAM. Executable IS
(executable in Varchar2,
application in varchar2, (full name)
short_name in varchar2, (executable short name)
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 default null);
For Java Concurrent Program.
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 default null,
save_output in varchar2 default ‘Y’,
print in varchar2 default ‘Y’,
cols in varchar2 default null,
rows in varchar2, default null,
style in varchar2, default null,
style_required in varchar2, default ‘N’,
printer in varchar2, default null,
Requets_Type in varchar2, default null,
Request_type_Application in varchar2 default null,
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’,
Restart in varchar2 default ‘Y’,
nls_complaint in varchar2 default ‘Y’,
icon_name in varchar2 default null,
language_code in varchar2, default ‘US’,
mls_function_short_name in varchar2 default null,
mls_function_application in varchar2 default null,
incrementor in varchar2 default null);
Property Classes
A property class is a named object that contains a list of properties and their settings.Once a property class is created, it can be assigned to any object.An object based on a property class can inherit the settings of any property in that property class.There can be number of properties 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 locally also.
Property class are separate objects and can be copied between modules if required.A property class can be sub classed in only number of modules.
Visual Attributes
Visual attributes are the font, color and pattern properties that can be set for 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 programmatically to change the font, color and pattern of the object at runtime.
By default the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value.
NOCOPY: is a compiles hint that can be used with OUT and IN OUT parameter to request to 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 of a child object relative to its parent object. The end of the anchor should be attached to the parent object.
A) Parent Object B) Child Object
Since the size of the some layout objects may change when the report runs (When the data is actually fetched), anchors need to be defined to make the appearance of the object.
An Anchor defines the relative position of an object to the object to which if this anchored.
* Symbol is by pressing the shift key 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 parent object by 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 report builder to a program, which performs some function and then control returns to the Report Builder.
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 complex data manipulation.
Pass data to report builder from OS text files.
Support PL/SQL blocks.
Control real time devices be printer or robot.
5. Types of Triggers in Reports :-
* Report Triggers * Data model Triggers * Layout Triggers
Report Triggers :-
After Parameter form Trigger.
After Report Trigger
Before parameter form Trigger
Before Report Trigger.
Between Pages Trigger
+ 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 report is 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 first pages.
- This is used for customized page formatting.
* After Report Trigger
- Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mai lid etc….)
- This is used to clean up any initial processing that was done such as deleting the tables.
- This Trigger always fires irrespective of success or failure of the 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 must return a BOOLEAN value.
True ……. Includes the current record in the report.
False ……. Excludes the current record from the report.
* Formula Trigger
- 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
* Format Trigger.
- These are PL/SQL functions executed before the object is formatted.
- Used to dynamically change the formatting attributes of the object.
* Action Trigger
- These are Pl/SQL procedures executed when a button is selected in the run time previewer.
- 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 Placeholder columns.
Formulas are PL/SQL functions that populate formula or place holder columns.
Cannot be 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 report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is 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 set dynamically (Programmatically)
* The value can be set for a place holder column in the following places.
- Before report trigger if the place holder is a report level column.
- Report level formula column, if the place holder is a report level column.
- A formula in the place holders group below it (The value is set once for each record of the group)
9 Repeating Frame
- Repeating frame surrounds all of the 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 of it 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 of multiple queries.
- A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its 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.
- Routines can be modified online without interfering 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 security privileges.
* Improved performance.
- Reparsing for multiple users can be avoided by exploiting the shared SQL area.
- PL/SQL parsing at run-time can be avoided by pursing at compile time.
- Number of calls to the database can be reduced and network traffic decreased by bundling 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 PL/SQL statement.
* Must contain a RETURN clause in the header. Do not contain a RETURN
Clause in the header.
* Must return a value. Can return none, one or many
values.
* Must contain at fast one RETURN Can contain a RETURN
Statement. Statement.
* Do not contain OUT and INOUT 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:
* Implicit Cursors.
- Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL select statements, including queries that return only one row.
- Oracle Server implicitly opens a cursor to process each SQL statement not associated with on explicitly declared cursor.
- The most recent implicit cursor can be returned as the SQL cursor.
* Explicit Cursors
- For queries that return more than one row, explicit cursors are declared and named by the programmes and manipulated through specific statements in the block’s executable actions.
- Explicit cursors are used to individually process each row returned by a multiple-row SELECT statement.
- 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 most recent fetch doesn’t return a row.
% Found Boolean Evaluate to TRUE if the most recent fetch returns a row. Complement of % 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 different active set on each occasion for each execution, the previous cursor is closed and reopened with a new set of parameters.
- Sizes should not be mentioned for the data types of parameters the parameters names are for references in the query expression of the cursor.
17 Confined Mode: -
- If it is on, child objects cannot be moved outside their enclosing parent objects.
- If it is off child objects can be moved out sides their enclosing parent objects.
Flex Mode:-
- If it is on, parent borders stretch when child objects are moved against them.
- If it is off, parent borders remain fixed when child objects are moved against them.
18 Parameters
- A parameter is a variable whose value can be set at runtime (from the run time parameter of the command line).
- User parameters are created by the user and system parameters are created by Report Builder.
- System parameters cannot be 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 may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses of queries.
- Bind references cannot be referenced in FROM clauses.
- Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name.
- If the parameter / column is not created 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 SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
What is % Row type
- % Row types is used to declare a record based on a collection of columns in a database table or view.
- The fields in the record take their names and data types from the columns of the 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% row type.
Then emp_record will have a structure consisting of all the fields each representing a column in the employees table.
What is a Ref Cursor?
- Oracle server uses unnamed memory spaces to store data used in implicit cursors.
- Ref cursors are used to define a cursor variable, which will point to that memory space 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 block that terminates its main body of 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.
Ex:- when no rows are retrieved from the database in a SELECT statement, then error ORA-01403 occurs and the exception NO-DATA-FOUND is raised by PL/SQL.
Ex:- Exception can be raised explicitly by issuing the RAISE statement with in the block.
- The exception being raised may be either.
User-Defined or Pre Defined
Trapping an exception:-
- If the exception 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 successfully handles the exception, then the exception doesn’t propagate 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 exception 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. -à Implicitly Raised.
B) Non-Pre defined Oracle server exceptions. -à Implicitly Raised.
C) User-defined exceptions -à Explicitly Raised.
Pre-Defined Oracle Server Exceptions: -
- These are the error (20) that occurs most often 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 that the developer determines as abnormal.
These need to be declared and raised explicitly.
PRAGMA EXCEPTION_INIT
Statement is 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 cannot be used directly in SQL statements.
What is Dynamic SQL?
- Dynamic SQL is a SQL statement that contains variables that can change during runtime.
- It is a SQL statement with placeholders 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
- Basically Dynamic SQL means creating the SQL statements dynamically at runtime by using variables.
Ex:- Dynamic SQL can be used to create a procedure that operates on a table whose name is not known until runtime or to execute DDL/DCL/SCS statements.
----à In Pl/SQL such statements cannot be executed statically.
--à EXECUTE IMMEDIATE Statement can perform dynamic single row queries.
Declare
D_str varchar2 (200);
Val varchar2 (20);
Begin
D_str= insert into table1 values (;val);
Val= ‘ Bye’ Execute Immediate str using val;
end;
What are Autonomous Transactions?
- Autonomous transactions are the processes run independently of its parent.
- By means of Autonomous Transaction, the current transaction can be temporarily suspended and another operation can be begun.
- The basic idea behind this is to have some operation take place independently of the current transaction.
Ex: - to allow error messages written to table to be committed but to rollback everything else that has taken place prior to the error.
- The autonomous or child transaction can commit or rollback as applicable with the execution of the parent transaction being resumed upon its completion.
- The parent may then perform further operations of any 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 functionality internally, 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 get the 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 of the autonomous unit are executed.
- As the code unit exits and control returns to the parent the main (parent) transaction is resumed and the transaction context is switched back to the parent.
What is Bulk binding of Bulk collect?
Bulkbind:-
- The assignment of values 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 by minimizing the number of context switches between PL/SQL and SQL engines while they pass an entire collection of elements (varray, nested tables, index-by table or host array) as bind variables back and forth.
- Prior to Oracle 8i, 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 result of SELECT or FETCH statements.
- The keyword BULK COLLECT can be used with SELECT 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 point in time.
- In Oracle 7, it is termed 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.
Best Table -à The tables are that is (are) created by MVIEW create statement and that stores data that satisfy the MVIEW query.
Syntax:- Create materialized view
Refresh fast
Start with sysdate
Next sysdate +1 as
Select *from ;
- Since this is a fast refreshed MVIEW the master table should have a log (Master log) to record the changes on it that can be created by running.
Create materialized view log on master_table;
-à This statement creates 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 possibly also to the MVIEW.
- To keep a MVIEW data relatively current with the data in the master table, the MVIEW must be periodically refreshed.
* Refresh can be accomplished by one of the following procedures.
Dbms_mview.refresh (, )
Dbms _ refresh.refresh ()
Refresh Types -à Complete Refresh, Fast Refresh, Force Refresh
* Complete Refresh is performed by deleting the rows from the snapshot and inserting the rows satisfying the MVIEW query.
* In Fast refresh only the rows updated since last refresh are pulled from the master table to insert into MVIEW.
* This requires a log table called as MVIEW Log to be created on the Master Table.
* Force refresh first tries to run a Fast refresh if possible.
* If fast refresh is not possible, it performs complete refresh.
Refresh Groups
- These are used to refresh multiple snapshots in a transitionally consistent manner.
- When a refresh group is refreshed all MVIEWS in that group are populated with data from a consistent point in time.
- Refresh groups are managed by using 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-only materialized views
- DML cannot be performed on the snapshots in this category.
2 Up datable materialized views
- These MVIEWS eliminates the restriction of DML’s on snapshots.
3 Sub query materialized views
- These are the MVIEW’S that are created with sub queries in the WHERE clause of a 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 primary key for refresh are called primary key MVIE’S (Oracle 8).
* Fast refresh requires some association (mapping) Between rows at snapshot and master tables.
5 Multifier materialized views (Oracle 9)
- In this type MVIEW, its master table itself is a MVIEW.
- This feature enables fast refresh of MVIEW’S that have MVIEW’S as their masters.
- Many companies are structured on at least three levels
A) International
B) National
C) Local
- Many nodes at both the national and local levels are required
- The best possible solution in such cases is to use multifier MVIEW’S.
6 Simple Vs Complex MVIEW’S.
- MVIEW’S being simple or complex determines whether it can be fast refreshed or not.
- A MVIEW is fast refreshable if it is simple.
- A MVIEW is not fast refreshable if it is complex.
- A MVIEW can be considered CONNECT BY, INTERSECT, MINUS or UNION or UNION ALL clauses in its destining 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 warehouse or decision support systems.
Snapshots
- Snapshots are mirror or replace of tables in a single point of time.
- A Snapshot is a local copy of a table data that originates from one or more remote master tables.
- To keep a snapshots data current with the data of its master the Oracle server must periodically refresh 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 cannot be updated.
* A snapshot contains a complete or partial copy of a target master table from a single point in time.
- A snapshot may be read only or updatable.
26. How duplicate rows are deleted?
- Duplicate rows are deleted by using ROWID
Syntax à delete from Where ROWID not in (Select max (ROWID) from
Group by );
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)
Development Tools à ;
28. Difference between IN and OUT parameters.
Three types of parameters
1. IN 2. OUT 3.IN OUT
IN parameter:
- This parameter passes a values from the calling environment into the procedure.
- This is the default mode.
- A formal parameter of In mode cannot be assigned a value (we IN parameter cannot be modified in the body of the procedure)
- IN parameters can be assigned a default value in the parameter list.
- IN parameters are passed by reference.
OUT parameters:
- OUT parameter must be assigned a value before returning to the calling environment.
- OUT parameter passes a value from the procedure to the calling environment
- OUT parameter cannot be assigned a default value in the parameter list.
IN OUT parameter:
- This type of parameter pass a value from the calling environment into the procedure and a possibly different value from the procedure back to calling environment using the same parameter.
- IN OUT parameter cannot be assigned a default value. * By default OUT & IN OUT parameters are passed by value.
- These can be passed by reference 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 implicitly whenever 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 shut down) occurs on a schema or database.
- Executes implicitly when 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 implicitly when some user or data base system actions occur.
- Ex. When user logs on to the system.
When DBA shuts down the database.
- 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 what action 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 database or a schema.
- With a database, 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 - CURSORS. Data bases initialization parameter. (default value is 50).
- * A triggering statement should 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 on which the table or view causes the trigger to fire.
3 Trigger Type Statement, Row
- Determines how many times the trigger body executes
4 Table name Table, View
5 Trigger body à PL/SQL – block
- Determines what actions the trigger should perform.
* INSTEAD of triggers are used to provide a transparent way to modifying views that cannot be modified directly through SQL, DML statements because the view is not modifiable.
-à INSTEAD of triggers provide writing of Insert, Update and Delete statements against the view.
- The INSTEAD if trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
- INSTEAD of trigger execute the trigger body instead of the triggering statement.
Statement Triggers
- In this type of triggers, the trigger body executes once for the triggering event.
- This is the default.
- Statement trigger 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 cannot be modified by normal DML if the view query contains set operators, group functions, group by, connect By, start with clauses or joins.
Mutating Table
- A Mutating table is a table that is currently being modified by an UPDATE, DELETE OR INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
- A table is not considered mutating for statement triggers.
- A mutating table cannot be changed because the resulting DML could change data that is in consistent state.
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 that can be used to tune SQL operations.
- The SQL – Trace facility can be enabled / disabled for an individual session or at the instance level.
- If the initialization parameter SQL-TRACE is set to 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 at the system/session level by using.
Alter system/session set SQL-TRACE = TRUE/FALSE.
Explain Plan
- Explain plan command generates information that details the execution plan that will be used on a particular query.
- Uses a pre created table (PLAN_TABLE) in the current schema to store information about the 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 script creates an output table, 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 statement into 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 format the plan table data.
$ ORACLE_HOME / rdbms / admin / utlxpls. Sql – to format serial explain plans.
$ ORACLE_HOME/ rdbms/admin/utlxpil. Sql – to format parallel explain plans.
* AUTOTRACE
- The AUTOTRACE facility in SQL* plus allows analysts to view the execution pan d some useful statistics for a SQL statement within a SQL*plus session.
- AUTOTRACE needs to be initiated in the SQL*Plus session prior to executing the statement.
SET AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS]
- As with the explain plan command, to obtain an execution plan the PLAN-TABLE must be 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 must have access to dynamic performance tables.
- To achieve this, grant PLUS TRACE role to the user.
PLUS TRACE role is created by the plus trace. Sql script
$ ORACLE_HOME / sql plus admin SYS user must run this script.
DBA can them grant the role to the users who wish to use the AUTOTRACE.
TK PROF
- Tk prof facility accepts as input a SQL Trace File and produces a formatted output file.
- Tk Prof Filename_source filename_output EXPLAIN = [user name / password] sys = [yes/no] TABLE = [Table Name]
A) How do you add trace to a report?
- By using the 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 object data types.
BLOB: Represents a binary large object
CLOB: Represents a character large object
NCLOB: Represents a multibyte character object.
BFILE: Represents a binary file store of in an os binary file outside the database.
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 different segment unless 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’t contain 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.
- These are stored 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_LOB.READ of DBMS_ LOB.WRITE are used to manipulate LOBS.
Oracle Applications Architecture
- Internet computing Architecture is a framework for 3-tired, distributed computing that supports 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 Internet computing architecture, only the presentation layer of Oracle Applications is on the Desk Top tier in the form of a plug-in to a standard Internet browser.
TEMPLATE FORM
- The TEMPLATE form is the required starting point for all development of new forms.
- The development of a new form is started by copying the TEMPLATE.fmb file, located in $ AU_top / forms/ us, to a local directory and renaming it as appropriate.
TEMPLATE FORM CONTAINS THE FOLLOWING:-
*Platform-independent references to object groups in the APPSTAND form
(STANDARD _PCAND_VA,
STANDARD_TOOLBAR,
STANDARD_CALENDAR)
*Platform–independent attachments of several libraries .
FND SQF
APPCORE
APPDAYPK
*Several form level triggers with required code.
*Program units that include a specification and a body for the package APP_CUSTOM, which contains default behaviors for window opening and closing events.
-In general this code should be modified for the specific form under development.
* The application’s color pallete, containing the two colors required by the referenced visual attributes.
* Many referenced objects that support the calendar, the toolbar, alternative regions and the menu.
* Template form contains simple objects that show 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 plat form-independent attachments of several libraries.
- Some of the libraries are attached directly to the TEMPLATE (FNDSOF, APPCORE, APPDAYPK) while the others are attached to these libraries.
APPCORE, APPDAYPK, FNDSQF, CUSTOM, GLOBE, VERT, JA, JE, JL
APPCORE
- Contains the packages and procedures that are required of all forms to support the Menu, Toolbar and other required standard behaviors.
- Procedures and functions in APPCORE have names beginning with APP.
APPDAYPK
- Contains the packages that control the Oracle applications calendar feature.
FINDSQF
- Contains packages and procedures for message dictionary, flex fields, profiles and concurrent processing.
- It also has various other utilities for navigation, MRG, WHO etc.
- Procedures and functions have names beginning with FND.
CUSTOM
- Custom library allows extension of Oracle Applications forms without modification of Oracle applications code.
- Custom library can 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 must be in upper case) and disabling fields that are not required for a particular site.
- All logic must branch 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 statement that is embedded in a clause of another 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 query is one way of reading (data) every row in a table and comparing values in each row against related data.
- Oracle server performs correlated sub query when 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 query is evaluated once for each row processed by the parent statement.
GET
Candidate row from the outer query
EXECUTE
Inner query using the candidate row value
USE
Values from inner query to qualify / disqualify candidate row
Ex:- Select last_name, salary, department_id from employers OUTER
where salary> (select AVG (Salary) from employees
where department id = outer.department_id);
Types of Joins
1 Equi Join
2 Non-Equi Join
3 Outer Join
4 self Join
Equi Join:-
- Is also called simple or inner join.
- An equi join is a join condition that contains equality sign.
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 that do not meet the 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 deficient in information.
- This operator has the effect of creating one or more null rows to each one or more rows from the (join condition) non deficient table can be joined.
Self Join
Self join is used to join a table to itself.
Ex:- to find the name of each 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.STYLEà FUNCTION
CUSTOM.EVENTà PROCEDURE.
* 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 statement where as post-query executes 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
How to get second parameter value based on first parameter?
$flex
$value setname.
What is Ref Cursor?
Ref cursor is a data type and executed at server side and with ref cursor multiple select statements can be executed.
By increase the binary size, that number of records 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.fmb)
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 separately in 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 many applications.
When a packaged PL/SQL construct is called for the first time 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 part of 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 of different 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 about the 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_UPDATE and 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 built in USER.
APPCORE API’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
FNDSQF API’S
FND_CURRENCY
FND_DATE
FND_GLOBAL
FND_ORG
FND_STANDARD
FND_UTILITIES.OPEN_URL
FND_UTILITIES. PARAM_EXISTS
How to call flex fields in the form?
By using FND_FLEX.EVENT (EVENT varchar2)
How to register an executable and define a concurrent program through backend?
By using concurrent processing API’S
1. FND_CONC_GLOBAL.REQUEST_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
- Is used to define a concurrent program executable
- It takes 8 parameters (all are IN mode)
Syntax procedure FND_PROGRAM.EXECUTABLE
(Executable IN varchar2,
(Full name) à application IN varchar2,
(Executable short name) à short name IN varchar2, Description IN varchar2 default null, execution_method IN varchar2,
execution_file_name IN varchar2 default null,
(only for spawned & Immedaite) à subroutine_name IN varchar2 default null,icon_name IN varchar2 default null,language_code IN varchar2 default (VS)(for Java Conc Program) à execution_file_path IN varchar2 default null,
* FND_PROGRAM.REGISTER
- This procedure no used to define a concument program.
- 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, default null,
(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’t check for the existence of the registered table or column in the data base schema, but only updates the required SQL tables.
- It should be ensured that, all the tables and columns registered exist actually and have the same format as that defined using AD_DD API.
- Views need not be registered.
57. How to write to a file through concurrent program?
* 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 output files.
à FND_FILE supports a maximum buffer line size of 32k for both and output files.
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
- this procedure as used to set the temporary log file and output filenames and the temporary directory 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 default null,
program in varchar2 default null,
description in varchar2 default null,
start-time in varchar2 default null,
sub_request in boolean default False,
argument1,
arguemnt2,
argument 100) return number;
* If this is submitted from oracle forms, all the arguments (1 to 100 ) must be 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 current value of the specified 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 set who 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’t exist, it can also be created with PUT.
Procedure FND_ PROFILE.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 that can 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_Output and Gone_Print_Style.
- SA’s and end users also cannot see the values or change these values.
In which directory log and output files are stored?
* APPLCSF is the top-level directory in which the concurrent manager puts logs and output files.
* APPLLOG and APPLOUT are the subdirectories in which the concurrent manager puts log and output files.
*APPLCSF variable need to be set in the prod.env(environmental variable), so that all product log files
$ APPLCSF/ $ APPLLOG à log files
$APPLCSF/ $ APPLOUT à out files
* concurrent manager log and out put files should go to $ FND_TOP/ $ APPLOG and $ FND _TOP / $APPLOUT if $ APPLCSF is not set.
62. How to submit concurrent programs through OS?
- From the operating system the utility.CONCSUB is used to submit is concurrent program.
- This is basically used to test a concurrent program.
- By using the WAIT token. The utility checks the request status every 60 seconds and returns the OS prompt upon completion of the request.
- Concurrent manager doesn’t abort, shutdown or start up until the concurrent request completes.
* If the concurrent program is compatible with itself, it can be checked for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
*PL/SQL procedures can submit a request to run a program as a concurrent process by calling.
FND_REQUEST.SUBMIT_REQUEST
* Before submitting a request, the following functions also should 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 request states?
- A PL/SQL procedure can check the status of a concurrent request by calling.
FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST
- FND_CONCURRENT.GET_REQUEST_STATUS
- This function returns the status of a concurrent request
- If the request is already computed, it also returns the completion message.
- This function returns both user-friendly (translatable) phase and status values as well as developer phase and status values that can drive program logic.
Syntax à Function FND_CONCURRENT.GET_REQUEST_STATUS
(request_id in out number,
application in varchar2 default null,
program in varchar2 default null,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEAN;
- When application and program are specified, the request_id of the last request for 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 request completion, then returns the request phase/status and completion message to the caller.
- Goes to sleep between checks for request completion.
Syntax àFND_CONCURRENT.WAIT_FOR_REQUEST
( request_id in number default null,
interval in number default 60,
max_wait in numbe default 0,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEN;
* FND_CONCURRENT.SET_COMPLETION_STATUS
- this function should be called from a concurrent program to set its 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;
normal status warning message à any message 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=. Fmb
Output_file=/forms?/.fmx
Module_type=form bath =no compile_all=special
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 by using 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_FUNCTION package.
* for this purpose, CALL_FORM built-in cannot be used since the Oracle Applications libraries do not support it.
*FND_FUNCTION.EXECUTE should be used to open a new session of a 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 already been opened.
*APP_NAVIGATE.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 of a 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 default FALSE);
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_target in varchar2 default null);
67. What is the reason for not getting any data when a multi org view is required?
- To get the data correctly, the xxx-ALL must be 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 not return any rows, 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 set the value in CLIENT INFO using set_org_contest.
- Execute fnd_client_info.Set_org_context (‘’);
- Now querying of multiorg views can be done.
68. How do you find that multiorg is installed?
- multi organization architecture is meant to allow multiple 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 of Multi org generally includes more than one business group.
* To know whether multiorg is existing or not
select multi_org_flag form fnd_product_groups)
- if the result is ‘Y’ means the database is group for multiorg
69. What are the triggers that fire on item?
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 triggers in forms
- Transactional triggers are the triggers that are related to accessing a data source.
- These triggers fire for each record that is marked for insert, update or table when forms would 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 .
* Important 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 Post and Commit transaction process. Before form builder processes any (changes) records to change.
Specifically it fires after form builder determines that there are inserts, updates or deletes in the form to post or commit, but before it commits the changes.
This trigger doesn’t fire when there is an attempt to commit, but validation determines that there are no changed records in the form.
This is a form level trigger.
Enter query mode should be set as ‘No’
This can be used to perform an action, such as setting up special locking requirements, at any time a database commit is going to occur.
If this trigger fails, the post and commit processes fail, no records are written to the database and focus remains in the current item.
If a DML is performed in a pre-commit trigger and it fails, ten manual rollback must be performed, because form builder doesn’t perform an automatic roll back.
This trigger fires in post and commit transactions.
Post – Commit Trigger
This is also known as post-commit trigger.
Post-commit trigger fires once during the post and commit transactions.
If there are records in the form that have been marked as inserts, updates or deletes, the post-forms-commit trigger fires after these changes have been written to the database but before form builder issues the database commit to finalize the transaction.
If the operation or application initiates a commit when there are no records in the form have been marked as inserts, updates or deletes, form builder fires post-forms-commit trigger immediately, without posting changes to the database.
This is a form level trigger.
Enter query mode should be set to ‘No’
Post-forms-commit trigger should be used to perform an action, such as updating an audit trial any time a database commit is about to occur.
If this trigger fails, post and commit processing aborts and form builder issues a ROLLBACK and decrements the internal save point counter.
This trigger fires in Post and Commit transactions.
What is Decode 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 it to each search condition.
If the expression is the same as search, result is returned.
If the default value is committed, a null value is returned where a search value does not match any of the result values.
DECODE function facilitates conditional inquiries by doing the work of a CASE or IF_THEN_ELSE statement.
DECODE (column, expression, search1, result1, search2, result2, …..);
Ex:- Select last_name, job, salary,
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 many functions, procedures and exceptions that can 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
SRW.Run_Report
SRW.Run_Report (command_line, char);
Executes specified R25 RUN Command
SRW.RUN_REPORT_FAILURE;
Stops report execution when failure of SRW.Run_Report occurs.
By using SRW.Run_Report, another report can be called to the screan from a button with in one report.
If this is used from a Report Trigger, BATCH=YES must be 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 Report Rep-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 statement char);
Executes specified SQL statement
* SRW.DO_SQL_FAILURE
Stops report execution upon SRW.Do_SQL failure.
* SRW.Message (msg_number number, msg_text char);
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 current frame, 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 format triggers.
They are used to change data that will be displayed in 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 that can be applied to multiple reports.
For example template can be defined that include the company logo and sets font colors for selected areas of a report.
When a report is created through the Report Wizard, there is an option of applying a template (.tdf file) to the report.
When a template is chosen, objects in the margin area of a template are imported into the same locations in the current report section, over writing any objects if exists.
The characteristics of the 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 current report section.
Any template properties, parameters, report triggers, program 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 report the 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 by Reports. xxx – Template_Reso (who xxx specified a Report style) (Tabular, Break Above)
iii) For each Report style for which the template is defined
- to the Reports.xxx – Template_Disc list, add the description that should be appeared on the template page of the 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 template file (file name.tdf) to
ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US
Preferences file:-
Windows à ORACLE_HOME\CAUPREFS.ORA (user preferences)
ORACLE_HOME\CAGPREFS.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 request set for three concurrent programs, which are having same parameter?
i) For the first report in the Report set, click on the parameters button and ender the parameters that are to the shared by all reports in the Request (Report) set.
ii) Go to the nest report and click the parameters button and list the same shared parameters.
iii) Do the above step for each and every report in the request set
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 at submission time.
* Request set wizard can be used to quickly create a new Request set in which all of the request run sequentially or all of the request run in parallel.
*sequentially à One after another
* parallel à All at once.
- The action can be set whether to continue processing or abort processing. If a request ends with the statues ‘Error’.
79. What are Global variables in Reports?
*Global variables are the variables that can 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 by Oracle 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 of the trigger as an argument for 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 enables query-find.
Coding logic for item relations such as dependent fields.
Coding messages to use message dictionary.
Adding FF logic if required.
Adding choices to the special menu and logic to modify choices the default menu and tool bar behavior is necessary.
Coding any other logic.
Creating a form function for the developed form and registering any sub functions.
Testing the form by itself.
Registering the form with AOL.
Adding the form function to a menu or creating custom menu.
Assigning the menu to the responsibility and assigning the responsibility to the user.
Testing the form within Oracle Applications.
Registering of Application, form and a concurrent program through Application developer Responsibility
Application:-
Responsibility à Application Developer
Form:-
Menu:-
Messages:-
Table:-
Sequence:-
Concurrent Program:-
Application Developer (Responsibility)
*Flex field
+Key
+Descriptive
-Test
*Concurrent
-Program
-Executable
-Library
*Application
-Register
-Form
-Function
-Menu
-Messages
+Database
+Lookups
+Validation
*Profile
*Attachments
- Document Entities
- Document Categories
- Attachment Functions
*Other
*Requests
- Run
-Set
-Profile
-Concurrent
-Change Organization
-Running Jobs
+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 cannot be made in Pl/SQL statements.
Bind references can be done in a PL/SQL statements.
Lexical parameters can be referenced by entering an ampersand () followed immediately by 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 must be defined so that report builder uses this value to validate the query with a lexical reference.
Token
If Oracle reports are executed by a concurrent program, (for Oracle Reports Program), then a keyword or a parameter with the same name as in the report builder, should be defined which for each parameter, which is known as token.
This is used to pass the parameters to the reports from the application (SRS Window)
Request Set:Request set is the group of requests, that can be submitted regularly using a single transaction.
Incompatibility
These are the list of programs that can be defined as incompatible with a certain program.
If any program is defined as incompatible to a particular program, then that program should not run simultaneously with the concurrent program, because they might interfere with its execution.
Application Developer Responsibility
Various Screens
Different Executable Methods
1 Host
2 Immediate
3 Java Stored Procedure
4 Java Concurrent Program
5 Multi Language Function
6 Oracle Reports
7 PL/SQL stored Procedure
8 Request set stage function
9 Spawned
10 SQL*Loader
11 SQL*Plus
Concurrent Library
Library types Transaction Library
User
Access Levels Extensible
System
List of values
List types Long List of Values
Poplist
No security
Security type Hierarchical Security
Non- Hierarchical Security
Char
Format type Date
Date time
Number
Standard date
Standard date time
Time
Validation types à Dependent
Independent
None
Pair
Special
Table
Translatable Independent
Translatable Dependent
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, concurrent programs and reports connect to.
86. What is a Responsibility?
- Responsibility defines Applications Privileges.
- A responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.
- Each user has at least one or more responsibilities and several users can share the same responsibility.
* Each responsibility allows access to
- a specific application or a set of applications.
- A set of books.
- A restricted list of windows that an user can navigate
- Reports in a specific application.
87. What are security Attributes?
- Security Attributes are used by Oracle self-service 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 set of changeable options that affects 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 of the flex field.
- Definition of the flex field.
90. What is an application /Module?
- Application is a collection of forms, function and menus.
91. What are Alerts?
- Alert is a mechanism that checks the database for a specific exception condition.
- An alert is characterized by the SQL select statements it contains.
- A SQL select statement fells the application what database 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
RECORD 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
(field declaration,…..);
identifier type_name;
Ex:- TYPE emp_record_type is RECORD
last_name varchar2(50),
job_id varchar2(10),
salary number(8,2));
emp_record emp_ record_type;
- fields declared as NOT NULL must be 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 (but not the same as) data base tables.
- INDEX BY Table is a primary key to provide the user with array-like access to rows.
- INDEX BY table is similar to an ARRAY.
- It can be increased in size dynamically because they are 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 unconstrained increase dynamically so that INDEX 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.
- The column can belong to any scalar or record data type, but the primary key must be ling to type BINARY_INTEGER.
- INDEX BY Tables cannot be initialized at the time of its declaration and also it cannot be populated at the time of declaration.
- An exploit executable statement is required to initialize (populate) the INDEX BY TABLE.
INDEX BY TABLE STRUCTURE
Unique identifier Column
…….
1
2
……
…
Gopi
Raj
….
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. COUNT
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 of a 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 about all of the fields of a data base table, the table of records greatly increases the functionality of 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 of type TABLE.
Nested Table contains multiple columns and can be used as variables, parameters, results, attributes and columns.
They can be thought of as one column database tables.
Rows of a nested table are not stored in any particular order.
The size of a nested table can be increased dynamically i.e. nested tables are unbounded.
Elements in a table initially have consecutive subscripts, but as elements are deleted, they can 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 must be used.
To delete elements, the built-in procedure DELETE must be used.
An uninitialized nested table is automatically null, so the IS NULL comparison operator can be used to sees if nested table is null.
The operators CAST, THE and MULTISET are used for manipulating nested tables.
1. Creation of a Nested Table
Defining an object type.
SQL> Create type ELEMENTS AS OBJECT
(ELEM_ID Number (6),
PRICE Number (7,2));
/
2. Create a table type ELEMENTS_TAB that 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 of type VARRAY.
VARRAYS can be used to associate a single identifier with an entire collection.
This allows manipulation of the collection as a whole and easy reference of individual elements.
The maximum size of VARRAY 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) VARRAY, then the VARRAY is automatically null.
The main use of VARRAY is to group small of uniform-sized collection of objects.
Elements of a VARRAY cannot be accessed individually SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript.
The type of the element of 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 implicitly defined.
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 cannot be compared for equality or in equality.
Creating a VARRAY:-
1. Defining object type 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 nested tables 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 database, nested tables do not retain their ordering and subscripts, where as Varrays do.
*Nested tables support indexes while VARRAYS do not.
Differences between conversions and Interfaces:-
Conversion Interface
1. Conversion is one-time process 1. Interface is the post production which is performed once before process. Production go on live (Pre-production process)
2. Data comes into Oracle Applications 2. Interface is the two integration of only (One way process) systems process
3. Interative Process 3. Scheduled and repetive process.
Oracle Reports – Trouble Shooting
1. Concurrent Request Logs: -
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 not resolved with log, then the report should be run through from the operating system.
Along with the standard report arguments, the report should be run along with the arguments passed by the concurrent manager.
If it is run successfully then the problem is with the environment from with the concurrent manager 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.
4. Running the Print Environment Variable Values Report: -
The concurrent manager inherits its environment variables from the shell from which it was started and then runs report using this environment.
This environment could be different from that a user sees logging in to the Applications because the concurrent manager may have been started by a different user with different environment settings.
Due to this difference, it is sometimes difficult to determine the cause of error in running reports.
To examine the values of few variables, prints environment variable values, Report to print out the variable as seen by the concurrent manager to see if is correct.
Very common and often problems such as a problem in compilation or the concurrent managers inability to locate a library happen due to incorrect REPORTS 60_PATH.
5. Emulate Concurrent Manager Environment:-
For UNIX platforms, to assist in determining where the problem lies, Oracle Applications AOL ships a program called $ FND_TOP / Srw / ar60run.oc.
This program helps to emulate the concurrent manager environment when testing reports from the OS command line.
This program writes all the environment variables and arguments passed to it in a log file ar60run.log. (Located by default in the $FND_TOP / $APPLLOG directory).
Save the ar60run.oc source code to a file named ar60run.oc compile it and 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 into fnd.mk which will, by default build an executable $FND_TOP / bin /ar60rund, which can be renamed to ar60run (new).
Submit the report from concurrent manager and look at ar60run.log.
Then run the report from OS with the same arguments as shown by ar60run.log shows using old ar60run.
Bitmapped Reports
Printer drivers should be provided with print style (Landscape) to determine how to print text files.
Bitmapped reports are not text files and these are output as postscript files.
The postscript file is asset of instructions telling the printer exactly a landscape report; the postscript file must be generated as landscape.
Frequently asked questions in Reports
1. Why does my report only fail from the concurrent manager?
This is because the environment from which the concurrent manager launches a report is different from the one when running the report from OS command line.
2. Why does my report show different data?
If the report shows different data when it is run as a standalone report, sometimes the data in the output may be different for different situations.
This is usually due 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, they should be re-enables, before the report is run through concurrent manager.
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 not in this file, then the error REP_0713 error.
4.Why do I get many pages of nonsense when I print my Report?
Postscript code should be recognized by the printer driver.
‘ enscript ’ program cannot be used for printing.
5. What does the ‘ SEP-0065 ’; virtual memory system error?
This error could be due to the following reasons.
* By default Oracle Reports uses / tmp directory to write temporary files, which may be getting full.
These files could be directed to another directory using the environment variable TMPDIR.
* Are the failing reports using page N or M? This can consume a lot of Oracle Reports virtual memory.
If possible, Reports should be run against a smaller database.
FND_PROGRAM Package
FND_PROGRAM.Executable:-
Procedure FND_PROGRAM. Executable IS
(executable in Varchar2,
application in varchar2, (full name)
short_name in varchar2, (executable short name)
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 default null);
For Java Concurrent Program.
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 default null,
save_output in varchar2 default ‘Y’,
print in varchar2 default ‘Y’,
cols in varchar2 default null,
rows in varchar2, default null,
style in varchar2, default null,
style_required in varchar2, default ‘N’,
printer in varchar2, default null,
Requets_Type in varchar2, default null,
Request_type_Application in varchar2 default null,
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’,
Restart in varchar2 default ‘Y’,
nls_complaint in varchar2 default ‘Y’,
icon_name in varchar2 default null,
language_code in varchar2, default ‘US’,
mls_function_short_name in varchar2 default null,
mls_function_application in varchar2 default null,
incrementor in varchar2 default null);
Property Classes
A property class is a named object that contains a list of properties and their settings.Once a property class is created, it can be assigned to any object.An object based on a property class can inherit the settings of any property in that property class.There can be number of properties 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 locally also.
Property class are separate objects and can be copied between modules if required.A property class can be sub classed in only number of modules.
Visual Attributes
Visual attributes are the font, color and pattern properties that can be set for 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 programmatically to change the font, color and pattern of the object at runtime.
Oracle Apps Technical Interview Questions And Answers
1. What is NOCOPY?
By default the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value.
NOCOPY: is a compiles hint that can be used with OUT and IN OUT parameter to request to 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 of a child object relative to its parent object. The end of the anchor should be attached to the parent object.
A) Parent Object B) Child Object
Since the size of the some layout objects may change when the report runs (When the data is actually fetched), anchors need to be defined to make the appearance of the object.
An Anchor defines the relative position of an object to the object to which if this anchored.
* Symbol is by pressing the shift key 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 parent object by 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 report builder to a program, which performs some function and then control returns to the Report Builder.
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 complex data manipulation.
Pass data to report builder from OS text files.
Support PL/SQL blocks.
Control real time devices be printer or robot.
5. Types of Triggers in Reports :-
* Report Triggers * Data model Triggers * Layout Triggers
Report Triggers :-
After Parameter form Trigger.
After Report Trigger
Before parameter form Trigger
Before Report Trigger.
Between Pages Trigger
+ 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 report is 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 first pages.
- This is used for customized page formatting.
* After Report Trigger
- Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mai lid etc….)
- This is used to clean up any initial processing that was done such as deleting the tables.
- This Trigger always fires irrespective of success or failure of the 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 must return a BOOLEAN value.
True ……. Includes the current record in the report.
False ……. Excludes the current record from the report.
* Formula Trigger
- 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
* Format Trigger.
- These are PL/SQL functions executed before the object is formatted.
- Used to dynamically change the formatting attributes of the object.
* Action Trigger
- These are Pl/SQL procedures executed when a button is selected in the run time previewer.
- 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 Placeholder columns.
Formulas are PL/SQL functions that populate formula or place holder columns.
Cannot be 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 report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is 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 set dynamically (Programmatically)
* The value can be set for a place holder column in the following places.
- Before report trigger if the place holder is a report level column.
- Report level formula column, if the place holder is a report level column.
- A formula in the place holders group below it (The value is set once for each record of the group)
9 Repeating Frame
- Repeating frame surrounds all of the 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 of it 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 of multiple queries.
- A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its 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.
- Routines can be modified online without interfering 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 security privileges.
* Improved performance.
- Reparsing for multiple users can be avoided by exploiting the shared SQL area.
- PL/SQL parsing at run-time can be avoided by pursing at compile time.
- Number of calls to the database can be reduced and network traffic decreased by bundling 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 PL/SQL statement.
* Must contain a RETURN clause in the header. Do not contain a RETURN
Clause in the header.
* Must return a value. Can return none, one or many
values.
* Must contain at fast one RETURN Can contain a RETURN
Statement. Statement.
* Do not contain OUT and INOUT 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:
* Implicit Cursors.
- Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL select statements, including queries that return only one row.
- Oracle Server implicitly opens a cursor to process each SQL statement not associated with on explicitly declared cursor.
- The most recent implicit cursor can be returned as the SQL cursor.
* Explicit Cursors
- For queries that return more than one row, explicit cursors are declared and named by the programmes and manipulated through specific statements in the block’s executable actions.
- Explicit cursors are used to individually process each row returned by a multiple-row SELECT statement.
- 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 most recent fetch doesn’t return a row.
% Found Boolean Evaluate to TRUE if the most recent fetch returns a row. Complement of % 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 different active set on each occasion for each execution, the previous cursor is closed and reopened with a new set of parameters.
- Sizes should not be mentioned for the data types of parameters the parameters names are for references in the query expression of the cursor.
17 Confined Mode: -
- If it is on, child objects cannot be moved outside their enclosing parent objects.
- If it is off child objects can be moved out sides their enclosing parent objects.
Flex Mode:-
- If it is on, parent borders stretch when child objects are moved against them.
- If it is off, parent borders remain fixed when child objects are moved against them.
18 Parameters
- A parameter is a variable whose value can be set at runtime (from the run time parameter of the command line).
- User parameters are created by the user and system parameters are created by Report Builder.
- System parameters cannot be 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 may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses of queries.
- Bind references cannot be referenced in FROM clauses.
- Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name.
- If the parameter / column is not created 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 SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
What is % Row type
- % Row types is used to declare a record based on a collection of columns in a database table or view.
- The fields in the record take their names and data types from the columns of the 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% row type.
Then emp_record will have a structure consisting of all the fields each representing a column in the employees table.
What is a Ref Cursor?
- Oracle server uses unnamed memory spaces to store data used in implicit cursors.
- Ref cursors are used to define a cursor variable, which will point to that memory space 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 block that terminates its main body of 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.
Ex:- when no rows are retrieved from the database in a SELECT statement, then error ORA-01403 occurs and the exception NO-DATA-FOUND is raised by PL/SQL.
Ex:- Exception can be raised explicitly by issuing the RAISE statement with in the block.
- The exception being raised may be either.
User-Defined or Pre Defined
Trapping an exception:-
- If the exception 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 successfully handles the exception, then the exception doesn’t propagate 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 exception 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. -à Implicitly Raised.
B) Non-Pre defined Oracle server exceptions. -à Implicitly Raised.
C) User-defined exceptions -à Explicitly Raised.
Pre-Defined Oracle Server Exceptions: -
- These are the error (20) that occurs most often 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 that the developer determines as abnormal.
These need to be declared and raised explicitly.
PRAGMA EXCEPTION_INIT
Statement is 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 cannot be used directly in SQL statements.
What is Dynamic SQL?
- Dynamic SQL is a SQL statement that contains variables that can change during runtime.
- It is a SQL statement with placeholders 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
- Basically Dynamic SQL means creating the SQL statements dynamically at runtime by using variables.
Ex:- Dynamic SQL can be used to create a procedure that operates on a table whose name is not known until runtime or to execute DDL/DCL/SCS statements.
----à In Pl/SQL such statements cannot be executed statically.
--à EXECUTE IMMEDIATE Statement can perform dynamic single row queries.
Declare
D_str varchar2 (200);
Val varchar2 (20);
Begin
D_str= insert into table1 values (;val);
Val= ‘ Bye’ Execute Immediate str using val;
end;
What are Autonomous Transactions?
- Autonomous transactions are the processes run independently of its parent.
- By means of Autonomous Transaction, the current transaction can be temporarily suspended and another operation can be begun.
- The basic idea behind this is to have some operation take place independently of the current transaction.
Ex: - to allow error messages written to table to be committed but to rollback everything else that has taken place prior to the error.
- The autonomous or child transaction can commit or rollback as applicable with the execution of the parent transaction being resumed upon its completion.
- The parent may then perform further operations of any 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 functionality internally, 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 get the 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 of the autonomous unit are executed.
- As the code unit exits and control returns to the parent the main (parent) transaction is resumed and the transaction context is switched back to the parent.
What is Bulk binding of Bulk collect?
Bulkbind:-
- The assignment of values 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 by minimizing the number of context switches between PL/SQL and SQL engines while they pass an entire collection of elements (varray, nested tables, index-by table or host array) as bind variables back and forth.
- Prior to Oracle 8i, 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 result of SELECT or FETCH statements.
- The keyword BULK COLLECT can be used with SELECT 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 point in time.
- In Oracle 7, it is termed 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.
Best Table -à The tables are that is (are) created by MVIEW create statement and that stores data that satisfy the MVIEW query.
Syntax:- Create materialized view
Refresh fast
Start with sysdate
Next sysdate +1 as
Select *from ;
- Since this is a fast refreshed MVIEW the master table should have a log (Master log) to record the changes on it that can be created by running.
Create materialized view log on master_table;
-à This statement creates 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 possibly also to the MVIEW.
- To keep a MVIEW data relatively current with the data in the master table, the MVIEW must be periodically refreshed.
* Refresh can be accomplished by one of the following procedures.
Dbms_mview.refresh (, )
Dbms _ refresh.refresh ()
Refresh Types -à Complete Refresh, Fast Refresh, Force Refresh
* Complete Refresh is performed by deleting the rows from the snapshot and inserting the rows satisfying the MVIEW query.
* In Fast refresh only the rows updated since last refresh are pulled from the master table to insert into MVIEW.
* This requires a log table called as MVIEW Log to be created on the Master Table.
* Force refresh first tries to run a Fast refresh if possible.
* If fast refresh is not possible, it performs complete refresh.
Refresh Groups
- These are used to refresh multiple snapshots in a transitionally consistent manner.
- When a refresh group is refreshed all MVIEWS in that group are populated with data from a consistent point in time.
- Refresh groups are managed by using 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-only materialized views
- DML cannot be performed on the snapshots in this category.
2 Up datable materialized views
- These MVIEWS eliminates the restriction of DML’s on snapshots.
3 Sub query materialized views
- These are the MVIEW’S that are created with sub queries in the WHERE clause of a 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 primary key for refresh are called primary key MVIE’S (Oracle 8).
* Fast refresh requires some association (mapping) Between rows at snapshot and master tables.
5 Multifier materialized views (Oracle 9)
- In this type MVIEW, its master table itself is a MVIEW.
- This feature enables fast refresh of MVIEW’S that have MVIEW’S as their masters.
- Many companies are structured on at least three levels
A) International
B) National
C) Local
- Many nodes at both the national and local levels are required
- The best possible solution in such cases is to use multifier MVIEW’S.
6 Simple Vs Complex MVIEW’S.
- MVIEW’S being simple or complex determines whether it can be fast refreshed or not.
- A MVIEW is fast refreshable if it is simple.
- A MVIEW is not fast refreshable if it is complex.
- A MVIEW can be considered CONNECT BY, INTERSECT, MINUS or UNION or UNION ALL clauses in its destining 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 warehouse or decision support systems.
Snapshots
- Snapshots are mirror or replace of tables in a single point of time.
- A Snapshot is a local copy of a table data that originates from one or more remote master tables.
- To keep a snapshots data current with the data of its master the Oracle server must periodically refresh 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 cannot be updated.
* A snapshot contains a complete or partial copy of a target master table from a single point in time.
- A snapshot may be read only or updatable.
26. How duplicate rows are deleted?
- Duplicate rows are deleted by using ROWID
Syntax à delete from Where ROWID not in (Select max (ROWID) from
Group by );
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)
Development Tools à ;
28. Difference between IN and OUT parameters.
Three types of parameters
1. IN 2. OUT 3.IN OUT
IN parameter:
- This parameter passes a values from the calling environment into the procedure.
- This is the default mode.
- A formal parameter of In mode cannot be assigned a value (we IN parameter cannot be modified in the body of the procedure)
- IN parameters can be assigned a default value in the parameter list.
- IN parameters are passed by reference.
OUT parameters:
- OUT parameter must be assigned a value before returning to the calling environment.
- OUT parameter passes a value from the procedure to the calling environment
- OUT parameter cannot be assigned a default value in the parameter list.
IN OUT parameter:
- This type of parameter pass a value from the calling environment into the procedure and a possibly different value from the procedure back to calling environment using the same parameter.
- IN OUT parameter cannot be assigned a default value. * By default OUT & IN OUT parameters are passed by value.
- These can be passed by reference 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 implicitly whenever 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 shut down) occurs on a schema or database.
- Executes implicitly when 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 implicitly when some user or data base system actions occur.
- Ex. When user logs on to the system.
When DBA shuts down the database.
- 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 what action 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 database or a schema.
- With a database, 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 - CURSORS. Data bases initialization parameter. (default value is 50).
- * A triggering statement should 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 on which the table or view causes the trigger to fire.
3 Trigger Type Statement, Row
- Determines how many times the trigger body executes
4 Table name Table, View
5 Trigger body à PL/SQL – block
- Determines what actions the trigger should perform.
* INSTEAD of triggers are used to provide a transparent way to modifying views that cannot be modified directly through SQL, DML statements because the view is not modifiable.
-à INSTEAD of triggers provide writing of Insert, Update and Delete statements against the view.
- The INSTEAD if trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
- INSTEAD of trigger execute the trigger body instead of the triggering statement.
Statement Triggers
- In this type of triggers, the trigger body executes once for the triggering event.
- This is the default.
- Statement trigger 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 cannot be modified by normal DML if the view query contains set operators, group functions, group by, connect By, start with clauses or joins.
Mutating Table
- A Mutating table is a table that is currently being modified by an UPDATE, DELETE OR INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
- A table is not considered mutating for statement triggers.
- A mutating table cannot be changed because the resulting DML could change data that is in consistent state.
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 that can be used to tune SQL operations.
- The SQL – Trace facility can be enabled / disabled for an individual session or at the instance level.
- If the initialization parameter SQL-TRACE is set to 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 at the system/session level by using.
Alter system/session set SQL-TRACE = TRUE/FALSE.
Explain Plan
- Explain plan command generates information that details the execution plan that will be used on a particular query.
- Uses a pre created table (PLAN_TABLE) in the current schema to store information about the 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 script creates an output table, 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 statement into 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 format the plan table data.
$ ORACLE_HOME / rdbms / admin / utlxpls. Sql – to format serial explain plans.
$ ORACLE_HOME/ rdbms/admin/utlxpil. Sql – to format parallel explain plans.
* AUTOTRACE
- The AUTOTRACE facility in SQL* plus allows analysts to view the execution pan d some useful statistics for a SQL statement within a SQL*plus session.
- AUTOTRACE needs to be initiated in the SQL*Plus session prior to executing the statement.
SET AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS]
- As with the explain plan command, to obtain an execution plan the PLAN-TABLE must be 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 must have access to dynamic performance tables.
- To achieve this, grant PLUS TRACE role to the user.
PLUS TRACE role is created by the plus trace. Sql script
$ ORACLE_HOME / sql plus admin SYS user must run this script.
DBA can them grant the role to the users who wish to use the AUTOTRACE.
TK PROF
- Tk prof facility accepts as input a SQL Trace File and produces a formatted output file.
- Tk Prof Filename_source filename_output EXPLAIN = [user name / password] sys = [yes/no] TABLE = [Table Name]
A) How do you add trace to a report?
- By using the 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 object data types.
BLOB: Represents a binary large object
CLOB: Represents a character large object
NCLOB: Represents a multibyte character object.
BFILE: Represents a binary file store of in an os binary file outside the database.
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 different segment unless 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’t contain 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.
- These are stored 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_LOB.READ of DBMS_ LOB.WRITE are used to manipulate LOBS.
Oracle Applications Architecture
- Internet computing Architecture is a framework for 3-tired, distributed computing that supports 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 Internet computing architecture, only the presentation layer of Oracle Applications is on the Desk Top tier in the form of a plug-in to a standard Internet browser.
TEMPLATE FORM
- The TEMPLATE form is the required starting point for all development of new forms.
- The development of a new form is started by copying the TEMPLATE.fmb file, located in $ AU_top / forms/ us, to a local directory and renaming it as appropriate.
TEMPLATE FORM CONTAINS THE FOLLOWING:-
*Platform-independent references to object groups in the APPSTAND form
(STANDARD _PCAND_VA,
STANDARD_TOOLBAR,
STANDARD_CALENDAR)
*Platform–independent attachments of several libraries .
FND SQF
APPCORE
APPDAYPK
*Several form level triggers with required code.
*Program units that include a specification and a body for the package APP_CUSTOM, which contains default behaviors for window opening and closing events.
-In general this code should be modified for the specific form under development.
* The application’s color pallete, containing the two colors required by the referenced visual attributes.
* Many referenced objects that support the calendar, the toolbar, alternative regions and the menu.
* Template form contains simple objects that show 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 plat form-independent attachments of several libraries.
- Some of the libraries are attached directly to the TEMPLATE (FNDSOF, APPCORE, APPDAYPK) while the others are attached to these libraries.
APPCORE, APPDAYPK, FNDSQF, CUSTOM, GLOBE, VERT, JA, JE, JL
APPCORE
- Contains the packages and procedures that are required of all forms to support the Menu, Toolbar and other required standard behaviors.
- Procedures and functions in APPCORE have names beginning with APP.
APPDAYPK
- Contains the packages that control the Oracle applications calendar feature.
FINDSQF
- Contains packages and procedures for message dictionary, flex fields, profiles and concurrent processing.
- It also has various other utilities for navigation, MRG, WHO etc.
- Procedures and functions have names beginning with FND.
CUSTOM
- Custom library allows extension of Oracle Applications forms without modification of Oracle applications code.
- Custom library can 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 must be in upper case) and disabling fields that are not required for a particular site.
- All logic must branch 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 statement that is embedded in a clause of another 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 query is one way of reading (data) every row in a table and comparing values in each row against related data.
- Oracle server performs correlated sub query when 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 query is evaluated once for each row processed by the parent statement.
GET
Candidate row from the outer query
EXECUTE
Inner query using the candidate row value
USE
Values from inner query to qualify / disqualify candidate row
Ex:- Select last_name, salary, department_id from employers OUTER
where salary> (select AVG (Salary) from employees
where department id = outer.department_id);
Types of Joins
1 Equi Join
2 Non-Equi Join
3 Outer Join
4 self Join
Equi Join:-
- Is also called simple or inner join.
- An equi join is a join condition that contains equality sign.
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 that do not meet the 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 deficient in information.
- This operator has the effect of creating one or more null rows to each one or more rows from the (join condition) non deficient table can be joined.
Self Join
Self join is used to join a table to itself.
Ex:- to find the name of each 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.STYLEà FUNCTION
CUSTOM.EVENTà PROCEDURE.
* 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 statement where as post-query executes 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
How to get second parameter value based on first parameter?
$flex
$value setname.
What is Ref Cursor?
Ref cursor is a data type and executed at server side and with ref cursor multiple select statements can be executed.
By increase the binary size, that number of records 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.fmb)
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 separately in 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 many applications.
When a packaged PL/SQL construct is called for the first time 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 part of 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 of different 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 about the 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_UPDATE and 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 built in USER.
APPCORE API’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
FNDSQF API’S
FND_CURRENCY
FND_DATE
FND_GLOBAL
FND_ORG
FND_STANDARD
FND_UTILITIES.OPEN_URL
FND_UTILITIES. PARAM_EXISTS
How to call flex fields in the form?
By using FND_FLEX.EVENT (EVENT varchar2)
How to register an executable and define a concurrent program through backend?
By using concurrent processing API’S
1. FND_CONC_GLOBAL.REQUEST_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
- Is used to define a concurrent program executable
- It takes 8 parameters (all are IN mode)
Syntax procedure FND_PROGRAM.EXECUTABLE
(Executable IN varchar2,
(Full name) à application IN varchar2,
(Executable short name) à short name IN varchar2, Description IN varchar2 default null, execution_method IN varchar2,
execution_file_name IN varchar2 default null,
(only for spawned & Immedaite) à subroutine_name IN varchar2 default null,icon_name IN varchar2 default null,language_code IN varchar2 default (VS)(for Java Conc Program) à execution_file_path IN varchar2 default null,
* FND_PROGRAM.REGISTER
- This procedure no used to define a concument program.
- 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, default null,
(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’t check for the existence of the registered table or column in the data base schema, but only updates the required SQL tables.
- It should be ensured that, all the tables and columns registered exist actually and have the same format as that defined using AD_DD API.
- Views need not be registered.
57. How to write to a file through concurrent program?
* 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 output files.
à FND_FILE supports a maximum buffer line size of 32k for both and output files.
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
- this procedure as used to set the temporary log file and output filenames and the temporary directory 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 default null,
program in varchar2 default null,
description in varchar2 default null,
start-time in varchar2 default null,
sub_request in boolean default False,
argument1,
arguemnt2,
argument 100) return number;
* If this is submitted from oracle forms, all the arguments (1 to 100 ) must be 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 current value of the specified 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 set who 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’t exist, it can also be created with PUT.
Procedure FND_ PROFILE.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 that can 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_Output and Gone_Print_Style.
- SA’s and end users also cannot see the values or change these values.
In which directory log and output files are stored?
* APPLCSF is the top-level directory in which the concurrent manager puts logs and output files.
* APPLLOG and APPLOUT are the subdirectories in which the concurrent manager puts log and output files.
*APPLCSF variable need to be set in the prod.env(environmental variable), so that all product log files
$ APPLCSF/ $ APPLLOG à log files
$APPLCSF/ $ APPLOUT à out files
* concurrent manager log and out put files should go to $ FND_TOP/ $ APPLOG and $ FND _TOP / $APPLOUT if $ APPLCSF is not set.
62. How to submit concurrent programs through OS?
- From the operating system the utility.CONCSUB is used to submit is concurrent program.
- This is basically used to test a concurrent program.
- By using the WAIT token. The utility checks the request status every 60 seconds and returns the OS prompt upon completion of the request.
- Concurrent manager doesn’t abort, shutdown or start up until the concurrent request completes.
* If the concurrent program is compatible with itself, it can be checked for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
*PL/SQL procedures can submit a request to run a program as a concurrent process by calling.
FND_REQUEST.SUBMIT_REQUEST
* Before submitting a request, the following functions also should 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 request states?
- A PL/SQL procedure can check the status of a concurrent request by calling.
FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST
- FND_CONCURRENT.GET_REQUEST_STATUS
- This function returns the status of a concurrent request
- If the request is already computed, it also returns the completion message.
- This function returns both user-friendly (translatable) phase and status values as well as developer phase and status values that can drive program logic.
Syntax à Function FND_CONCURRENT.GET_REQUEST_STATUS
(request_id in out number,
application in varchar2 default null,
program in varchar2 default null,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEAN;
- When application and program are specified, the request_id of the last request for 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 request completion, then returns the request phase/status and completion message to the caller.
- Goes to sleep between checks for request completion.
Syntax àFND_CONCURRENT.WAIT_FOR_REQUEST
( request_id in number default null,
interval in number default 60,
max_wait in numbe default 0,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEN;
* FND_CONCURRENT.SET_COMPLETION_STATUS
- this function should be called from a concurrent program to set its 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;
normal status warning message à any message 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=. Fmb
Output_file=/forms?/.fmx
Module_type=form bath =no compile_all=special
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 by using 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_FUNCTION package.
* for this purpose, CALL_FORM built-in cannot be used since the Oracle Applications libraries do not support it.
*FND_FUNCTION.EXECUTE should be used to open a new session of a 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 already been opened.
*APP_NAVIGATE.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 of a 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 default FALSE);
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_target in varchar2 default null);
67. What is the reason for not getting any data when a multi org view is required?
- To get the data correctly, the xxx-ALL must be 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 not return any rows, 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 set the value in CLIENT INFO using set_org_contest.
- Execute fnd_client_info.Set_org_context (‘’);
- Now querying of multiorg views can be done.
68. How do you find that multiorg is installed?
- multi organization architecture is meant to allow multiple 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 of Multi org generally includes more than one business group.
* To know whether multiorg is existing or not
select multi_org_flag form fnd_product_groups)
- if the result is ‘Y’ means the database is group for multiorg
69. What are the triggers that fire on item?
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 triggers in forms
- Transactional triggers are the triggers that are related to accessing a data source.
- These triggers fire for each record that is marked for insert, update or table when forms would 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 .
* Important 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 Post and Commit transaction process. Before form builder processes any (changes) records to change.
Specifically it fires after form builder determines that there are inserts, updates or deletes in the form to post or commit, but before it commits the changes.
This trigger doesn’t fire when there is an attempt to commit, but validation determines that there are no changed records in the form.
This is a form level trigger.
Enter query mode should be set as ‘No’
This can be used to perform an action, such as setting up special locking requirements, at any time a database commit is going to occur.
If this trigger fails, the post and commit processes fail, no records are written to the database and focus remains in the current item.
If a DML is performed in a pre-commit trigger and it fails, ten manual rollback must be performed, because form builder doesn’t perform an automatic roll back.
This trigger fires in post and commit transactions.
Post – Commit Trigger
This is also known as post-commit trigger.
Post-commit trigger fires once during the post and commit transactions.
If there are records in the form that have been marked as inserts, updates or deletes, the post-forms-commit trigger fires after these changes have been written to the database but before form builder issues the database commit to finalize the transaction.
If the operation or application initiates a commit when there are no records in the form have been marked as inserts, updates or deletes, form builder fires post-forms-commit trigger immediately, without posting changes to the database.
This is a form level trigger.
Enter query mode should be set to ‘No’
Post-forms-commit trigger should be used to perform an action, such as updating an audit trial any time a database commit is about to occur.
If this trigger fails, post and commit processing aborts and form builder issues a ROLLBACK and decrements the internal save point counter.
This trigger fires in Post and Commit transactions.
What is Decode 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 it to each search condition.
If the expression is the same as search, result is returned.
If the default value is committed, a null value is returned where a search value does not match any of the result values.
DECODE function facilitates conditional inquiries by doing the work of a CASE or IF_THEN_ELSE statement.
DECODE (column, expression, search1, result1, search2, result2, …..);
Ex:- Select last_name, job, salary,
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 many functions, procedures and exceptions that can 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
SRW.Run_Report
SRW.Run_Report (command_line, char);
Executes specified R25 RUN Command
SRW.RUN_REPORT_FAILURE;
Stops report execution when failure of SRW.Run_Report occurs.
By using SRW.Run_Report, another report can be called to the screan from a button with in one report.
If this is used from a Report Trigger, BATCH=YES must be 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 Report Rep-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 statement char);
Executes specified SQL statement
* SRW.DO_SQL_FAILURE
Stops report execution upon SRW.Do_SQL failure.
* SRW.Message (msg_number number, msg_text char);
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 current frame, 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 format triggers.
They are used to change data that will be displayed in 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 that can be applied to multiple reports.
For example template can be defined that include the company logo and sets font colors for selected areas of a report.
When a report is created through the Report Wizard, there is an option of applying a template (.tdf file) to the report.
When a template is chosen, objects in the margin area of a template are imported into the same locations in the current report section, over writing any objects if exists.
The characteristics of the 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 current report section.
Any template properties, parameters, report triggers, program 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 report the 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 by Reports. xxx – Template_Reso (who xxx specified a Report style) (Tabular, Break Above)
iii) For each Report style for which the template is defined
- to the Reports.xxx – Template_Disc list, add the description that should be appeared on the template page of the 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 template file (file name.tdf) to
ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US
Preferences file:-
Windows à ORACLE_HOME\CAUPREFS.ORA (user preferences)
ORACLE_HOME\CAGPREFS.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 request set for three concurrent programs, which are having same parameter?
i) For the first report in the Report set, click on the parameters button and ender the parameters that are to the shared by all reports in the Request (Report) set.
ii) Go to the nest report and click the parameters button and list the same shared parameters.
iii) Do the above step for each and every report in the request set
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 at submission time.
* Request set wizard can be used to quickly create a new Request set in which all of the request run sequentially or all of the request run in parallel.
*sequentially à One after another
* parallel à All at once.
- The action can be set whether to continue processing or abort processing. If a request ends with the statues ‘Error’.
79. What are Global variables in Reports?
*Global variables are the variables that can 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 by Oracle 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 of the trigger as an argument for 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 enables query-find.
Coding logic for item relations such as dependent fields.
Coding messages to use message dictionary.
Adding FF logic if required.
Adding choices to the special menu and logic to modify choices the default menu and tool bar behavior is necessary.
Coding any other logic.
Creating a form function for the developed form and registering any sub functions.
Testing the form by itself.
Registering the form with AOL.
Adding the form function to a menu or creating custom menu.
Assigning the menu to the responsibility and assigning the responsibility to the user.
Testing the form within Oracle Applications.
Registering of Application, form and a concurrent program through Application developer Responsibility
Application:-
Responsibility à Application Developer
Form:-
Menu:-
Messages:-
Table:-
Sequence:-
Concurrent Program:-
Application Developer (Responsibility)
*Flex field
+Key
+Descriptive
-Test
*Concurrent
-Program
-Executable
-Library
*Application
-Register
-Form
-Function
-Menu
-Messages
+Database
+Lookups
+Validation
*Profile
*Attachments
- Document Entities
- Document Categories
- Attachment Functions
*Other
*Requests
- Run
-Set
-Profile
-Concurrent
-Change Organization
-Running Jobs
+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 cannot be made in Pl/SQL statements.
Bind references can be done in a PL/SQL statements.
Lexical parameters can be referenced by entering an ampersand () followed immediately by 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 must be defined so that report builder uses this value to validate the query with a lexical reference.
Token
If Oracle reports are executed by a concurrent program, (for Oracle Reports Program), then a keyword or a parameter with the same name as in the report builder, should be defined which for each parameter, which is known as token.
This is used to pass the parameters to the reports from the application (SRS Window)
Request Set:Request set is the group of requests, that can be submitted regularly using a single transaction.
Incompatibility
These are the list of programs that can be defined as incompatible with a certain program.
If any program is defined as incompatible to a particular program, then that program should not run simultaneously with the concurrent program, because they might interfere with its execution.
Application Developer Responsibility
Various Screens
Different Executable Methods
1 Host
2 Immediate
3 Java Stored Procedure
4 Java Concurrent Program
5 Multi Language Function
6 Oracle Reports
7 PL/SQL stored Procedure
8 Request set stage function
9 Spawned
10 SQL*Loader
11 SQL*Plus
Concurrent Library
Library types Transaction Library
User
Access Levels Extensible
System
List of values
List types Long List of Values
Poplist
No security
Security type Hierarchical Security
Non- Hierarchical Security
Char
Format type Date
Date time
Number
Standard date
Standard date time
Time
Validation types à Dependent
Independent
None
Pair
Special
Table
Translatable Independent
Translatable Dependent
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, concurrent programs and reports connect to.
86. What is a Responsibility?
- Responsibility defines Applications Privileges.
- A responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.
- Each user has at least one or more responsibilities and several users can share the same responsibility.
* Each responsibility allows access to
- a specific application or a set of applications.
- A set of books.
- A restricted list of windows that an user can navigate
- Reports in a specific application.
87. What are security Attributes?
- Security Attributes are used by Oracle self-service 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 set of changeable options that affects 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 of the flex field.
- Definition of the flex field.
90. What is an application /Module?
- Application is a collection of forms, function and menus.
91. What are Alerts?
- Alert is a mechanism that checks the database for a specific exception condition.
- An alert is characterized by the SQL select statements it contains.
- A SQL select statement fells the application what database 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
RECORD 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
(field declaration,…..);
identifier type_name;
Ex:- TYPE emp_record_type is RECORD
last_name varchar2(50),
job_id varchar2(10),
salary number(8,2));
emp_record emp_ record_type;
- fields declared as NOT NULL must be 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 (but not the same as) data base tables.
- INDEX BY Table is a primary key to provide the user with array-like access to rows.
- INDEX BY table is similar to an ARRAY.
- It can be increased in size dynamically because they are 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 unconstrained increase dynamically so that INDEX 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.
- The column can belong to any scalar or record data type, but the primary key must be ling to type BINARY_INTEGER.
- INDEX BY Tables cannot be initialized at the time of its declaration and also it cannot be populated at the time of declaration.
- An exploit executable statement is required to initialize (populate) the INDEX BY TABLE.
INDEX BY TABLE STRUCTURE
Unique identifier Column
…….
1
2
……
…
Gopi
Raj
….
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. COUNT
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 of a 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 about all of the fields of a data base table, the table of records greatly increases the functionality of 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 of type TABLE.
Nested Table contains multiple columns and can be used as variables, parameters, results, attributes and columns.
They can be thought of as one column database tables.
Rows of a nested table are not stored in any particular order.
The size of a nested table can be increased dynamically i.e. nested tables are unbounded.
Elements in a table initially have consecutive subscripts, but as elements are deleted, they can 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 must be used.
To delete elements, the built-in procedure DELETE must be used.
An uninitialized nested table is automatically null, so the IS NULL comparison operator can be used to sees if nested table is null.
The operators CAST, THE and MULTISET are used for manipulating nested tables.
1. Creation of a Nested Table
Defining an object type.
SQL> Create type ELEMENTS AS OBJECT
(ELEM_ID Number (6),
PRICE Number (7,2));
/
2. Create a table type ELEMENTS_TAB that 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 of type VARRAY.
VARRAYS can be used to associate a single identifier with an entire collection.
This allows manipulation of the collection as a whole and easy reference of individual elements.
The maximum size of VARRAY 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) VARRAY, then the VARRAY is automatically null.
The main use of VARRAY is to group small of uniform-sized collection of objects.
Elements of a VARRAY cannot be accessed individually SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript.
The type of the element of 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 implicitly defined.
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 cannot be compared for equality or in equality.
Creating a VARRAY:-
1. Defining object type 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 nested tables 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 database, nested tables do not retain their ordering and subscripts, where as Varrays do.
*Nested tables support indexes while VARRAYS do not.
Differences between conversions and Interfaces:-
Conversion Interface
1. Conversion is one-time process 1. Interface is the post production which is performed once before process. Production go on live (Pre-production process)
2. Data comes into Oracle Applications 2. Interface is the two integration of only (One way process) systems process
3. Interative Process 3. Scheduled and repetive process.
Oracle Reports – Trouble Shooting
1. Concurrent Request Logs: -
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 not resolved with log, then the report should be run through from the operating system.
Along with the standard report arguments, the report should be run along with the arguments passed by the concurrent manager.
If it is run successfully then the problem is with the environment from with the concurrent manager 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.
4. Running the Print Environment Variable Values Report: -
The concurrent manager inherits its environment variables from the shell from which it was started and then runs report using this environment.
This environment could be different from that a user sees logging in to the Applications because the concurrent manager may have been started by a different user with different environment settings.
Due to this difference, it is sometimes difficult to determine the cause of error in running reports.
To examine the values of few variables, prints environment variable values, Report to print out the variable as seen by the concurrent manager to see if is correct.
Very common and often problems such as a problem in compilation or the concurrent managers inability to locate a library happen due to incorrect REPORTS 60_PATH.
5. Emulate Concurrent Manager Environment:-
For UNIX platforms, to assist in determining where the problem lies, Oracle Applications AOL ships a program called $ FND_TOP / Srw / ar60run.oc.
This program helps to emulate the concurrent manager environment when testing reports from the OS command line.
This program writes all the environment variables and arguments passed to it in a log file ar60run.log. (Located by default in the $FND_TOP / $APPLLOG directory).
Save the ar60run.oc source code to a file named ar60run.oc compile it and 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 into fnd.mk which will, by default build an executable $FND_TOP / bin /ar60rund, which can be renamed to ar60run (new).
Submit the report from concurrent manager and look at ar60run.log.
Then run the report from OS with the same arguments as shown by ar60run.log shows using old ar60run.
Bitmapped Reports
Printer drivers should be provided with print style (Landscape) to determine how to print text files.
Bitmapped reports are not text files and these are output as postscript files.
The postscript file is asset of instructions telling the printer exactly a landscape report; the postscript file must be generated as landscape.
Frequently asked questions in Reports
1. Why does my report only fail from the concurrent manager?
This is because the environment from which the concurrent manager launches a report is different from the one when running the report from OS command line.
2. Why does my report show different data?
If the report shows different data when it is run as a standalone report, sometimes the data in the output may be different for different situations.
This is usually due 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, they should be re-enables, before the report is run through concurrent manager.
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 not in this file, then the error REP_0713 error.
4.Why do I get many pages of nonsense when I print my Report?
Postscript code should be recognized by the printer driver.
‘ enscript ’ program cannot be used for printing.
5. What does the ‘ SEP-0065 ’; virtual memory system error?
This error could be due to the following reasons.
* By default Oracle Reports uses / tmp directory to write temporary files, which may be getting full.
These files could be directed to another directory using the environment variable TMPDIR.
* Are the failing reports using page N or M? This can consume a lot of Oracle Reports virtual memory.
If possible, Reports should be run against a smaller database.
FND_PROGRAM Package
FND_PROGRAM.Executable:-
Procedure FND_PROGRAM. Executable IS
(executable in Varchar2,
application in varchar2, (full name)
short_name in varchar2, (executable short name)
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 default null);
For Java Concurrent Program.
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 default null,
save_output in varchar2 default ‘Y’,
print in varchar2 default ‘Y’,
cols in varchar2 default null,
rows in varchar2, default null,
style in varchar2, default null,
style_required in varchar2, default ‘N’,
printer in varchar2, default null,
Requets_Type in varchar2, default null,
Request_type_Application in varchar2 default null,
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’,
Restart in varchar2 default ‘Y’,
nls_complaint in varchar2 default ‘Y’,
icon_name in varchar2 default null,
language_code in varchar2, default ‘US’,
mls_function_short_name in varchar2 default null,
mls_function_application in varchar2 default null,
incrementor in varchar2 default null);
Property Classes
A property class is a named object that contains a list of properties and their settings.Once a property class is created, it can be assigned to any object.An object based on a property class can inherit the settings of any property in that property class.There can be number of properties 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 locally also.
Property class are separate objects and can be copied between modules if required.A property class can be sub classed in only number of modules.
Visual Attributes
Visual attributes are the font, color and pattern properties that can be set for 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 programmatically to change the font, color and pattern of the object at runtime.
By default the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value.
NOCOPY: is a compiles hint that can be used with OUT and IN OUT parameter to request to 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 of a child object relative to its parent object. The end of the anchor should be attached to the parent object.
A) Parent Object B) Child Object
Since the size of the some layout objects may change when the report runs (When the data is actually fetched), anchors need to be defined to make the appearance of the object.
An Anchor defines the relative position of an object to the object to which if this anchored.
* Symbol is by pressing the shift key 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 parent object by 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 report builder to a program, which performs some function and then control returns to the Report Builder.
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 complex data manipulation.
Pass data to report builder from OS text files.
Support PL/SQL blocks.
Control real time devices be printer or robot.
5. Types of Triggers in Reports :-
* Report Triggers * Data model Triggers * Layout Triggers
Report Triggers :-
After Parameter form Trigger.
After Report Trigger
Before parameter form Trigger
Before Report Trigger.
Between Pages Trigger
+ 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 report is 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 first pages.
- This is used for customized page formatting.
* After Report Trigger
- Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mai lid etc….)
- This is used to clean up any initial processing that was done such as deleting the tables.
- This Trigger always fires irrespective of success or failure of the 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 must return a BOOLEAN value.
True ……. Includes the current record in the report.
False ……. Excludes the current record from the report.
* Formula Trigger
- 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
* Format Trigger.
- These are PL/SQL functions executed before the object is formatted.
- Used to dynamically change the formatting attributes of the object.
* Action Trigger
- These are Pl/SQL procedures executed when a button is selected in the run time previewer.
- 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 Placeholder columns.
Formulas are PL/SQL functions that populate formula or place holder columns.
Cannot be 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 report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is 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 set dynamically (Programmatically)
* The value can be set for a place holder column in the following places.
- Before report trigger if the place holder is a report level column.
- Report level formula column, if the place holder is a report level column.
- A formula in the place holders group below it (The value is set once for each record of the group)
9 Repeating Frame
- Repeating frame surrounds all of the 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 of it 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 of multiple queries.
- A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its 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.
- Routines can be modified online without interfering 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 security privileges.
* Improved performance.
- Reparsing for multiple users can be avoided by exploiting the shared SQL area.
- PL/SQL parsing at run-time can be avoided by pursing at compile time.
- Number of calls to the database can be reduced and network traffic decreased by bundling 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 PL/SQL statement.
* Must contain a RETURN clause in the header. Do not contain a RETURN
Clause in the header.
* Must return a value. Can return none, one or many
values.
* Must contain at fast one RETURN Can contain a RETURN
Statement. Statement.
* Do not contain OUT and INOUT 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:
* Implicit Cursors.
- Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL select statements, including queries that return only one row.
- Oracle Server implicitly opens a cursor to process each SQL statement not associated with on explicitly declared cursor.
- The most recent implicit cursor can be returned as the SQL cursor.
* Explicit Cursors
- For queries that return more than one row, explicit cursors are declared and named by the programmes and manipulated through specific statements in the block’s executable actions.
- Explicit cursors are used to individually process each row returned by a multiple-row SELECT statement.
- 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 most recent fetch doesn’t return a row.
% Found Boolean Evaluate to TRUE if the most recent fetch returns a row. Complement of % 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 different active set on each occasion for each execution, the previous cursor is closed and reopened with a new set of parameters.
- Sizes should not be mentioned for the data types of parameters the parameters names are for references in the query expression of the cursor.
17 Confined Mode: -
- If it is on, child objects cannot be moved outside their enclosing parent objects.
- If it is off child objects can be moved out sides their enclosing parent objects.
Flex Mode:-
- If it is on, parent borders stretch when child objects are moved against them.
- If it is off, parent borders remain fixed when child objects are moved against them.
18 Parameters
- A parameter is a variable whose value can be set at runtime (from the run time parameter of the command line).
- User parameters are created by the user and system parameters are created by Report Builder.
- System parameters cannot be 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 may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses of queries.
- Bind references cannot be referenced in FROM clauses.
- Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name.
- If the parameter / column is not created 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 SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
What is % Row type
- % Row types is used to declare a record based on a collection of columns in a database table or view.
- The fields in the record take their names and data types from the columns of the 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% row type.
Then emp_record will have a structure consisting of all the fields each representing a column in the employees table.
What is a Ref Cursor?
- Oracle server uses unnamed memory spaces to store data used in implicit cursors.
- Ref cursors are used to define a cursor variable, which will point to that memory space 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 block that terminates its main body of 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.
Ex:- when no rows are retrieved from the database in a SELECT statement, then error ORA-01403 occurs and the exception NO-DATA-FOUND is raised by PL/SQL.
Ex:- Exception can be raised explicitly by issuing the RAISE statement with in the block.
- The exception being raised may be either.
User-Defined or Pre Defined
Trapping an exception:-
- If the exception 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 successfully handles the exception, then the exception doesn’t propagate 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 exception 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. -à Implicitly Raised.
B) Non-Pre defined Oracle server exceptions. -à Implicitly Raised.
C) User-defined exceptions -à Explicitly Raised.
Pre-Defined Oracle Server Exceptions: -
- These are the error (20) that occurs most often 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 that the developer determines as abnormal.
These need to be declared and raised explicitly.
PRAGMA EXCEPTION_INIT
Statement is 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 cannot be used directly in SQL statements.
What is Dynamic SQL?
- Dynamic SQL is a SQL statement that contains variables that can change during runtime.
- It is a SQL statement with placeholders 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
- Basically Dynamic SQL means creating the SQL statements dynamically at runtime by using variables.
Ex:- Dynamic SQL can be used to create a procedure that operates on a table whose name is not known until runtime or to execute DDL/DCL/SCS statements.
----à In Pl/SQL such statements cannot be executed statically.
--à EXECUTE IMMEDIATE Statement can perform dynamic single row queries.
Declare
D_str varchar2 (200);
Val varchar2 (20);
Begin
D_str= insert into table1 values (;val);
Val= ‘ Bye’ Execute Immediate str using val;
end;
What are Autonomous Transactions?
- Autonomous transactions are the processes run independently of its parent.
- By means of Autonomous Transaction, the current transaction can be temporarily suspended and another operation can be begun.
- The basic idea behind this is to have some operation take place independently of the current transaction.
Ex: - to allow error messages written to table to be committed but to rollback everything else that has taken place prior to the error.
- The autonomous or child transaction can commit or rollback as applicable with the execution of the parent transaction being resumed upon its completion.
- The parent may then perform further operations of any 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 functionality internally, 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 get the 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 of the autonomous unit are executed.
- As the code unit exits and control returns to the parent the main (parent) transaction is resumed and the transaction context is switched back to the parent.
What is Bulk binding of Bulk collect?
Bulkbind:-
- The assignment of values 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 by minimizing the number of context switches between PL/SQL and SQL engines while they pass an entire collection of elements (varray, nested tables, index-by table or host array) as bind variables back and forth.
- Prior to Oracle 8i, 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 result of SELECT or FETCH statements.
- The keyword BULK COLLECT can be used with SELECT 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 point in time.
- In Oracle 7, it is termed 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.
Best Table -à The tables are that is (are) created by MVIEW create statement and that stores data that satisfy the MVIEW query.
Syntax:- Create materialized view
Refresh fast
Start with sysdate
Next sysdate +1 as
Select *from ;
- Since this is a fast refreshed MVIEW the master table should have a log (Master log) to record the changes on it that can be created by running.
Create materialized view log on master_table;
-à This statement creates 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 possibly also to the MVIEW.
- To keep a MVIEW data relatively current with the data in the master table, the MVIEW must be periodically refreshed.
* Refresh can be accomplished by one of the following procedures.
Dbms_mview.refresh (, )
Dbms _ refresh.refresh ()
Refresh Types -à Complete Refresh, Fast Refresh, Force Refresh
* Complete Refresh is performed by deleting the rows from the snapshot and inserting the rows satisfying the MVIEW query.
* In Fast refresh only the rows updated since last refresh are pulled from the master table to insert into MVIEW.
* This requires a log table called as MVIEW Log to be created on the Master Table.
* Force refresh first tries to run a Fast refresh if possible.
* If fast refresh is not possible, it performs complete refresh.
Refresh Groups
- These are used to refresh multiple snapshots in a transitionally consistent manner.
- When a refresh group is refreshed all MVIEWS in that group are populated with data from a consistent point in time.
- Refresh groups are managed by using 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-only materialized views
- DML cannot be performed on the snapshots in this category.
2 Up datable materialized views
- These MVIEWS eliminates the restriction of DML’s on snapshots.
3 Sub query materialized views
- These are the MVIEW’S that are created with sub queries in the WHERE clause of a 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 primary key for refresh are called primary key MVIE’S (Oracle 8).
* Fast refresh requires some association (mapping) Between rows at snapshot and master tables.
5 Multifier materialized views (Oracle 9)
- In this type MVIEW, its master table itself is a MVIEW.
- This feature enables fast refresh of MVIEW’S that have MVIEW’S as their masters.
- Many companies are structured on at least three levels
A) International
B) National
C) Local
- Many nodes at both the national and local levels are required
- The best possible solution in such cases is to use multifier MVIEW’S.
6 Simple Vs Complex MVIEW’S.
- MVIEW’S being simple or complex determines whether it can be fast refreshed or not.
- A MVIEW is fast refreshable if it is simple.
- A MVIEW is not fast refreshable if it is complex.
- A MVIEW can be considered CONNECT BY, INTERSECT, MINUS or UNION or UNION ALL clauses in its destining 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 warehouse or decision support systems.
Snapshots
- Snapshots are mirror or replace of tables in a single point of time.
- A Snapshot is a local copy of a table data that originates from one or more remote master tables.
- To keep a snapshots data current with the data of its master the Oracle server must periodically refresh 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 cannot be updated.
* A snapshot contains a complete or partial copy of a target master table from a single point in time.
- A snapshot may be read only or updatable.
26. How duplicate rows are deleted?
- Duplicate rows are deleted by using ROWID
Syntax à delete from Where ROWID not in (Select max (ROWID) from
Group by );
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)
Development Tools à ;
28. Difference between IN and OUT parameters.
Three types of parameters
1. IN 2. OUT 3.IN OUT
IN parameter:
- This parameter passes a values from the calling environment into the procedure.
- This is the default mode.
- A formal parameter of In mode cannot be assigned a value (we IN parameter cannot be modified in the body of the procedure)
- IN parameters can be assigned a default value in the parameter list.
- IN parameters are passed by reference.
OUT parameters:
- OUT parameter must be assigned a value before returning to the calling environment.
- OUT parameter passes a value from the procedure to the calling environment
- OUT parameter cannot be assigned a default value in the parameter list.
IN OUT parameter:
- This type of parameter pass a value from the calling environment into the procedure and a possibly different value from the procedure back to calling environment using the same parameter.
- IN OUT parameter cannot be assigned a default value. * By default OUT & IN OUT parameters are passed by value.
- These can be passed by reference 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 implicitly whenever 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 shut down) occurs on a schema or database.
- Executes implicitly when 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 implicitly when some user or data base system actions occur.
- Ex. When user logs on to the system.
When DBA shuts down the database.
- 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 what action 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 database or a schema.
- With a database, 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 - CURSORS. Data bases initialization parameter. (default value is 50).
- * A triggering statement should 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 on which the table or view causes the trigger to fire.
3 Trigger Type Statement, Row
- Determines how many times the trigger body executes
4 Table name Table, View
5 Trigger body à PL/SQL – block
- Determines what actions the trigger should perform.
* INSTEAD of triggers are used to provide a transparent way to modifying views that cannot be modified directly through SQL, DML statements because the view is not modifiable.
-à INSTEAD of triggers provide writing of Insert, Update and Delete statements against the view.
- The INSTEAD if trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
- INSTEAD of trigger execute the trigger body instead of the triggering statement.
Statement Triggers
- In this type of triggers, the trigger body executes once for the triggering event.
- This is the default.
- Statement trigger 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 cannot be modified by normal DML if the view query contains set operators, group functions, group by, connect By, start with clauses or joins.
Mutating Table
- A Mutating table is a table that is currently being modified by an UPDATE, DELETE OR INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
- A table is not considered mutating for statement triggers.
- A mutating table cannot be changed because the resulting DML could change data that is in consistent state.
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 that can be used to tune SQL operations.
- The SQL – Trace facility can be enabled / disabled for an individual session or at the instance level.
- If the initialization parameter SQL-TRACE is set to 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 at the system/session level by using.
Alter system/session set SQL-TRACE = TRUE/FALSE.
Explain Plan
- Explain plan command generates information that details the execution plan that will be used on a particular query.
- Uses a pre created table (PLAN_TABLE) in the current schema to store information about the 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 script creates an output table, 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 statement into 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 format the plan table data.
$ ORACLE_HOME / rdbms / admin / utlxpls. Sql – to format serial explain plans.
$ ORACLE_HOME/ rdbms/admin/utlxpil. Sql – to format parallel explain plans.
* AUTOTRACE
- The AUTOTRACE facility in SQL* plus allows analysts to view the execution pan d some useful statistics for a SQL statement within a SQL*plus session.
- AUTOTRACE needs to be initiated in the SQL*Plus session prior to executing the statement.
SET AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS]
- As with the explain plan command, to obtain an execution plan the PLAN-TABLE must be 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 must have access to dynamic performance tables.
- To achieve this, grant PLUS TRACE role to the user.
PLUS TRACE role is created by the plus trace. Sql script
$ ORACLE_HOME / sql plus admin SYS user must run this script.
DBA can them grant the role to the users who wish to use the AUTOTRACE.
TK PROF
- Tk prof facility accepts as input a SQL Trace File and produces a formatted output file.
- Tk Prof Filename_source filename_output EXPLAIN = [user name / password] sys = [yes/no] TABLE = [Table Name]
A) How do you add trace to a report?
- By using the 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 object data types.
BLOB: Represents a binary large object
CLOB: Represents a character large object
NCLOB: Represents a multibyte character object.
BFILE: Represents a binary file store of in an os binary file outside the database.
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 different segment unless 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’t contain 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.
- These are stored 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_LOB.READ of DBMS_ LOB.WRITE are used to manipulate LOBS.
Oracle Applications Architecture
- Internet computing Architecture is a framework for 3-tired, distributed computing that supports 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 Internet computing architecture, only the presentation layer of Oracle Applications is on the Desk Top tier in the form of a plug-in to a standard Internet browser.
TEMPLATE FORM
- The TEMPLATE form is the required starting point for all development of new forms.
- The development of a new form is started by copying the TEMPLATE.fmb file, located in $ AU_top / forms/ us, to a local directory and renaming it as appropriate.
TEMPLATE FORM CONTAINS THE FOLLOWING:-
*Platform-independent references to object groups in the APPSTAND form
(STANDARD _PCAND_VA,
STANDARD_TOOLBAR,
STANDARD_CALENDAR)
*Platform–independent attachments of several libraries .
FND SQF
APPCORE
APPDAYPK
*Several form level triggers with required code.
*Program units that include a specification and a body for the package APP_CUSTOM, which contains default behaviors for window opening and closing events.
-In general this code should be modified for the specific form under development.
* The application’s color pallete, containing the two colors required by the referenced visual attributes.
* Many referenced objects that support the calendar, the toolbar, alternative regions and the menu.
* Template form contains simple objects that show 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 plat form-independent attachments of several libraries.
- Some of the libraries are attached directly to the TEMPLATE (FNDSOF, APPCORE, APPDAYPK) while the others are attached to these libraries.
APPCORE, APPDAYPK, FNDSQF, CUSTOM, GLOBE, VERT, JA, JE, JL
APPCORE
- Contains the packages and procedures that are required of all forms to support the Menu, Toolbar and other required standard behaviors.
- Procedures and functions in APPCORE have names beginning with APP.
APPDAYPK
- Contains the packages that control the Oracle applications calendar feature.
FINDSQF
- Contains packages and procedures for message dictionary, flex fields, profiles and concurrent processing.
- It also has various other utilities for navigation, MRG, WHO etc.
- Procedures and functions have names beginning with FND.
CUSTOM
- Custom library allows extension of Oracle Applications forms without modification of Oracle applications code.
- Custom library can 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 must be in upper case) and disabling fields that are not required for a particular site.
- All logic must branch 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 statement that is embedded in a clause of another 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 query is one way of reading (data) every row in a table and comparing values in each row against related data.
- Oracle server performs correlated sub query when 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 query is evaluated once for each row processed by the parent statement.
GET
Candidate row from the outer query
EXECUTE
Inner query using the candidate row value
USE
Values from inner query to qualify / disqualify candidate row
Ex:- Select last_name, salary, department_id from employers OUTER
where salary> (select AVG (Salary) from employees
where department id = outer.department_id);
Types of Joins
1 Equi Join
2 Non-Equi Join
3 Outer Join
4 self Join
Equi Join:-
- Is also called simple or inner join.
- An equi join is a join condition that contains equality sign.
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 that do not meet the 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 deficient in information.
- This operator has the effect of creating one or more null rows to each one or more rows from the (join condition) non deficient table can be joined.
Self Join
Self join is used to join a table to itself.
Ex:- to find the name of each 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.STYLEà FUNCTION
CUSTOM.EVENTà PROCEDURE.
* 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 statement where as post-query executes 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
How to get second parameter value based on first parameter?
$flex
$value setname.
What is Ref Cursor?
Ref cursor is a data type and executed at server side and with ref cursor multiple select statements can be executed.
By increase the binary size, that number of records 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.fmb)
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 separately in 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 many applications.
When a packaged PL/SQL construct is called for the first time 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 part of 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 of different 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 about the 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_UPDATE and 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 built in USER.
APPCORE API’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
FNDSQF API’S
FND_CURRENCY
FND_DATE
FND_GLOBAL
FND_ORG
FND_STANDARD
FND_UTILITIES.OPEN_URL
FND_UTILITIES. PARAM_EXISTS
How to call flex fields in the form?
By using FND_FLEX.EVENT (EVENT varchar2)
How to register an executable and define a concurrent program through backend?
By using concurrent processing API’S
1. FND_CONC_GLOBAL.REQUEST_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
- Is used to define a concurrent program executable
- It takes 8 parameters (all are IN mode)
Syntax procedure FND_PROGRAM.EXECUTABLE
(Executable IN varchar2,
(Full name) à application IN varchar2,
(Executable short name) à short name IN varchar2, Description IN varchar2 default null, execution_method IN varchar2,
execution_file_name IN varchar2 default null,
(only for spawned & Immedaite) à subroutine_name IN varchar2 default null,icon_name IN varchar2 default null,language_code IN varchar2 default (VS)(for Java Conc Program) à execution_file_path IN varchar2 default null,
* FND_PROGRAM.REGISTER
- This procedure no used to define a concument program.
- 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, default null,
(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’t check for the existence of the registered table or column in the data base schema, but only updates the required SQL tables.
- It should be ensured that, all the tables and columns registered exist actually and have the same format as that defined using AD_DD API.
- Views need not be registered.
57. How to write to a file through concurrent program?
* 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 output files.
à FND_FILE supports a maximum buffer line size of 32k for both and output files.
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
- this procedure as used to set the temporary log file and output filenames and the temporary directory 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 default null,
program in varchar2 default null,
description in varchar2 default null,
start-time in varchar2 default null,
sub_request in boolean default False,
argument1,
arguemnt2,
argument 100) return number;
* If this is submitted from oracle forms, all the arguments (1 to 100 ) must be 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 current value of the specified 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 set who 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’t exist, it can also be created with PUT.
Procedure FND_ PROFILE.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 that can 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_Output and Gone_Print_Style.
- SA’s and end users also cannot see the values or change these values.
In which directory log and output files are stored?
* APPLCSF is the top-level directory in which the concurrent manager puts logs and output files.
* APPLLOG and APPLOUT are the subdirectories in which the concurrent manager puts log and output files.
*APPLCSF variable need to be set in the prod.env(environmental variable), so that all product log files
$ APPLCSF/ $ APPLLOG à log files
$APPLCSF/ $ APPLOUT à out files
* concurrent manager log and out put files should go to $ FND_TOP/ $ APPLOG and $ FND _TOP / $APPLOUT if $ APPLCSF is not set.
62. How to submit concurrent programs through OS?
- From the operating system the utility.CONCSUB is used to submit is concurrent program.
- This is basically used to test a concurrent program.
- By using the WAIT token. The utility checks the request status every 60 seconds and returns the OS prompt upon completion of the request.
- Concurrent manager doesn’t abort, shutdown or start up until the concurrent request completes.
* If the concurrent program is compatible with itself, it can be checked for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
*PL/SQL procedures can submit a request to run a program as a concurrent process by calling.
FND_REQUEST.SUBMIT_REQUEST
* Before submitting a request, the following functions also should 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 request states?
- A PL/SQL procedure can check the status of a concurrent request by calling.
FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST
- FND_CONCURRENT.GET_REQUEST_STATUS
- This function returns the status of a concurrent request
- If the request is already computed, it also returns the completion message.
- This function returns both user-friendly (translatable) phase and status values as well as developer phase and status values that can drive program logic.
Syntax à Function FND_CONCURRENT.GET_REQUEST_STATUS
(request_id in out number,
application in varchar2 default null,
program in varchar2 default null,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEAN;
- When application and program are specified, the request_id of the last request for 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 request completion, then returns the request phase/status and completion message to the caller.
- Goes to sleep between checks for request completion.
Syntax àFND_CONCURRENT.WAIT_FOR_REQUEST
( request_id in number default null,
interval in number default 60,
max_wait in numbe default 0,
phase out varchar2,
status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEN;
* FND_CONCURRENT.SET_COMPLETION_STATUS
- this function should be called from a concurrent program to set its 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;
normal status warning message à any message 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=. Fmb
Output_file=/forms?/.fmx
Module_type=form bath =no compile_all=special
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 by using 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_FUNCTION package.
* for this purpose, CALL_FORM built-in cannot be used since the Oracle Applications libraries do not support it.
*FND_FUNCTION.EXECUTE should be used to open a new session of a 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 already been opened.
*APP_NAVIGATE.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 of a 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 default FALSE);
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_target in varchar2 default null);
67. What is the reason for not getting any data when a multi org view is required?
- To get the data correctly, the xxx-ALL must be 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 not return any rows, 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 set the value in CLIENT INFO using set_org_contest.
- Execute fnd_client_info.Set_org_context (‘’);
- Now querying of multiorg views can be done.
68. How do you find that multiorg is installed?
- multi organization architecture is meant to allow multiple 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 of Multi org generally includes more than one business group.
* To know whether multiorg is existing or not
select multi_org_flag form fnd_product_groups)
- if the result is ‘Y’ means the database is group for multiorg
69. What are the triggers that fire on item?
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 triggers in forms
- Transactional triggers are the triggers that are related to accessing a data source.
- These triggers fire for each record that is marked for insert, update or table when forms would 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 .
* Important 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 Post and Commit transaction process. Before form builder processes any (changes) records to change.
Specifically it fires after form builder determines that there are inserts, updates or deletes in the form to post or commit, but before it commits the changes.
This trigger doesn’t fire when there is an attempt to commit, but validation determines that there are no changed records in the form.
This is a form level trigger.
Enter query mode should be set as ‘No’
This can be used to perform an action, such as setting up special locking requirements, at any time a database commit is going to occur.
If this trigger fails, the post and commit processes fail, no records are written to the database and focus remains in the current item.
If a DML is performed in a pre-commit trigger and it fails, ten manual rollback must be performed, because form builder doesn’t perform an automatic roll back.
This trigger fires in post and commit transactions.
Post – Commit Trigger
This is also known as post-commit trigger.
Post-commit trigger fires once during the post and commit transactions.
If there are records in the form that have been marked as inserts, updates or deletes, the post-forms-commit trigger fires after these changes have been written to the database but before form builder issues the database commit to finalize the transaction.
If the operation or application initiates a commit when there are no records in the form have been marked as inserts, updates or deletes, form builder fires post-forms-commit trigger immediately, without posting changes to the database.
This is a form level trigger.
Enter query mode should be set to ‘No’
Post-forms-commit trigger should be used to perform an action, such as updating an audit trial any time a database commit is about to occur.
If this trigger fails, post and commit processing aborts and form builder issues a ROLLBACK and decrements the internal save point counter.
This trigger fires in Post and Commit transactions.
What is Decode 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 it to each search condition.
If the expression is the same as search, result is returned.
If the default value is committed, a null value is returned where a search value does not match any of the result values.
DECODE function facilitates conditional inquiries by doing the work of a CASE or IF_THEN_ELSE statement.
DECODE (column, expression, search1, result1, search2, result2, …..);
Ex:- Select last_name, job, salary,
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 many functions, procedures and exceptions that can 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
SRW.Run_Report
SRW.Run_Report (command_line, char);
Executes specified R25 RUN Command
SRW.RUN_REPORT_FAILURE;
Stops report execution when failure of SRW.Run_Report occurs.
By using SRW.Run_Report, another report can be called to the screan from a button with in one report.
If this is used from a Report Trigger, BATCH=YES must be 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 Report Rep-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 statement char);
Executes specified SQL statement
* SRW.DO_SQL_FAILURE
Stops report execution upon SRW.Do_SQL failure.
* SRW.Message (msg_number number, msg_text char);
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 current frame, 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 format triggers.
They are used to change data that will be displayed in 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 that can be applied to multiple reports.
For example template can be defined that include the company logo and sets font colors for selected areas of a report.
When a report is created through the Report Wizard, there is an option of applying a template (.tdf file) to the report.
When a template is chosen, objects in the margin area of a template are imported into the same locations in the current report section, over writing any objects if exists.
The characteristics of the 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 current report section.
Any template properties, parameters, report triggers, program 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 report the 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 by Reports. xxx – Template_Reso (who xxx specified a Report style) (Tabular, Break Above)
iii) For each Report style for which the template is defined
- to the Reports.xxx – Template_Disc list, add the description that should be appeared on the template page of the 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 template file (file name.tdf) to
ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US
Preferences file:-
Windows à ORACLE_HOME\CAUPREFS.ORA (user preferences)
ORACLE_HOME\CAGPREFS.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 request set for three concurrent programs, which are having same parameter?
i) For the first report in the Report set, click on the parameters button and ender the parameters that are to the shared by all reports in the Request (Report) set.
ii) Go to the nest report and click the parameters button and list the same shared parameters.
iii) Do the above step for each and every report in the request set
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 at submission time.
* Request set wizard can be used to quickly create a new Request set in which all of the request run sequentially or all of the request run in parallel.
*sequentially à One after another
* parallel à All at once.
- The action can be set whether to continue processing or abort processing. If a request ends with the statues ‘Error’.
79. What are Global variables in Reports?
*Global variables are the variables that can 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 by Oracle 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 of the trigger as an argument for 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 enables query-find.
Coding logic for item relations such as dependent fields.
Coding messages to use message dictionary.
Adding FF logic if required.
Adding choices to the special menu and logic to modify choices the default menu and tool bar behavior is necessary.
Coding any other logic.
Creating a form function for the developed form and registering any sub functions.
Testing the form by itself.
Registering the form with AOL.
Adding the form function to a menu or creating custom menu.
Assigning the menu to the responsibility and assigning the responsibility to the user.
Testing the form within Oracle Applications.
Registering of Application, form and a concurrent program through Application developer Responsibility
Application:-
Responsibility à Application Developer
Form:-
Menu:-
Messages:-
Table:-
Sequence:-
Concurrent Program:-
Application Developer (Responsibility)
*Flex field
+Key
+Descriptive
-Test
*Concurrent
-Program
-Executable
-Library
*Application
-Register
-Form
-Function
-Menu
-Messages
+Database
+Lookups
+Validation
*Profile
*Attachments
- Document Entities
- Document Categories
- Attachment Functions
*Other
*Requests
- Run
-Set
-Profile
-Concurrent
-Change Organization
-Running Jobs
+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 cannot be made in Pl/SQL statements.
Bind references can be done in a PL/SQL statements.
Lexical parameters can be referenced by entering an ampersand () followed immediately by 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 must be defined so that report builder uses this value to validate the query with a lexical reference.
Token
If Oracle reports are executed by a concurrent program, (for Oracle Reports Program), then a keyword or a parameter with the same name as in the report builder, should be defined which for each parameter, which is known as token.
This is used to pass the parameters to the reports from the application (SRS Window)
Request Set:Request set is the group of requests, that can be submitted regularly using a single transaction.
Incompatibility
These are the list of programs that can be defined as incompatible with a certain program.
If any program is defined as incompatible to a particular program, then that program should not run simultaneously with the concurrent program, because they might interfere with its execution.
Application Developer Responsibility
Various Screens
Different Executable Methods
1 Host
2 Immediate
3 Java Stored Procedure
4 Java Concurrent Program
5 Multi Language Function
6 Oracle Reports
7 PL/SQL stored Procedure
8 Request set stage function
9 Spawned
10 SQL*Loader
11 SQL*Plus
Concurrent Library
Library types Transaction Library
User
Access Levels Extensible
System
List of values
List types Long List of Values
Poplist
No security
Security type Hierarchical Security
Non- Hierarchical Security
Char
Format type Date
Date time
Number
Standard date
Standard date time
Time
Validation types à Dependent
Independent
None
Pair
Special
Table
Translatable Independent
Translatable Dependent
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, concurrent programs and reports connect to.
86. What is a Responsibility?
- Responsibility defines Applications Privileges.
- A responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.
- Each user has at least one or more responsibilities and several users can share the same responsibility.
* Each responsibility allows access to
- a specific application or a set of applications.
- A set of books.
- A restricted list of windows that an user can navigate
- Reports in a specific application.
87. What are security Attributes?
- Security Attributes are used by Oracle self-service 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 set of changeable options that affects 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 of the flex field.
- Definition of the flex field.
90. What is an application /Module?
- Application is a collection of forms, function and menus.
91. What are Alerts?
- Alert is a mechanism that checks the database for a specific exception condition.
- An alert is characterized by the SQL select statements it contains.
- A SQL select statement fells the application what database 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
RECORD 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
(field declaration,…..);
identifier type_name;
Ex:- TYPE emp_record_type is RECORD
last_name varchar2(50),
job_id varchar2(10),
salary number(8,2));
emp_record emp_ record_type;
- fields declared as NOT NULL must be 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 (but not the same as) data base tables.
- INDEX BY Table is a primary key to provide the user with array-like access to rows.
- INDEX BY table is similar to an ARRAY.
- It can be increased in size dynamically because they are 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 unconstrained increase dynamically so that INDEX 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.
- The column can belong to any scalar or record data type, but the primary key must be ling to type BINARY_INTEGER.
- INDEX BY Tables cannot be initialized at the time of its declaration and also it cannot be populated at the time of declaration.
- An exploit executable statement is required to initialize (populate) the INDEX BY TABLE.
INDEX BY TABLE STRUCTURE
Unique identifier Column
…….
1
2
……
…
Gopi
Raj
….
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. COUNT
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 of a 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 about all of the fields of a data base table, the table of records greatly increases the functionality of 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 of type TABLE.
Nested Table contains multiple columns and can be used as variables, parameters, results, attributes and columns.
They can be thought of as one column database tables.
Rows of a nested table are not stored in any particular order.
The size of a nested table can be increased dynamically i.e. nested tables are unbounded.
Elements in a table initially have consecutive subscripts, but as elements are deleted, they can 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 must be used.
To delete elements, the built-in procedure DELETE must be used.
An uninitialized nested table is automatically null, so the IS NULL comparison operator can be used to sees if nested table is null.
The operators CAST, THE and MULTISET are used for manipulating nested tables.
1. Creation of a Nested Table
Defining an object type.
SQL> Create type ELEMENTS AS OBJECT
(ELEM_ID Number (6),
PRICE Number (7,2));
/
2. Create a table type ELEMENTS_TAB that 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 of type VARRAY.
VARRAYS can be used to associate a single identifier with an entire collection.
This allows manipulation of the collection as a whole and easy reference of individual elements.
The maximum size of VARRAY 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) VARRAY, then the VARRAY is automatically null.
The main use of VARRAY is to group small of uniform-sized collection of objects.
Elements of a VARRAY cannot be accessed individually SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript.
The type of the element of 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 implicitly defined.
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 cannot be compared for equality or in equality.
Creating a VARRAY:-
1. Defining object type 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 nested tables 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 database, nested tables do not retain their ordering and subscripts, where as Varrays do.
*Nested tables support indexes while VARRAYS do not.
Differences between conversions and Interfaces:-
Conversion Interface
1. Conversion is one-time process 1. Interface is the post production which is performed once before process. Production go on live (Pre-production process)
2. Data comes into Oracle Applications 2. Interface is the two integration of only (One way process) systems process
3. Interative Process 3. Scheduled and repetive process.
Oracle Reports – Trouble Shooting
1. Concurrent Request Logs: -
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 not resolved with log, then the report should be run through from the operating system.
Along with the standard report arguments, the report should be run along with the arguments passed by the concurrent manager.
If it is run successfully then the problem is with the environment from with the concurrent manager 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.
4. Running the Print Environment Variable Values Report: -
The concurrent manager inherits its environment variables from the shell from which it was started and then runs report using this environment.
This environment could be different from that a user sees logging in to the Applications because the concurrent manager may have been started by a different user with different environment settings.
Due to this difference, it is sometimes difficult to determine the cause of error in running reports.
To examine the values of few variables, prints environment variable values, Report to print out the variable as seen by the concurrent manager to see if is correct.
Very common and often problems such as a problem in compilation or the concurrent managers inability to locate a library happen due to incorrect REPORTS 60_PATH.
5. Emulate Concurrent Manager Environment:-
For UNIX platforms, to assist in determining where the problem lies, Oracle Applications AOL ships a program called $ FND_TOP / Srw / ar60run.oc.
This program helps to emulate the concurrent manager environment when testing reports from the OS command line.
This program writes all the environment variables and arguments passed to it in a log file ar60run.log. (Located by default in the $FND_TOP / $APPLLOG directory).
Save the ar60run.oc source code to a file named ar60run.oc compile it and 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 into fnd.mk which will, by default build an executable $FND_TOP / bin /ar60rund, which can be renamed to ar60run (new).
Submit the report from concurrent manager and look at ar60run.log.
Then run the report from OS with the same arguments as shown by ar60run.log shows using old ar60run.
Bitmapped Reports
Printer drivers should be provided with print style (Landscape) to determine how to print text files.
Bitmapped reports are not text files and these are output as postscript files.
The postscript file is asset of instructions telling the printer exactly a landscape report; the postscript file must be generated as landscape.
Frequently asked questions in Reports
1. Why does my report only fail from the concurrent manager?
This is because the environment from which the concurrent manager launches a report is different from the one when running the report from OS command line.
2. Why does my report show different data?
If the report shows different data when it is run as a standalone report, sometimes the data in the output may be different for different situations.
This is usually due 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, they should be re-enables, before the report is run through concurrent manager.
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 not in this file, then the error REP_0713 error.
4.Why do I get many pages of nonsense when I print my Report?
Postscript code should be recognized by the printer driver.
‘ enscript ’ program cannot be used for printing.
5. What does the ‘ SEP-0065 ’; virtual memory system error?
This error could be due to the following reasons.
* By default Oracle Reports uses / tmp directory to write temporary files, which may be getting full.
These files could be directed to another directory using the environment variable TMPDIR.
* Are the failing reports using page N or M? This can consume a lot of Oracle Reports virtual memory.
If possible, Reports should be run against a smaller database.
FND_PROGRAM Package
FND_PROGRAM.Executable:-
Procedure FND_PROGRAM. Executable IS
(executable in Varchar2,
application in varchar2, (full name)
short_name in varchar2, (executable short name)
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 default null);
For Java Concurrent Program.
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 default null,
save_output in varchar2 default ‘Y’,
print in varchar2 default ‘Y’,
cols in varchar2 default null,
rows in varchar2, default null,
style in varchar2, default null,
style_required in varchar2, default ‘N’,
printer in varchar2, default null,
Requets_Type in varchar2, default null,
Request_type_Application in varchar2 default null,
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’,
Restart in varchar2 default ‘Y’,
nls_complaint in varchar2 default ‘Y’,
icon_name in varchar2 default null,
language_code in varchar2, default ‘US’,
mls_function_short_name in varchar2 default null,
mls_function_application in varchar2 default null,
incrementor in varchar2 default null);
Property Classes
A property class is a named object that contains a list of properties and their settings.Once a property class is created, it can be assigned to any object.An object based on a property class can inherit the settings of any property in that property class.There can be number of properties 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 locally also.
Property class are separate objects and can be copied between modules if required.A property class can be sub classed in only number of modules.
Visual Attributes
Visual attributes are the font, color and pattern properties that can be set for 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 programmatically to change the font, color and pattern of the object at runtime.
No comments:
Post a Comment