SQL,PL/SQL,D2K Interview Questions Part I

SQL,PL/SQL,D2K Interview Questions

Part I

1. How to implement ISNUMERIC function in SQL *Plus ?

Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)
Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
It returns 0 if it is a number, 1 if it is not.
2. How to Select last N records from a Table?
select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)
Here N = 10
The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.
Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno
What are steps required tuning this query to improve its performance?
„Ï Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO
„Ï Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date.
„Ï Also, you can change your SQL to:
SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;
3. What is the difference between Truncate and Delete interms of Referential Integrity?
DELETE removes one or more records in a table, checking referential
Constraints (to see if there are dependent child records) and firing any
DELETE triggers. In the order you are deleting (child first then parent)
There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers.
Also, it only checks for the existence (and status) of another foreign key
Pointing to the table. If one exists and is enabled, then you will get
The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before
issuing the TRUNCATE command, then re-enable them afterwards.
CLIENT/SERVER

What does preemptive in preemptive multitasking mean ?
Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.
What does the OLTP stands for ?
OLTP stands for On Line Transaction Processing
What is the most important requirement for OLTP ?
OLTP requires real time response.
In a client server environment, what would be the major work that the client deals with ?
The client deals with the user interface part of the system.
Why is the most of the processing done at the sever ?
To reduce the network traffic and for application sharing and implementing business rules.
What does teh term upsizing refer to ?
Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing.
What does one do when one is rightsizing ?
With rightsizing, one would move applications to the most appropriate server platforms.
What does the term downsizing refer to ?
A host based application is re-engineered to run in smaller or LAN based environment.
What is event trigger ?
An event trigger, a segment of code which is associated with each event and is fired when the event occurs.

Why do stored procedures reduce network traffic ?
When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
What are the types of processes that a server runs ?
Foreground process and
Background process.
What is a event handler ?
An event handler is a routine that is written to respond to a particular event.
What is an integrity constraint ?
An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.
What are the various uses of database triggers ?
Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.
What is a transaction ?
A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. BEGIN COMMIT/ROLLBACK are the boundries of a transaction.

Why are the integrity constraints preferred to database triggers ?
Because it is easier to define an integrity constraint than a database trigger.
Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure ?
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.
What are the three components of a client server model ?
A Client,
A Server and
A Network/Communication software.
What are the advantages of client/server model ?
Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.
What are the disadvantages of the client/server model ?
Heterogeneity of the system results in reduced reliablity. May not be suitable for all applications. Managing and tuning networks becomes difficult.
What are the different topologies available for network ?
Star,
Bus,
Ring.
What is the first work of Client process ?
A client process at first establishes connection with the Server.
What are the responsibilities of a Server ?
1. Manage resources optimally across multiple clients.
2. Controlling database access and security.
3. Protecting the databse and recovering it from crashes.
4. Enforcing integrity rules globally.

In a Client/Server context, what does API (Application Programming Interface) refer to ?
An API, in a Client/Server context, is a specification of a set of functions for communication between the client and the server.
Give some examples of standard API¡¦s ?
Open Database Connectivity (ODBC),
Integrated Database Application Programming Interface (IDAPI),
XOpen
SQL/CLI
What is the main advantage of developing an application using an API ?
The application can be connected to any back end server that is supported by the API.
What is the main disadvantage of developing an application using an API ?
The application cannot use any special features of the backend server.
Why is an event driven program referred to a passive program ?
Because an event driven program is always waiting for something to happen before processing.

What are the four types of events ?
1. System Events.
2. Control Events
3. User Events
4. Other Events.
What is the difference between file server and a database server ?
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.
What is inheritance ?
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
What are the two components of ODBC ?
1. An ODBC manager/administrator and
2. ODBC driver.
What is the function of a ODBC manager ?
The ODBC Manager manages all the data sources that exists in the system.
What is the function of a ODBC Driver ?
The ODBC Driver allows the developer to talk to the back end database.
What description of a data source is required for ODBC ?
The name of the DBMS, the location of the source and the database dependent information.
How is a connection establised by ODBC ?
ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.

RDBMS FUNDAMENTALS
I. INTRODUCING DATABASES :
Concept of a Database :
Traditional Approach : In this approach, independent application programs access their own independent data files. This results in many problems in data storage and retrieval.
Database Approach : In this approach, all application access a common database, which is a centralized data storage system. This approach has the following advantages :
Redundancy of data storage is reduced, Inconsistency in data is eliminated & Data sharing between applications is possible.
Interacting with a Database :
Database Management System (DBMS) : DBMS is a software that interfaces between applications and a database for all data processing activities.
Users of a DBMS : End Users, Application Programmers and Database Administrators use a DBMS, either directly or indirectly.
How users interact with a Database :
1. End users send queries to the DBMS through applications.
2. The DBMS translates the queries.
3. The DBMS retrieves data from the database.
4. The DBMS sends data to the application, which present the data to the end users.
Functions of a DBMS :
Function of DBMS Description Provided by using
Defining the data structure Defining structure of data to be stored in database Data Definition Language (DDL)
Manipulating Data Retrieving, adding, modifying, deleting data. Data Manipulation Language (DML)
Data Security Preventing unauthorized access to data. User-ids and Passwords.
Control of Data Access Allowing users to use only relevant data Data Control Language (DCL)
Architecture of a Database :
Need for an Architecture : The details about complexity and structure of data in a database in not required by end-users. Therefore, differentiating what the end-users see and what is actually there in a database is important.
Architecture of a Database : The architecture of a database comprises a set of three levels at which a database can be viewed.
External Level or View, Conceptual Level or View & Internal Level or View.
II. USING RELATIONAL DATABASE :
Basics of Relational Database :
Relational Database Management System (RDBMS) : RDBMS is the most popular form of DBMS used in the world. It uses a relational database to organize data. A relational database comprise relations, which are represented as tables.
Relation : A relation stores information about an object in the real world. A relation is represented as a table.
Attribute : Each attribute of a relation stores a piece of information about an object. Attributes are represented as columns in a tables and can be arranged in any order. Each attribute in a relation is unique and contain atomic values i.e. Atomic value contain a single value of data and Non-Atomic values contain a set of values. The number of attributes in a relation is called the degree of the relation.
Tuple : A row in a table is called a tuple of the relation. The number of tuples in a relation is known as the cardinality of the relation. Tuples in a table are unique and can be arranged in any order.
Domain : A domain is a set of valid atomic values that an attribute can take. Within a single database, an attribute cannot have different domains associated with it. A domain can include a null value, if the value for the domain is unknown or does not exist.
Identifiers for Relations :
Primary Key : An attribute that uniquely identifies a row in a table is called its primary key. A relation can have only one primary key. The primary key cannot have any null values. In case no unique key is found in a relation, two or more attributes can be treated as the primary key. Such keys are called Composite Keys.
Candidate Key : A relation can have more than one attribute that uniquely identifies a tuple. Any one of these keys can be selected as the primary key. All such attributes are called Candidate Keys. All candidate keys that are not primary keys are called Alternate Keys.
Foreign Key : An attribute that is not a candidate key is called a Nonkey. A nonkey attribute of a relation whose value matches the primary key in some other table is called Foreign Key OR is a column in a table that uniquely identifies rows from a different table.
III. INTERPRETING DATA :
Entities and Relationships :
Entity : An entity is an object that exists in the real world and is distinguishable from other objects. Each entity is represented as a table in a relational database.
Types of Entities : Entities can be classified in two ways - based on existence and based on subsets.
Based on existence, entities can be classified as Dominant and Weak entities.
Based on subsets, entities can be classifies as Supertypes and Subtypes.
Relationships : A relationship is an association between two entities.
Types of Relationships : Relationships are classified into three types based on the occurrence of the related entities.
One-to-One(1-1), One-to-Many(1-M) & Many-to-Many(M-M).
Using E/R Diagram : A E/R diagram represent entities and relationships in a database system.
Reducing E/R Diagrams to Relations :
Mapping Entities : A dominant entity is mapped to a new relation. A weak entity is mapped to a new relation. The primary key of the corresponding dominant entity is included as the foreign key in the weak entity relation.
Supertypes and subtypes are mapped to separate relations. The primary key of the supertype becomes the primary key of the subtype.
Mapping Relationships : A 1-1 relationship is mapped using a foreign key. The primary key of either of the entities is include as a foreign key in the relation of the other entity. This relationship is rare, because data elements related in this way are normally placed in the same table.
A 1-M or M-1 is mapped by introducing a foreign key. A primary key is the ¡¥one¡¦ side of the relationship, and the foreign key is the ¡¥many¡¦ side of the relationship. This relationship are most common.
A M-M involves the creation of a new relation. M-M are problematic and cannot be adequately expressed directly in a relational db. It is expressed using intersection tables. An intersection table contains two (or more) foreign keys, relating the primary key values of two (or more) tables to each other. The role of an intersection table is to convert the M-M into two 1-M relationships that can be easily handled by the database.
IV. SIMPLIFYING DATA :
Need for Simplifying Data :
Normalization : Normalization is a formal process of developing data structures in a manner that eliminates redundancy and promotes integrity. You need to simplify structure of data in relations for easy storage and retrieval. The process of simplifying relations is called normalization. The new relations that are obtained after normalization are called normalized relations.
Normalization has three well defined steps :
The relations that you get at the end of the first step are said to be in 1NF.
The relations that you get at the end of the second step are said to be in 2NF.
The relations that you get at the end of the third step are said to be in 3NF.
Simplifying Data to 1NF (Eliminate Repeating Groups) : A repeating group is a set of columns that store similar info that repeats in the same table. To simplify data to 1NF, you ensure that all attributes values in a relation have atomic values. If there are attributes in a relation with non-atomic values, move these attributes to a new relation and choose an appropriate primary key for it. E.g. SupItem Table Item field having atomic.
Simplifying Data to 2NF (Eliminate Redundant Data) :
Redundant data is data that is expressed multiple times unnecessarily, or depends only on part of a multi-valued key.
Functionally Dependent Attributes : Functionally Dependent Attributes are those that belong to a single entity or relationship and depend on its unique identifier. To simplify data to 2NF, you ensure that all nonkey attributes in a relation are functionally dependent on the whole key and not part of the key.
Conversion from 1NF to 2NF : To convert a relation in 1NF to 2NF, move all nonkey attributes that are not wholly dependent on the primary key, to a new relation. Then, choose an appropriate primary key for the new relation. E.g. Separating Sup. table and Item table.
Simplifying Data to 3NF (Eliminate Columns not Dependent on the Key) :
Columns in each table should be a group of columns in which the data in each column contributes to the description of each row in the table.
Transitively Dependent Attributes : Transitively Dependent Attributes in a relation are those that are dependent on a nonkey attribute and not the primary key. To simplify data to 3NF, you ensure that there are no attributes in a relation that are transitively dependent on other attributes.
Conversion from 2NF to 3NF : To convert a relation in 2NF to 3NF, move all transitively dependent attributes to a new relation. Then, choose an appropriate primary key for the new relation. E.g. Status is dependent on City in Sup. table, so move those two to separate table.
Simplifying Data to 4NF (Isolate Independent Multiple Relationships) :
V. STORING & RETRIEVING DATA :
Language Support for an RDBMS :
SQL :SQL is the language that provides command to interact with the data in the database. SQL consists of three components - DDL, DML & DCL.
DDL : DDL comprises commands you can use to create and modify the database structure.
DML : DML comprises commands you can use to add, modify, delete and query data in the database.
DCL : DCL comprises commands you can use to control the user access to the database.
Organizing the Database :
Base Tables : A database comprises base tables, which have the following features :
They physically exist on the disk, Each of them has a unique name & they contain data that is crucial to an organization.
Their attributes have data types such as character, integer, decimal, date and time.
CREATE TABLE : This is a DDL command in SQL that creates a new table in a database.
Syntax : CREATE TABLE table-name (column-name data-type [size]
NOT NULL/DEFAULT default-value]
CHECK (column-name > 0)
UNIQUE (column-name)
PRIMARY KEY (column-name)
FOREIGN KEY (column-name) REFERENCES table-name)
ALTER TABLE : This is a DDL command in SQL that modifies the structure of an existing table.
Syntax : ALTER TABLE table-name
ADD (column-name data-type [size] [NOT NULL DEFAULT]...)
primary key definition / foreign key definition
DROP PRIMARY KEY / DROP FOREIGN KEY)
DROP TABLE : This is DDL command in SQL that deletes the an existing table. Once you delete a table, all data contained in it is lost and cannot be recovered. The storage space used by this table is also released.
Syntax : DROP TABLE table-name
Interacting with a Database :
SELECT : This is a DML command in SQL that retrieves data from the database in the form of query results. The command supports the following keywords and clauses :
FROM This keyword specifies the name of the table.
* This keyword selects all the columns of the table.
WHERE This keyword gives the search condition that specifies the data to be retrieved.
AND This operator is used to combine two or more search conditions.
ORDER BY This keyword sorts the query result on one or more columns.
GROUP BY This keyword groups the query result and lets you generate summary result for each group.
NULL values This value indicates that the data is not present.
Subquery This is the query that is place inside the main query. It passes its query result to the main query.
INSERT : This is a DML command in SQL that you use to add data in rows of a table.
SYNTAX : INSERT INTO table-name (column-names) VALUES (constant/NULL)
UPDATE : This is a DML command in SQL that you use to change data on rows of a table.
Syntax : UPDATE table-name SET column-name-value WHERE condition
DELETE : This is a DML command in SQL that removes one or more rows of data from a table.
Syntax : DELETE FROM table-name WHERE condition.
End-user's View of a Database :
Views : Views are relations that are derived from one or more source tables. Views have the following features:
Views let you restrict the access to data so that end-users see data relevant to them.
Views do not physically exist in the database and only their definition is stored by an RDBMS.
An RDBMS accesses the source tables for data to be retrieved from a view.
Any changes that users make to views do not reflect in the source tables if the view has been created using a Join condition.
Views created WITH CHECK OPTION allows for an added measure of security in a view. For example, the user will not be able to insert or update a row that could not be selected by the view-with check option prevents this from happening.
CREATE VIEW : A view can be created using the CREATE VIEW command.
Syntax : CREATE VIEW view-name (column-names) AS query.
Retrieving Data from a View : Once you create a view, you can retrieve data from it using the SELECT command, just as you do for a table.
Restricting Access to a Database :
GRANT : This is a DCL command in SQL that you use to grant a specific set of authorities to one or more users.
Syntax : GRANT (SQL command) (column-names) ON table-name TO user-name.
REVOKE : This is a DCL command in SQL that you use to take away a specific set of authorities from one or more users.
Syntax : REVOKE (SQL command) ON table-name TO user-name.
VI. ENSURING INTEGRITY OF DATA :
The concept of Data Integrity :
Data Integrity : Data Integrity refers to the correctness and completeness of data in a database.
Integrity Constraints : Integrity constraints allows only correct changes to be made to a database. There are two types of integrity constraints - entity integrity and referential integrity.
Entity Integrity : Entity Integrity ensures that for each row in a table, the value of the primary key is unique and is not null.
Referential Integrity : Referential Integrity ensures that for each row in a table, the value of the foreign key is present in the reference table.
Grouping commands related to a task :
Transaction Processing : A transaction is a sequence of one or more SQL commands that together form a logical task. Transaction Processing ensures that when the RDBMS is making changes related to a single task, either all changes are made as a unit or no changes are made.
Commit : Commit is an SQL command that indicates the successful end of a transaction. After an RDBMS executes this command all the changes are made to the database.
Rollback : Rollback is an SQL command that cancels a transaction before it is complete. The rollback command removes the changes of all previous commands in a transaction from the buffer.
Controlling Concurrent Data Access :
Concurrency Control : All RDBMS must ensure that the transactions of concurrent users do not interfere with each other. If it does not handle the transactions properly, the problems of lost update, uncommitted data, or inconsistent data might occur.
Lost Update Problem : Lost update problem occurs when an update made by a transaction is lost due to an update made by another transaction.
Uncommitted Data Problem : Uncommitted data problem occurs when a transaction accesses data that has been updated by a previous transaction that has not yet ended.
Inconsistent Data Problem : Inconsistent data problem occurs when a transaction accesses data from the database and simultaneously another transaction is changing that data.
Locking : Locking is a facility provided by an RDBMS to ensure that a transaction does not interfere with any other transaction. Locking prevents the problem of lost update, uncommitted data and inconsistent data. An RDBMS provided two types of locks for locking a part of the database - shared locks and exclusive locks.
Shared Locks : If a transaction is only reading data from a database, it gets a shared lock on that part of the database. Other transactions can also get a shared lock on that part of the database to read data. However, they cannot change the data.
Exclusive Locks : If a transaction is updating data in a database, it gets an exclusive lock on that part of the database. No other transaction can read or change this data.
Client Server Computing Model
I. Client Server Paradigm :
Introduction : In the past decade, organizations have restructured and become global. The globalization and restructuring have led to a need for distributed and flexible information access. That traditional computing paradigms like host-based and master/slave processing do not adequately address the information requirements of modern business. That the client/server model provides an architecture that harness contemporary technology to meet the computing needs of the modern business organization. Also called Distributed Application Processing or Co-Operative Application Processing
Host Based Processing - The Centralized Paradigm : Centralized computing treated applications as an integrated unit. Applications ran on a single processor. The same processor generated the user interface and manipulated data. Dumb terminals were used for data access and display. Disadvantages are : Data was centralized and often not accessible to those who needed it. The host computed did all the work, was frequently overloaded, and response times were often poor. Hardware choices and scalability were limited by proprietary architecture's. User interfaces were unfriendly, Data access was inflexible and governed by available 3GL programs.
Master/Slave - The First Distributed Computing Paradigm : As PC's and intelligent terminals became available, a limited amount of processing was transferred to the terminal. Intelligent terminals often validated data and provided editing functions. Such terminal were called Slaves. Slaves were controlled by Master computer which did the main processing and stored all data. It had one distinct advantage. It reduced the load on the main processor. However, the other problems associated with host-based processing remained.
Client/Server - A Distributed Computing Paradigm : The client/server paradigm evolved as an attempt to address the new computing needs of business and utilize new technologies.
Advantages : Client/Server makes it possible to harness the computing power available on PCs and other workstations in an organization. Response times are improved as processors are not overloaded. Hardware and software choices can be application oriented as they do not necessarily have to run on a proprietary computer. Network traffic is reduced because the processing power available at the end user terminal makes it unnecessary to send detailed instructions over the network as in the case of host based & master/slave systems. Computing power can be added in smaller units making the system easily scaleable.
Disadvantages :The inherent heterogeneity makes System Integration, Administration & Maintenance a formidable challenge.
ORACLE 7 is an exceptional RDBMS & also an excellent DB server because it supports all major OS
for both clients & servers i.e. MSDOS, Netware, Unixware, OS/2 etc. Oracle Network software SQL*NET support all major network communication protocols TCP/IP, SPX/IPS, Named Pipes & DECNET. It has got client server feature that developers can use to minimize network traffic between clients & servers. Has features that makes it easy to administer a complicated client server system.
Client/Server Approach : The client/server paradigm optimizes the use of computer resources by distributing application processing among multiple processors. The client/server model computers are classified as Clients and Servers, where Clients are requesters of services and Servers are provider of services. Typically, clients handle user interface function & server handle data management function.
Client/Server Architecture requires Processing to be distributed over more than one computing system. Client-initiated client/server interactions. Server control over the services that can be requested by the client. Servers to arbitrate between conflicting client requests. A communication system that enables clients & servers to communicate.
Multitasking can be defines as the capability of an OS to run multiple applications concurrently. A Multitasking OS allocates a certain amount of CPU time to each task. In Preemptive multitasking, the OS controls the amount of CPU time allocated to a task. In non-preemptive multitasking, the application controls the CPU time and the CPU is released only after the task is completed.
Multithreading is an extension of multitasking. It allows multitasking within an application. Programs and subroutines within a program can execute concurrently in a multithreaded environment. Several user process for a single server process.
Database Server should have preemptive multitasking & multithreading capability. Support a standard RDBMS. Support a standard Network Operating System.
Tools such as RDBMS, Application Software, Application Program Interfaces, Stored Procedures, Remote Procedure Call (RPC) & Application Development Tools are an important part of client/server systems. Such tools improve productivity and also play a role in making client/server systems more open. An API is a set of functions that translates client requests into a format that the server can understand. RPC is essentially a messaging system which allows stored procedures to be invoked. Many RPCs allows procedures to be invoked across heterogeneous platforms and also provide the required data translation services.
There are several application designs possible in the client/server model depending on how application processing is distributed.
In Distributed Presentation, the presentation function is divided between the client & the server. Useful in situations where PC or workstations are connected to mainframes. Used to enhance the user interface of mainframe based applications.
In Remote Presentation, the entire presentation part of the application resides on a different computer than the one that has the logic function. Used in applications where user interaction is completely static and predetermined.
In Distributed Logic, the logic function is placed on more than one platform thus, improving response time by allowing the logic to execute simultaneously on several processors.
In Remote Data Management, the application logic resides on different computer than the one that has the data and the DBMS. Easy to implement and often provide end-users with totally transparent access to data.
In Distributed Data Management, the data and DBMS is distributed among multiple nodes and distribution of application logic.
Goals of Client/Server Paradigm is the end-user. Client/Server seeks to provide end-user transparent access to the computing resources of the organization. The goal is referred to as single system image. There are four attributes of single system image :
Location Transparency : Users must be able to access data without knowing the location of the data. Users should not have to learn & use different commands for accessing data from different locations.
Interoperability requires that applications and processing tasks be freely portable across heterogeneous computing resources.
Consistent User Interfaces require that applications retain the same user interface across heterogeneous computing platforms. Common computing tasks are represented consistently across applications.
Enterprise-wide Resource Sharing is the common thread that links all of an enterprise's computing resource
II. Concepts for Client/Server :
Introduction : RDBMS's standardize data storage and access and are therefore ideal for implementing client/server systems. The physical structure of a network is called Network Topology i.e. refers to the way the cabling of a network is physically structured. The rules that govern the process of data transmission in a network are collectively referred to as Network Protocol. Graphical User Interfaces improve productivity because they reduce learning time
and are easier to use.
Distributed Database Support : The capability of an RDBMS to manage databases lying at more than one location. To provide distributed database support, an RDBMS must be able to provide transparent access to data, Join tables on different platforms, Handle and manage distributed queries and Ensure that transactions are successfully completed on all relevant databases.
Network : Network has six basic functions, Naming, Segmentation, Segmenting, Flow Control, Synchronization, Priority & Error Control. There are three types of network topologies, Bus, Star & Ring. There are three types of transmission media, Twisted Pair, Coaxial Cable & Optic Fibre. There are three types of data transmission methods, Centralized, Distributed & Random.
GUIs : GUI must support mouse events, keyboard events, menu events, resizing events, activation/deactivation events and initialize/terminate events. GUI should be portable, should support wide variety of development tools & be an industry standard.
III. Client/Server Software :
Back-end Software : It is made up of Database Servers & Network Operating Systems. The database server manages data on the system & maintains data integrity. Database server requires some special features. Compatibility i.e. must be able to work on different operating systems. SQL Implementation i.e. must support standard ANSI SQL since it can communicate with different SQL dialects. Stored Procedures i.e. must be able to use SP as they are analyzed, compiled and optimized. Referential Integrity i.e. allows the server to synchronize change to columns that are part of multiple tables. Declarative RI establishes precise rules for use of columns that are common across tables. Built into db software and enforced by default. In Procedural RI, each db command is associated with a trigger. When command is issued, the trigger sets of a series of commands that make the necessary changes. Disadvantage is programmers must write triggers leading to errors. Multithreading i.e. support execution of multiple tasks simultaneously. Distributed Database Support i.e. able to divide database tasks among multiple CPUs. Also, able to join tables located on different servers, & manage SQL queries sent to different servers. Concurrency Control i.e. support automatic escalation, the server locks a record, if a single record is being modified. A page, if several records are being modified. A table, if several pages are being modified. Transaction Control i.e. protect transactions from system failures. In two phase commit, all workstations are notified of database changes, and if system fails, all sites rollback.
NOS controls the transmission of data across the network and manages the sharing of hardware and software resource on the network. Four important features are Operating Environment Support, Workstation Support, Security & Protocol Support.
Development Tools are made up of SQL/3GL programming tools & Front-end development tools.
End User Tools are made up of Decision Support Tools and PC-based add-ons.
IV. Migrating to Client/Server :
Evaluating the Client/Server Option : To evaluate the following before deciding on client/server. Application requirements, Geographical requirements & Productivity Gains. Client/Server is most suitable for applications are decision support or on-line transaction processing (OLTP). The distances are site or city & both developer & end-user productivity are expected to increase.
Planning for Migration : Planning is needed to reduce problems with network load, training & systems maintenance. Migration plan must include analysis, selection, prototyping & implementation as activities. Planning must include end-users, developers and system administrators as resources. To specify system requirements for end-users, developers, system managers and the business as a whole. To evaluate business priorities to eliminate stagnation & disruption.
Implementing Client/Server : The Systems Integration Life Cycle (SILC) is made up of preparation, detailing and execution. Managing SILC projects involves planning, controlling & completing. System maintenance involves ensures reliability, ensuring serviceability and monitoring performance. The training must cover end-users, developers & system administrators.
ORACLE
I. SQL*PLUS :
SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.
SQL*PLUS is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.
NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression.
Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST.
Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.
TTITLE & BTITLE are commands to control report headings & footers.
COLUMN command define column headings & format data values.
BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.
COMPUTE command control computations on subsets created by the BREAK command.
SET command changes the system variables affecting the report environment.
SPOOL command creates a print file of the report.
JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.
Set Operators supported by Oracle are :
Union is the product of two or more tables.
Intersect is the product of two tables listing only the matching rows.
Minus is the product of two tables listing only the non-matching rows.
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.
Multiple columns can be returned from a Nested Subquery.
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience. Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.
Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.
Data types :
Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.
Cannot Query on a long column.
Char, Varchar2 Max. size is 2000 & default is 1 byte.
Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
Long Character data of variable length upto 2GB.
Date Range from Jan 4712 BC to Dec 4712 AD.
Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.
Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.
Order of SQL statement execution : Where clause, Group By clause, Having clause, Order By clause & Select.
Transaction is defined as all changes made to the database between successive commits.
Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions.
Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.
Rollback causes work in the current transaction to be undone.
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
Set Transaction is to establish properties for the current transaction.
Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve
Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it.
Integrity : Assures database data and structures reflects all changes made to them in the correct sequence.
Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks. Types of locks are given below.
Data Locks protects data i.e. Table or Row lock.
Dictionary Locks protects the structure of database object i.e. ensures table's structure does not change for the duration of the transaction.
Internal Locks & Latches protects the internal database structures. They are automatic.
Exclusive Lock allows queries on locked table but no other activity is allowed.
Share Lock allows concurrent queries but prohibits updates to the locked tables.
Row Share allows concurrent access to the locked table but prohibits for a exclusive table lock.
Row Exclusive same as Row Share but prohibits locking in shared mode.
Shared Row Exclusive locks the whole table and allows users to look at rows in the table but prohibit others from locking the table in share or updating them.
Share Update are synonymous with Row Share.
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement.
Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints.
Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.
SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename
PL/SQL :
Data types are NUMBER, CHAR/VARCHAR2, DATE & BOOLEAN.
Arrays are not allowed & only one identifier per line is allowed.
Attributes of PL/SQL objects are %TYPE, %ROWTYPE.
PL/SQL Block is a standard PL/SQL code segment. Block consists of three parts.
Declarative Section for variables, constants & exceptions. This section is optional.
Executable Section which is mandatory.
Exception Handlers which is optional.
PL/SQL supports only DML i.e. INSERT, UPDATE, DELETE & SELECT...INTO.
SQL Functions can be referenced within a SQL statement i.e. Numeric (SQRT,ROUND,POWER),
Character (LENGTH,UPPER), DATE (ADD_MONTHS,MONTHS_BETWEEN) &
Group (AVG,MAX,COUNT). Most SQL functions are available outside SQL statement except for group functions.
Code Simple Loops repeats a sequence of statements multiple times.
Syntax : LOOP

