Q. What is RDBMS? What are different database models?
RDBMS: Relational Database Management System.
In RDBMS the data are stored in the form of tables
i.e. rows & columns.
The different database models
are 1. HDBMS = Hierarchical Database Management system.
2. NDBMs = Network Database Management System.
3. RDBMS = Relational Database Management System.
Q. What is SQL?
SQL stands for Structured Query Language. SQL was derived from the
Greek word called "SEQUEL". SQL is a non- procedural language that
is written in simple English.
Q. What is a transaction?
Transaction is a piece of logical unit of work done
between two successive commits or commit and rollback.
Q. What is a commit?
Commit is transaction statements that make the changes permanent
into the database.
Q. What is a Rollback?
Rollback is a transaction statement that undoes all changes to a savepoint or since the beginning of the transaction.
Q. What is DDL?
DDL - Data Definition Language.
It is a set of statements that is used to define or alter the
User_defined objects like tables, views, procedures, functions etc.,
present in a tablespace.
Q. What is DML?
DML - Data Manipulation Language.
It is a set of statements that is used for manipulation of data.
E.g. inserting a row into a table, delete a row from a table etc.
Q. What is locking?
The mechanism followed by the SQL to control concurrent operations
On a table is called locking.
Q. What is a Dead lock?
When two users attempt to perform actions that interfere with one
another,this situation is defined as Deadlock.
E.g.: - If two users try to change both a foreign and its parent key
value at the same time.
Q. What is a Shared Lock?
The type of lock that permits other users to perform a query, but
could not manipulate it, i.e. it cannot perform any modification
or insert or delete a data.
Q. What is Exclusive Lock?
The type of lock that permits users to query data but not change
it and does not permits another user to any type of lock
on the same data. They are in effect until the end of the transaction.
Q. What is Share Row-Exclusive lock?
Share Row Exclusive locks are used to look at a whole table and to
allow others to look at rows in the table but to prohibit others
from locking the table in Share mode or updating rows.
Q. What is Group - Functions?
The Functions that are used to get summary information’s about group
Or set of rows in a table.
The group functions are also termed as aggregate functions.
Following are the examples of aggregate functions:
1. AVG () - To find the average value
2. MIN () - To find the minimum value of the set of rows.
3. MAX () - To find the maximum value of the set of rows.
4. COUNT () - To find the total no of rows that has values.
5. SUM () - To find the summation of the data of a given column.
Q. What is indexing?
An index is an ordered list of the contents of a column or a group of
columns of a table.
By indexing a table, it reduces the time in performing queries,
especially if the table is large.
Q. What are clusters?
A Cluster is a schema object that contains one or more tables that have
one or more columns in common. Rows of one or more tables that share
the same value in these common columns are physically stored together
within the database.
Q. What is a View?
View is like a window through which you can view or change the information in table. A view is also termed as a 'virtual table'.
Q. What is a Rowid?
For each row in the database, The ROWID pseudo column returns a row's
address. ROWID values contain information necessary to locate a row:
* Which datablock in the data file
* Which row in the datablock (first row is 0)
* Which data file (first file is 1)
Values of the Rowid pseudocolum have the datatype ROWID.
Q. What is a PRIMARY KEY?
PRIMARY KEY CONSTRAINT:
Q. Identified the columns or set of columns, which uniquely identify each row of a table and ensure that no duplicate rows exist in the table.
2. Implicitly creates a unique index for the column (S) and
specifies the column(s) as being NOT NULL.
3. The name of the index is the same as the constraint name.
4. Limited to one per table.
Example:
CREATE TABLE loans (account NUMBER (6),
loan_number NUMBER(6),
...
CONSTRAINT loan_pk PRIMARY KEY
(account, loan_number));
Q. What is a Unique constraint?
UNIQUE Constraint:
1. Ensures that no two rows of a table have duplicate values
in the specified columns(s).
2. Implicitly creates a unique index on the specified columns.
3. Index name is the given constraint name.
Example:
CREATE TABLE loans (
Loan_number NUMBER (6) NOT NULL UNIQUE,
...
);
Q. What is the difference between a unique and primary key?
The Primarykey constraint is a constraint that takes care maintaining
the uniqueness of the data, enforcing the not null characteristic,
creates a self-index.
The Unique key constraint maintains only the uniqueness of the
data and does not enforce the not null characteristic to the
data column.
Q. What is a foreign key?
FOREIGN KEY Constraint:
Q. Enforces referential integrity constraint, which requires that for each row of a table, the value in the foreign key matches a value in the primary key or is null.
2. No limit to the number of foreign keys.
3. Can be in the same table as referenced primary key.
4. Can not reference a remote table or synonym.
Examples:
1. Explicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10) ,
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer (account),
...);
2. Implicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10),
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer,
...);
Q. What is data integrity? What are the types of integrity?
1. A mechanism used by the RDBMS to prevent invalid data entry
into the base tables of the database.
2. Defined on tables so conditions remain true regardless of
method of data entry or type of transactions.
The following are the type of integrity
* Entity integrity
* Referential Integrity
* General Business rules
Q. What is a Referential Integrity?
1. Enforces master/detail relationship between tables based on keys.
* Foreign key
* Update Delete restricts action
* Delete Cascade action
Q. What are different data types?
The following are the different data types available in Oracle
1. Internal Data types
2. Composite Data types
Internal Data types
1. Character Datatype
2. Date Datatype
3. Row and long row data types
4. Rowid Datatype
Composite Data types
1. Table Data type
2. Record Data type
Q. What is VARCHAR2? How is it different CHAR?
The Varchar2 datatype specifies a variable length character string. When you create a varchar2 column, you can supply the maximum number of bytes of data that it can hold. Oracle Subsequently stores each value in the column exactly as you specify. If you try to insert a value that exceeds this length, Oracle returns an error.
The Char datatype length is 1byte. The maximum size of the Char datatype is 255. Oracle compares Char values using the blank-padded comparison semantics. If you insert a value that is shorter than the column length, Oracle blank-pads the value to the column length.
Q. What is datatype mixing?
There are two data types %TYPE and %ROWTYPE. The first one declares
a variable to be of the data type of the column of the table to which it is referring.
For example, if you declare a variable like this:
my_empno emp.empno%TYPE
then, my_empno will have the data type of the empno column of the table emp.
Similarly if you declare the variable like this:
my_emprec emp%ROWTYPE
then, my_emprec will have the data type of all the fields of the emp table.
For example. Suppose employee table is like this:
empno number(2),
empname varchar2(10),
Sal number (10,2).
Then my_emprec will be of a data type whose first 2 positions will be of number data type, the next 10 will be of varchar2 data type while the last 10 will be of data type number.
One can refer to the individual fields of this record variable as
my_emprec.empno, my_emprec.empname, my_emprec.sal.
Q. What is NULL?
A data field without any value in it is called a null value.
A Null can arise in the following situation
* Where a value is unknown.
* Where a value is not meaningful (i.e.) in column representing
commission for a row that does not represent salesman.
Q. What is a sequence?
A sequence is a database object from which multiple users may generate
unique integers.
Q. What are pseudo-columns in ORACLE?
The columns that are not part of the table are called as pseudo columns.
Q. What is like operator? How is it different from IN operator?
The type of operator that is used in character string comparisons
with pattern matching.
Like operator is used to match a portion of the one character string
to another whereas IN operator performs equality condition between
two strings.
Q. What is Single Row numbers Functions?
The type of function that will return value after every row is being
processed.
Following are some of the row number functions.
Function Name Purpose
1. ABS (n) returns the absolute value of a number
2. Floor (n) returns the largest integer value
equal or less than n.
3. Mod (m, n) returns the remainder of m divided by n.
4. Power (m, n) returns m raised to the n power.
5. Round (n) returns n rounded to m places
right of a decimal point.
6. Sqrt (x) returns the sqrt value of x.
7. Trunk (n, m) Returns n truncated to m decimal
places.
Q. What are single row character functions?
The function that processes at value of data, which is of character datatype, and returns a character datatype after every row is being processed are termed as single row character functions.
Function Name Purpose.
Q. Char (n) returns the character having
an ASCII value.
2. Initcap (n) returns character with first
letter of each argument in
UPPERCASE.
3. Lower (n) returns characters with
all the letters forced to
lower case.
4. ltrim(n) Removes the spaces towards
the left of the string.
5. upper(n) Returns characters with
all the letters forced to
upper case.
Q. What are Conversion Functions?
The functions used to convert a value from one datatype to another
are being termed as conversion functions.
Function Name Purpose
To_char (n, (fmt)) converts a value of number datatype
to a value of character datatype.
To_number (n) converts a character value into a number.
Rowidtochar (n) converts rowid values to character datatype.
the result of this conversion is always
18 character long.
Q. What are Date functions?
Functions that operate on Oracle Dates are termed as Date functions.
All date functions return a value of date datatype except the \
function months_between returns a numeric value.
Function Purpose
ADD_MONTHS (d, n) returns the date 'd' plus n months.
n must be an integer.
n can be positive or negative.
LAST_DAY (d) returns the date of the last day
of the month containing the date 'd'.
NEXT_DAY (d, char) returns date of first day of week
named after char that is later than
d, char must be a valid day of the
week.
MONTHS_BETWEEN (d, e) returns no of months between dates
d & e.
Q. What is NEW_TIME function?
SYNTAX: New_time (d, a, b)
New_time function returns date and time in a time zone b and time in time zone. a and b are character expressions.
Following are the some of the character expressions:
Character expression Description
AST Atlantic Stand or daylight time
BST, BDT Burning stand or daylight time
GMT Greenwich Mean Time.
PST, PDT Pacific Standard Time.
YST, YDT Yukon standard or daylight time.
Q. What is Convert function?
Convert function converts two different implementations of the
same character set .
For instance: from DEC 8 bit multi-lingual characters to HP 8 bit
Multi-lingual character set.
Following are the character sets
US7ASCII - US7bit ASCII character set
WE8DEC - Western European 8 bit ASCII set
WE8HP - HP's Western European 8 bit ASCII set
F7DEC - DEC's French 7-bit ASCII set
Convert (char [destination], [source])
Q. What is a translate function?
The function that returns a character after replacing all occurrences
of the character specified with the corresponding character is called
as translate function.
E.g. TRANSLATE ('Hello','l','L') gives you HeLLo
Q. What is a soundex function?
Soundex is a function that returns a character string representing
the sound of the words in char. This function returns a phonetic
representation of each word and allows you to compare words
that are spelled differently but sound alike.
Soundex (char);
Q. What is a replace function?
Replace function returns character with every occurrence of the
search string replaced with the replacement string. If the replacement
string is not supplied, all occurrences of search_string are being
removed. Replace allows you to substitute one string from another.
Q. What is a Floor function?
Floor Function returns the largest integer equal to or than n
Syntax: floor (n);
Q. What is INITCAP Function?
The initcap function returns char,with first letter of each word in uppercase, all other letters in lowercase. A word is delimited by white space
Q. What is ASCII Function?
The ASCII function returns the collating sequence of the first character of lchar. There is no corresponding EBCDIC function.
On EBCDIC systems, the ASCII function will return EBCDIC collating
sequence values.
Q. What is a Decode Function?
The Decode function is used to compare an expression to each search
value and returns the result if expr equals the search value.
E.g.: Decode (expr, search1, result1, [search2, result2], [default]);
Q. What is Greatest Function?
The Greatest function returns the greatest of a list of values. All expr after the first are converted to the datatype of the first before
comparison is done.
Q. What are Format models?
Format models are used to affect how column values are displayed when
a format retrieved with a select command. Format models
do not affect the actual internal representation of the column.
Q. Give 5 examples for DATE, Number function?
Examples for Number Function:
Q. Select abs (-15) "Absolute:" from dual
2. Select mod (7,5) "Modula" from dual
3. Select round (1235.85,1) from dual
4. Select power (2,3) from dual
5. Select floor (7.5) "Floor" from dual
Examples for Date Function
1. Select sysdate from dual
2. Select sysdate-to_date (23-Sep-93) from dual
3. Select sysdate + 90 from dual
4. Select sysdate -90 from dual
5. Select next_day (sysdate,"Friday") from dual
Q. What is an expression?
An expression is a group of value and operators which may be evaluated a single values.
Q. What are the types of expression?
The different types of expressions are
1. Logical expression
2. Compound expression
3. Arithmetic expression
4. Negating expression.
Q. What is a synonym?
The synonym is a user-defined object that is used to define an alias name for the user defined objects like table view etc.
Q. What is a condition?
A Condition could be said to be of the logical datatype that evaluates
the expression to a True or False value.
Q. What are the 7 forms of condition?
There are totally 7 forms of condition
Q. A comparison with expression or subquery results.
Q. A comparison with any or all members in a list or a subquery
3. A test for membership in a list or a subquery
4. A test for inclusion in a range
5. A test for nulls.
6. A test for existence of rows in a subquery
7. A test involving pattern matching
8. A combination of other conditions
Q. What are cursors?
Oracle uses work areas called private SQL areas to execute SQL statements and store processing information. This private SQL work area is known as cursors.
Q. What are explicit cursors?
Cursors that are defined for performing a multiple row select are known
as explicit cursors.
Implicit cursors are the type of cursors that is implicitly opened
by the Oracle itself whenever you perform any DML statements like
Update, delete, insert or select into statements.
Q. What is a PL/SQL?
PL/SQL is a transaction processing language that offers procedural
solutions.
Q.What is an embedded SQL?
All the SQL statements written in a Host language are known
as Embedded SQL statements.
Q. What are the different conditional constructs of PL/SQL?
The statements that are useful to have a control over the set
of the statements being executed as a single unit are called as
conditional constructs.
The following are the different type of conditional constructs
of PL/SQL
1. if <condition>
then
elsif<condition> then
end if
2. While <condition>
loop
end loop
3. loop
exit when<conditon>
end loop
4. for <var> in range1..range2
loop
end loop
5. for i in <query/cursor identifier>
loop
end loop
Q. How is an array defined in PL/SQL?
Typedef <identifier> table of <datatype>
Index by binary_integer;
Q. How to define a variable in PL/SQL?
Variablename datatype<size> <not null> <: = value>
Q. How to define a cursor in PL/SQL?
Cursor variable is <query>
Q. What are exceptions?
The block where the statements are being defined to handle internally
and userdefined PL/SQL errors.
Q. What are the systems exceptions?
When an Oracle error is internally encountered PL/SQL block
raises an error by itself. Such errors are called as internal
or system defined exception.
Following are some of the internal exceptions:
1. Zero_divide, 2. No_data_found 3. Value_error 4. Too_many_rows
Q. How to define our own exceptions in PL/SQL?
Define a PL/SQL variable as an exception in the variable declaration section.
In order to invoke the variable that is an exception type
use the raise statement.
Declare a exception;
Begin
statements....
-----
if x > y
then
raise a;
end if;
exception
when a then
statements,
rollback;
when others then
commit;
end;
Q. How is the performance of Oracle improved by PL/SQL in Oracle?
Without PL/SQL the ORACLE RDBMS must process SQL statements one at a time, Each SQL statement results in another call to RDBMS and higher performance overhead. This overhead can be significant when you are issuing many statements in a network environment.
With the PL/SQL all the SQL statements can be sent to RDBMS at one time.
This reduces the I/O operations. With PL/SQL a tool like Forms can do
all data calculations quickly and efficiently without calling on
the RDBMS .
Q. What is SCHEMA?
A SCHEMA is a logical collection of related items of tables and Views.
Q. What are profiles?
A Profile is a file that contains information about the areas that a user can access.
Q. What are roles?
A role is a collection of related privileges that an administrator can grant collectively to database users.
Q. How can we alter a user's password in ORACLE?
Inorder to Alter the password of the user we have to use the following statement:
ALTER USER user_name identified by passwd
E.g.: Alter user sam identified by Paul
Q. What is a tablespace in Oracle?
A tablespace is a partition or logical area of storage in a database that directly corresponds to one or more physical data files.
Q. What is an extent?
An extent is nothing more that a number of contiguous blocks that ORACLE-7 allocates for an object when more space is necessary for the object data.
Q. What are PCTFREE and PCTUSED parameters?
PCTFREE: - PCTFREE controls how much of the space in a block is reserved for statements that update existing rows in the object.
PCTUSED: - PCTUSED is a percentage of used space in a block that triggers the database to return to the table's free space list.
Q. What is a block in Oracle?
The Place where the data related to Oracle are stored physically in an Operating System is known as block.
Q. What is Client-server architecture?
A client/server system has three distinct components
• Focusing on a specific job
• A database server
A client application and a network.
A server (or back end) focuses on efficiently managing its resource Such as database information. The server's primary job is to manage its resource optimally among multiple clients that concurrently request the server for the same resource.
Database servers concentrate on tasks such as
* Managing a single database of information among many concurrent
users.
* Controlling database access and other security requirements.
* Protecting database information with backup and recovery features.
* Centrally enforces global data integrity rules across all
Client applications.
A client application ("the front end") is the part of the system that users employ to interact with data. The client applications in a client/server database system focus on jobs such as
* Presenting an interface a user can interact with to accomplish work.
* Managing presentation logic such as popup lists on a
data entry form or bar graphs in a graphical data presentation
tool.
* Performing application logic, such as calculating fields
in a dataentry form.
* Validating data entry.
* Requesting and receiving information from a database server.
A network and communication software is the vehicle that transmit
data between the clients and the server in a system. Both the clients
and the server run communication software that allows them to
talk across a network.
Types of Client Server Architecture:
1. Dedicated Client Server Architecture
2. Multi-threaded Client Server Architecture
3. Single- Task Client Server Architecture
Dedicated Server: Connects the Client Directly to the dedicated server
Multi-Threaded Server: It is a type of architecture that is a combination of dispatcher, listener and front-end server process to serve the requests of many clients with minimal process overhead on the database server.
Single-Task server: In host-based database server system a user
employs a dumb terminal or terminal emulator to establish a session
on the host computer and run the client database application.
73. What is a segment in Oracle? Explain the different types?
The places where the data are stored in the allotted tablespace are called as segments. The data may be a table or index data required by DBMS to operate. Segments are the next logical level of a storage tablespace.
There are basically 5 types of segments
* Data segment: Contains all the data of each table
* Index segment: Contains all the index data for one or more indexes
Created for a table.
* Rollback segment: Contains the recorded actions, which should be undone under certain circumstances like
* Transaction rollback
* Read consistency
* Temporary segment:
Whenever a processing occurs Oracle often requires temporary workspace for intermediate stages of statement processing. These areas are known as temporary segments.
* Bootstrap segment: Contains information of the data dictionary definition for the tables to be loaded whenever a database is opened.
Q. What is the use of Rollback segment?
It is a portion of a database that records the information about the actions that should be undone under certain circumstances like
* Transaction Rollback
* Read consistency
Q. What is read-consistency in Oracle?
Read consistency in Oracle is a process that ignores the changes by others in a table whenever a table is queried. Read consistency in Oracle is achieved by a statement
SET TRANSACTION READ ONLY
Q. What is SGA?
SGA is System Global Area.
The library cache and dictionary cache makes up the shared pool. The shared pool combined with buffer-cache make up the System Global Area.
Library Cache: - It stores the SQL Statements and PL/SQL Procedures.
Dictionary Cache: - Holds dictionary information in memory.
Buffer and Cache: - the place where the data related to recently requested transaction is stored.
Q. What is Back Ground Process?
The Process of server is being classified into two processes namely Foreground and Background.
Foreground handles the request from client processes while background handle other specific row of the database server like writing data to data and transaction Log Files.
Q. System Userid?
Whenever you create a database an Userid is automatically created related with database administration connections. This account/userid is called System Userid.
Q. SYS Userid?
It is a special account through which DBA can execute special database administration connections. SYS is the owner of database's data dictionary table.
Q. Data Dictionary?
It provides the details on the database objects such as columns, views Etc., the oracle users, the privileges and the rights of users over different objects.
Q. SQL*DBA?
SQL*DBA is a utility through which you can manage a database system effectively.
Q. ORACLE ADMINISTRATOR?
The person who takes care of monitoring the entering performances of the database system is called, as an Oracle Administrator.Oracle Administrator is the main person who takes care of assigning the set of to act as DBA for monitoring certain jobs like
1. Creating primary database storage structure.
2. Monitoring database performance and efficiency.
3. Backing up and restoring.
4. Manipulating the physical location of the database.
TO CREATE DATABASE:
1. Determining appropriate values for the file limit parameters of the create database command.
Parameters
Max data files: Determines the maximum number of data files that can ever be allocated for the database
Max Log Files: Determines the maximum number of log groups for the database.
Max Log Members: Maximum number of members for each log group.
Q. What are database files?
The physical files of Oracle are known as database files.
Q. What is a Log File?
The files that contains information about the information of recovery of oracle database at the event of a SYSTEM CRASH or a MEDIA Failure.
Q. What is an Init file?
Init files are known as Initialization Parameter files.
Init files are used for setting the parameters
* For an Oracle instance
* For Log files
Q. What is a control file? What is its significance?
A control is a small binary file. It contains the entire system executable code named as ORACLE.DCF.
A control file always consists of the following
1: Name of the database
2: Log files
3: Database creation
Q. What does an UPDATE statement does?
To update rows in a table.
Q. What does a Delete statement does?
To remove the rows from the table.
Q. What does an insert statement do?
To insert new rows into a database.
Q. What does a Select statement do?
To query data from tables in a database
Q. How to create a table using selects and inserts statements?
Using Select statement:
Create table tablename
as
<Query >
Using insert statement we cannot create a table but can only append
the data into the table
Using Insert statement:
Insert into tablename
<Query>
Q. How to delete duplicate rows in a table?
Delete from tablename where rowid not in
(Select min (rowid) from tablename group by column1, column2...)
Q. What is an instance?
An Oracle instance is a mechanism that provides the mechanism for processing and controlling the database.
Q. What is startup and shutdown?
Startup is a process making the Oracle Database to be accessed by all
the users
There are three phases to database startup
Q. Start a new instance for the database
2. Mount the database to the instance
3. Opening the mounted database
Shutdown is a process making the Oracle Database unavailable for all
the users.
There are three phases to database shutdown
1. Close database
2. Dismount the database from the instance
3. Terminate the instance.
Q. What is mounting of database?
Q. What is a two-phase commit?
Q. What are snap-shots?
A Snapshot is a stable that contains the results of query Of one or more tables or views, often located on a remote database.
Q. What are triggers and stored Procedures?
A procedure is a group of PL/SQL statement that you call by a name. Compiled version of procedure that is stored in a database are known as Stored Procedures.
A database trigger is a stored procedure that is associated with a table.
Oracle automatically fires or executes when a triggering statement is issued.
Q. What are Packages?
A package is an encapsulated collection of related program objects stored together in the database.
Q. What is SQL*Forms3.0? Is it a Client or a server?
Sql*Forms is a general-purpose tool for developing and executing forms based interactive applications. The component of this tool is specially
designed for application developers and programmers and it is used for the following tasks :
* Define transactions that combine data from multiple tables into a single form.
* Customize all aspects of an application definition using std-fill-in- interface to enhance the productivity and reduce learning time.
Sql*Forms3.0 is a Client.
Q. What are Packaged Procedures?
A packaged procedure is a built in PL/SQL procedure that is available in all forms.
Using packaged procedure we can build triggers to perform the following
Tasks to
* Reduce the amount of repetitive data entry.
* Control the flow of application
* Ensuring the operators always follow sequence of actions when
they use a form.
Q. What are different types of triggers?
The following are the different type of triggers they are
1. Key-triggers
2. Navigational Triggers.
3. Transactional Triggers.
4. Query-based Triggers
5. Validation Triggers
6. Message - Error handling Triggers.
Q. What is the difference between the restricted and Un-Restricted Packaged Procedure?
Any packaged procedure that does not interfere with the basic function
of SQL*Forms is an unrestricted packaged procedure. The Un-restricted
Packaged procedure can be used in all types of triggers.
Any packaged procedure that affects basic SQL*FORMS function is a restricted packaged procedure. Restricted packaged procedure can be used only in key-triggers and user-named triggers.
Q. What is a system variable?
A System variable is a SQL*Forms variable that keeps track of some internal process of SQL*Forms in state. The system variable helps us to control the way an application behaves. SQL*Forms maintains the value of a system on a performance basis. That is the values of all the system variables correspond only to the current form.
Q. What are Global Variables?
A Global variable is a SQL*Form variable that is active in any trigger within a form and is active throughout SQL*Form (Run-Form) session. The variable stores string value upto 255 characters.
Q. What are the different types of objects in SQL*Forms?
A SQL*Form application is made up of objects. These objects contain all
the information that is needed and produce the SQL*Forms application.
Following are the objects of the SQL*Forms:
1. Form
2. Block
3. Fields
4. Pages
5. Triggers
6. Form-Level-Procedures.
Q. What are Pages?
Pages are collection of display information such as constant text and graphics. All fields are displayed in a page.
Q. What are a Block and its types? Explain the different types of blocks?
Block is an object of Forms that describes section of a form or a subsection of a Form and serve as the basis of default database interface.
Types of Blocks:
1. Control Block: Control block is not associated with any table in the
database. It is made up of fields that are base table fields, such as temporary data fields.
2. Detail Block: Detail Block is associated with a master block in
Master-detail relationship. The detail block displays detail records associated with master records in a block.
3. Master-Block: A master block is associated with a master-detail relationship. The master block display master records associated with detail records in the detail block.
4. Multi-record Block: A multi-record block can display more than one record at a time.
5. Non-enterable Block: A non-enterable block consists of all non-enterable fields.
6. Single-record Block: A single record block can display only one record at a time.
Q. What is a Screen Painter?
This is a SQL Forms "work area" where you can modify the layout of forms. The screen painter displays one page area at a time.
Q. What are the different field types?
The different types of fields in SQL*Forms are
1. Base - table field
2. Control-field
3. Enterable-field
4. Hidden-field
5. Look-up field
6. Non-enterable field
7. Scrolled - field
Q.What is page Zero?
The place where the hidden fields are being placed in an application
Q. What does Message procedure do?
The Message procedure displays specified text on the message line.
Q. What does Name_in function do?
The Name_in packaged function returns the contents of the variable to which you apply it. The returned value is in form of a string.
Q. What does CLEAR_EOL procedure do?
Clear_Eol clears the current field's value from the current cursor position to the end of the line or field.
Q. What does On-Error trigger do?
The On-error trigger fires whenever SQL*Forms would normally cause an error message to display. The actions of an On-Error triggers is used for the following purposes:
* Trap and recover an error.
* Replace a standard error message with a customized message.
Q. What does copy procedure do?
The Copy procedure writes a value into a field. Copy exists specifically to write a value into that is referenced through NAME_IN packaged function.
Q. What is the Arraysize parameter?
The Array-Size parameter is a block-characteristic that specifies the Maximum number of records that SQL Forms (Run-Form) can fetch from the database at one time.
Q. What does Go_Block packaged procedures do?
The Go_Block packaged procedure navigates to the indicated Block.If the target is non-enterable an error occurs.
Q. What does ANCHOR_VIEW procedure do?
Anchor_view moves a view of a page to a new location on the screen. This procedure effectively changes where on the screen the operator sees the view.
Q. How to call a form from inside a form?
Inorder to call a form from inside a form we have to use the CALL packaged-procedure.
Inorder to call a form from inside a form the packaged procedure Call is used.
Syntax: CALL (Formname).
When call runs an indicated form while keeping the parent form active.
SQL*Forms runs the called form with the same SQL*Forms options as the
parent form.
Q. How to send parameters to another form?
Inorder to send parameters across the forms we use the global variables.
Q. How to give automatic hint text for fields?
Inorder to give automatic hint text for fields, In the field definition screen of the fields we are having an option called Hint value. Inorder to activate this option in the Select attribute section invoke the option called Automatic Hint.
Q. How to see key map sequences?
Inorder to see key map sequences we have to press the SHOW KEY screen
key function.
Q. What is SYNCHRONIZE procedure does?
The synchronize procedure synchronizes the terminal screen with the internal state of form, that is synchronize updates the screen display to reflect the information that SQL*Forms has in its internal representation of the screen.
Q. What is EXECUTE_QUERY procedure?
The Execute_query procedure flushes the current block, opens a query and fetches a number of selected records. If there are changes to commit, SQL*Forms prompts the operator to commit them during the execute_qury event.
Q. How to customize system message in SQL*Forms?
Inorder to customize the system messages the On-Message trigger is used.
Q. How to define the fields in WYSIWYG Format?
Q. What is an On-Insert trigger? How is it different from Pre-insert
trigger?
An On-insert trigger replaces the default SQL*Forms processing for handling inserted records during transaction posting. It fires once for each rows that is marked for insertion into the database. An On-insert Trigger fires during the Post and Commit Transactions event. Specifically it fires after the Pre-insert trigger and before the
Post-insert trigger.
Q. What is the difference between a Trigger and a Procedure?
Procedures can take in arguments where as Triggers cannot take in arguments.
Q. How to call a stored procedure from inside a form?
To call a Stored Procedure inside a form
Trigger Text:
Procdurename<paramaters>
Q. What are V2 Triggers?
V2 Triggers are the types of Triggers in which we can perform only a simple query. And we cannot write a PL/SQL block.
Q. How to rename a Form?
To rename a form select the rename option in the Action Menu. Then give the form name. Press Accept. In the next field give the new name. Press Accept to Execute.
Q. What is a Pop -up page? How to define one?
Pop-Up Pages: -
Pop-Up page is a SQL*Forms object which overlays on an area of the current displayed page in response to some event or for user call. To define a Pop-Up page use the page definition form which is in the Image-Modify option. In that form put an X in the Pop-Up field to make the current page as Pop-Up.
Q. What is a Group in SQL * REPORTWRITER?
Group in ReportWriter: -
Group is a collection of fields, or single field. Usually by default a group will bare the field, which are references by a single query. But we can change from single query group -multi groups.
Q. How do you define a Parent-child relationship in ReportWriter?
Parent - Child Relation: -
To define a Parent-Child relationship first we need more that one
Query. We should first enter the Parent Query and then the Child Query. In the Child Query Form we should give the Parent Query Name in the desired position and the common columns in both queries.
Q. What is a Rowcount function in ReportWriter?
It is a field level function that is used for generation of automatic row numbers related to database column that does not have null values.
Q. How do you define a matrix report?
Matrix Report: -
Matrix Report is a Report that consists of Two Parent Queries and one Child Query.
Procedure for Defining a Matrix Report:
Q. Define Two Parent Queries.
2. Define a Child Query. In the Definition screen specific which column of the child is to be related to the Query1 and to Query2.
3. After defining the queries in the Query option, In the
Group option
Place All the Groups in the option called MatrixGroup
Define the Print Direction for Query1 as down
Define the Print Direction for Query2 as across
Define the Print Direction for Child Query as cross tab
Q. How do you execute a report from within a form?
Use the following command to run a report from the FORM.
Host ('runrep <rep_name> term<terminal_type> userid=<userid/passwd>);
Q. What are exp and imp utilities?
Export & Import: -
Export utility is to write data from database to operating system files called Export Files. An export does this by changing the data and table structures in to ASCII or EBCDIC codes.
Import is a utility with which we can write the data from Export file to database. Export Files can be only read by Import.
RDBMS: Relational Database Management System.
In RDBMS the data are stored in the form of tables
i.e. rows & columns.
The different database models
are 1. HDBMS = Hierarchical Database Management system.
2. NDBMs = Network Database Management System.
3. RDBMS = Relational Database Management System.
Q. What is SQL?
SQL stands for Structured Query Language. SQL was derived from the
Greek word called "SEQUEL". SQL is a non- procedural language that
is written in simple English.
Q. What is a transaction?
Transaction is a piece of logical unit of work done
between two successive commits or commit and rollback.
Q. What is a commit?
Commit is transaction statements that make the changes permanent
into the database.
Q. What is a Rollback?
Rollback is a transaction statement that undoes all changes to a savepoint or since the beginning of the transaction.
Q. What is DDL?
DDL - Data Definition Language.
It is a set of statements that is used to define or alter the
User_defined objects like tables, views, procedures, functions etc.,
present in a tablespace.
Q. What is DML?
DML - Data Manipulation Language.
It is a set of statements that is used for manipulation of data.
E.g. inserting a row into a table, delete a row from a table etc.
Q. What is locking?
The mechanism followed by the SQL to control concurrent operations
On a table is called locking.
Q. What is a Dead lock?
When two users attempt to perform actions that interfere with one
another,this situation is defined as Deadlock.
E.g.: - If two users try to change both a foreign and its parent key
value at the same time.
Q. What is a Shared Lock?
The type of lock that permits other users to perform a query, but
could not manipulate it, i.e. it cannot perform any modification
or insert or delete a data.
Q. What is Exclusive Lock?
The type of lock that permits users to query data but not change
it and does not permits another user to any type of lock
on the same data. They are in effect until the end of the transaction.
Q. What is Share Row-Exclusive lock?
Share Row Exclusive locks are used to look at a whole table and to
allow others to look at rows in the table but to prohibit others
from locking the table in Share mode or updating rows.
Q. What is Group - Functions?
The Functions that are used to get summary information’s about group
Or set of rows in a table.
The group functions are also termed as aggregate functions.
Following are the examples of aggregate functions:
1. AVG () - To find the average value
2. MIN () - To find the minimum value of the set of rows.
3. MAX () - To find the maximum value of the set of rows.
4. COUNT () - To find the total no of rows that has values.
5. SUM () - To find the summation of the data of a given column.
Q. What is indexing?
An index is an ordered list of the contents of a column or a group of
columns of a table.
By indexing a table, it reduces the time in performing queries,
especially if the table is large.
Q. What are clusters?
A Cluster is a schema object that contains one or more tables that have
one or more columns in common. Rows of one or more tables that share
the same value in these common columns are physically stored together
within the database.
Q. What is a View?
View is like a window through which you can view or change the information in table. A view is also termed as a 'virtual table'.
Q. What is a Rowid?
For each row in the database, The ROWID pseudo column returns a row's
address. ROWID values contain information necessary to locate a row:
* Which datablock in the data file
* Which row in the datablock (first row is 0)
* Which data file (first file is 1)
Values of the Rowid pseudocolum have the datatype ROWID.
Q. What is a PRIMARY KEY?
PRIMARY KEY CONSTRAINT:
Q. Identified the columns or set of columns, which uniquely identify each row of a table and ensure that no duplicate rows exist in the table.
2. Implicitly creates a unique index for the column (S) and
specifies the column(s) as being NOT NULL.
3. The name of the index is the same as the constraint name.
4. Limited to one per table.
Example:
CREATE TABLE loans (account NUMBER (6),
loan_number NUMBER(6),
...
CONSTRAINT loan_pk PRIMARY KEY
(account, loan_number));
Q. What is a Unique constraint?
UNIQUE Constraint:
1. Ensures that no two rows of a table have duplicate values
in the specified columns(s).
2. Implicitly creates a unique index on the specified columns.
3. Index name is the given constraint name.
Example:
CREATE TABLE loans (
Loan_number NUMBER (6) NOT NULL UNIQUE,
...
);
Q. What is the difference between a unique and primary key?
The Primarykey constraint is a constraint that takes care maintaining
the uniqueness of the data, enforcing the not null characteristic,
creates a self-index.
The Unique key constraint maintains only the uniqueness of the
data and does not enforce the not null characteristic to the
data column.
Q. What is a foreign key?
FOREIGN KEY Constraint:
Q. Enforces referential integrity constraint, which requires that for each row of a table, the value in the foreign key matches a value in the primary key or is null.
2. No limit to the number of foreign keys.
3. Can be in the same table as referenced primary key.
4. Can not reference a remote table or synonym.
Examples:
1. Explicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10) ,
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer (account),
...);
2. Implicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10),
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer,
...);
Q. What is data integrity? What are the types of integrity?
1. A mechanism used by the RDBMS to prevent invalid data entry
into the base tables of the database.
2. Defined on tables so conditions remain true regardless of
method of data entry or type of transactions.
The following are the type of integrity
* Entity integrity
* Referential Integrity
* General Business rules
Q. What is a Referential Integrity?
1. Enforces master/detail relationship between tables based on keys.
* Foreign key
* Update Delete restricts action
* Delete Cascade action
Q. What are different data types?
The following are the different data types available in Oracle
1. Internal Data types
2. Composite Data types
Internal Data types
1. Character Datatype
2. Date Datatype
3. Row and long row data types
4. Rowid Datatype
Composite Data types
1. Table Data type
2. Record Data type
Q. What is VARCHAR2? How is it different CHAR?
The Varchar2 datatype specifies a variable length character string. When you create a varchar2 column, you can supply the maximum number of bytes of data that it can hold. Oracle Subsequently stores each value in the column exactly as you specify. If you try to insert a value that exceeds this length, Oracle returns an error.
The Char datatype length is 1byte. The maximum size of the Char datatype is 255. Oracle compares Char values using the blank-padded comparison semantics. If you insert a value that is shorter than the column length, Oracle blank-pads the value to the column length.
Q. What is datatype mixing?
There are two data types %TYPE and %ROWTYPE. The first one declares
a variable to be of the data type of the column of the table to which it is referring.
For example, if you declare a variable like this:
my_empno emp.empno%TYPE
then, my_empno will have the data type of the empno column of the table emp.
Similarly if you declare the variable like this:
my_emprec emp%ROWTYPE
then, my_emprec will have the data type of all the fields of the emp table.
For example. Suppose employee table is like this:
empno number(2),
empname varchar2(10),
Sal number (10,2).
Then my_emprec will be of a data type whose first 2 positions will be of number data type, the next 10 will be of varchar2 data type while the last 10 will be of data type number.
One can refer to the individual fields of this record variable as
my_emprec.empno, my_emprec.empname, my_emprec.sal.
Q. What is NULL?
A data field without any value in it is called a null value.
A Null can arise in the following situation
* Where a value is unknown.
* Where a value is not meaningful (i.e.) in column representing
commission for a row that does not represent salesman.
Q. What is a sequence?
A sequence is a database object from which multiple users may generate
unique integers.
Q. What are pseudo-columns in ORACLE?
The columns that are not part of the table are called as pseudo columns.
Q. What is like operator? How is it different from IN operator?
The type of operator that is used in character string comparisons
with pattern matching.
Like operator is used to match a portion of the one character string
to another whereas IN operator performs equality condition between
two strings.
Q. What is Single Row numbers Functions?
The type of function that will return value after every row is being
processed.
Following are some of the row number functions.
Function Name Purpose
1. ABS (n) returns the absolute value of a number
2. Floor (n) returns the largest integer value
equal or less than n.
3. Mod (m, n) returns the remainder of m divided by n.
4. Power (m, n) returns m raised to the n power.
5. Round (n) returns n rounded to m places
right of a decimal point.
6. Sqrt (x) returns the sqrt value of x.
7. Trunk (n, m) Returns n truncated to m decimal
places.
Q. What are single row character functions?
The function that processes at value of data, which is of character datatype, and returns a character datatype after every row is being processed are termed as single row character functions.
Function Name Purpose.
Q. Char (n) returns the character having
an ASCII value.
2. Initcap (n) returns character with first
letter of each argument in
UPPERCASE.
3. Lower (n) returns characters with
all the letters forced to
lower case.
4. ltrim(n) Removes the spaces towards
the left of the string.
5. upper(n) Returns characters with
all the letters forced to
upper case.
Q. What are Conversion Functions?
The functions used to convert a value from one datatype to another
are being termed as conversion functions.
Function Name Purpose
To_char (n, (fmt)) converts a value of number datatype
to a value of character datatype.
To_number (n) converts a character value into a number.
Rowidtochar (n) converts rowid values to character datatype.
the result of this conversion is always
18 character long.
Q. What are Date functions?
Functions that operate on Oracle Dates are termed as Date functions.
All date functions return a value of date datatype except the \
function months_between returns a numeric value.
Function Purpose
ADD_MONTHS (d, n) returns the date 'd' plus n months.
n must be an integer.
n can be positive or negative.
LAST_DAY (d) returns the date of the last day
of the month containing the date 'd'.
NEXT_DAY (d, char) returns date of first day of week
named after char that is later than
d, char must be a valid day of the
week.
MONTHS_BETWEEN (d, e) returns no of months between dates
d & e.
Q. What is NEW_TIME function?
SYNTAX: New_time (d, a, b)
New_time function returns date and time in a time zone b and time in time zone. a and b are character expressions.
Following are the some of the character expressions:
Character expression Description
AST Atlantic Stand or daylight time
BST, BDT Burning stand or daylight time
GMT Greenwich Mean Time.
PST, PDT Pacific Standard Time.
YST, YDT Yukon standard or daylight time.
Q. What is Convert function?
Convert function converts two different implementations of the
same character set .
For instance: from DEC 8 bit multi-lingual characters to HP 8 bit
Multi-lingual character set.
Following are the character sets
US7ASCII - US7bit ASCII character set
WE8DEC - Western European 8 bit ASCII set
WE8HP - HP's Western European 8 bit ASCII set
F7DEC - DEC's French 7-bit ASCII set
Convert (char [destination], [source])
Q. What is a translate function?
The function that returns a character after replacing all occurrences
of the character specified with the corresponding character is called
as translate function.
E.g. TRANSLATE ('Hello','l','L') gives you HeLLo
Q. What is a soundex function?
Soundex is a function that returns a character string representing
the sound of the words in char. This function returns a phonetic
representation of each word and allows you to compare words
that are spelled differently but sound alike.
Soundex (char);
Q. What is a replace function?
Replace function returns character with every occurrence of the
search string replaced with the replacement string. If the replacement
string is not supplied, all occurrences of search_string are being
removed. Replace allows you to substitute one string from another.
Q. What is a Floor function?
Floor Function returns the largest integer equal to or than n
Syntax: floor (n);
Q. What is INITCAP Function?
The initcap function returns char,with first letter of each word in uppercase, all other letters in lowercase. A word is delimited by white space
Q. What is ASCII Function?
The ASCII function returns the collating sequence of the first character of lchar. There is no corresponding EBCDIC function.
On EBCDIC systems, the ASCII function will return EBCDIC collating
sequence values.
Q. What is a Decode Function?
The Decode function is used to compare an expression to each search
value and returns the result if expr equals the search value.
E.g.: Decode (expr, search1, result1, [search2, result2], [default]);
Q. What is Greatest Function?
The Greatest function returns the greatest of a list of values. All expr after the first are converted to the datatype of the first before
comparison is done.
Q. What are Format models?
Format models are used to affect how column values are displayed when
a format retrieved with a select command. Format models
do not affect the actual internal representation of the column.
Q. Give 5 examples for DATE, Number function?
Examples for Number Function:
Q. Select abs (-15) "Absolute:" from dual
2. Select mod (7,5) "Modula" from dual
3. Select round (1235.85,1) from dual
4. Select power (2,3) from dual
5. Select floor (7.5) "Floor" from dual
Examples for Date Function
1. Select sysdate from dual
2. Select sysdate-to_date (23-Sep-93) from dual
3. Select sysdate + 90 from dual
4. Select sysdate -90 from dual
5. Select next_day (sysdate,"Friday") from dual
Q. What is an expression?
An expression is a group of value and operators which may be evaluated a single values.
Q. What are the types of expression?
The different types of expressions are
1. Logical expression
2. Compound expression
3. Arithmetic expression
4. Negating expression.
Q. What is a synonym?
The synonym is a user-defined object that is used to define an alias name for the user defined objects like table view etc.
Q. What is a condition?
A Condition could be said to be of the logical datatype that evaluates
the expression to a True or False value.
Q. What are the 7 forms of condition?
There are totally 7 forms of condition
Q. A comparison with expression or subquery results.
Q. A comparison with any or all members in a list or a subquery
3. A test for membership in a list or a subquery
4. A test for inclusion in a range
5. A test for nulls.
6. A test for existence of rows in a subquery
7. A test involving pattern matching
8. A combination of other conditions
Q. What are cursors?
Oracle uses work areas called private SQL areas to execute SQL statements and store processing information. This private SQL work area is known as cursors.
Q. What are explicit cursors?
Cursors that are defined for performing a multiple row select are known
as explicit cursors.
Implicit cursors are the type of cursors that is implicitly opened
by the Oracle itself whenever you perform any DML statements like
Update, delete, insert or select into statements.
Q. What is a PL/SQL?
PL/SQL is a transaction processing language that offers procedural
solutions.
Q.What is an embedded SQL?
All the SQL statements written in a Host language are known
as Embedded SQL statements.
Q. What are the different conditional constructs of PL/SQL?
The statements that are useful to have a control over the set
of the statements being executed as a single unit are called as
conditional constructs.
The following are the different type of conditional constructs
of PL/SQL
1. if <condition>
then
elsif<condition> then
end if
2. While <condition>
loop
end loop
3. loop
exit when<conditon>
end loop
4. for <var> in range1..range2
loop
end loop
5. for i in <query/cursor identifier>
loop
end loop
Q. How is an array defined in PL/SQL?
Typedef <identifier> table of <datatype>
Index by binary_integer;
Q. How to define a variable in PL/SQL?
Variablename datatype<size> <not null> <: = value>
Q. How to define a cursor in PL/SQL?
Cursor variable is <query>
Q. What are exceptions?
The block where the statements are being defined to handle internally
and userdefined PL/SQL errors.
Q. What are the systems exceptions?
When an Oracle error is internally encountered PL/SQL block
raises an error by itself. Such errors are called as internal
or system defined exception.
Following are some of the internal exceptions:
1. Zero_divide, 2. No_data_found 3. Value_error 4. Too_many_rows
Q. How to define our own exceptions in PL/SQL?
Define a PL/SQL variable as an exception in the variable declaration section.
In order to invoke the variable that is an exception type
use the raise statement.
Declare a exception;
Begin
statements....
-----
if x > y
then
raise a;
end if;
exception
when a then
statements,
rollback;
when others then
commit;
end;
Q. How is the performance of Oracle improved by PL/SQL in Oracle?
Without PL/SQL the ORACLE RDBMS must process SQL statements one at a time, Each SQL statement results in another call to RDBMS and higher performance overhead. This overhead can be significant when you are issuing many statements in a network environment.
With the PL/SQL all the SQL statements can be sent to RDBMS at one time.
This reduces the I/O operations. With PL/SQL a tool like Forms can do
all data calculations quickly and efficiently without calling on
the RDBMS .
Q. What is SCHEMA?
A SCHEMA is a logical collection of related items of tables and Views.
Q. What are profiles?
A Profile is a file that contains information about the areas that a user can access.
Q. What are roles?
A role is a collection of related privileges that an administrator can grant collectively to database users.
Q. How can we alter a user's password in ORACLE?
Inorder to Alter the password of the user we have to use the following statement:
ALTER USER user_name identified by passwd
E.g.: Alter user sam identified by Paul
Q. What is a tablespace in Oracle?
A tablespace is a partition or logical area of storage in a database that directly corresponds to one or more physical data files.
Q. What is an extent?
An extent is nothing more that a number of contiguous blocks that ORACLE-7 allocates for an object when more space is necessary for the object data.
Q. What are PCTFREE and PCTUSED parameters?
PCTFREE: - PCTFREE controls how much of the space in a block is reserved for statements that update existing rows in the object.
PCTUSED: - PCTUSED is a percentage of used space in a block that triggers the database to return to the table's free space list.
Q. What is a block in Oracle?
The Place where the data related to Oracle are stored physically in an Operating System is known as block.
Q. What is Client-server architecture?
A client/server system has three distinct components
• Focusing on a specific job
• A database server
A client application and a network.
A server (or back end) focuses on efficiently managing its resource Such as database information. The server's primary job is to manage its resource optimally among multiple clients that concurrently request the server for the same resource.
Database servers concentrate on tasks such as
* Managing a single database of information among many concurrent
users.
* Controlling database access and other security requirements.
* Protecting database information with backup and recovery features.
* Centrally enforces global data integrity rules across all
Client applications.
A client application ("the front end") is the part of the system that users employ to interact with data. The client applications in a client/server database system focus on jobs such as
* Presenting an interface a user can interact with to accomplish work.
* Managing presentation logic such as popup lists on a
data entry form or bar graphs in a graphical data presentation
tool.
* Performing application logic, such as calculating fields
in a dataentry form.
* Validating data entry.
* Requesting and receiving information from a database server.
A network and communication software is the vehicle that transmit
data between the clients and the server in a system. Both the clients
and the server run communication software that allows them to
talk across a network.
Types of Client Server Architecture:
1. Dedicated Client Server Architecture
2. Multi-threaded Client Server Architecture
3. Single- Task Client Server Architecture
Dedicated Server: Connects the Client Directly to the dedicated server
Multi-Threaded Server: It is a type of architecture that is a combination of dispatcher, listener and front-end server process to serve the requests of many clients with minimal process overhead on the database server.
Single-Task server: In host-based database server system a user
employs a dumb terminal or terminal emulator to establish a session
on the host computer and run the client database application.
73. What is a segment in Oracle? Explain the different types?
The places where the data are stored in the allotted tablespace are called as segments. The data may be a table or index data required by DBMS to operate. Segments are the next logical level of a storage tablespace.
There are basically 5 types of segments
* Data segment: Contains all the data of each table
* Index segment: Contains all the index data for one or more indexes
Created for a table.
* Rollback segment: Contains the recorded actions, which should be undone under certain circumstances like
* Transaction rollback
* Read consistency
* Temporary segment:
Whenever a processing occurs Oracle often requires temporary workspace for intermediate stages of statement processing. These areas are known as temporary segments.
* Bootstrap segment: Contains information of the data dictionary definition for the tables to be loaded whenever a database is opened.
Q. What is the use of Rollback segment?
It is a portion of a database that records the information about the actions that should be undone under certain circumstances like
* Transaction Rollback
* Read consistency
Q. What is read-consistency in Oracle?
Read consistency in Oracle is a process that ignores the changes by others in a table whenever a table is queried. Read consistency in Oracle is achieved by a statement
SET TRANSACTION READ ONLY
Q. What is SGA?
SGA is System Global Area.
The library cache and dictionary cache makes up the shared pool. The shared pool combined with buffer-cache make up the System Global Area.
Library Cache: - It stores the SQL Statements and PL/SQL Procedures.
Dictionary Cache: - Holds dictionary information in memory.
Buffer and Cache: - the place where the data related to recently requested transaction is stored.
Q. What is Back Ground Process?
The Process of server is being classified into two processes namely Foreground and Background.
Foreground handles the request from client processes while background handle other specific row of the database server like writing data to data and transaction Log Files.
Q. System Userid?
Whenever you create a database an Userid is automatically created related with database administration connections. This account/userid is called System Userid.
Q. SYS Userid?
It is a special account through which DBA can execute special database administration connections. SYS is the owner of database's data dictionary table.
Q. Data Dictionary?
It provides the details on the database objects such as columns, views Etc., the oracle users, the privileges and the rights of users over different objects.
Q. SQL*DBA?
SQL*DBA is a utility through which you can manage a database system effectively.
Q. ORACLE ADMINISTRATOR?
The person who takes care of monitoring the entering performances of the database system is called, as an Oracle Administrator.Oracle Administrator is the main person who takes care of assigning the set of to act as DBA for monitoring certain jobs like
1. Creating primary database storage structure.
2. Monitoring database performance and efficiency.
3. Backing up and restoring.
4. Manipulating the physical location of the database.
TO CREATE DATABASE:
1. Determining appropriate values for the file limit parameters of the create database command.
Parameters
Max data files: Determines the maximum number of data files that can ever be allocated for the database
Max Log Files: Determines the maximum number of log groups for the database.
Max Log Members: Maximum number of members for each log group.
Q. What are database files?
The physical files of Oracle are known as database files.
Q. What is a Log File?
The files that contains information about the information of recovery of oracle database at the event of a SYSTEM CRASH or a MEDIA Failure.
Q. What is an Init file?
Init files are known as Initialization Parameter files.
Init files are used for setting the parameters
* For an Oracle instance
* For Log files
Q. What is a control file? What is its significance?
A control is a small binary file. It contains the entire system executable code named as ORACLE.DCF.
A control file always consists of the following
1: Name of the database
2: Log files
3: Database creation
Q. What does an UPDATE statement does?
To update rows in a table.
Q. What does a Delete statement does?
To remove the rows from the table.
Q. What does an insert statement do?
To insert new rows into a database.
Q. What does a Select statement do?
To query data from tables in a database
Q. How to create a table using selects and inserts statements?
Using Select statement:
Create table tablename
as
<Query >
Using insert statement we cannot create a table but can only append
the data into the table
Using Insert statement:
Insert into tablename
<Query>
Q. How to delete duplicate rows in a table?
Delete from tablename where rowid not in
(Select min (rowid) from tablename group by column1, column2...)
Q. What is an instance?
An Oracle instance is a mechanism that provides the mechanism for processing and controlling the database.
Q. What is startup and shutdown?
Startup is a process making the Oracle Database to be accessed by all
the users
There are three phases to database startup
Q. Start a new instance for the database
2. Mount the database to the instance
3. Opening the mounted database
Shutdown is a process making the Oracle Database unavailable for all
the users.
There are three phases to database shutdown
1. Close database
2. Dismount the database from the instance
3. Terminate the instance.
Q. What is mounting of database?
Q. What is a two-phase commit?
Q. What are snap-shots?
A Snapshot is a stable that contains the results of query Of one or more tables or views, often located on a remote database.
Q. What are triggers and stored Procedures?
A procedure is a group of PL/SQL statement that you call by a name. Compiled version of procedure that is stored in a database are known as Stored Procedures.
A database trigger is a stored procedure that is associated with a table.
Oracle automatically fires or executes when a triggering statement is issued.
Q. What are Packages?
A package is an encapsulated collection of related program objects stored together in the database.
Q. What is SQL*Forms3.0? Is it a Client or a server?
Sql*Forms is a general-purpose tool for developing and executing forms based interactive applications. The component of this tool is specially
designed for application developers and programmers and it is used for the following tasks :
* Define transactions that combine data from multiple tables into a single form.
* Customize all aspects of an application definition using std-fill-in- interface to enhance the productivity and reduce learning time.
Sql*Forms3.0 is a Client.
Q. What are Packaged Procedures?
A packaged procedure is a built in PL/SQL procedure that is available in all forms.
Using packaged procedure we can build triggers to perform the following
Tasks to
* Reduce the amount of repetitive data entry.
* Control the flow of application
* Ensuring the operators always follow sequence of actions when
they use a form.
Q. What are different types of triggers?
The following are the different type of triggers they are
1. Key-triggers
2. Navigational Triggers.
3. Transactional Triggers.
4. Query-based Triggers
5. Validation Triggers
6. Message - Error handling Triggers.
Q. What is the difference between the restricted and Un-Restricted Packaged Procedure?
Any packaged procedure that does not interfere with the basic function
of SQL*Forms is an unrestricted packaged procedure. The Un-restricted
Packaged procedure can be used in all types of triggers.
Any packaged procedure that affects basic SQL*FORMS function is a restricted packaged procedure. Restricted packaged procedure can be used only in key-triggers and user-named triggers.
Q. What is a system variable?
A System variable is a SQL*Forms variable that keeps track of some internal process of SQL*Forms in state. The system variable helps us to control the way an application behaves. SQL*Forms maintains the value of a system on a performance basis. That is the values of all the system variables correspond only to the current form.
Q. What are Global Variables?
A Global variable is a SQL*Form variable that is active in any trigger within a form and is active throughout SQL*Form (Run-Form) session. The variable stores string value upto 255 characters.
Q. What are the different types of objects in SQL*Forms?
A SQL*Form application is made up of objects. These objects contain all
the information that is needed and produce the SQL*Forms application.
Following are the objects of the SQL*Forms:
1. Form
2. Block
3. Fields
4. Pages
5. Triggers
6. Form-Level-Procedures.
Q. What are Pages?
Pages are collection of display information such as constant text and graphics. All fields are displayed in a page.
Q. What are a Block and its types? Explain the different types of blocks?
Block is an object of Forms that describes section of a form or a subsection of a Form and serve as the basis of default database interface.
Types of Blocks:
1. Control Block: Control block is not associated with any table in the
database. It is made up of fields that are base table fields, such as temporary data fields.
2. Detail Block: Detail Block is associated with a master block in
Master-detail relationship. The detail block displays detail records associated with master records in a block.
3. Master-Block: A master block is associated with a master-detail relationship. The master block display master records associated with detail records in the detail block.
4. Multi-record Block: A multi-record block can display more than one record at a time.
5. Non-enterable Block: A non-enterable block consists of all non-enterable fields.
6. Single-record Block: A single record block can display only one record at a time.
Q. What is a Screen Painter?
This is a SQL Forms "work area" where you can modify the layout of forms. The screen painter displays one page area at a time.
Q. What are the different field types?
The different types of fields in SQL*Forms are
1. Base - table field
2. Control-field
3. Enterable-field
4. Hidden-field
5. Look-up field
6. Non-enterable field
7. Scrolled - field
Q.What is page Zero?
The place where the hidden fields are being placed in an application
Q. What does Message procedure do?
The Message procedure displays specified text on the message line.
Q. What does Name_in function do?
The Name_in packaged function returns the contents of the variable to which you apply it. The returned value is in form of a string.
Q. What does CLEAR_EOL procedure do?
Clear_Eol clears the current field's value from the current cursor position to the end of the line or field.
Q. What does On-Error trigger do?
The On-error trigger fires whenever SQL*Forms would normally cause an error message to display. The actions of an On-Error triggers is used for the following purposes:
* Trap and recover an error.
* Replace a standard error message with a customized message.
Q. What does copy procedure do?
The Copy procedure writes a value into a field. Copy exists specifically to write a value into that is referenced through NAME_IN packaged function.
Q. What is the Arraysize parameter?
The Array-Size parameter is a block-characteristic that specifies the Maximum number of records that SQL Forms (Run-Form) can fetch from the database at one time.
Q. What does Go_Block packaged procedures do?
The Go_Block packaged procedure navigates to the indicated Block.If the target is non-enterable an error occurs.
Q. What does ANCHOR_VIEW procedure do?
Anchor_view moves a view of a page to a new location on the screen. This procedure effectively changes where on the screen the operator sees the view.
Q. How to call a form from inside a form?
Inorder to call a form from inside a form we have to use the CALL packaged-procedure.
Inorder to call a form from inside a form the packaged procedure Call is used.
Syntax: CALL (Formname).
When call runs an indicated form while keeping the parent form active.
SQL*Forms runs the called form with the same SQL*Forms options as the
parent form.
Q. How to send parameters to another form?
Inorder to send parameters across the forms we use the global variables.
Q. How to give automatic hint text for fields?
Inorder to give automatic hint text for fields, In the field definition screen of the fields we are having an option called Hint value. Inorder to activate this option in the Select attribute section invoke the option called Automatic Hint.
Q. How to see key map sequences?
Inorder to see key map sequences we have to press the SHOW KEY screen
key function.
Q. What is SYNCHRONIZE procedure does?
The synchronize procedure synchronizes the terminal screen with the internal state of form, that is synchronize updates the screen display to reflect the information that SQL*Forms has in its internal representation of the screen.
Q. What is EXECUTE_QUERY procedure?
The Execute_query procedure flushes the current block, opens a query and fetches a number of selected records. If there are changes to commit, SQL*Forms prompts the operator to commit them during the execute_qury event.
Q. How to customize system message in SQL*Forms?
Inorder to customize the system messages the On-Message trigger is used.
Q. How to define the fields in WYSIWYG Format?
Q. What is an On-Insert trigger? How is it different from Pre-insert
trigger?
An On-insert trigger replaces the default SQL*Forms processing for handling inserted records during transaction posting. It fires once for each rows that is marked for insertion into the database. An On-insert Trigger fires during the Post and Commit Transactions event. Specifically it fires after the Pre-insert trigger and before the
Post-insert trigger.
Q. What is the difference between a Trigger and a Procedure?
Procedures can take in arguments where as Triggers cannot take in arguments.
Q. How to call a stored procedure from inside a form?
To call a Stored Procedure inside a form
Trigger Text:
Procdurename<paramaters>
Q. What are V2 Triggers?
V2 Triggers are the types of Triggers in which we can perform only a simple query. And we cannot write a PL/SQL block.
Q. How to rename a Form?
To rename a form select the rename option in the Action Menu. Then give the form name. Press Accept. In the next field give the new name. Press Accept to Execute.
Q. What is a Pop -up page? How to define one?
Pop-Up Pages: -
Pop-Up page is a SQL*Forms object which overlays on an area of the current displayed page in response to some event or for user call. To define a Pop-Up page use the page definition form which is in the Image-Modify option. In that form put an X in the Pop-Up field to make the current page as Pop-Up.
Q. What is a Group in SQL * REPORTWRITER?
Group in ReportWriter: -
Group is a collection of fields, or single field. Usually by default a group will bare the field, which are references by a single query. But we can change from single query group -multi groups.
Q. How do you define a Parent-child relationship in ReportWriter?
Parent - Child Relation: -
To define a Parent-Child relationship first we need more that one
Query. We should first enter the Parent Query and then the Child Query. In the Child Query Form we should give the Parent Query Name in the desired position and the common columns in both queries.
Q. What is a Rowcount function in ReportWriter?
It is a field level function that is used for generation of automatic row numbers related to database column that does not have null values.
Q. How do you define a matrix report?
Matrix Report: -
Matrix Report is a Report that consists of Two Parent Queries and one Child Query.
Procedure for Defining a Matrix Report:
Q. Define Two Parent Queries.
2. Define a Child Query. In the Definition screen specific which column of the child is to be related to the Query1 and to Query2.
3. After defining the queries in the Query option, In the
Group option
Place All the Groups in the option called MatrixGroup
Define the Print Direction for Query1 as down
Define the Print Direction for Query2 as across
Define the Print Direction for Child Query as cross tab
Q. How do you execute a report from within a form?
Use the following command to run a report from the FORM.
Host ('runrep <rep_name> term<terminal_type> userid=<userid/passwd>);
Q. What are exp and imp utilities?
Export & Import: -
Export utility is to write data from database to operating system files called Export Files. An export does this by changing the data and table structures in to ASCII or EBCDIC codes.
Import is a utility with which we can write the data from Export file to database. Export Files can be only read by Import.
No comments:
Post a Comment