END LOOP;
Code Numeric FOR Loops repeat a sequence of statements a fixed number of times.
Syntax : FOR IN [ REVERSE ] .. LOOP

END LOOP;
is implicitly of type number. Defined only within the loop & Value can be referenced in an expression, but a new value cannot be assigned to the index within the loop.
Code While Loops repeats a sequence of statements until a specific condition is no longer TRUE.
Syntax : WHILE LOOP

END LOOP;
can be any legal PL/SQL condition & statements will be repeated as long as condition evaluates to TRUE.
Code GOTO Statements jumps to a different place in the PL/SQL block.
Syntax : GOTO label_name;
Legally use a GOTO a statement that is in the same sequence of statements as the GOTO.
In the sequence of statements that encloses the GOTO statement (outer block).
Labels can label any statement. Used as targets for GOTO statements, use labels for blocks and loops, Label a block to allow referencing of DECLAREd objects that would otherwise not be visible because of scoping rules, Label a block to allow a variable to be referenced that might be hidden by a column name, Label a loop to allow an object to be reference that would otherwise not be visible because of scoping rules & Label an EXIT as a convenient way to specify exits from outer loops.
Cursors are associated with every SQL DML statement processed by PL/SQL. Two types are Explicit i.e. Multiple row SELECT statements & Implicit i.e. INSERT, UPDATE, DELETE & SELECT...INTO statements. Implicit cursor is called the SQL cursor-it stores info concerning the processing of the last SQL statement not associated with an explicit cursor. OPEN, FETCH & CLOSE do not apply. All cursor attributes apply.
Cursor has to be explicitly defined when a query returns multiple rows to process beyond the first row returned by the query & to keep track of which row is currently being processed.
Declare the cursor to associate its name with a SELECT statement.
Syntax : DECLARE
CURSOR
IS ;
Open the cursor to process the SELECT statement and store the returned rows in the cursor.
Syntax : OPEN ;
Fetch data from the cursor and store it in specified variables.
Syntax : FETCH INTO ;
Close the cursor to free up resources. Cursors must be closed before they can be reopened.
Syntax : CLOSE
Explicit Cursor Attributes are %NOTFOUND, %FOUND, %ROWCOUNT & %ISOPEN.
Reference the current cursor row with the WHERE CURRENT OF statement. The cursor must be declared with a FOR UPDATE OF clause.
Syntax : WHERE CURRENT OF
Reference Cursors FOR Loops to specify a sequence of statements to be repeated once for each row that is returned by the cursor with the Cursor FOR Loop.
Syntax : FOR IN LOOP
--statements to be repeated go here
END LOOP;
Cursor FOR loops (CFL) are similar to Numeric For Loops(NFL). CFL specify a set of rows from a table using the cursor's name. NFL specify an integer range. CFL record takes on vales of each row. NFL index takes on each value in the range. Record_name is implicitly declared as
record_name cursor_name%ROWTYPE
When a CFL is initiated, an implicit OPEN cursor_name is initiated.
For each row that satisfies the query associated with the cursor, an implicit FETCH is executed into the components of record_name.
When there are no more rows left to FETCH, an implicit CLOSE cursor_name is executed and the loop is exited.
Declare cursors to use parameters
Syntax : DECLARE
CURSOR [(param_name param_type)]
IS ;
Exception Handlers : In PL/SQL, errors are called exceptions. When an exception is raised, processing jumps to the exception handlers. An exception handler is a sequence of statements to be processed when a certain exception occurs. When an exception handler is complete, processing of the block terminates. Two types are Predefined Internal Exceptions which corresponds to approximately 20 common ORACLE errors & Raised automatically by PL/SQL in response to an ORACLE error.
Eg.too_many_rows,no_data_found,invalid_cursor,value_errori.e. arithmetic,numeric,string,conversion or constraint error occurred, zero_divide, dup_val_on_index,cursor_already_open etc.
User-Defined Exceptions must be declared & must be RAISEd explicitly.
Only one handler per block may be active at a time & If an exception is raised in a handler, the search for a handler for the new exception begins in the enclosing block of the current block.
Exception-Init : Exceptions may only be handled by name not ORACLE error number. So, name an ORACLE error so that a handler can be provided specifically for that error.
Syntax : PRAGMA EXCEPTION_INIT (, );
SQLCODE & SQLERRM provides info on the exception currently being handled & especially useful in the OTHERS handler.
SQLCODE returns the ORACLE error number of the exception, or 1 if it was a user-defined exception.
SQLERRM returns the ORACLE error message associated with the current value of SQLCODE & can also use any ORACLE error number as an argument.
SQLCODE & SQLERRM cannot be used within a SQL statement. If no exception is active SQLCODE = 0 & SQLERRM = 'normal, successful completion'.

SQL*FORMS :
Form is a tool for developing and executing forms based interactive applications that can access info from ORACLE database.
Blocks describes each section or subsection of the form and serves as the basis of default database interaction.
Fields represents columns or data entry areas and describes how data should be displayed and validated and how an operator should interact with the data while it is entered.
Pages is a collection of display info such as constant texts and graphics. All fields have to be displayed on some page.
Pop-Up Pages Non-Pop-Up Pages
Appear in windows Overlay the entire screen
Created by selecting pop-up page attribute Default type of page
Can be larger or smaller than the screen Can only be the size of the screen
Can appear anywhere on the screen Must be positioned at the upper left hand corner of the screen
Can be a section (view) of a page Must fill the entire text region
Many pages can appear on the screen at one time Only one non-pop-up page can appear on the screen
Page Size specifies the size of the pop-up page.
View Size specifies the size of the view that appears on the screen i.e. how much of the pop-up is shown.
View Location specifies where on the screen the view of the pop-up page appears; the X and Y coordinates of the screen define the upper left corner of the view.
View Page specifies the initial location of the view on the page, i.e. the part of the pop-up page that is shown; the X & Y coordinates of the page define the upper left corner of the view.
Screen Painter is used to edit screen images, add constant text and graphic elements.
Zoom In : Displays the form or spread table for the objects that are owned by the current object.
Zoom Out : Displays the form or spread table for the object that owns the current object.
Validation Unit is a characteristic which determines the max. amount to data to be entered before form initiates validation. It corresponds to a unit of data which can be field, record, block or form.
Navigation is performed to move the cursor from one location to another.
Cursor is an instance of field, outside the form or undefined.
Row Id is a column created by ORACLE when a table is created. It contains a value for each row which uniquely identifies that row. When a block is created, forms add a non-displayable, non-up dateable field named row id. Forms uses row id to determine what rows to fetch from db or reserve in db and what rows to update or delete during posting. It helps forms to manage transactions and is used to update a table that is not associated with a block in the form.
Trigger is a piece of PL/SQL code that is executed or triggered by an event while the form is running. It validates data entry, performs calculations, control the flow of application & replace or enhance default processing.
Trigger Point is a temporal space in an event with which a specific trigger type is associated.
Types of Triggers : Most key triggers are function key triggers; they have a one-to-one relationship with specific keys.
Function Key Triggers : Fires when a particular Forms function key is pressed.
Replace or supplement default function key functionality, Perform multiple or complex functions & Disable function keys.
Key Startup : Fires at the end of the entering the form event. Considered as key trigger because its action is similar to an operator pressing a startup function key.
Set up form default, Send a message to the operator as soon as the form comes up on the screen & Perform an automatic query upon entering the form.
Key Others : Associate a key-others trigger with all keys that can have key triggers associated with them but are not currently defined by a function key triggers.
Key Fn : Attach key-Fn triggers to any one of ten key sequences that normally do not perform any SQL*Form operations. Before attaching key triggers to these keys, run Oracle*Terminal to map the keys to the appropriate functions.
Navigational Triggers : Fires when entering or leaving a form, block, record or field.
Restrict access to a form, Print messages, Derive a complex default value, Keep a running total & Perform calculations.
Validation Triggers : Validation is an internal process by which Forms determines whether the data in an object is correct. Validation triggers fire when validation is performed. Validation occurs when the operator has entered or changed data in an object and then tries to leave the object. Validation does not occur when the operator is in Enter Query mode. Validation or Navigation triggers cannot contain Restricted Packaged Procedures.
Fires as last part of field validation, Changes, Calculates & Validates a field value.
Query Triggers : Fires when entering & executing a query, and when counting query hits. Defined only at the block or form level. Pre-Query fires once before block is queried. Post-Query fires once for every record fetched by the query.
Error & Message Handling Triggers : Write these triggers to replace the default SQL*Forms error or informative messages. On-Error & On-Message trigger fires when Forms displays an error or an informative message respectively.
Trap & recover from an error, Replace a standard message with a custom message.
Transactional Triggers : Fires during commit processing - an event that makes the data in the database identical to the data in the form.
Sets up special locking requirements, Update an audit trial, Prevent Insert/Update/Delete actions & Modify Insert/Update/Delete actions.
Packaged Procedure is a predefined piece of PL/SQL procedure that executes a SQL*Forms function.
Restricted Packaged Procedure is any packaged procedure that affects the basic functions of SQL forms. It is used only in Key triggers, user named triggers that are invoked by key triggers & On-New-Field-Instance trigger.
Do_Key packaged procedure executes the key trigger that corresponds to the specified packaged procedure. If no such trigger executes, then the specified packaged procedure executes. This behavior is analogous to the user pressing the corresponding function key.
Syntax : DO-KEY (package procedure name)
Packaged Function is a predefined piece of PL/SQL function that evaluates some aspect of the current SQL*Forms session and returns a value.
Name_In packaged function returns the contents of the variable to which you apply it. Returned value is in the form of character string. Also use Name_In to return number and dates as character strings and then convert those strings to the appropriate data types.
Call packaged procedure runs an indicated form while keeping the parent form active. SQL form runs the called form with the same options as the parent form. When called form is exited thro' the EXIT function or as a result of navigational failure, processing resumes in parent form at the point where the call occurred.
Call_Form runs an indicated form while keeping the parent form active.
Open_Form opens an indicated form to create multiple form application called form is modal.
New_Form exits the current form and enters the indicated form.
Key-Duprec trigger copies the values of each field in the record with the next lower sequence number to the corresponding fields in the current record. The current record must not correspond already to a row in the database. If it does, an error occurs and calls the Duplicate_Record packaged procedure.
Primary Key Field Attributes indicates that the field is a unique characteristics for a record or part of unique key. To ensure that an inserted record or updated record does not duplicate an existing record, give the critical field the primary key characteristics and give the block the primary key characteristics.
Anonymous Block is a PL/SQL block without a name and this block can be executed from the trigger in which it is defined. It does not require BEGIN & END keywords. It has to be included if it has Declaration section.
Form Level Procedure are callable PL/SQL blocks. The full PL/SQL syntax, including declarations and keywords BEGIN & END are required. They cannot contain anonymous block. These procedures can use any command that a trigger can use. They can also take arguments and return values, just as subroutines do in 3GL. It can be called from other procedures & triggers. Advantages are
Reduce the amount of logic the designer needs to write for any task, Leads to more efficient & consistent applications, Can be called by another procedure or trigger & Can pass parameters.
User Exits is a subroutine which is written and linked into SQL forms executable files. It is a link to pass data from forms to host language programs and receives the result. It performs complex data manipulation, pass data to forms from OS files, manipulate long raw data, support PL/SQL blocks and control real time devices such as printer or robot. It returns a integer value which indicates Success, Failure or Fatal Error.
Types :
Oracle PreCompiler User Exits : Incorporates Oracle Precompiler interface. Allow to write a subroutine using one of the following host language & embedded SQL commands. Host language are ADA, C, COBOL, FORTRAN, PASCAL & PL/1. With embedded SQL commands, an Oracle Precompiler user exit can access oracle databases. Also access SQL*Forms variables & fields. Because of this feature, most of user exits is Oracle Precompiler user exits.
Oracle Call Interface User Exits : Incorporates Oracle Call Interface. Allows to write a subroutine that contains call to Oracle database but cannot access SQL*Forms variable & fields.
Non-Oracle User Exits : Does not incorporate either precompiler interface or OCI. Non-Oracle user exit might be entirely written using C. Cannot access Oracle database & SQL*Forms variable & fields.
PL/SQL Variable is a local variable that is active only within the anonymous block or form level procedure in which it has been declared.
Global Variable is a forms variable that is active in any trigger within a form and is active through out the session. Stores a character string of upto 255 characters long. Before a variable is active, it should be initialized thro' a trigger or it will be initialized first time you assign a value to it. Delete any variable with ERASE package procedure. Global variable declared in one form can be used in called form. Used to store data values that should not be stored inside a block or you want to share between forms during form session.
Syntax : GLOBAL.variable_name Oracle Naming Conventions.
System Variable is a SQL*Forms variable to keep track of some internal SQL*Forms state. Able to reference the value of system variable in order to control the way an application behaves. Value of system variable corresponds to the current form. They are Block_Status,Record_Status,Form_Status, Current_Block,Current_Field,Current_Form,Current_Value,Cursor_Block,Cursor_Field,Cursor_Record,Cursor_Value,Last_Record,Last_Query,Message_Level,Record_Status,Trigger_Block,Trigger_Field & Trigger_Record.
Features of Oracle 7 :
Stored Procedures is commonly used procedures can be written once in PL/SQL and stored in the database for repeated use by applications. This ensures consistent behavior among applications and reduce development & testing time. Centralizing the application logic and can be accessed from any Oracle tools.
Difference between Stored Procedure and Oracle Forms Procedure :
Stored Procedure Oracle Forms Procedure
Stored within the database. Stored within the Oracle Forms Appln.
Documented within the data dictionary. Documented from the Oracle Forms Appln.
Executed from any db tool or appln. Executed from an Oracle Forms appln. only.
May reference db stored procedures only. May reference Oracle Forms procedures in addition to db stored procedures.
Made available to applns. by means of db security. Made available to Oracle forms appln. by means of form-level security and the Copy/Reference facility.
Invoked independently of, and in addition to, Oracle Forms procedures. Invoked independently of, and in addition to, stored procedures.
Stored Package is a group of related Stored Procedures, Functions & other package constructs stored together as a unit in the db. Stored Package provides the db administrator or appln. developer organizational benefits, they also offer increased functionality and db perfomance.
QEP for Stored Procedures is generated at compile time.
Benefit from Stored Procedures & Functions : In addition to modularizing appln. development, other benefits are
Improve Data Security and Integrity :
Control indirect access to db objects from non-privileged users with security privileges.
Ensure that related actions are performed together, or not at all, by funneling activity for related tables thro¡¦ a single path.
Improve Performance :
Avoid reparsing for multiple users by exploiting shared SQL.
Avoid PL/SQL parsing at runtime by parsing at compile time.
Reduce the number of calls to the database and decrease network traffic by bundling commands.
Conserve Memory :
Store a single copy of code in the db to avoid multiple copies of the same code for diff. applns.
Share sql to avoid multiple cursors for different applns.
Improve Maintenance :
Modify routines online without interfering with other users.
Modify one routine to affect multiple applications.
Modify one routine to eliminate duplicate testing.
Database Triggers is a stored procedure that is implicitly fired when an Insert, Update or Delete statement is issued against the associated table. Complex business rules that cannot be enforced using declarative integrity constraints can be enforced using triggers. It is similar to procedures that are stored in the databases and implicitly fired when a table is modified.
Guidelines :
Use triggers to guarantee that when a specific operation is performed, related actions are performed.
Use DB triggers for centralized global operations that should be fired for triggering statement regardless of which user or database application issued the statement.
Do not define triggers to duplicate the functionality & do not create Recursive triggers.
Triggers will be compiled when it is fired. So, limit the size of triggers. Compilation of triggers smaller in size will have insignificant effect on system performance. If the trigger has to be execute many lines of code, include the code in the Stored Procedure.
Mutating Error in DB triggers is when a select statement within the scope of the trigger selects rows corresponding to the trigger table itself. It can be solved by placing the select statement in a stored procedure and calling the stored procedure from the scope of the trigger.
Differences between Oracle 6.0 & 7.0 :
Only not null was implemented in 6 where as in 7 all the integrity constraints are implemented i.e. Not Null, Primary, Foreign, Check, Default & Unique constraints.
Database Triggers, Stored Procedures, Functions & Packages are available in 7.
Roles : ORACLE provides for easy & controlled privilege management thro¡¦ the use of Roles. Roles are named groups of related privileges that are granted to users or other roles. The properties are Reduced granting of privileges, Dynamic privilege management, Selective availability of privileges & Application awareness.
Schemas is a collection of objects i.e. tables, views etc. and schema exists for each oracle user.
Profiles is assigned to each user that specifies limitations on several system resources available to the user i.e. the number of concurrent sessions the user can establish, the CPU processing time, the amount of logical I/O, the allowed amount of idle time for the user¡¦s session & the allowed amount of connect time for the user¡¦s session.
Cost Based Optimizer is available. It uses statistics about tables, along with info about the available indexes to select an execution plan for SQL statements. This allows even inexperienced users to submit complex queries without having to worry about the performance. If we know the better execution path, provide hints to it to allow it to select the proper execution path.
Table Replication is a snapshot of a table.
Multi threaded architecture several user process for a single server process and the user process can be configured.
Declarative Referential Integrity establishes precise rules for use of columns that are common across tables. Built into db software and enforced by default.
SQL*REPORTWriter :
Features :
Application development tool for designing & executing reports. ANSI standard SQL used to retrieve records in the report. Menu-driven, simple spreadsheet-style screens. Default values for report format. Ability to customize report format, date & number formats. Complex data relationships. Calculations & summaries. Text processing and highlighting features. Reports can be viewed immediately on-line for corrections. Generate reports interactively or in a production environment. Parameters provide run-time flexibility. Report management facility for easy report maintenance & Integration with Oracle application tools.
Components :
Query - retrieves data from the database. Group - used to cluster columns returned from the query. Fields - containers for data values. Summary - calculated summary function on a field. Text - physical areas of report. Report - dimensions, security and history of the report. Parameters - entered at run-time to control production, data values.
Queries : Every report must contain atleast one query. Query retrieves data for the report from database tables or views. Queries can be unrelated i.e Master/Master report. Related queries form a hierarchy, in which child query is executed once for each record returned by the parent. Can create relationships between queries. No limit to the number of queries in a report. No limit to the level of relationships (nesting) in queries. i.e. Master/Detail (parent/child) queries. Matrix queries i.e. Two parents/One child queries.
On-line report can be viewed either in Browse i.e. Page by page or Window i.e. scrolls horizontally and vertically through a page.
Groups : A group is a set of one or more columns. Every report must contain at least one group. Each query automatically generates one group consisting of all columns in the query.
Break Groups : A break is a set of groups generated from one query. Can create new groups (break groups) that group records by the distinct values of a column or set of columns. Group settings provide format control.
Field : A field is a container for values derived from columns or calculated column values in the SELECT statement. Computed fields created by user. System variables (page number, number of pages, date etc.). User exits & DML statements.
Summaries : Summaries calculate subtotals, grand totals, running totals & other standard functions on field values. All summaries are manually created. Multiple summaries may be computed on any field, including fields derived from user exits & system variables. Summary settings provide format control. Summaries may be referenced in queries.
Text Objects : Text objects represent the physical layout of the report. Text objects are used to manipulate positions of fields & summaries. Customize text. Add page numbering. Changes made on field, group & summary setting screens are reflected on all associated text objects that have not been edited. Text object changes are not dynamically reflected in field, group & summary settings.
Parameters : Parameters contain default values that can be modified for each report or with each interactive run. Parameters may be placed in queries, user exits & text. The default parameter value, width & data type applying to a report may be changed on the parameter screen. Parameters may be selected for appearance or may be changed at run-time on the run-time parameter form. Parameters may be entered from the command line with the sqlrep or runrep command.
System Parameters provide production control. They are DESTYPE-Device type i.e. Screen, File, Printer, Sysout & Mail. DESNAME-Destination of report i.e. File name, Printer name & Oracle*Mail user id or user list. DESFORMAT-Printer format (used when sending the report to a file, printer, stream, or Oracle*Mail). COPIES-Number of copies to print (used when sending the report to the printer). CURRENCY-Symbols to use for the dollar sign. THOUSANDS-Symbol to use for the comma. DECIMAL-Symbol to use for the period.
Query Parameters can be created to specify data for the report at run-time.
Bind Parameter binds in a specific value when the query is run. To create a bind parameter Include the parameter in the SELECT statement; precede the name with a colon. Specify a default value on the parameter screen, or enter the value at run-time.
Lexical Parameter : Insert a SQL clause when the query is run. May be used to replace WHERE, GROUP BY, ORDER BY, HAVING, CONNECT WITH, START WITH clauses. To create a lexical parameter Create a new record on the parameter screen. Enter the parameter name, data type & width. Enter the default value (SQL clause) on the Parameter screen. Include the parameter in the SELECT statement; precede the parameter name with &. Use the default value, or specify the value at run-time.
SQL*ReportWriter Utilities : SQLREP - Program used to design reports. GENREP - Generates a runfile for a report that has not been executed via the Action menu. RUNREP - Runs the reports (stored as runfiles) & creates final output. May be loaded on systems without SQLREP to produce reports. DUMPREP - Creates an ASCII file containing report definitions. LOADREP - Loads report definitions into an ORACLE database. PRINTDEF - Creates printer definitions. TERMDEF - Created terminal definitions. MOVEREP - SQL*ReportWriter V1.0 to V1.1 conversion program.
Matrix Reports : A matrix report has the following characteristics :
Matrix report is a grid containing three sets of data. Matrix Report require exactly three queries: two parents & one child. Each query has only one group. Each group must be identified as a matrix group. Print direction is Down for one group, Across for one group, and Cross tab for the third (the group of the child query). Summaries are placed by default in the group subfoots of the print group.
PRO*C :
Oracle Precompiler is a programming tool that allows to embed SQL statements in a high level source program. Precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls & generates a modified source program that can be compiled, linked & executed in the normal way.
Why use Precompiler : It allows to pack the power & flexibility of SQL into application programs. A convenient, easy to use interface lets your application access Oracle directly. Help to fine tune the application & saves time because the precompiler & not the user translates each embedded SQL statement into several native language Oracle calls.
Oracle Precompilers are full featured tools that support professional approach to embedded SQL programming. FIPS flagger helps to develop portable applications & to identify nonconforming SQL elements.
Embedded SQL Statements are divided into
Executable Declarative
Call to runtime library SQLLIB To declare Oracle objects, communication areas & SQL variables.
Connect to Oracle, to define query & manipulate data, to control access to data & to process transactions. Placed wherever host language host language executable statements can be placed. Placed wherever host language declarations can be placed.
For SQL statements, begin with keywords EXEC SQL & end with SQL statement terminator. For PL/SQL block, begin with EXEC SQL EXECUTE & END EXEC.
Host Variables are key to communication between Oracle and the program. Host variable is a scalar or array variable declared in the host language & shared with Oracle. Program uses i/p host variable to pass data to Oracle. Oracle uses o/p host variable to pass data & status info to the program. In SQL statements, the variable are prefixed with a colon.
Indicator Variables is an integer variable that indicates the value or condition of its host variable. Use indicator variable to assign nulls to i/p host variable & to detect nulls or truncated values in o/p host variables.
Oracle Precompiler offers two error handling mechanisms :
SQLCA is a data structure copied into your host program. It defines program variables used by Oracle to pass run time status info to the program. E.g. Check to see if a Delete was successful & how many rows were deleted. SQLCA is used to provide diagnostic checking & event handling.
ORACA is a data structure copied into your host program to handle ORACLE specific communications. When we need more run time info than SQLCA provides, we use ORACA. ORACA helps to monitor PRO*C programs use of ORACLE resources such as SQL statement executor & the cursor cache, an area of memory reserved for memory management.
SQLDA is a structure copied into your host program to process dynamic SQL statements that contains unknown number of select-list items or place holders for bind variables.
Whenever, we can specify actions to be taken automatically when oracle detects an error or warning condition. Actions include continuing with the next statement, calling a subroutine, branching to a labeled statement.
Precompiling adds a step to the traditional development process, but it lets to write very flexible applications.
PL/SQL blocks can be embedded in PRO*C. Stored Procedures can be called from PRO*C. Pointers can be used in PRO*C but it can't be used in SQL statements.
VARCHAR implementation in C after Precompilation : For most applications, use C pseudo type VARCHAR instead of standard C character arrays because Oracle does not null terminate strings. After precompilation, expands the Varchar declaration into a structure with array & length number.
Data Type Equivalencing is conversion from Oracle to C data type.
SQL*DBA :
Auditing : To aid in the investigation of suspicious db use. Statement Auditing is the auditing of specific SQL statements. Privilege Auditing is the auditing of the use of powerful system privileges. Object Auditing is the auditing of access to specific schema objects.
Audit Trial : Results of audited operations are stored in a table in data dictionary.
Physical DB Structure : ORACLE db consists of atleast one or more data files, two or more redo log files & one or more control files. The files of a db provide the actual physical storage for db info.
Logical DB Structure : ORACLE db consists of one or more tablespaces, the db schema¡¦s objects (i.e. tables, views, indexes, clusters, sequences, sp). Tablespaces, Segments, Extents dictate how physical space of a db is used.
Tablespaces : A db is divided into logical storage units called TS. TS is used to group related logical structures together. Each db is logically divided into one or more TS. One or more data files are explicitly created for each TS to physically store the data of all logical structures in a TS. Combined size of the data file is the total storage capacity of TS. Combined storage capacity of the TS¡¦s is the total storage capacity of the db.
Online & Offline TS : A TS can be online (accessible) or offline (not accessible). A TS can be offline to make portion of the db unavailable while allowing normal access for the remainder of the db to make administrative tasks easier.
Schema is a collection of objects. Schema Objects are the logical structures that directly refer to the db¡¦s data. Schema objects includes tables, views, sequences, synonyms, stored procedures, indexes, clusters & db links. No relation between ts & schema. Objects in same schema can be in diff. ts & vice versa.
Index Clusters are group of one or more tables physically stored together because they share common columns & are often used together. The related columns of the tables in a cluster is called cluster key. The data in a cluster key of an index cluster is store only once for multiple tables, so disk access time improves.
Hash Clusters : Also cluster table data in a manner similar to normal cluster. A row is stored in a hash cluster based on the result of applying a hash function to the row¡¦s cluster key value. All rows with the same hash key value are stored together on disk. Hash clusters are better than using indexed table or indexed clusters when a table is queried with equality queries. For such queries, the specified cluster key is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
Database Links is a name object that describes a path from one db to another. DB links are implicitly used when a reference is made to a global object name in a distributed db.
Data Blocks : At the finest level of granularity, an ORACLE db¡¦s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical db space on a disk. A data block size is specified when the db is created. A db uses & allocates free db space in ORACLE data blocks.
Extents is the next level of logical db space. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of info.
Segments is the next level of logical db storage above extent. A segment is a set of extents allocated for a certain logical structure. Different types are
Data Segment : Each non clustered table has a data segment. All the table¡¦s data is stored in the extents of its data segment. Each cluster has a data segment.
Index Segment : Each index has a index segment that stores all of its data.
Rollback Segment : One or more rollback segments are created by the db administrator for a db to temporarily store undo info.
Temporary Segments are created by ORACLE when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temp. segments extents are returned to the system for future use. ORACLE allocates space for all types of segments in extents. Therefore, when the existing extents of a segment are full, ORACLE allocates another extent for that segment as needed. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.
Data Files : ORACLE db should have atleast one or more physical data files. It contains all db data. A data file can be associated with only one db. Once created, a data file cannot change in size. One or more data files form a logical unit of db storage called a tablespace.
Redo Log Files : ORACLE db should have atleast two or more redo log files. The set of redo log files for a db is collectively known as the db¡¦s redo log. The primary function is to record all changes made to data. Should a failure prevent modified data from being permanently written to the data files, the changes can be obtained from redo log & work is never lost. Redo Log files are critical in protecting a db against failures. To protect against a failure involving the redo log itself, ORACLE allows a mirrored redo log so that two or more copies of the redo log can be maintained in diff. disks.
The use of Redo Log Files : The info in redo log file is used only to recovered the db from a system or media failure that prevents db data from being written to a db¡¦s data files.
Rolling Forward is the process of applying the redo log during a recovery operation.
Control Files : ORACLE db should have atleast one control file. A control file records the physical structure of the db. It contains db name, names & locations of db¡¦s data & redo log files & time stamp of db creation. Control files can be mirrored for protection of control files.
The use of Control Files : Every time an instance of an ORACLE db is started, its control file is used to identify the db & the redo log files that must be opened for db operation to proceed. DB¡¦s control file is also used if db recovery is necessary.
Data Dictionary : ORACLE db should have a data dictionary. The data dictionary of a ORACLE DB is a set of tables & views that are used as a read only reference about the db. It stores info about physical & logical structure of db. It also stored the info about valid users of an ORACLE db, info about integrity constraints defined for tables in the db & how much space is allocated for a schema object and how much of it is being used. DD is created when a db is created. The dd is critical to the operation of the db, which relies on the dd to record, verify and conduct ongoing work.
ORACLE has three basic memory structures to function - System Global Area (SGA), Program or Process Global Area (PGA) & Context Areas.
SGA is a shared memory region allocated by ORACLE that contain data & control info for one Oracle db instance. It is written to only by RDBMS processes. SGA & Oracle Background processes make up an Instance. SGA is allocated when an instance starts and deallocated when the instance is shut down. Data in SGA is shared among all users currently connected to the database. For optimal performance, the entire SGA should be as large as possible, to store as much data in memory as possible & minimize disk I/O. Most Oracle servers support only one Instance per Server. SGA contains Database Buffers, Redo Log Buffers & Shared Pool. These areas have fixed sizes are created at the instance startup.
DB Buffers Cache of the SGA store the most recently used blocks of db data; the set of db buffers in an instance is the db buffer cache. These buffers can contain modified data that has not been permanently written to the disk. Because the most recently used is kept in memory, less disk I/O is necessary and performance is increased. It consists of two blocks Data Segment Block & Rollback Segment Block.
During the course of transaction, changes to data are not written to the database file but these steps take place (a) Each statement executed in the transaction modifies the appropriate data segment block in the DB pool buffer. (b) Info that can be used to undo the transaction is stored in a Rollback block in the db buffer pool. (c) A record of each change made to Data & Rollback block is entered in a Redo Log Buffer. When transaction is committed, info in the redo log buffer is written to Redo Log File which are used in Recovery operations.
Redo Log Buffer of the SGA stores redo entries - a lot of changes made to the db. The redo entries stored in the redo log buffers are written to an online redo log file, which is used if db recovery is necessary. It size is static.
Shared Pool is a portion of the SGA that contains shared memory constructs such as Shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a db. It contains info such as the parse tree and execution plan for the corresponding statement. A single shared SQL areas is used by multiple applns. that issue the same statement, leaving more shared memory for other uses.
PGA is a memory buffer that contains data & control info for a single client process. PGA is allocated on the server for each client that connects to the server. It contains info about connection & maintains info so that user can communicate with oracle. PGA includes Context Areas. PGA is a writeable, non-shared memory area. It is exclusive to the user processes & is read & written only by Oracle processes acting on behalf of the user.
Context Areas is a memory buffer of the Server that contains the current status of one SQL statement.
Virtual Memory is an OS feature that offers more apparent memory than is provided by real memory. Simulates memory by swapping RAM & Secondary storage.
Processes is a mechanism in an OS that can execute a series of steps. Some OS uses the terms job or tasks. A process normally has its own memory area in which it runs. It has two general type of processes
User (Client) Processes is created and maintained to execute the software code of an appln. program (Pro*C) or an ORACLE tool (SQL*DBA). It also manages the communication with the server process thro¡¦ program interface.
ORACLE Processes are called by other processes to perform functions on behalf of the invoking process. Diff. types of Oracle processes are
Server Processes : ORACLE creates server processes to handle requests from connected user processes. A server process is in charge of communicating with the user process and interacting with ORACLE to carry out requests of the associated user process. Can be configured to vary the number of user processes per server process
In a dedicated server config, a server process handles requests for a single user process. A multi threaded config. allows many user process to share a small number of server processes, minimizing the number of server processes and maximizing the utilization of available system resources & the user and server processes should be separate.
Background Process : ORACLE creates a set of background processes for each instance. They consolidate functions that would otherwise handled by multiple ORACLE programs running for each user process. They asynchronously perform I/O & monitor other ORACLE processes to provide increased parallelism for better performance & reliability. BG processes are given below.
Database Writer (DBWR) processes writes modified blocks from the database buffer cache to the database files. Blocks are written in proper order to maintain database integrity. DBWR is optimized to minimize disk writes. DBWR writes only when more data needs to be read into the SGA and too few db buffers free. The least recently used data is written to the data files first.
Log Writer (LGWR) processes writes redo log entries to disk when transaction is committed & the log buffer fills. Redo Log data is generated in the redo log buffer of the SGA.
Checkpoint (CKPT) : At specific times, all modified db buffers in the SGA are written to the data files by DBWR; this event is called a checkpoint. The checkpoint process is responsible for signaling DBWR at checkpoints and updating all data & control files of the db to indicate the most recent checkpoint. CKPT is optional; LGWR assumes the responsibilities of CKPT, if CKPT is not present.
System Monitor (SMON) performs instance recover at instance startup. In a multiple instance system i.e. Parallel Server, SMON of one instance can also perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use & recovers dead transactions skipped during crash & instance recovery because of file read or offline errors. These transactions are eventually recovered by SMON when the tb or file is brought back online. SMON also coalesces free extents within the db, to make free space contiguous and easier to allocate.
Process Monitor (PMON) processes perform recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher and server processes and restarts them if they have failed.
Archive (ARCH) processes copies on-line redo log files to on-line archival storage when they are full.
ARCH is active only when a db¡¦s redo log is used in ARCHIVELOG mode.
Recoverer (RECO) : The recoverer is used to resolve distributed transactions that are pending due to a network or system failure in a distributed db. At timed intervals, the local RECO attempts to connect to remote dbs and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
Dispatcher (Dnnn) : Dispactchers are optional background processes, present only when a multi-threaded server configuration is used. At least one dispatcher process is created for every communication protocol in use. Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.
Lock (LCKn) : Up to ten lock processes are used for inter-instance locking when the ORACLE parallel server is used.
Network Listener (NLSN) process listens to network for connection requests made to server by client applications. When it detects, it starts a Shadow process that performs all further server communication with a client.
Oracle Instance : Every time a database is started on a server, an SGA is allocated & five background processes are started. The combination of these processes & memory buffers is an Instance.
SQL*Net is ORACLE¡¦s interface to standard communications protocols that allows for the proper transmission of data between computers.
ORACLE Parallel Server : Multiple Instance Systems : Some hardware architectures i.e. loosely coupled processors allow multiple computers to share access of data, software or peripheral devices. ORACLE with the Parallel Server option can take advantage of such architecture by running multiple instances that share a single physical db. In appropriate applications, ORACLE Parallel Server allows access to a single db by the users on multiple machines with increased performance.
Example of how ORACLE works : The following illustrates an configuration where the user and the associated server process are on separate machines.
1. An instance is currently running on a computer that is executing ORACLE i.e. DB Server.
2. A computer used to run an appln. i.e. client ws runs the appln. in a user process. The client attempts to establish a connection to the server using the proper SQL*Net. driver.
3. The server is running proper SQL*Net driver & the server detects the connection request from the appln. and creates a (dedicated) server process on behalf of the user process.
4. The user creates a SQL statement and commits the transaction. E.g. changes a name in a row of a table.
5. The server process receives the statement and checks the shared pool for any shared SQL area that contains an identical SQL statement. If a shared SQL area is found, the server process checks the user¡¦s access privileges to the requested data and the previously existing shared SQL is used to process the statement; if not, a new shared SQL area is allocated for the statement so that it can be parsed and processed.
6. The server process retrieves any necessary data values from the actual data file or those stored in SGA.
7. The server process modifies data in the SGA. The DBWR process writes modified blocks permanently to disk when doing so is efficient. Because the transaction committed, the LGWR process immediately records the transaction in the online redo log file.
8. If the transaction is successful, the server process send a message across the network to the appln. If it is not successful, an appropriate error message is transmitted.
9. Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the db server manages other users transactions and prevents contention between transaction that request the same data.
Trusted ORACLE : is ORACLE corp. multilevel secure DBMS product. It is designed to provide high level of secure data management capabilities required by organizations processing sensitive or classified info. It enforces Mandatory Access Control (MAC) which is mean of restricting access to info based on labels.
Distributed Processing uses more than one processor to divide the processing for a set of related jobs. Reduces the processing load on a single processor by allowing different processors to concentrate on a subset of related tasks, thus improving the performance. An ORACLE db system can easily take advantage of the distributed processing by using its client server architecture.
Distributed Databases is a network of db¡¦s managed by multiple db servers that appears to a user as a single logical db. The data of all db¡¦s in the distributed db can be simultaneously accessed and modified. Benefit is data of physically separate db¡¦s can be logically combined & potentially made accessible to all users on a nw. The db to which a user is directly connected is known as local db. Any additional db¡¦s accessed are called remote db. Distributed db allows increased access to a large amount of data across a nw, it must also provide the ability to hide the location of the data (Location Transparency) & hide the complexity of accessing it across the nw. Site Autonomy i.e. each db participating in a distributed db is administered separately & independently from the other db¡¦s, as though each db was a non-networked db.
Distributed Data Manipulation : To query a table named emp in the remote db SALES select * from emp@sales.
Two Phase Commit mechanism guarantees that all db servers participating in a distributed transaction either all commit or all roll back the statements in the transaction. Consists of two phases
Prepare Phase : The global coordinator (initiating node) asks participants to prepare (to promise to commit or rollback the transaction, even if there is a failure).
Commit Phase : If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction; if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.
Table Replication : Distributed db systems often locally replicate remote tables that are frequently queried by local users. By having read-only copies of heavily accessed data on several nodes, the distd. db does not need to send info across a nw repeatedly, thus helping to maximize the performance of the db appln. ORACLE provides automatic method for table replication & update called Snapshots.
Snapshots are read-only copies of a master table located on a remote node. Can be queried but not updated.
Optimization is to choose the most efficient way to execute a SQL statement.
Execution Plan : To execute a DML statement, ORACLE may have to physically retrieves rows of data from the db or prepares them in some way for the user issuing the statement.. The combination of the steps the optimizer chooses to execute a statement is called an execution plan.
Explain Plan : Examine the execution plan chosen by the optimizer for a SQL statement by using this command. This command causes the optimizer to choose the execution plan and then inserts data describing the plan into a db table.
Rule based approach : The optimizer chooses an execution plan based on the access paths available and the ranks of these access paths in table. If there is more than one way to execute an SQL statement, this approach uses the operation with the lower rank. Operation of lower rank executes faster than those associated with constructs with higher rank.
Cost based approach : The optimizer generates a set of potential execution plan for the statement based on its available access paths and hints.
The optimizer estimates the cost of each execution plan based on data distribution and storage characteristics statistics for the tables, clusters and indexes in the data dictionary. The cost is an estimated value proportional to the expected elapsed time needed to execute the statement using the execution plan. The optimizer calculates the cost based on the estimated computer resources including but not limited to I/O, CPU time and memory required to execute the statement using the plan. Execution plans with greater costs take more time to execute than those with smaller costs.
The optimizer compares the cost of the execution plans and chooses one with the smaller cost.
Goal of the Cost based Approach is the best throughput or minimal elapsed time necessary to process all rows accessed by the statement.
Statistics used for the Cost based approach : This approach uses statistics to estimate the cost of each execution plan. These statistics quantify the data distribution and storage characteristics of tables, columns and indexes. These statistics are generated using the ANALYZE command. Using these statistics, the optimizer estimates how much I/O, CPU time, and memory are required to execute a SQL statement using a particular execution plan.
The statistics are visible through these tables in the data dictionary.
USER_TABLES, ALL_TABLES & DBA_TABLES.
USER_TAB_COLUMNS, ALL_TAB_COLUMNS & DBA_TAB_COLUMNS.
USER_INDEXES, ALL_ INDEXES & DBA_ INDEXES.
USER_CLUSTERS & DBA_CLUSTERS.
ORACLE Optimizes SQL statements : For any SQL statement processes by ORACLE, the optimizer performs these tasks.
Evaluation of expressions and conditions : The optimizer first evaluates expressions and conditions containing constants as fully as possible.
Statement Transformation : For a complex statement, the optimizer may transform the original statement into an equivalent join statement.
View Merging : For a SQL statement that access a view, the optimizer often merges the views query into the original statement or the original statement into the view¡¦s query and then optimizes the result.
Choice of Optimization approaches : Chooses either a rule based or cost based based to optimization.
Choice of Access Paths : For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain the table¡¦s data.
Choice of Join Orders : For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result.
Choice of Join Operations : For any join statement, the optimizer chooses an operation to use to perform the join.

Important Questions in Oracle, Developer /2000(Form 4.5 and Reports 2.5)
Oracle :
1) What are the Back ground processes in Oracle and what are they.
This is one of the most frequently asked question.There are basically 9 Processes but in a general system we need to mention the first five background processes.They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer(DBWR) : Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b) LogWriter(LGWR) : LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) : The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d) Process Monitor(PMON) : The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) : At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f) Archieves(ARCH) : The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) : The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) : The Dispatcher is useful in Multi Threaded Architecture
i) Lckn : We can have upto 10 lock processes for inter instance locking in parallel sql.
2) How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Defination Language(DDL) : The DDL statments define and maintain objects and drop objects.
b) Data Manipulation Language(DML) : The DML statments manipulate database data.
c) Transaction Control Statements : Manage change by DML
d) Session Control : Used to control the properties of current session enabling and disabling roles and changing .e.g Alter Statements,Set Role
e) System Control Statements : Change Properties of Oracle Instance .e.g:: Alter System
f) Embedded Sql : Incorporate DDL,DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close
3) What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.
4) Key Words Used in Oracle
The Key words that are used in Oracle are
a) Commiting : A transaction is said to be commited when the transaction makes
permanent changes resulting from the SQL statements.
b) Rollback : A transaction that retracts any of the changes resulting from SQL
statements in Transaction.
c) SavePoint : For long transactions that contain many SQL statements, intermediate
markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward : Process of applying redo log during recovery is called rolling
forward.
e) Cursor : A cursor is a handle ( name or a pointer) for the memory associated with a
specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) : The SGA is a shared memory region allocated by the
Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) : The PGA is a memory buffer that contains data and
control information for server process.
h) Database Buffer Cache : Databese Buffer of SGA stores the most recently used
blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
i) Redo log Buffer : Redo log Buffer of SGA stores all the redo log entries.
j) Redo Log Files : Redo log files are set of files that protect altered database data in
memory that has not been written to Data Files. They are basically used for backup when a database crashes.
k) Process : A Process is a 'thread of control' or mechansim in Operating System that
executes series of steps.
5) What are Procedure,functions and Packages
Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
Procedures do not Return values while Functions return one One Value
Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
6) What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
eg. operations insert,update ,delete 3 before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12. Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures : Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
7) How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule : The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule : The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules : The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
8) What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated : The Master cannot be deleted when a child is exisiting
b) Isolated : The Master can be deleted when the child is exisiting
c) Cascading : The child gets deleted when the Master is deleted.
9) What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query The operator must navigate to the detail block and explicitly execute a query
10) What are the Different Optimisation Techniques
The Various Optimisation techniques are
a) Execute Plan : we can see the plan of the query and change it accordingly based on the indexes
b)Optimizer_hint: set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept where (Deptno > 25)
c) Optimize_Sql : By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp : By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No
11) How do u implement the If statement in the Select Statement
We can implement the if statement in the select statement by using the Decode statement.
e.g select DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done .
12) How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
13) What are the inline and the precompiler directives
The inline and precompiler directives detect the values directly
14) How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code
15) How many minimum groups are required for a matrix report
The minimum number of groups in matrix report are 4
16) What is the difference between static and dynamic lov
The static lov contains the predetermined values while the dynamic lov contains values that come at run time
17) What are snap shots and views
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated
18) What are the OOPS concepts in Oracle.
Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the peroperty classes as classes and the items as objects
19) What is the difference between candidate key, unique key and primary key
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.
20) What is concurrency
Cuncurrency is allowing simultaneous access of same data by different users. Locks useful for accesing the database are
a) Exclusive
The exclusive lock is useful for locking the row when an insert,update or delete is being done.This lock should not be applied when we do only select from the row.
b) Share lock
We can do the table as Share_Lock as many share_locks can be put on the same resource.
21) Previleges and Grants
Previleges are the right to execute a particulare type of SQL statements.
eg. Right to Connect, Right to create, Right to resource
Grants are given to the objects so that the object might be accessed accordingly.The grant has to be given by the owner of the object.
22) Table Space,Data Files,Parameter File, Control Files
Table Space : The table space is useful for storing the data in the database.When a database is created two table spaces are created.
a) System Table space : This data file stores all the tables related to the system and dba tables
b) User Table space : This data file stores all the user related tables
We should have seperate table spaces for storing the tables and indexes so that the access is fast.
Data Files : Every Oracle Data Base has one or more physical data files.They store the data for the database.Every datafile is associated with only one database.Once the Data file is created the size cannot change.To increase the size of the database to store more data we have to add data file.
Parameter Files : Parameter file is needed to start an instance.A parameter file contains the list of instance configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files : Control files record the physical structure of the data files and redo log files. They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.
23) Physical Storage of the Data
The finest level of granularity of the data base are the data blocks.
Data Block : One Data Block correspond to specific number of physical database space
Extent : Extent is the number of specific number of contigious data blocks.
Segments : Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment : Non Clustered Table has data segment data of every table is stored in
cluster data segment
b) Index Segment : Each Index has index segment that stores data
c) Roll Back Segment : Temporarily store 'undo' information
24) What are the Pct Free and Pct Used
Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table
eg. Pctfree 20, Pctused 40
25) What is Row Chaining
The data of a row in a table may not be able to fit the same data block.Data for row is stored in a chain of data blocks .
26) What is a 2 Phase Commit
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase : Global coordinator asks participants to prepare
b) Commit Phase : Commit all participants to coordinator to Prepared, Read only
or abort Reply
27) What is the difference between deleting and truncating of tables
Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.
28) What are mutating tables
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
29) What are Codd Rules
Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.

30) What is Normalisation
Normalisation is the process of organising the tables to remove the redundancy.There are mainly 5 Normalisation rules.
a) 1 Normal Form : A table is said to be in 1st Normal Form when the attributes are
atomic
b) 2 Normal Form : A table is said to be in 2nd Normal Form when all the candidate
keys are dependant on the primary key
c) 3rd Normal Form : A table is said to be third Normal form when it is not dependant
transitively
31) What is the Difference between a post query and a pre query
A post query will fire for every row that is fetched but the pre query will fire only once.
32) Deleting the Duplicate rows in the table
We can delete the duplicate rows in the table by using the Rowid
33) Can U disable database trigger? How?
Yes. With respect to table
ALTER TABLE TABLE
[ DISABLE all_trigger ]
34) What is pseudo columns ? Name them?
A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:
* CURRVAL
* NEXTVAL
* LEVEL
* ROWID
* ROWNUM
35) How many columns can table have?
The number of columns in a table can range from 1 to 254.
36) Is space acquired in blocks or extents ?
In extents .
37) what is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values . Can not applied for HASH.
38) what are the datatypes supported By oracle (INTERNAL)?
Varchar2, Number,Char , MLSLABEL.

39 ) What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
40) Can you use select in FROM clause of SQL select ?
Yes.
Forms 4.5 Questions
1) Which trigger are created when master -detail rela?
master delete property
* NON-ISOLATED (default)
a) on check delete master
b) on clear details
c) on populate details
* ISOLATED
a) on clear details
b) on populate details
* CASCADE
a) per-delete
b) on clear details
c) on populate details

2) which system variables can be set by users?
SYSTEM.MESSAGE_LEVEL
SYSTEM.DATE_THRESHOLD
SYSTEM.EFFECTIVE_DATE
SYSTEM.SUPPRESS_WORKING
3) What are object group?
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or reference them in another module.
4) What are referenced objects?
Referencing allows you to create objects that inherit their functionality and appearance from other objects. Referencing an object is similar to copying an object, except that the resulting reference object maintains a link to its source object. A reference object automatically inherits any changes that have been made to the source object when you open or regenerate the module that contains the reference object.
5) Can you store objects in library?
Referencing allows you to create objects that inherit their functionality and appearance from other objects. Referencing an object is similar to copying an object, except that the resulting reference object maintains a link to its source object. A reference object automatically inherits any changes that have been made to the source object when you open or regenerate the module that contains the reference object.

6) Is forms 4.5 object oriented tool ? why?
yes , partially. 1) PROPERTY CLASS - inheritance property
2) OVERLOADING : procedures and functions.
7) Can you issue DDL in forms?
yes, but you have to use FORMS_DDL.
Referencing allows you to create objects that inherit their functionality and appearance from other objects. Referencing an object is similar to copying an object, except that the resulting reference object maintains a link to its source object. A reference object automatically inherits any changes that have been made to the source object when you open or regenerate the module that contains the reference object.
Any string expression up to 32K:
¡P a literal
¡P an expression or a variable representing the text of a block of dynamically
created PL/SQL code
¡P a DML statement or
¡P a DDL statement
Restrictions:
The statement you pass to FORMS_DDL may not contain bind variable references in the string, but the values of bind variables can be concatenated into the string before passing the result to FORMS_DDL.

8) What is SECURE property?
Hides characters that the operator types into the text item. This setting is typically used for password protection.
9 ) What are the types of triggers and how the sequence of firing in text item
Triggers can be classified as Key Triggers, Mouse Triggers ,Navigational Triggers.
Key Triggers : Key Triggers are fired as a result of Key action.e.g : Key-next-field, Key-up,Key-Down Mouse Triggers : Mouse Triggers are fired as a result of the mouse navigation.e.g. When-mouse-button-presed,when-mouse-doubleclicked,etc
Navigational Triggers : These Triggers are fired as a result of Navigation. E.g : Post-Text-item,Pre-text-item. We also have event triggers like when -new-form-instance and when-new-block-instance.
We cannot call restricted procedures like go_to(¡¥my_block.first_item¡¦) in the Navigational triggers . But can use them in the Key-next-item. The Difference between Key-next and Post-Text is an very important question. The key-next is fired as a result of the key action while the post text is fired as a result of the mouse movement. Key next will not fire unless there is a key event.
The sequence of firing in a text item are as follows ::
a) pre - text
b) when new item
c) key-next
d) when validate
e) post text

10 ) Can you store pictures in database? How?
Yes , in long Raw datatype.
11) What are property classes ? Can property classes have trigger?
Property class inheritance is a powerful feature that allows you to quickly define objects that conform to your own interface and functionality standards. Property classes also allow you to make global changes to applications quickly. By simply changing the definition of a property class, you can change the definition of all objects that inherit properties from that class. Yes . All type of triggers .
12) If you have property class attached to an item and you have same trigger written for the
item . Which will fire first?
Item level trigger fires , If item level trigger fires, property level trigger won't fire. Triggers at the lowest level are always given the first preference. The item level trigger fires first and then the block and then the Form level trigger.

13) What are record groups ? Can record groups created at run-time?
A record group is an internal Oracle Forms data structure that has a column/row framework similar to a database table. However, unlike database tables, record groups are separate objects that belong to the form module in which they are defined. A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of columns does not exceed 64K.Record group column names cannot exceed 30 characters. Programmatically, record groups can be used whenever the functionality offered by a two-dimensional array of multiple data types is desirable.
TYPES OF RECORD GROUP:
Query Record Group A query record group is a record group that has an associated SELECT statement.
The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement. The records in a query record group are the rows retrieved by the query associated with that record group.
Non-query Record Group A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime.
Static Record Group A static record group is not associated with a query; rather, you define its structure and row values at design time, and they remain fixed at runtime.
14) What are ALERT?
An ALERT is a modal window that displays a message notifiying operator of some application condition.
15) Can a button have icon and lable at the same time ?
-NO
16) What is mouse navigate property of button?
When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to move the focus to the item when the operator activates the item with the mouse.
When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulting validation) to move to the item when an operator activates the item with the mouse.
17) What is FORMS_MDI_WINDOW?
Forms run inside the MDI application window. This property is useful for calling a form from another one.
18) What are timers ? when when-timer-expired does not fire?
The When-Timer-Expired trigger can not fire during trigger, navigation, or transaction processing.
19) Can object group have a block?
Yes , object group can have block as well as program units.
20) How many types of canvases are there.
There are 2 types of canvases called as Content and Stack Canvas. Content canvas is the default and the one that is used mostly for giving the base effect. Its like a plate on which we add items and stacked canvas is used for giving 3 dimensional effect.
These are some other questions that may be asked

1) What are user-exits?
It invokes 3GL programs.

2) Can you pass values to-and-fro from foreign function ? how ?
Yes . You obtain a return value from a foreign function by assigning the return value to an Oracle Forms variable or item. Make sure that the Oracle Forms variable or item is the same data type as the return value from the foreign function. After assigning an Oracle Forms variable or item value to a PL/SQL variable, pass the PL/SQL variable as a parameter value in the PL/SQL interface of the foreign function. The PL/SQL variable that is passed as a parameter must be a valid PL/SQL data type; it must also be the appropriate parameter type as defined in the PL/SQL interface.
3) What is IAPXTB structure ?
The entries of Pro * C and user exits and the form which simulate the proc or user_exit are stored in IAPXTB table in d/b.
4) Can you call WIN-SDK thruo' user exits?
YES.
5) Does user exits supports DLL on MSWINDOWS ?
YES .
6) What is path setting for DLL?
Make sure you include the name of the DLL in the FORMS45_USEREXIT variable of the ORACLE.INI file, or rename the DLL to F45XTB.DLL. If you rename the DLL to F45XTB.DLL, replace the existing F45XTB.DLL in the \ORAWIN\BIN directory with the new F45XTB.DLL.
7) How is mapping of name of DLL and function done?
The dll can be created using the Visual C++ / Visual Basic Tools and then the dll is put in the path that is defined the registery.
8) what is precompiler?
It is similar to C precompiler directives.
9) Can you connect to non - oracle datasource ? How?
Yes .
10 ) what are key-mode and locking mode properties? level ?
Key Mode : Specifies how oracle forms uniquely identifies rows in the database.This is
property includes for application that will run against NON-ORACLE
datasources .
Key setting unique (default.)
dateable
n-updateable.
Locking mode : Specifies when Oracle Forms should attempt to obtain database locks on
rows that correspond to queried records in the form.
a) immediate b) delayed
11) What are savepoint mode and cursor mode properties ? level?
Specifies whether Oracle Forms should issue savepoints during a session. This property is included primarily for applications that will run against non-ORACLE data sources. For applications that will run against ORACLE, use the default setting.
Cursor mode - define cursur state across transaction
Open/close.
12) What is transactional trigger property?
Identifies a block as transactional control block. i.e. non - database block that oracle forms should manage as transactional block.(NON-ORACLE datasource) default - FALSE.
13) What is OLE automation ?
OLE automation allows an OLE server application to expose a set of commands and functions that can be invoked from an OLE container application. OLE automation provides a way for an OLE container application to use the features of an OLE server application to manipulate an OLE object from the OLE container environment. (FORMS_OLE)
14) What does invoke built-in do?
This procedure invokes a method.
Syntax:
PROCEDURE OLE2.INVOKE
(object obj_type,
method VARCHAR2,
list list_type := 0);
Parameters:
object Is an OLE2 Automation Object.
method Is a method (procedure) of the OLE2 object.
list Is the name of an argument list assigned to the
OLE2.CREATE_ARGLIST function.
15) What are OPEN_FORM,CALL_FORM,NEW_FORM? diff?
CALL_FORM : It calls the other form. but parent remains active, when called form
completes the operation , it releases lock and control goes back to the calling form. When you call a form, Oracle Forms issues a savepoint for the called form. If the
CLEAR_FORM function causes a rollback when the called form is current, Oracle Forms
rolls back uncommitted changes to this savepoint.
OPEN_FORM : When you call a form, Oracle Forms issues a savepoint for the called
form. If the
CLEAR_FORM function causes a rollback when the called form is current, Oracle Forms
rolls back uncommitted changes to this savepoint.
NEW_FORM : Exits the current form and enters the indicated form.The calling form is
terminated as the parent form. If the calling form had been called by a higher form, Oracle Forms keeps the higher call active and treats it as a call to the new form. Oracle Forms releases memory (such as database cursors) that the terminated form was using. Oracle Forms runs the new form with the same Runform options as the parent form. If the parent form was a called form, Oracle Forms runs the new form with the same options as the parent form.
16 ) What is call form stack?
When successive forms are loaded via the CALL_FORM procedure, the resulting module hierarchy is known as the call form stack.
17) Can u port applictions across the platforms? how?
Yes we can port applications across platforms.Consider the form developed in a windows system.The form would be generated in unix system by using f45gen my_form.fmb scott/tiger

GUI
1) What is a visual attribute?
Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application's interface.
2) Diff. between VAT and Property Class? imp
Named visual attributes define only font, color, and pattern attributes; property classes can contain these and any other properties.You can change the appearance of objects at runtime by changing the named visual attribute programmatically; property class assignment cannot be changed programmatically. When an object is inheriting from both a property class and a named visual attribute, the named visual attribute settings take precedence, and any visual attribute properties in the class are ignored.
3 ) Which trigger related to mouse?
* When-Mouse-Click
* When-Mouse-DoubleClick
* When-Mouse-Down
* When-Mouse-Enter
* When-Mouse-Leave
* When-Mouse-Move
* When-Mouse-Up
4) What is Current record attribute property?
Specifies the named visual attribute used when an item is part of the current record.
Current Record Attribute is frequently used at the block level to display the current row in a multi-record If you define an item-level Current Record Attribute, you can display a pre-determined item in a special color when it is part of the current record, but you cannot dynamically highlight the current item, as the input focus changes.
5) Can u change VAT at run time?
Yes. You can programmatically change an object's named visual attribute setting to change the font, color, and pattern of the object at runtime.
6) Can u set default font in forms?
Yes. Change windows registry(regedit). Set form45_font to the desired font.
7) Can u have OLE objects in forms?
Yes.
8) Can u have VBX and OCX controls in forms ?
Yes.
9) What r the types of windows (Window style)?
Specifies whether the window is a Document window or a Dialog window.
10) What is OLE Activation style property?
Specifies the event that will activate the OLE containing item.
11) Can u change the mouse pointer ? How?
Yes. Specifies the mouse cursor style. Use this property to dynamically change the shape of the cursor.
Reports 2.5
1) How many types of columns are there and what are they
Formula columns : For doing mathematical calculations and returning one value
Summary Columns : For doing summary calculations such as summations etc.
Place holder Columns : These columns are useful for storing the value in a variable
2) Can u have more than one layout in report
It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
3) Can u run the report with out a parameter form
Yes it is possible to run the report without parameter form by setting the PARAM value to Null
4) What is the lock option in reports layout
By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields .
5) What is Flex
Flex is the property of moving the related fields together by setting the flex property on
6) What are the minimum number of groups required for a matrix report
The minimum of groups required for a matrix report are 4

Important Questions in Oracle, Developer /2000(Form 4.5 and Reports 2.5)

Oracle
1) What are the Back ground processes in Oracle and what are they.
1) This is one of the most frequently asked question.There are basically 9 Processes but in a
general system we need to mention the first five background processes.They do the house keeping
activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the
most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

2) How many types of Sql Statements are there in Oracle
2) There are basically 6 types of sql statments.They are
a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statments manipulate database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g :: Alter Statements,Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g:: Alter System
f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close
3) What is a Transaction in Oracle
3) A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.
4) Key Words Used in Oracle
4) The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.
5) What are Procedure, functions and Packages
5) Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
Procedures do not return values while Functions return only One Value
Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
6) What are Database Triggers and Stored Procedures
6) Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
7) How many Integrity Rules are there and what are they
7) There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
8) What are the Various Master and Detail Relation ships.
8) The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.
9) What are the Various Block Coordination Properties
9) The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query
10) What are the Different Optimisation Techniques
10) The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint:
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No
11) How do u implement the If statement in the Select Statement
11) We can implement the if statement in the select statement by using the Decode statement.
e.g select DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done .
12)How many types of Exceptions are there
12) There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
13) What are the inline and the precompiler directives
13) The inline and precompiler directives detect the values directly
14) How do you use the same lov for 2 columns
14) We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code
15) How many minimum groups are required for a matrix report
15) The minimum number of groups in matrix report are 4
16) What is the difference between static and dynamic lov
16) The static lov contains the predetermined values while the dynamic lov contains values that come at run time
17) What are snapshots and views
17) Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated
18) What are the OOPS concepts in Oracle.
18) Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the
objects. OOPS supports the concepts of objects and classes and we can consider the peroperty classes as classes and the items as objects
19) What is the difference between candidate key, unique key and primary key
19) Candidate keys are the columns in the table that could be the primary keys and the primary key
is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.
20)What is concurrency
20) Cuncurrency is allowing simultaneous access of same data by different users. Locks useful for accesing the database are
a) Exclusive
The exclusive lock is useful for locking the row when an insert,update or delete is being done.This lock should not be applied when we do only select from the row.
b) Share lock
We can do the table as Share_Lock as many share_locks can be put on the same resource.
21) Previleges and Grants
21) Previleges are the right to execute a particulare type of SQL statements.
e.g :: Right to Connect, Right to create, Right to resource
Grants are given to the objects so that the object might be accessed accordingly.The grant has to be
given by the owner of the object.
22)Table Space,Data Files,Parameter File, Control Files
22)Table Space :: The table space is useful for storing the data in the database.When a database is created two table spaces are created.
a) System Table space :: This data file stores all the tables related to the system and dba tables
b) User Table space :: This data file stores all the user related tables
We should have seperate table spaces for storing the tables and indexes so that the access is fast.
Data Files :: Every Oracle Data Base has one or more physical data files.They store the data for the database.Every datafile is associated with only one database.Once the Data file is created the size cannot change.To increase the size of the database to store more data we have to add data file.
Parameter Files :: Parameter file is needed to start an instance.A parameter file contains the list of instance configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files :: Control files record the physical structure of the data files and redo log files
They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.
23) Physical Storage of the Data
23) The finest level of granularity of the data base are the data blocks.
Data Block :: One Data Block correspond to specific number of physical database space
Extent :: Extent is the number of specific number of contigious data blocks.
Segments :: Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment :: Non Clustered Table has data segment data of every table is stored in
cluster data segment
b) Index Segment :: Each Index has index segment that stores data
c) Roll Back Segment :: Temporarily store 'undo' information
24) What are the Pct Free and Pct Used
24) Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40
25) What is Row Chaining
25) The data of a row in a table may not be able to fit the same data block.Data for row is stored in a chain of data blocks .
26) What is a 2 Phase Commit
26) Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants to prepare
b) Commit Phase :: Commit all participants to coordinator to Prepared, Read only or abort Reply
27) What is the difference between deleting and truncating of tables
27) Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.
28) What are mutating tables
28) When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
29) What are Codd Rules
29) Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.
30) What is Normalisation
30) Normalization is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalization rules.
a) 1 Normal Form :: A table is said to be in 1st Normal Form when the attributes are atomic
b) 2 Normal Form :: A table is said to be in 2nd Normal Form when all the candidate keys are dependant on the primary key
c) 3rd Normal Form :: A table is said to be third Normal form when it is not dependant transitively
31) What is the Difference between a post query and a pre query
31) A post query will fire for every row that is fetched but the pre query will fire only once.
32) Deleting the Duplicate rows in the table
32) We can delete the duplicate rows in the table by using the Rowid

33) Can U disable database trigger? How?
33) Yes. With respect to table
ALTER TABLE TABLE
[ DISABLE all_trigger ]
34) What is pseudo columns ? Name them?
34) A pseudocolumn behaves like a table column, but is not actually
stored in the table. You can select from pseudocolumns, but you
cannot insert, update, or delete their values. This section
describes these pseudocolumns:
* CURRVAL
* NEXTVAL
* LEVEL
* ROWID
* ROWNUM
35) How many columns can table have?
The number of columns in a table can range from 1 to 254.
36) Is space acquired in blocks or extents ?
In extents .
37) what is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values .
Can not applied for HASH.
38) what are the datatypes supported By oracle (INTERNAL)?
Varchar2, Number,Char , MLSLABEL.
39 ) What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
40) Can you use select in FROM clause of SQL select ?
Yes.

Forms 4.5 Questions
1) Which trigger are created when master -detail rela?
1) master delete property
* NON-ISOLATED (default)
a) on check delete master
b) on clear details
c) on populate details
* ISOLATED
a) on clear details
b) on populate details
* CASCADE
a) per-delete
b) on clear details
c) on populate details

2) which system variables can be set by users?
2)
SYSTEM.MESSAGE_LEVEL
SYSTEM.DATE_THRESHOLD
SYSTEM.EFFECTIVE_DATE
SYSTEM.SUPPRESS_WORKING
3) What are object group?
3)
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or reference them in another module.
4) What are referenced objects?
4)
Referencing allows you to create objects that inherit their functionality and appearance from other objects.
Referencing an object is similar to copying an object, except that the resulting reference object maintains a
link to its source object. A reference object automatically inherits any changes that have been made to the
source object when you open or regenerate the module that contains the reference object.
5) Can you store objects in library?
5)
Referencing allows you to create objects that inherit their functionality and appearance from other
objects. Referencing an object is similar to copying an object, except that the resulting reference
object maintains a link to its source object. A reference object automatically inherits any changes that
have been made to the source object when you open or regenerate the module that contains the
reference object.

6) Is forms 4.5 object oriented tool ? why?
6)
yes , partially. 1) PROPERTY CLASS - inheritance property
2) OVERLOADING : procedures and functions.
7) Can you issue DDL in forms?
7)
yes, but you have to use FORMS_DDL.
Referencing allows you to create objects that inherit their functionality and appearance from other
objects. Referencing an object is similar to copying an object, except that the resulting reference object
maintains a link to its source object. A reference object automatically inherits any changes that have
been made to the source object when you open or regenerate the module that contains the reference object.
Any string expression up to 32K:
¡Pa literal
¡P an expression or a variable representing the text of a block of dynamically created PL/SQL code
¡P a DML statement or
¡P a DDL statement
Restrictions:
The statement you pass to FORMS_DDL may not contain bind variable references in the string, but the
values of bind variables can be concatenated into the string before passing the result to FORMS_DDL.

8) What is SECURE property?
8)- Hides characters that the operator types into the text item. This setting is typically used for
password protection.
9 ) What are the types of triggers and how the sequence of firing in text item
9)
Triggers can be classified as Key Triggers, Mouse Triggers ,Navigational Triggers.
Key Triggers :: Key Triggers are fired as a result of Key action.e.g :: Key-next-field, Key-up,Key-Down
Mouse Triggers :: Mouse Triggers are fired as a result of the mouse navigation.e.g. When-mouse-button-presed,when-mouse-doubleclicked,etc
Navigational Triggers :: These Triggers are fired as a result of Navigation. E.g : Post-Text-item,Pre-text-item.
We also have event triggers like when ¡Vnew-form-instance and when-new-block-instance.
We cannot call restricted procedures like go_to(¡¥my_block.first_item¡¦) in the Navigational triggers
But can use them in the Key-next-item.
The Difference between Key-next and Post-Text is an very important question. The key-next is fired as a result of the key action while the post text is fired as a result of the mouse movement. Key next will not fire unless there is a key event.
The sequence of firing in a text item are as follows ::
a) pre - text
b) when new item
c) key-next
d) when validate
e) post text
10 ) Can you store pictures in database? How?
10)Yes , in long Raw datatype.
11) What are property classes ? Can property classes have trigger?
11) Property class inheritance is a powerful feature that allows you to quickly define objects that conform to
your own interface and functionality standards. Property classes also allow you to make global changes to
applications quickly. By simply changing the definition of a property class, you can change the definition
of all objects that inherit properties from that class.
Yes . All type of triggers .
* 12 a) If you have property class attached to an item and you have same trigger written for the item .
Which will fire first?
12)Item level trigger fires , If item level trigger fires, property level trigger won't fire. Triggers at the lowest level are always given the first preference. The item level trigger fires first and then the block and then the Form level trigger.

13) What are record groups ? * Can record groups created at run-time?
13)A record group is an internal Oracle Forms data structure that has a column/row framework similar to a
database table. However, unlike database tables, record groups are separate objects that belong to the
form module in which they are defined. A record group can have an unlimited number of columns of type
CHAR, LONG, NUMBER, or DATE provided that the total number of columns does not exceed 64K.
Record group column names cannot exceed 30 characters.
Programmatically, record groups can be used whenever the functionality offered by a two-dimensional
array of multiple data types is desirable.
TYPES OF RECORD GROUP:
Query Record Group A query record group is a record group that has an associated SELECT statement.
The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement. The records in a query record group are the rows retrieved by the query associated with that record group.
Non-query Record Group A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime.
Static Record Group A static record group is not associated with a query; rather, you define its
Structure and row values at design time, and they remain fixed at runtime.

14) What are ALERT?
14)An ALERT is a modal window that displays a message notifying operator of some application
Condition.
15) Can a button have icon and label at the same time ?
15) -NO
16) What is mouse navigate property of button?
16)
When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to move the focus
to the item when the operator activates the item with the mouse.
When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulting validation) to move to the item when an operator activates the item with the mouse.
17) What is FORMS_MDI_WINDOW?
17) forms run inside the MDI application window. This property is useful for calling a form from another one.
18) What are timers ? when when-timer-expired does not fire?
18) The When-Timer-Expired trigger can not fire during trigger, navigation, or transaction processing.
19 ) Can object group have a block?
19)Yes , object group can have block as well as program units.
20) How many types of canvases are there.
20)There are 4 types of canvases called as Content and Stack Canvas. Content canvas is the default and the one that is used mostly for giving the base effect. Its like a plate on which we add items and stacked canvas is used for giving 3 dimensional effect.
The following questions might not be asked in an Average Interview and could be asked when the Interviewer wants to trouble u and go deeppppppppppppp¡K¡KHe cannot go further¡K..

1) What are user-exits?
1) It invokes 3GL programs.
2) Can you pass values to-and-fro from foreign function ? how ?
2) Yes . You obtain a return value from a foreign function by assigning the return value to an Oracle Forms
variable or item. Make sure that the Oracle Forms variable or item is the same data type as the return value
from the foreign function.
After assigning an Oracle Forms variable or item value to a PL/SQL variable, pass the PL/SQL variable as
a parameter value in the PL/SQL interface of the foreign function. The PL/SQL variable that is passed as
a parameter must be a valid PL/SQL data type; it must also be the appropriate parameter type as defined
in the PL/SQL interface.
3) What is IAPXTB structure ?
3) The entries of Pro * C and user exits and the form which simulate the proc or user_exit are stored in IAPXTB table in d/b.
4) Can you call WIN-SDK thruo' user exits?
4) YES.
5) Does user exits supports DLL on MSWINDOWS ?
5) YES .
6) What is path setting for DLL?
6) Make sure you include the name of the DLL in the FORMS45_USEREXIT variable of the ORACLE.INI file, or rename the DLL to F45XTB.DLL. If you rename the DLL to F45XTB.DLL, replace the existing F45XTB.DLL in the \ORAWIN\BIN directory with the new F45XTB.DLL.
7) How is mapping of name of DLL and function done?
7) The dll can be created using the Visual C++ / Visual Basic Tools and then the dll is put in the
path that is defined the registery.
8) what is precompiler?
8) It is similar to C precompiler directives.
9) Can you connect to non - oracle datasource ? How?
9) Yes .
10 ) what are key-mode and locking mode properties? level ?
10) Key Mode : Specifies how oracle forms uniquely identifies rows in the database.This is property includes
for application that will run against NON-ORACLE datasources .
Key setting unique (default.)
dateable
n-updateable.
Locking mode :
Specifies when Oracle Forms should attempt to obtain database locks on rows that correspond to queried records in the form.
a) immediate b) delayed
11) What are savepoint mode and cursor mode properties ? level?
11) Specifies whether Oracle Forms should issue savepoints during a session. This property is included primarily for applications that will run against non-ORACLE data sources. For applications that will run against ORACLE, use the default setting.
Cursor mode - define cursur state across transaction
Open/close.
12) Can you replace default form processing ? How ?
13) What is transactional trigger property?
13) Identifies a block as transactional control block. i.e. non - database block that oracle forms should manage as transactional block.(NON-ORACLE datasource) default - FALSE.
14) What is OLE automation ?
14) OLE automation allows an OLE server application to expose a set of commands and functions that can be
invoked from an OLE container application. OLE automation provides a way for an OLE container application to use the features of an OLE server application to manipulate an OLE object from the OLE container environment. (FORMS_OLE)
15) What does invoke built-in do?
15) This procedure invokes a method.
Syntax:
PROCEDURE OLE2.INVOKE
(object obj_type,
method VARCHAR2,
list list_type := 0);
Parameters:
object Is an OLE2 Automation Object.
method Is a method (procedure) of the OLE2 object.
list Is the name of an argument list assigned to the OLE2.CREATE_ARGLIST function.
16) What are OPEN_FORM,CALL_FORM,NEW_FORM? diff?
16) CALL_FORM : It calls the other form. but parent remains active, when called form completes the operation , it releases lock and control goes back to the calling form.
When you call a form, Oracle Forms issues a savepoint for the called form. If the CLEAR_FORM function
causes a rollback when the called form is current, Oracle Forms rolls back uncommitted changes to this
savepoint.
OPEN_FORM : When you call a form, Oracle Forms issues a savepoint for the called form. If the
CLEAR_FORM function causes a rollback when the called form is current, Oracle Forms rolls back
uncommitted changes to this savepoint.
NEW_FORM : Exits the current form and enters the indicated form. The calling form is terminated as
the parent form. If the calling form had been called by a higher form, Oracle Forms keeps the higher call
active and treats it as a call to the new form. Oracle Forms releases memory (such as database cursors)
that the terminated form was using.
Oracle Forms runs the new form with the same Runform options as the parent form. If the parent form was
a called form, Oracle Forms runs the new form with the same options as the parent form.
17 ) What is call form stack?
17) When successive forms are loaded via the CALL_FORM procedure, the resulting module hierarchy is known as the call form stack.
18) Can u port applictions across the platforms? how?
18) Yes we can port applications across platforms.Consider the form developed in a windows system.The form would be generated in unix system by using f45gen my_form.fmb scott/tiger
GUI
1) What is a visual attribute?
1) Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application's interface.
2) Diff. between VAT and Property Class? imp
2)Named visual attributes define only font, color, and pattern attributes; property classes can contain these and any other properties.
You can change the appearance of objects at runtime by changing the named visual attribute
programmatically; property class assignment cannot be changed programmatically.
When an object is inheriting from both a property class and a named visual attribute, the named visual
attribute settings take precedence, and any visual attribute properties in the class are ignored.
3 ) Which trigger related to mouse?
3) When-Mouse-Click
When-Mouse-DoubleClick
When-Mouse-Down
When-Mouse-Enter
When-Mouse-Leave
When-Mouse-Move
When-Mouse-Up
4) What is Current record attribute property?
4) Specifies the named visual attribute used when an item is part of the current record.
Current Record Attribute is frequently used at the block level to display the current row in a multi-record
If you define an item-level Current Record Attribute, you can display a pre-determined item in a special color
when it is part of the current record, but you cannot dynamically highlight the current item, as the input focus changes.
5) Can u change VAT at run time?
5) Yes. You can programmatically change an object's named visual attribute setting to change the font, color,
and pattern of the object at runtime.
6) Can u set default font in forms?
6) Yes. Change windows registry(regedit). Set form45_font to the desired font.
7) Can u have OLE objects in forms?
7) Yes.
8) Can u have VBX and OCX controls in forms ?
8) Yes.
9) What r the types of windows (Window style)?
9) Specifies whether the window is a Document window or a Dialog window.
10) What is OLE Activation style property?
10) Specifies the event that will activate the OLE containing item.

11) Can u change the mouse pointer ? How?
11) Yes. Specifies the mouse cursor style. Use this property to dynamically change the shape of the cursor.

Reports 2.5
1) How many types of columns are there and what are they
1) Formula columns :: For doing mathematical calculations and returning one value
Summary Columns :: For doing summary calculations such as summations etc.
Place holder Columns :: These columns are useful for storing the value in a variable
2) Can u have more than one layout in report
2) It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
3) Can u run the report with out a parameter form
3) Yes it is possible to run the report without parameter form by setting the PARAM value to Null
4) What is the lock option in reports layout
4) By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields .
5) What is Flex
5) Flex is the property of moving the related fields together by setting the flex property on
6) What are the minimum number of groups required for a matrix report
6) The minimum of groups required for a matrix report are 4
3.0 Data Base Administration

3.1 Introduction to DBA
1. What is a database instance ? Explain
A database instance (server) is a set of memory structures and background processes that access a set of database files.
The process can be shared by all users.
The memory structures that are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.
2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).
3. What is Schema ?
The set of objects owned by user account is called the schema
4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.
An index is automatically created when a unique or primary key constraint clause is specified in create table command (Ver 7.0)
5. What is clustres ?
Group of tables physically stored together because they share common columns and are often used together is called Clusters.
6. What is a cluster key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.
7. What are the basic element of Base configuration of an oracle Database ?
It consists of
one or more data files
one or more control files
two or more redo log files

The database contains
Multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables
User objects (tables,indexes,views etc)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA
8. What is deadlock ? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.
In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

3.2 Memory Management
9. What is SGA ? How it is different from Ver 6 and Ver 7 ?
The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information about the database.
The structure is Database buffers, Dictionary Cache, Redo Log Buffer and Shared SQL pool (Ver 7) area.
10. What is Shared SQL pool ?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

11. What is meant by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User process.
12. What is a data segment ?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
13. What are the factors causing the reparsing of SQL statements in SGA ?
Due to insufficient Shared SQL pool size
Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater that 1 then increase the SHARED_POOL_SIZE.

3.3 Logical & Physical Architecture of Database
14. What is Database Buffers ?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters. DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.
15. What is dictionary cache ?
Dictionary cache is information about the database objects stored in a data dictionary table.
16. What is meant by recursive hits ?
Number of times processes repeatedly query the dictionary table is called recursive hits. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.
17. What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo log files so that they can be used in roll forward operation during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

18. How will you swap objects into a different table space for an existing database ?
Export the user
Perform import using the command imp system/manager file=export.dp indexfile=newfile.sql. This will create all definitions into newfile.sql.
Drop necessary objects.
Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.
19. List the Optimal Flexible Architecture (OFA) of Oracle database ? or
How can we organise the tablespaces in Oracle database to have maximum performance ?
SYSTEM - Data dictionary tables
DATA - Standard operational tables
DATA2 - Static tables used for standard operations
INDEXES - Indexes for Standard operational tables
INDEXES1 - Indexes of static tables used for standard operations
TOOLS - Tool table
TOOLS1 - Indexes for tools table
RBS - Standard Operations Rollback Segments
RBS1,RBS2 - Additional/Special rollback segments
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users
USERS - User tablespaces.
20. How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name
21. What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
22. How free extents are managed in Ver 6 and Ver 7. ?
Free extents cannot be merged together in Ver 6.0
Free extents are periodically coalesces with the neighboring free extent Ver 7.0.
23. Which parameter in Storage clause will reduce no of rows per block ?
PCTFREE parameter
Row size also reduces no of rows per block.

24. What is significance of having storage clause ?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.
25. How does space allocation take place within a block ?
Each block contains entries as follows :
Fixed block header
Variable block header
Row header, row date (Multiple rows may exists)
PCTFREE (% of free space for row updation in future)
26. What is the role of PCTFREE parameter is Storage clause ?
This is used to reserve certain amount of space in a block for expansion of rows.
27. What is the OPTIMAL parameter ?
It is used to set the optimal length of rollback segment.
28. What is the functionality of SYSTEM tablespace ?
To manage the database level of transactions such as modifications of the data dictionary table that record information about the free space usage.
29. How will you create multiple rollback segments in a database ?
Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace.
Create a Second Rollback Segment name R0 in the SYSTEM tablespace.
Make new rollback segment available (After shutdown, modify init.ora file and Start database)
Create other tablespace (RBS) for rollback segments.
Create additional Rollback segment in tablespace (RBS)
Deactivate Rollback Segment R0 and activate the newly created rollback segments.
30. How the space utilisation takes place within rollback segments ?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent. (No of extents is based on the OPTIMAL size).

31. Why query fails sometimes ?
Rollback segment dynamically extent to handle larger transactions entry loads.
A single transaction may wipeout all available free space in the Rollback Segment Tablespace. This prevents other user using Rollback segment.
32. How will you monitor the space allocation ?
By querying DBA_SEGMENT table/View
33. How will you monitor rollback segment status ?
Querying the DBA_ROLLBACK_SEGS view
The status available as follows :
IN USE - Rollback Segment is on-line
AVAILABLE - Rollback Segment available bur not on-line
OFF-LINE - Rollback Segment us off-line
INVALID - Rollback Segment dropped
NEEDS RECOVERY - Contains data but need recovery or corrupted
PARTLY AVAILABLE - Contains data from an unresolved transaction involving a distributed database
34. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend.
Transaction Begins
An entry is made in the RBS header for new transactions entry
Transaction acquired blocks in an extent of RBS
The entry attempts to wrap into second extent. None is available. So that the RBS must extent.
The RBS checks to see if it is oldest inactive segment
Oldest inactive segment is eliminated
RBS extends
The Data dictionary table for space management are updated
Transaction Completes.
35. How can we plan storage for very large tables ?
Limit the number of extents in the table
Separate the Table from its indexes
Allocate sufficient temporary storage

36. How will you estimate the space required by non-clustered tables ?
Calculate the total block header size
Calculate the available data space per block
Calculate the combined column length of the average row
Calculate the total average row size
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table
After arriving the calculation add the additional space to calculate the initial extent size for working area
37. Is it possible to use raw devices as data file and what is the advantages over file system files ?
Yes.
The advantages over file system files :
I/O will be improved because Oracle is bye-passing the kernal while writing into disk.
Disk Corruption will be very less.

38. What is a control file ?
Database's overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.
39. How to implement the multiple control files for an existing database ?
Shutdown the database
Copy one of the existing control file to new location'
Edit config.ora file by adding new control file name
Restart the database
40. What is meant by Redo Log file mirroring ? How it can be achieved ?
Process of having a copy of redo log files is called mirroring.
This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group.
41. What is advantage of having disk shadowing/Mirroring ?
Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk.
Improved performance because of most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
42. What is use of rollback segment in Database ?
They allow the database to maintain read consistency between multiple transactions.
43. What is a Rollback segment entry ?
It is the set of before image data blocks that contain rows that are modified by a transaction.
Each Rollback Segment entry must be completed within one rollback segment.
A single rollback segment can have multiple rollback segment entries.
44. What a hit ratio ?
It is a measure of well the data cache buffer is handling requests for data.
Hit Ratio = (Logical Reads - Physical reads - Hit Misses) / Logical reads.
45. When will be a segment released ?
When Segment is dropped.
When Shrink (RBS only)
When truncated (TRUNCATE used with drop storage option)
46. What are disadvantages of having raw devices ?
We should depend on export/import utility for backup/recovery (fully reliable)
The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
47. List the factors that can affect the accuracy of the estimations ?
The space used transaction entries and deleted records does not become free immediately after completion due to delayed cleanout.
Trailing nulls and length bytes are not stored.
Inserts of, updates to, and deletes of rows as well as columns larger than a single data block, can cause fragmentation and chained row pieces.

3.4 Database Security & Administration
48. What is user account in Oracle database ?
An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.

49. How will you enforce security using stores procedures ?
Don't grant user access directly to tables within application
Instead grant the ability to access the procedures that access the tables
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
50. What are the dictionary tables used to monitor a database spaces ?
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES
51. What are responsibilities of a Database Administrator ?
1. Installing and upgrading the Oracle Server and application tools
2. Allocating system storage and planning future storage requirements for the database system.
3. Managing primary database structures(tablespaces)
4. Managing primary objects (table,views,indexes)
5. Enrolling users and maintaining system security
6. Ensuring compliance with Oracle license agreement
7. Controlling and monitoring user access to the database
8. Monitoring and optimising the performance of the database
9. Planning for backup and recovery of database information
10. Maintain archived data on tape
11. Backing up and restoring the database
12. Contacting Oracle Corporation for technical support
52. What are requirements one should fulfill to connect to ORACLE as internal?
Operating system account has the operating system privileges that allow you to connect
One should be authorised to connect as internal
Database has a password for internal connections, and you know the password must use a dedicated server
53. What are the roles and user accounts created automatically with the database ?
DBA role - Contains all database system privileges
SYS user account - The DBA role will be assigned to this account. All of the base tables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE.
SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using the username.
54. What are the database administrators utilities available ?
SQL*DBA - This allows DBA to monitor and control an ORACLE database.
SQL*Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables.
EXPORT(exp) and IMPOER (imp) utilities allow you to move existing data in ORACLE format to and from ORACLE database.
55. What are the minimum parameters should exist in the parameter file (init.ora) ?
DB_NAME - Must set to a text string of not more that 8 characters and it will be stored inside the datafiles, redo log files and control file while database creation.
DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters (DB_NAME & DB_DOMAIN)
CONTROL_FILES - List of control filenames of the database. If name is not mentioned then default name will be used.
DB_BLOCK_SIZE - The default data block size and is operating system dependent. It cannot be changed after database creation except by re-creating the database.
DB_BLOCK_BUFFERS - The maximum number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process) and additional 1 for each user.
ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup.
Also optionally LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.
56. What is a trace file and how it is created ?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
57. What are roles ? How can we implement roles ?
Roles are easiest way to grant and manage common privileges needed by different groups of database users.
Creating roles and assigning privies to roles.
Assign each role to group of users. This will simplify the job of assigning privileges to individual users.
58. What are the steps to switch a database's archiving mode between NOARCHIEVELOG and ARCHIVELOG mode ?
1. Shutdown the database instance
2. Backup the database
3. Perform any operating system specific steps (optional)
4. Start up a new instance and mount but do not open the database
5. Switch the database's archiving mode.
59. How can you enable automatic archiving ?
Shut the database
Backup the database
Modify/Include LOG_ARCHIVE_START = TRUE in init.ora file
Start up the database
60. How can we specify the Archived log file name format and destination ?
By setting the following values in init.ora file
LOG_ARCHIVE_FORMAT = arch%S/s/T/t.arc (%S - Log sequence number and is zero left-paded, %s - Log sequence number not paded, %T - Thread number left-zero-paded and %t - Thread number not paded). The file name created is arch0001.arc %S is used.
LOG_ARCHIEVE_DEST = path
Shut the database and change these parameters in init.ora files.
61. What is the user of ANALYZE command ?
To perform one of these function on an index, table, or cluster :
to collect statistics about object used by the optimizer and store them in the data dictionary.
to delete statistics about the object from the data dictionary
to validate the structure of the object
to identify migrated and chained rows of the table or cluster.

3.5 Managing Distributed Databases
62. How can we reduce the network traffic ?
Replication of data in distributed environment
Using snapshots to replicate data
Using remote procedure calls.
63. What is a snapshot ?
Snapshot is an object used to dynamically replicate data between distributed databases at specified time intervals. In ver 7.0 they are read only.

64. What are the various type of snapshots ?
Simple and Complex.
65. Differentiative simple and complex, snapshots
A simple snapshot is based on a query that does not contains GROUP BY clauses, CONNECT by clauses, JOINs, Subquery or a set of operations.
A complex snapshots contain at least any one of the above.
66. What is dynamic data replication ?
Updating or inserting records in remote database through database triggers. It may fail if remote database is having any problem.
67. How can you enforce referential integrity in snapshots ?
Time the references to occur when master tables are not in use.
Perform the references manually immediately after locking the master tables.
We can join tables in snapshots by creating a complex snapshot that will be based on the master tables.

68. What are the options available to refresh snapshots ?
COMPLETE - Tables are completely regenerated using the snapshot's query and the master tables every time the snapshot referenced.
FAST - If simple snapshot used then a snapshot log can be used to send only the changes to the snapshot tables.
FORCE - The default value. If possible it performs a FAST refresh; Otherwise it will perform a COMPLETE refresh.
69. What is a snapshot tag ?
It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.
70. When will the data in the snapshot log be used ?
The data in the snapshot log is used during fast references of the table's snapshots.

71. What are the pre-requisites to create a snapshot log ?
We must be able to create a after row trigger on table (i.e. it should not be already available)
After giving table previleges.
We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects that support its snapshot log.
The master table name should be less than or equal to 23 characters.
(The table name created will be MLOG$_tablename, and trigger name will be TLOG$_tablename)
72. What are the benefits of distributed options in databases ?
Database on other servers can be updated and those transactions can be grouped together with others in a logical unit.
Database uses a two phase commit
73. What is a two-phase commit ?
Database on other servers can be updated and those transactions can be grouped together with others in a logical unit is called two-phase commit. They are
The Preparation Phase : An initiating node called the global coordinator notifies all sites involved in the transaction to be ready either commit or rollback the transaction.
The Commit Phase : If there is no problem with prepare phase, then all sites commit their transactions. If a network or node failure occurs, then all sites rollback their transactions.
3.6 Managing Backup & Recovery
74. What are the different methods of backing up oracle database ?
Logical Backups
Cold Backups
Hot Backups (Archive log)
75. What is a logical backup ?
Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.

76. What is cold backup ? What are the elements of it ?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take
All Data files
All Control files
All on-line redo log files
Then init.ora file (optional)
77. What are the different kind of export backups ?
Full backup - Complete database
Incremental Backup - Only affected tables from last incremental date / Full backup date
Cumulative backup - Only affected table from the last cumulative date / Full backup date
78. What is hot backup and how it can be taken ?
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up :
All data files
All archive log, redo log files
On control file.
79. What is the use of FILE option in EXP command ?
To give the export file name.
80. What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.
81. What is the use of GRANT option in EXP command ?
A flag to indicate whether grants on database objects will be exported or not. Values is 'Y' or 'N'.
82. What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.
83. What is use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the database objects will be created.

84. What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to be exported.
85. What is the use of FULL option in EXP command ?
A flag to indicate whether full database export should be performed.
86. What is the use of OWNER option in EXP command ?
List of table accounts should be exported.
87. What is the use of TABLES option in EXP command ?
List of tables should be exported.
88. What is use of RECORD LENGTH option in EXP command ?
Record length in bytes.
89. What is use of INCTYPE option in EXP command ?
Type export should be performed. COMPLETE, CUMULATIVE, INCREMENTAL
90. What is use of RECORD option in EXP command ?
For incremental exports, the flag indicates whether a record will be stored in data dictionary tables recording the export.
91. What is the use of PARFILE option in EXP command ?
Name of the parameter file to passed for export.
92. What is the use of ANALYSE (Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
93. What is use of CONSISTENT (Ver 7) option in EXP command ?
A flag to indicate whether a read consistent version of all the exported objects should be maintained.
94. What is the use of Log (Ver 7) option in EXP command ?
The name of file to which log of the export will be written.

95. What is use of FILE option in IMP command ?
The name of file from which import should be performed.
96. What is the use of SHOW option in IMP command ?
A flag to indicate whether file content should be displayed or not.
97. What is the use of IGNORE option in IMP command ?
A flag to indicate whether import should ignore errors encounter when issuing CREATE command.
98. What is the use of GRANT option in IMP command ?
A flag to indicate whether grants on database objects will be imported.
99. What is use of INDEXES option in IMP command ?
A flag to indicate whether import should import index on tables or not.
100. What is use of ROWS option in IMP command ?
A flag to indicate whether rows should be imported. I f this is set to 'N' then only DDL for the database objects will be executed ?
101. What is the use of FULL option in IMP command ?
A flag to indicate whether full import should be done or not.
102. What is the use of FROMUSER option in IMP command ?
A list of database accounts whose objects should be read from the export dump file.
103. What is use of TOUSER option in IMP command ?
A list of database accounts into which objects in the export dump file will be imported
104. What is use of TABLES option in IMP command ?
A list of tables to be imported.
105. What is use of RECORDLENGTH option in IMP command ?
The length of the record in bytes of the export dump file.

106. What is use of INCTYPE option in the IMP command ?
The type of import being performed.
107. What is use of COMMIT option in IMP command ?
A flag to indicate whether import should commit after each array. If 'N' then commit will take place at table level
108. What is use of PARFILE option in IMP command ?
Name of the parameter file to passed for import command.
109. What is use of INDEXFILE option in IMP command ?
If filename is given then all the DDL will be created in the given file.
110. What is use of DESTROY (Ver 7) option in IMP command ?
A flag to indicate whether the create tablespace command found in dump files from full exports will be executed.
111. What is use of LOG option in IMP command ?
Name of the file to which the log of the import will be written.

112. Consider a case below : User is taking the backup in the following fashion :
Type F I I I I C I I I I C I I
Date 1 2 3 4 5 6 7 8 9 10 11 12 13
F - Full Backup
I - Incremental Backup
C - Cumulative Backup
Suppose database crash on 14th morning. How can we retrieve the database ?
Create the database
Import from the Full backup which was taken on 1st
Import from Cumulative backups which was taken on 6th
Import from Cumulative backups which was taken on 1th
Import from the Incremental backups 12,13 respectively.
Now the database will be available to latest status provided there is no transaction taken place after the 13th incremental backup.

113. List the steps to restore the database if data file lost. (Assume we are taking hot backups)
Copy the lost file from the backup to the original location
Start the instance
Mount the database
Recover the database using recover database command
Open the database
114. What are the points to be taken care when we are using SQL*Loader for importing data from flat files ?
Whether table and indexes are properly sized.
Direct option being used or not (Ver 7)
If one time load do not create any index until data has been loaded and table size is verified.
115. What are the advantages of using direct path option in SQL*Loader ?
It bypasses the normal processing of insert statements and instead writes directly to tables data blocks.
When direct option is used index become invalid and once the load complete the new key is merged with all old one and bring the status to valid.
Data should be presorted otherwise it needs the double the size in tablespace.

116. What are areas a DBA can monitor the database using SQLDBA command?
DBA can monitor the following areas to do fine tuning of the database :
Processes
Sessions
Tables(Locks etc)
SQL Area
Library Cache
Latch
Locks
File I/O
System I/O
Rollback Segments
Statistics (System, Sessions)
Apart from this all DBA activities can be performed through SQLDBA command.
DDE ¡V OLE

DDE - Dynamic Data Exchange.
DLL - Dynamic Link Library
OLE - Object Linking and Embedding.
MAPI ¡V Messaging Application Program Interface

What is DDE ?
DDE is method for Inter Process Communication. An inter process communication is a method of passing data between processes and synchronising events.
How does DDE work ?
DDE uses shared memory to exchange data and a protocol to synchronize passing of data.
What does a DLL contain ?
A DLL contains code, data and windows resources.
How does a DLL work ?
A DLL allocates a global memory block to an application and uses this to exchange data with application.
What are the two types of DDE application ?
Message based DDE applications and Dynamic Data Exchange Management Library application.
What are the parts of a DDE application ?
Client application, Server application, Client/Server application and Monitor application.
What is a monitor application in the context of DDE ?
A monitor application can only intercept messages but cannot act on it.
What is the use of a monitor application ?
A monitor application can be used as a debugging tool.
What is the connection between OLE and DDE ?
OLE is a set of DDE executable commands to which DDE protocol is applicable.

What is the difference between an embedded object and a linked object ?
An embedded object is stored in the document itself while the document just stores a reference to the linked object.
If a link object is changed independently of the document, wht happens the linked object in the document ?
1. The reference object is automatically refreshed
2. The reference object is not refreshed
3. The user decides whether the object is to refreshed or if the older version is retained.
Answer is 2
What are the types of OLE applications ?
Client, Server and Object handlers.
What is an object handler ?
An Object handler is a set of DLL that facilitate communication between client application and server application.
What are the advantages of OLE ?
No need to switch between applications. Facilitate the use of specialized applications to create objects which can be embedded.
What is the difference between a stored procedure and a database trigger ?
A trigger is automatically executed when the firing event occurs while stored procedure has to be invoked.

Oracle Forms:
1. How to use single data block to query multiple tables
2. How to see the select statement when we issue execute_query
3. What are the system variables can be changed
4. How do you trap default forms processing ( DML)
5. What is the difference between post-query and post-select
6. What is purpose and order of firing the following triggers
1. on fetch
2. on select
7. What is the number of records buffered and Query array size properties of data block
8. What is the difference between object libraries and object groups
9. What is the difference between pre-query and pre-select triggers
10. What is the difference between pre-text-item and when-new-item-instance triggers
11. What is the order of firing the following triggers
1. when-new-form-instance
2. pre-text-item
(Both are in form-level)
12. What is the validation unit property of form module
13. What is the order of checking for a program unit from form module(local program unit, library, stored procedure)
14. What is the difference between PL/SQL library and object library
15. what is the use of pre text/pre record/pre form
16. types of record groups and usage
17. what is id_null function
18. what is the return data types of id_null, show_alert, show_lov
19. what is the difference between call_form , open_form, new_form
Which is restricted built-in and why?
Which can¡¦t issue savepoint
What is session parameter of open_form?
20. which are the triggers will fire in the following situation
I have three text items
1. Text-item1
1.key-next-item
Go_item (¡¥text_item2¡¦);
Go_item (¡¥text_item3¡¦);

2. Text-item2
1. Pre-text-item
2. When-validate-item
3. When-new-item-instance
4. Key-next-item
5. Post-text-item
6. Post-change
3. Text-item3
1. Pre-text-item
2. When-validate-item
3. When-new-item-instance
4. Key-next-item
5. Post-text-item
6. Post-change
If I press tab or enter key at text-item1 what are the triggers will fire and order of firing during the entire navigation.
If I change key-next-item trigger of text-item1
Go_item (¡¥text_item2¡¦);
: Text_item2: = ¡¥Nagendra¡¦;
Go_item (¡¥text_item3¡¦);
Then what are the triggers will fire and order of firing
21. What is synchronous and asynchronous parameters for run_product built-in
22. How do you suppress the logon screen while running the form for the first time
23. What is primary canvas property of window and where it will be useful
24. I have when-button-pressed trigger at form , block and item level
If I want to execute first block, form then item level trigger what changes I have to make.
25. what is data parameter and text parameter
26. Can we re-generate a library that is currently accessing by some other session
27. Can we re-generate and save a library that has been attached with some forms but they are running currently.
28. Can we re-generate a library that has been attached with some forms , will the changes will reflect in the referenced forms
29. What is the use of transactional triggers
30. Can we modify a sub classed object ( from object group and from object libraries)
31. How to set forms default directory
32. What is the return data type of populate_group built-in
33. What is the difference between OLE object created at design time and runtime
34. Will the timer will expire during large query executing
35. What is the built-in package available to manipulate text files( forms)
36. Can we define a relation between two control blocks
37. If we change relation property from non-isolated to cascading what changes will occur
38. What is the coordination property of a relation
39. If we delete on-clear-details trigger in a relation what happens
40. What is the first trigger fires when we run a form
41. What is the use of enforce primary key property of data block
42. Can we put items other than buttons in the toolbars
43. Which object relates content and stacked canvases(window)
44. How to navigate from one form to other form(built-in)
45. How to copy values from list item to record group
46. In a non-isolated relation what is the order of firing the following triggers
1. on-populate-details
2. on-clear-details
3. pre-query
4. pre-select
47. How to find out the previous form id in multi form application(it¡¦s system variable)
48. How to use single LOV for multiple items

Oracle reports:
1. Minimum requirement to make a matrix report
(Queries, Groups and Repeating frames)
2. How to change the font of an field at runtime based on the value
3. What is the anchoring and enclosing object
4. What is anchor object
5. How to rotate a field( data base field in the layout)
6. What is the difference between lexical and bind parameters
7. What is the place holder
8. What is the use of frame(not repeating)
9. What is the use of format triggers
10. SRW package
11. What is flex mode and confine mode
12. what difference between the logical and physical pages and planes
13. what is the use of group filter

SQL and PL/SQL
1. Queries for Nth maximum ,Nth row
2. what is use of the index ( maximum try to cover)
3. Select the departments whose sum of the salary greater than the sum of salaries of any department?
4. What is implicit cursors
5. What is public synonyms and uses
6. When index will be used in the query
7. What is the result of the following queries
1. Select * from emp where Comm in (null);
2. Select * from emp where Comm = null;
3. Select * from emp where Comm = ¡¥¡¦;
4. Select * from emp where Comm is ( select null from dual);
8. Query to display employee name and his managers name
9. Query to find the employees who is having more then one subordinate
10. Query to find the employees whose salary is greater then his department average salary
11. Query to display employees salary as 2000 for department 20 and rest as their salaries
12. Query to display no of employees in the department 10, 20,30 in a single row
Output
Dept10 dept20 dept30
2 5 6
13. Query to find the no of subordinate levels for given manager
14. Query to find the no of employees who is drawing less than 1500 and greater than 2000

Server concepts
1. what are the physical database components
2. what are the logical database components
3. what is row chaining
4. what is the relation between oracle data block ,extents and segments
5. how many types of segments are there
6. what is temporary segments
7. what is redo log
8. what is the difference between rollback segments and redo log files
9. what is the difference between database buffers and redo log buffer of SGA
10.
1) How delete duplicate records in a table
delete from emp where rowid not in (select max(rowid) from emp group by empno);
2) How to make a column into not null column.
Ans: alter table emp
modify (empno not null); ->this can be done only when all the values in empno are non null (i.e not empty)

„h To make a not null column into null column
alter table emp
modify (empno null);
The fallowing are the rules for

i. adding null or not null property
>You may change a column¡¦s null property to not null only when that field does contain null values(i. It can¡¦t be empty)
>At any time you may change a column¡¦s not null property to null
ii. adding a colum to a table
>You may add a column at any time if NOT NULL isn¡¦t specified (i.e when new column can accept null values )
>You may add a NOT NULL column in three steps.
a. Add the column without NOT NULL specified.
b. Fill every row in that column with data.
c. Modify the column to be NOT NULL.
iii. modifying a column
>You can increase a CHAR column¡¥s width at any time.
>You can increase the number of digits and the number of decimal places in a NUMBER column at any time
>To CHANGE data types or to DECREASE column¡¦s width the column should be null for every row
3)Write queries for the fallowing
ex. select * from emp_self;
EMP_NO EMPN_NAME SAL MGR DEPTID
--------- -------------------- ---------- ---------- ----------
400 jane 20000 110 20
102 Mary 19000 110 20
101 charles 8000 105 50
104 Linda 9000 100 10
110 john 25000 105 20
105 newton 2000 50
100 ALEN 15000 105 50
200 BORIS 3000 110 20
103 DAVID 10000 100 10
300 monica 7000 105 50
i) Query to get the employees who are working under mgr with salary > 10000
select emp_no,mgr from emp_self where mgr in (select emp_no from emp_self where sal > 10000);
ii) Query to get the employees who are getting salaries more than their managers
select a.emp_no from emp_self a,emp_self b where a.mgr =b.emp_no and a.sal > b.sal;
EMP_NO
-------
101
100
110
300
iii) Query to find the nth highest salary
select a.empn_name,a.sal from emp_self a where &n = (select count(*) from emp_self b where a.sal < b.sal);
When n=1 , sal =20000 ->second highest salary ; n=4 , sal =10000 ->fifth highest salary ;
iv) Query to find the second highest salary in different departments.
select a.deptid,min(a. sal) from emp_self a where 1 in (select count(*) from emp_self b
where a.sal < b.sal group by b.deptid) group by a.deptid;
DEPTID MIN(A.SAL)
------- ----------
10 9000
20 20000
50 8000
v) Query to find departments with total salary >25000
select deptid from emp_self having sum(sal) >25000 group by deptid;
DEPTID
------
20
50

4) study the fallowing pl/sql block and find the type of error ->syntax,semantic(logical) or precedence
begin
for i in 1..5 loop
update emp
set sal = 1000 where empno =100 ;
end loop;
end;

5) Difference between (MAX,MIN) and (GREATEST ,LEAST)
The functions max and min compares different rows . Whereas greatest and least work on a group of columns ,either actual or calculated values within a single row.
Ex. select * from emp_self;
EMP_NO EMPN_NAME SAL MGR
------ -------------------- ------- ----------
100 alen 9,000 105
200 boris 10,000 110
101 charles 8,000 105

SQL> select max(sal),min(sal) from emp_self;
MAX(SAL) MIN(SAL)
---------- ----------
10000 8000
SQL> select greatest(sal),least(sal) from emp_self;
GREATEST(SAL) LEAST(SAL)
------------- ----------
9000 9000
10000 10000
8000 8000
6)Different kinds of constraints .
6)Where Procedures,Functions and Triggers are stored ?.
7)What are the improtant differences between Procedures,Functions and Triggers ?.
8)Can we call a Procedure from a Trigger ?.
9)what are packages ?.
9)What are the different kinds of parameters ?.
10)Can we return a OUT parameter from a procedure ?.
11)Differences between ROWNUM and ROWID .
12)How do you handle exceptions ?.
12)How many system defined exceptions are there ?.
13)How do you write user defined message for all the system defined exceptions.
14)Difference between Commit and H(?)ost .
15)Differences between delete ,truncate and drop commands .
16) How do you display messages in the backend procedure ?.
17) why can¡¦t you use create/drop while declaring a trigger ?.
18)Advantages of union over joins .
19)Definitions of commit, rollback, save point
20)Difference between truncate and delete (truncate =delete + commit)