1. What is Propagation of Exceptions?
2. What is Instance? What are all the background processes start when you start a database instance?
3. What is a database trigger and what are the different kinds of triggers available?
4. What is INSTEAD OF trigger?
5. What are Implicit and Explicit Cursors?
6. What are different kinds of views available? What is the syntax?
7. What is Join? How many types of joins are available. What is special feature of Join in Oracle 8i?
8. What is the use of GROUP BY clause? And How HAVING clause works?
9. What is synonym and what is the use of synonym?
10. What are the basic grants to be given when a new user is created?
11. What are types of backups?
12. What is Explain Plan?
13. What are Hierarchical Queries and Cursors?
14. What is LEVEL?
15. What are pseudo columns available in Oracle?
16. What is Exception? What are the different kinds of Exceptions?
17. What is PRAGMA?
18. What is Dynamic SQL? What is difference between Native Dynamic SQL and DBMS_SQL?
19. What is Indexing? What are different kinds of Indexes?
20. What is PCTFREE and PCTUSED?
21. What is the difference between compiled and uncompiled version of Procedure or Function?
22. What is Package and what is use of Packages?
23. What are anonymous PL/SQL blocks?
24. What is TK Prof?
25. What are Implicit and Explicit Cursor attributes?
26. What is Strong and Weak Cursor?
27. What is Data Dictionary?
28. Can you Insert into, Update, Delete a View?
29. What is materialized view?
30. What is Snapshot?
31. What is mutating trigger?
32. What is the use of CUBE and GROUPING function?
33. What is REF Cursor?
Oracle Querieis
1. Q) 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 Q) How many types of Sql Statements are there in Oracle
A ) 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.Q) 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 one 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 snap shots 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) 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
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.
1. What is RDBMS? What are different database models.
RDBMS : Relational Database Management System.
In RDBMS the datas are stored in the form of tables
i.e. rows & columns.
The different database models
are 1. HDBMS = Hierrachial Database Management system.
2. NDBMs = Network Database Management System.
3. RDBMS = Relational Database Management System.
2. 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.
3. What is a transaction.
Transaction is a piece of logical unit of work done
between two sucessive commits or commit and rollback.
4. What is a commit ?
Commit is a transaction statement that make the changes permanent
into the database.
5. What is a Rollback?
Rollback is a transaction statement that undoes all changes to a savepoint
or since the beginning of the transaction.
6. 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.
7. What is DML?
DML - Data Manipulation Language.
It is a set of statements that is used for manipulation of datas.
eg. Inserting a row into a table,delete a row from a table etc.
8. What is Locking?
The mechanism followed by the SQL to control concurrent operations
on a table is called locking.
9. What is a Dead lock?
When two users attempt to perform actions that interfere with one
another,this situation is defined as Deadlock.
Eg:- If two users try to chage both a foreign and its parent key
value at the same time.
10. What is a Shared Lock?
The type of lock that permits other users to perform a query, but
could not manipulate it, i.e.) cannot perform any modification
or insert or delete a data.
11. 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.
12. 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.
13. What are Group - Functions ?
The Functions that are used to get summary informations 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 datas of a given column.
14. 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.
15. 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.
16. 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'.
17. What is a Rowid?
For each row in the database, The ROWID pseudocolumn 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.
18. What is a PRIMARY KEY ?
PRIMARY KEY CONSTRAINT:
1. Identified the columns or set of columns which uniquely identify
each row of a table and ensures that no duplicate rows exist
in the table.
2. Implicitly creates a uniqu 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));
19. What is a Unique constraint?
UNIQUE Constraint :
1. ensures that no two ros of a tbale have duplicte values
in the specified columns(s).
2. implicitly creattes 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,
........,
);
20. 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.
21. What is a foreign key ?
FOREIGN KEY Constraint :
1. Enforces referential integrity constraint which requires that for
each row of a tbale , 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,
.......);
22. 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
23. What is a Referential Integrity :
1. ENforces master/detail relationship between tables based on keys.
* Foreign key
* Update Delete restrict action
* Delete Cascade action
24. What are different datatypes?
The following are the different datatypes available in Oracle
1. Internal Datatypes 2. Composite Datatypes
Internal Datatypes
1. Character Datatype
2. Date Datatype
3. Raw and Long Raw datatypes
4. Rowid Datatype
Composite Datatypes
1. Table Datatype
2. Record Datatype
25. 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 values 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.
26. What is datatype mixing?
27. 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.
28. What is a sequence.
A sequence is a database object from whiich multiple users may generate
unique integers.
29. What are pseudo-columns in ORACLE?
The columns that are not part of the table are called as pseudocolumns
30. 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.
31. What are Single Row number 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. trunc(n,m) returns n truncated to m decimal
places.
32. What are single row character functions.
The function that process a value of datas which is of character datatype and returns a character datatype after every row is being processed are termed as single row character functions.
Function Nam Purpose.
1. Chr(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.
33. 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.
34. What are Date functions?
Functions that operate on Oracle Dates are termed as Date functions.
All datefunctions 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 lthe
week.
MONTHS_BETWEEN(d,e) returns no of months between dates
d & e.
35. What is NEW_TIME funtion?
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 Berning stand or daylight time
GMT Greenwich Mean time.
PST,PDT Pacific standard time.
YST,YDT Yukon standard or daylight time.
36. What is Convert function?
Convert function converts two different implementations of lthe
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 sett
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])
37. What is a translate function?
The function that returns a character after replacing all occurences
of the character specified with the corresponding character is called
as translate function.
eg. TRANSLATE('Hello','l','L')
38. What is a soundex function?
Soundex is a functions 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);
39. What is a replace function?
Replace function returns character with every occurence of the search string replaced with the replacement string. If the replacement string is not supplied, all occurences of search_string are being removed. Replace allows you to substitute one string from another.
40. What is a Floor function?
Floor Function returns the largest integer equal to or than n
syntax : floor(n);
41. 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
42. 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.
43. 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.
Eg: Decode(expr,search1,result1,[search2,result2],[default]);
44. 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.
45. 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.
46. Give 5 examples for DATE, Number function
Examples for Number Function:
1. 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
47. What is an expression?
An expression is a group of value and operators which may be evalueated a single values.
48. What are the types of expression?
The different types of expressions are
1. Logical expression
2. Compound expression
3. Arithmetic expression
4. Negating expression.
49. 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.
50. 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.
51. What are the 7 forms of condition?
There are totally 7 forms of condition
1. A comparison with expression or subquery results.
2. 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
51. What are cursors?
Oracle uses work areas called private SQL areas to execute SQL statements
and store processing information. This private SQL work area are known as cursors.
52. 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.
53. What is a PL/SQL?
PL/SQL is a transaction processing language that offers procedural solutions.
54.What is an embedded SQL?
All the SQL statements written in a Host language are known as Embedded SQL statements.
56. 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
then
elsif
end if
2. While
loop
end loop
3. loop
exit when
end loop
4. for in range1..range2
loop
end loop
5. for i in
loop
end loop
57. How is an arry defined in Pl/SQl?
Typedef
58. How to define a variable in Pl/SQL?
variablename datatype
59. How to define a cursor in PL/SQL?
Cursor variable is
60. What are exceptions?
The block where the statements are being defined to handle internally
and userdefined Pl/SQL errors.
61. 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
62. 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;
63. How is the performance of Oralce 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
statement 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 .
64. What is SCHEMA?
A SCHEMA is a logical collections of related items of tables and views.
65. What are profiles ?
A Profile is a file that contains information about the areas that a user can access .
66. What are roles?
A role is a collection of related priveleges that an administrator can
grant collectively to database users.
67. 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
eg: Alter user sam identified by paul
68. 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.
69. 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 objects
data.
70. 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.
71. What is a block in Oracle?
The Place where the datas related to Oracle are stored physically
in an Operating System is known as Block.
72. What is a 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 enforce 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 dataentry form or bargraphs 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 are the vehicles 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 place where the datas are stored in the alloted 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 work space
for intermediate stages of statement processing. These area are known
as temporary segments.
* Bootstrap segment : Contains information of the data dictionary definition
for the tables to be loaded whenever a databases opened.
74. 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
75. 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
*/
76. 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 datas related to recently requested transaction is stored.
77. What are 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 Back-Ground handle other specific row of the database server like writing data to data and transaction Log Files.
78. System Userid-
Whenever You create a database an userid is automatically created
related with database administration connections. This account/userid is
called System Userid.
79. SYS Userid:-
It is a special account through which DBS can execute special
database administration connections.
SYS is the owner of database's data dictionary table.
80. Data Dictionary:-
It provides the details on the database objects such as columns, views
etc., the oracle users, the priveleges and the rights of users over
different objects.
81. SQL*DBA:-
SQL*DBA is a utility through which you can manage a database system
effectively.
82. ORACLE ADMINISTRATOR:-
The person who takes care of monitoring the entiring 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 datafiles 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.
84. What are database files?
The physical files of Oracle are known as database files.
85. 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.
86. What is an init file?
Init files are known as Initialisation Parameter files.
Init files are used for setting the parameters
* for an Oracle instance
* for Log files
87. What is a control file ? What is its significance?
A control is a small binary file. It contains all the 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
88. What does an UPDATE statement do?
To update rows in a table.
89. What does an Delete statement do?
To remove the rows from the table.
90. What does an insert statement do?
To insert new rows into a database .
91. What does an Select statement do?
To query data from tables in a database
92. How to create a table using select and insert statements?
Using Select statement:
Create table tablename
as
Using insert statement we cannot create a table but can only append
the datas into the table
Using Insert statement:
Insert into tablename
93. How to delete duplicate rows in a table?
delete from tablename where rowid not in
(select min(rowid) from tablename group by column1,column2,...)
94. What is an instance?
An Oracle instance is a mechanism that provides the mechanism for processing
and controlling the database.
95. 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
1. 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.
96. What is mounting of database?
97. What is a two-phase commit?
98. What are snap-shots?
A Snap-shot is a stable that contains the results of queryof one or more tables or views, often located on a remote database.
99. 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.
100. What are Packages?
A package is an encapsulated collection of related program objects stored
together in the database.
101. What is SQL*Forms? 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.
* customise all aspects of an application definition using std-fill-in- interface
to enhance the productivity and reduce learning time.
Sql*Forms is a Client.
102. 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.
103. What are different type 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.
104. 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 fucntion is a restricted
packaged procedure. Restricted packaged procedure can be used only in
key-triggers and user-named triggers.
105. What is a system variables.
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 correspronds onl
only to the current form.
106. 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 a string value upto 255 characters.
107. 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.
108. What are Pages?
Pages are collection of display information such as constant text and graphics
All fields are displayed in a page.
109. What is 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.
110. 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.
111. 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
112.What is page Zero?
The place where the hidden fields are being placed in an application
113. What does Message procedure do?
The Message procedure displays specified text on the message line.
114. 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.
115. What does CLEAR_EOL procedure do?
Clear_Eol clears the current field's value from the current cursor position
to the end of lthe line or field.
116. 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 customised messages.
117. 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.
118. 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.
119. What does Go_Block packaged procedure do?
The Go_Block packaged procedure navigates to the indicated Block.
If the target is non-enterable an error occurs.
120. 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.
121. 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-pr
Inorder to call a from 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.
122. How to send parameters to another form?
Inorder to send parameters across the forms we use the global variables.
123. How to give automatic hint text for fields?
Inorder to give automatic hint text for fields, In the field defintion
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.
124, How to see key map sequences?
Inorder to see key map sequences we have to press the SHOW KEY screen
key function.
125. 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.
126. 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_query
event.
127. How to customise system message in SQL*Forms?
Inorder to customise the system messages the On-Message trigger is
used.
128. How to define the fields in WYSIWYG Format?
129. 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 row 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.
130. What is the difference between a Trigger and a Procedure?
Procedures can take in arguments where as Triggers cannot take in
arguments.
131. How to Call a stored procedure from inside a form?
To call a Stored Procedure inside a form
Trigger Text :
Procdurename
132. 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.
133. 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.
134. 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 a X in the Pop-Up field to make the
current page as Pop-Up.
135. 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.
136. 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.
137. 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.
138. 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 :
1. 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
139. How do you execute a report from within a form ?
Use the following command to run a report from the FORM.
host('runrep
140. What are exp and imp utilities?
Export & Import:-
Export utility is to write data from database to operating system files
called Export Files. Exports 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.
1. To select all the rows from emp table
select * from emp;
2. To delete duplicate rows from the table
delete from table1 where rowid not in
(select max(rowid) from table1 group by n);
3.To select even numbered rows from the table
select * from emp where rowid in
(select decode(mod(rownum,2),0,rowid) from emp);
4. To select odd numbered rows from emp table
select * from emp where rowid in
(select decode(mod(rownum,2),1,rowid) from emp)
5. To select first 3 maximum salary from the emp table
select sal from emp x where 3>(select count(*) from emp y where x.sal
6 To select last two rows from emp table
select * from emp
minus
select * from emp where rownum<=(select count(*) - 2 from emp);
7. To check whether the given number is Positive or negative or zero
select decode(sign(&n),-1,'negative',1,'positive','zero') from dual;
8. To select the rows between 5 and 8
select * from emp where rownum<=8
minus
select * from emp where rownum<=5
9. To select the nth row of a table
select * from emp where rowid in
(select max(rowid) from emp where rownum<=&n)
10. Consider a table named student whth following columns
name varchar(20)
mark number
result varchar(10)
You want to write a query which will check each row of the mark in the
table student and update the result field if the mark is 40
The query is
update student set result =(decode(sign(mark-40),1,'pass','fail') );
11. To find the cumulative sum of salries
consider the table run_total with the following fields
run_date date Primary key,
amount number;
Here we want to find the cumulative sum of salaries
select r1.run_date,r1.amount,sum(r2.amount)
from run_total r1,run_total r2
where
r2.run_date<=r1.run_date
group by r1.run_date,r1.amount
12. To select nth maximum salary
select max(sal) from emp x where &n=
(select count(*) from emp y where x.sal<=y.sal);
TABLE SALESPEOPLE
SNUM SNAME CITY COMM
1001 Peel London .12
1002 Serres San Jose .13
1004 Motika London .11
1007 Rafkin Barcelona .15
1003 Axelrod New york .1
TABLE CUST
CNUM CNAME CITY RATING SNUM
2001 Hoffman London 100 1001
2002 Giovanne Rome 200 1003
2003 Liu San Jose 300 1002
2004 Grass Berlin 100 1002
2006 Clemens London 300 1007
2007 Pereira Rome 100 1004
ORDERS
ONUM AMT ODATE CNUM SNUM
3001 18.69 03-OCT-94 2008 1007
3003 767.19 03-OCT-94 2001 1001
3002 1900.10 03-OCT-94 2007 1004
3005 5160.45 03-OCT-94 2003 1002
3006 1098.16 04-OCT-94 2008 1007
3009 1713.23 04-OCT-94 2002 1003
3007 75.75 05-OCT-94 2004 1002
3008 4723.00 05-OCT-94 2006 1001
3010 1309.95 06-OCT-94 2004 1002
3011 9891.88 06-OCT-94 2006 1001
SNUM SNAME CITY COMM
INSERT INTO SALESPEOPLE (SNUM,SNAME,CITY,COMM) VALUES ('&SNUM','&SNAME','&CITY',&COMM);
1001 Peel London .12
1002 Serres San Jose .13
1004 Motika London .11
1007 Rafkin Barcelona .15
1003 Axelrod New york .1
TABLE CUST
CNUM CNAME CITY RATING SNUM
2001 Hoffman London 100 1001
2002 Giovanne Rome 200 1003
2003 Liu San Jose 300 1002
2004 Grass Berlin 100 1002
2006 Clemens London 300 1007
2007 Pereira Rome 100 1004
INSERT INT0 CUST (CNUM,CNAME,CITY,RATING,SNUM) VALUES ('&CNUM','&CNAME','&CITY',&RATING,'&SNUM');
ORDERS
ONUM AMT ODATE CNUM SNUM
3001 18.69 03-OCT-94 2008 1007
3003 767.19 03-OCT-94 2001 1001
3002 1900.10 03-OCT-94 2007 1004
3005 5160.45 03-OCT-94 2003 1002
3006 1098.16 04-OCT-94 2008 1007
3009 1713.23 04-OCT-94 2002 1003
3007 75.75 05-OCT-94 2004 1002
3008 4723.00 05-OCT-94 2006 1001
3010 1309.95 06-OCT-94 2004 1002
3011 9891.88 06-OCT-94 2006 1001
INSERT INTO ORDERS(ONUM,AMT,ODATE,CNUM,SNUM) VALUES ('&ONUM',&AMT,'&ODATE','&CNUM','&SNUM');
CREATE TABLE SALESPEOPLE
(
SNUM VARCHAR2(6) PRIMARY KEY,
SNAME VARCHAR2(20),
CITY VARCHAR2(10),
COMM NUMBER(5,2)
)
CREATE TABLE CUST
(
CNUM VARCHAR2(6) PRIMARY KEY,
CNAME VARCHAR2(10),
CITY VARCHAR2(10),
RATING NUMBER(3),
SNUM VARCHAR2(6) CONSTRAINT FK_SNUM_CUST_01 REFERENCES SALESPEOPLE(SNUM)
)
CREATE TABLE ORDERS
(
ONUM VARCHAR2(6) PRIMARY KEY,
AMT NUMBER(5,3),
ODATE DATE,
CNUM VARCHAR2(6) CONSTRAINT FK_CNUM REFERENCES CUST(CNUM)
)
Problems :
1.Display snum, sname, city and Comm of all salespeople.
Select snum, sname, city, Comm From salespeople;
2.Display all snum without duplicates from all orders.
Select distinct snum From orders;
3.Display names and commissions of all salespeople in London.
Select sname,comm from salespeople Where city = 'London';
4.All customers with rating of 100.
Select cname from cust where rating = 100;
5.Produce orderno, amount and date form all rows in the order table.
Select ordno, amt, odate from orders;
6.All customers in San Jose, who have rating more than 200.
Select cname from custwhere rating > 200;
7.All customers who were either located in San Jose or had a rating above
200.
Select cname from cust where city = 'San Jose' or rating > 200;
8.All orders for more than $1000.
Select * from orders where amt > 1000;
9.Names and citires of all salespeople in london with commission above 0.10.
Select sname, city from salepeople where comm > 0.10 and city =
'London';
10. All customers excluding those with rating <= 100 unless they are located
in Rome.
Select cname from cust where rating <= 100 or city = 'Rome';
11.All salespeople either in Barcelona or in london.
Select sname, city from salespeople where city in
('Barcelona','London');
12. All salespeople with commission between 0.10 and 0.12. (Boundary values
should be excluded)
Select sname, comm from salespeople where comm > 0.10 and comm <
0.12;
13.All customers with NULL values in city column.
Select cname from cust where city is null;
14. All orders taken on Oct 3Rd and Oct 4th 1994.
Select * from orders where odate in ('03-OCT-94','04-OCT-94');
15.All customers serviced by peel or Motika.
Select cname from cust, orders where orders.cnum = cust.cnum and
orders.snum in ( select snum
from salespeople
where sname in 'Peel','Motika'));
16.All customers whose names begin with a letter from A to B.
Select cname from cust where cname like 'A%' or cname like 'B%';
17. All orders except those with 0 or NULL value in amt field.
Select onum from orders where amt != 0 or amt is not null;
18.Count the number of salespeople currently listing orders in the order
table.
Select count(distinct snum) from orders;
19.Largest order taken by each salesperson, datewise.
Select odate, snum, max(amt) from orders group by odate, snum
order by odate,snum;
20. Largest order taken by each salesperson with order value more than
$3000.
Select odate, snum, max(amt) from orders where amt > 3000
group by odate, snum order by odate,snum;
21.Which day had the hightest total amount ordered.
Select odate, amt, snum, cnum from orders
where amt = (select max(amt) from orders)
21.Which day had the hightest total amount ordered.
Select odate, amt, snum, cnum from orders
where amt = (select max(amt) from orders)
22.Count all orders for Oct 3rd.
Select count(*) from orders where odate = '03-OCT-94';
23.Count the number of different non NULL city values in customers table.
Select count(distinct city) from cust;
24. Select each customer's smallest order.
Select cnum, min(amt) from orders group by cnum;
25.First customer in alphabetical order whose name begins with G.
Select min(cname) from cust where cname like 'G%';
26. Get the output like " For dd/mm/yy there are ___ orders.
Select 'For ' || to_char(odate,'dd/mm/yy') || ' there are '||
count(*) || ' Orders' from orders group by odate;
27.Assume that each salesperson has a 12% commission. Produce order no.,
salesperson no., and amount of salesperson's commission for that order.
Select onum, snum, amt, amt * 0.12 from orders order by snum;
28.Find highest rating in each city. Put the output in this form. For the
city (city), the highest rating is : (rating).
Select 'For the city (' || city || '), the highest rating is : (' ||
max(rating) || ')'
from cust group by city;
29.Display the totals of orders for each day and place the results in
descending order.
Select odate, count(onum) from orders group by odate order by
count(onum);
30. All combinations of salespeople and customers who shared a city. (ie
same city).
Select sname, cname from salespeople, cust where salespeople.city =
cust.city;
31.Name of all customers matched with the salespeople serving them.
Select cname, sname from cust, salespeople where cust.snum =
salespeople.snum;
32. List each order number followed by the name of the customer who made the
order.
Select onum, cname from orders, cust where orders.cnum = cust.cnum;
33.Names of salesperson and customer for each order after the order number.
Select onum, sname, cname
from orders, cust, salespeople
where orders.cnum = cust.cnum and
orders.snum = salespeople.snum;
34. Produce all customer serviced by salespeople with a commission above
12%.
Select cname, sname, comm from cust, salespeople where comm > 0.12
and
cust.snum = salespeople.snum;
35. Calculate the amount of the salesperson's commission on each order with
a rating above 100.
Select sname, amt * comm from orders, cust, salespeople
where rating > 100 and salespeople.snum = cust.snum and
salespeople.snum = orders.snum and
cust.cnum = orders.cnum
36.Find all pairs of customers having the same rating.
Select a.cname, b.cname,a.rating from cust a, cust b
where a.rating = b.rating and a.cnum != b.cnum
37.Find all pairs of customers having the same rating, each pair coming once
only.
Select a.cname, b.cname,a.rating from cust a, cust b
where a.rating = b.rating and a.cnum != b.cnum and
a.cnum < b.cnum;
38.Policy is to assign three salesperson to each customers. Display all such
combinations.
Select cname, sname from salespeople, cust where sname in ( select
sname
from salespeople where rownum <= 3) order by cname;
39.Display all customers located in cities where salesman serres has
customer.
Select cname from cust where city = ( select city from cust,
salespeople
where cust.snum = salespeople.snum and sname = 'Serres');
40.Find all pairs of customers served by single salesperson.
Select cname from cust where snum in (select snum from cust
group by snum having count(snum) > 1);
41.produce all pairs of salespeople which are living in the same city.
Exclude combinations of salespeople with themselves as well as duplicates
with the order reversed.
Select a.sname, b.sname from salespeople a, salespeople b
where a.snum > b.snum and a.city = b.city;
42.Produce all pairs of orders by given customer, names that customers and
eliminates duplicates.
Select c.cname, a.onum, b.onum from orders a, orders b, cust c
where a.cnum = b.cnum and
a.onum > b.onum and c.cnum = a.cnum;
43.Produce names and cities of all customers with the same rating as
Hoffman.
Select cname, city from cust where rating = (select rating
from cust where cname = 'Hoffman') and cname != 'Hoffman';
44.Extract all the orders of Motika.
Select Onum from orders where snum = ( select snum
from salespeople where sname = 'Motika');
45.All orders credited to the same salesperson who services Hoffman.
Select onum, sname, cname, amt from orders a, salespeople b, cust c
where a.snum = b.snum and a.cnum = c.cnum and
a.snum = ( select snum from orders
where cnum = ( select cnum from cust where cname = 'Hoffman'));
46.All orders that are greater than the average for Oct 4.
Select * from orders where amt >
(
select avg(amt)
from orders
where odate = '03-OCT-94'
);
47.Find average commission of salespeople in london.
Select avg(comm)
from salespeople
where city = 'London';
48. Find all orders attributed to salespeople servicing customers in
london.
Select snum, cnum
from orders
where cnum in (select cnum
from cust where city = 'London');
49. Extract commissions of all salespeople servicing customers in
London.
Select comm
from salespeople
where snum in (select snum
from cust
where city = 'London');
50. Find all customers whose cnum is 1000 above the snum of serres.
Select cnum, cname from cust
where cnum > ( select snum+1000
from salespeople
where sname = 'Serres');
51. Count the customers with rating above San Jose's average.
Select cnum, rating
from cust
where rating > ( select avg(rating)
from cust
where city = 'San Jose');
52. Obtain all orders for the customer named Cisnerous. (Assume you
don't know his customer no. (cnum)).
Select onum, odate
from orders
where cnum = ( select cnum
from cust
where cname = 'Cisnerous');
53. Produce the names and rating of all customers who have above average
orders.
Select max(b.cname), max(b.rating), a.cnum
from orders a, cust b
where a.cnum = b.cnum
group by a.cnum
having count(a.cnum) > ( select avg(count(cnum))
from orders
group by cnum);
54. Find total amount in orders for each salesperson for whom this total
is greater than the amount of the largest order in the table.
Select snum,sum(amt)
from orders
group by snum
having sum(amt) > ( select max(amt)
from orders);
55. Find all customers with order on 3rd Oct.
Select cname
from cust a, orders b
where a.cnum = b.cnum and
odate = '03-OCT-94';
56. Find names and numbers of all salesperson who have more than one
customer.
Select sname, snum
from salespeople
where snum in ( select snum
from cust
group by snum
having count(snum) > 1 );
57. Check if the correct salesperson was credited with each sale.
Select onum, a.cnum, a.snum, b.snum
from orders a, cust b
where a.cnum = b.cnum and
a.snum != b.snum;
58. Find all orders with above average amounts for their customers.
select onum, cnum, amt
from orders a
where amt > ( select avg(amt)
from orders b
where a.cnum = b.cnum
group by cnum);
59. Find the sums of the amounts from order table grouped by date,
eliminating all those dates where the sum was not at least 2000 above the
maximum amount.
Select odate, sum(amt)
from orders a
group by odate
having sum(amt) > ( select max(amt)
from orders b
where a.odate = b.odate
group by odate);
60. Find names and numbers of all customers with ratings equal to the
maximum for their city.
Select a.cnum, a.cname
from cust a
where a.rating = ( select max(rating)
from cust b
where a.city = b.city);
61. Find all salespeople who have customers in their cities who they
don't service. ( Both way using Join and Correlated subquery.)
Select distinct cname
from cust a, salespeople b
where a.city = b.city and
a.snum != b.snum;
Select cname
from cust
where cname in ( select cname
from cust a, salespeople b
where a.city = b.city and
a.snum != b.snum );
62. Extract cnum,cname and city from customer table if and only if one
or more of the customers in the table are located in San Jose.
Select * from cust
where 2 < (select count(*)
from cust
where city = 'San Jose');
63. Find salespeople no. who have multiple customers.
Select snum
from cust
group by snum
having count(*) > 1;
64. Find salespeople number, name and city who have multiple customers.
Select snum, sname, city
from salespeople
where snum in ( select snum
from cust
group by snum
having count(*) > 1);
65. Find salespeople who serve only one customer.
Select snum
from cust
group by snum
having count(*) = 1;
66. Extract rows of all salespeople with more than one current order.
Select snum, count(snum)
from orders
group by snum
having count(snum) > 1;
67. Find all salespeople who have customers with a rating of 300. (use
EXISTS)
Select a.snum
from salespeople a
where exists ( select b.snum
from cust b
where b.rating = 300 and
a.snum = b.snum)
68. Find all salespeople who have customers with a rating of 300. (use
Join).
Select a.snum
from salespeople a, cust b
where b.rating = 300 and
a.snum = b.snum;
69. Select all salespeople with customers located in their cities who
are not assigned to them. (use EXISTS).
Select snum, sname
from salespeople
where exists ( select cnum
from cust
where salespeople.city = cust.city and
salespeople.snum != cust.snum);
70. Extract from customers table every customer assigned the a
salesperson who currently has at least one other customer ( besides the
customer being selected) with orders in order table.
Select a.cnum, max(c.cname)
from orders a, cust c
where a.cnum = c.cnum
group by a.cnum,a.snum
having count(*) < ( select count(*)
from orders b
where a.snum = b.snum)
order by a.cnum;
71. Find salespeople with customers located in their cities ( using both
ANY and IN).
Select sname
from salespeople
where snum in ( select snum from cust
where salespeople.city = cust.city and
salespeople.snum = cust.snum);
Select sname
from salespeople
where snum = any ( select snum
from cust
where salespeople.city = cust.city
and
salespeople.snum
= cust.snum);
72. Find all salespeople for whom there are customers that follow them
in alphabetical order. (Using ANY and EXISTS)
Select sname
from salespeople
where sname < any ( select cname
from cust
where salespeople.snum = cust.snum);
Select sname
from salespeople
where exists ( select cname
from cust
where salespeople.snum = cust.snum and
salespeople.sname < cust.cname);
73. Select customers who have a greater rating than any customer in
rome.
Select a.cname
from cust a
where city = 'Rome' and
rating > ( select max(rating)
from cust
where city != 'Rome');
74. Select all orders that had amounts that were greater that atleast
one of the orders from Oct 6th.
Select onum, amt
from orders
where odate != '06-oct-94' and
amt > ( select min(amt)
from orders
where odate = '06-oct-94');
75. Find all orders with amounts smaller than any amount for a customer
in San Jose. (Both using ANY and without ANY)
Select onum, amt
from orders
where amt < any ( select amt
from orders, cust
where city = 'San Jose' and
orders.cnum = cust.cnum);
Select onum, amt
from orders
where amt < ( select max(amt)
from orders, cust
where city = 'San Jose' and
orders.cnum = cust.cnum);
76. Select those customers whose ratings are higher than every customer
in Paris. ( Using both ALL and NOT EXISTS).
Select * from cust
where rating > any (select rating from cust
where city = 'Paris');
Select *
from cust a
where not exists ( select b.rating from cust b
where b.city != 'Paris' and
b.rating > a.rating);
77. Select all customers whose ratings are equal to or greater than ANY
of the Seeres.
Select cname, sname
from cust, salespeople
where rating >= any ( select rating
from cust
where snum = (select snum
from salespeople
where sname = 'Serres'))
and sname != 'Serres'
and salespeople.snum(+) = cust.snum;
78. Find all salespeople who have no customers located in their city. (
Both using ANY and ALL)
Select sname
from salespeople
where snum in ( select snum
from cust
where salespeople.city != cust.city and
salespeople.snum = cust.snum);
Select sname
from salespeople
where snum = any ( select snum
from cust
where salespeople.city != cust.city and
salespeople.snum
= cust.snum);
79. Find all orders for amounts greater than any for the customers in
London.
Select onum, amt
from orders
where amt > any ( select amt
from orders, cust
where city = 'London' and
orders.cnum = cust.cnum);
80. Find all salespeople and customers located in london.
Select sname, cname
from cust, salespeople
where cust.city = 'London' and
salespeople.city = 'London' and
cust.snum = salespeople.snum;
81. For every salesperson, dates on which highest and lowest orders were
brought.
Select a.amt, a.odate, b.amt, b.odate
from orders a, orders b
where (a.amt, b.amt) in (select max(amt), min(amt)
from orders
group by snum);
82. List all of the salespeople and indicate those who don't have
customers in their cities as well as those who do have.
Select snum, city, 'Customer Present'
from salespeople a
where exists ( select snum from cust
where a.snum = cust.snum and
a.city = cust.city)
UNION
select snum, city, 'Customer Not Present'
from salespeople a
where exists ( select snum from cust c
where a.snum = c.snum and
a.city != c.city and
c.snum not in ( select snum
from cust
where a.snum = cust.snum and
a.city = cust.city));
83. Append strings to the selected fields, indicating weather or not a
given salesperson was matched to a customer in his city.
Select a.cname, decode(a.city,b.city,'Matched','Not Matched')
from cust a, salespeople b
where a.snum = b.snum;
84. Create a union of two queries that shows the names, cities and
ratings of all customers. Those with a rating of 200 or greater will also
have the words 'High Rating', while the others will have the words 'Low
Rating'.
Select cname, cities, rating, 'Higher Rating'
from cust
where rating >= 200
UNION
Select cname, cities, rating, 'Lower Rating'
from cust
where rating < 200;
85. Write command that produces the name and number of each salesperson
and each customer with more than one current order. Put the result in
alphabetical order.
Select 'Customer Number ' || cnum "Code ",count(*)
from orders
group by cnum
having count(*) > 1
UNION
select 'Salesperson Number '||snum,count(*)
from orders
group by snum
having count(*) > 1;
86. Form a union of three queries. Have the first select the snums of
all salespeople in San Jose, then second the cnums of all customers in San
Jose and the third the onums of all orders on Oct. 3. Retain duplicates
between the last two queries, but eliminates and redundancies between either
of them and the first.
Select 'Customer Number ' || cnum "Code "
from cust
where city = 'San Jose'
UNION
select 'Salesperson Number '||snum
from salespeople
where city = 'San Jose'
UNION ALL
select 'Order Number '|| onum
from Orders
where odate = '03-OCT-94';
87. Produce all the salesperson in London who had at least one customer there.
Select snum, sname
from salespeople
where snum in ( select snum
from cust
where cust.snum = salespeople.snum and
cust.city = 'London')
and city = 'London';
88. Produce all the salesperson in London who did not have customers
there.
Select snum, sname
from salespeople
where snum in ( select snum
from cust
where cust.snum = salespeople.snum and
cust.city = 'London')
and city = 'London';
89. We want to see salespeople matched to their customers without
excluding those salesperson who were not currently assigned to any
customers. (User OUTER join and UNION)
Select sname, cname
from cust, salespeople
where cust.snum(+) = salespeople.snum;
Select sname, cname
from cust, salespeople
where cust.snum = salespeople.snum
UNION
select distinct sname, 'No Customer'
from cust, salespeople
where 0 = (select count(*) from cust
where cust.snum = salespeople.snum);
This Procedure uses Out Parameter
create or replace procedure ppp(b out number) is
begin
b:=100;
dbms_output.put_line(to_char(b));
end;
/
The following pl-sql code uses the above procedure
declare
a number;
b number;
begin
ppp(b);
dbms_output.put_line(to_char(b));
end;
This Example uses Paramererized Cursors
declare
cursor c1(empno emp.empno%type) is select empno from emp where
empno=empno;
a emp.empno%type;
begin
open c1(7984);
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line(to_char(a));
end loop;
close c1;
end;
ed
/
This Procedure uses Out Parameter
create or replace procedure ppp(b out number) is
begin
b:=100;
dbms_output.put_line(to_char(b));
end;
/
The following pl-sql code uses the above procedure
declare
a number;
b number;
begin
ppp(b);
dbms_output.put_line(to_char(b));
end;
This example describes the use of record type data
declare
type tname is record(empno emp.empno%type);
emptable tname;
a emp.empno%type;
cursor c1 is select empno from emp;
begin
open c1;
loop
fetch c1 into emptable;
exit when c1%notfound;
dbms_output.put_line(to_char(emptable.empno));
end loop;
close c1;
end;
/
This is the example for connect by command( for finding hirarcy).
select rpad(rownum,level+2)||ename
from emp
connect by prior empno = mgr
start with empno = 7369
What is SQL*Plus and where does it come from?
SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database.
If you are familiar with other databases, sqlplus is equivalent to "sql" in Ingres, "isql" in Sybase and SQLServer, "db2" in IBM DB2, "psql" in PostgresQL, and "mysql" in MySQL.
SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first Oracle releases up to Oracle v4. The UFI interface was extremely primitive and, in today's terms, anything but user friendly. If a statement was entered incorrectly, UFI issued an error and rolled back the entire transaction (ugggh).
• Back to top of file
• How does one use the SQL*Plus utility?
Start using SQL*Plus by executing the "sqlplus" command-line utility. Valid options are:
userid/password@db -- Connection details
/nolog -- Do not login to Oracle. You will need to do it yourself.
-s or -silent -- start sqlplus in silent mode. Not recommended for beginners!
@myscript -- Start executing script called "myscript.sql"
Look at this example session:
sqlplus /nolog
SQL> connect scott/tiger
SQL> select * from tab;
SQL> disconnect
SQL> exit
Please note that one must prepare the environment before starting sqlplus. Linux/ Unix example:
$ . oraenv
ORACLE_SID = [orcl] ? orcl
$ sqlplus scott/tiger
Windows Example:
Click on "Start" -> "Run" and enter "cmd"
C:> set ORACLE_SID=orcl
C:> sqlplus scott/tiger
• Back to top of file
________________________________________
What commands can be executed from SQL*Plus?
One can enter three kinds of commands from the SQL*Plus command prompt:
1. SQL*Plus commands - SQL*Plus commands are used to set options for SQL*Plus, format reports, edit files, edit the command buffer, and so on. SQL*Plus commands do not interact with the database. These commands do not have to be terminated with a semicolon (;), as is the case with SQL commands. The rest of this page is dedicated to SQL*Plus commands, eg.
SHOW USER
2. SQL commands - for more information see the Oracle SQL FAQ. Eg:
SELECT * FROM TAB;
3. PL/SQL blocks - for more information see the Oracle PLSQL FAQ. Eg:
4. BEGIN
5. DBMS_OUTPUT.PUT_LINE('Hello World!');
6. END;
7. /
• Back to top of file
________________________________________
What are the basic SQL*Plus commands?
The following SQL*Plus commands are available:
ACCEPT Get input from the user
DEFINE Declare a variable (short: DEF)
DESCRIBE Lists the attributes of tables and other objects (short: DESC)
EDIT Places you in an editor so you can edit a SQL command (short: ED)
EXIT or QUIT Disconnect from the database and terminate SQL*Plus
GET Retrieves a SQL file and places it into the SQL buffer
HOST Issue an operating system command (short: !)
LIST Displays the last command executed/ command in the SQL buffer (short: L)
PROMPT Display a text string on the screen. Eg prompt Hello World!!!
RUN List and Run the command stored in the SQL buffer (short: /)
SAVE Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql
SET Modify the SQL*Plus environment eg. SET PAGESIZE 23
SHOW Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
SPOOL Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst
START Run a SQL script file (short: @)
• Back to top of file
________________________________________
What is AFIEDT.BUF?
AFIEDT.BUF is the SQL*Plus default edit save file. When you issue the command "ed" or "edit" without arguments, the last SQL or PL/SQL command will be saved to a file called AFIEDT.BUF and opened in the default editor.
In the prehistoric days when SQL*Plus was called UFI, the file name was "ufiedt.buf", short for UFI editing buffer. When new features were added to UFI, it was the initially named Advanced UFI and the filename was changed to "aufiedt.buf" and then to "afiedt.buf". They presumably needed to keep the name short for compatibility with some of the odd operating systems that Oracle supported in those days. The name "Advanced UFI" was never used officially, as the name was changed to SQL*Plus before this version was released.
You can overwrite the default edit save file's name like this:
SET EDITFILE "afiedt.buf"
• Back to top of file
________________________________________
I'm unable to edit files using ED/EDIT. What is wrong?
One can edit SQL scripts and the command buffer (the last command entered) with the EDIT (or ED) command. However, sometimes one needs select a editor before using this command. Examples:
DEFINE _EDITOR=vi -- Use the Unix vi-editor
DEFINE _EDITOR=notepad -- Use the Notepad on Windows
TIP: Add this command in your login.sql or glogin.sql scripts so it executes every time you start sqlplus.
• Back to top of file
________________________________________
• What is the difference between ? and HELP?
There is no difference. Both "?" and HELP will read the SYSTEM.HELP table (if available) and shows help text on the screen.
To use the help facility, type HELP followed by the command you need to learn more about. For example, to get help on the SELECT statement, type:
HELP SELECT
• Back to top of file
________________________________________
What is the difference
• What is the difference between @ and @@?
The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.
A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH).
@@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script). This is normally used for nested command files.
• Back to top of file
________________________________________
What is the difference between & and &&?
--"&" is used to create a temporary substitution variable and will prompt you for a value every time it is referenced.
--"&&" is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement. Once you have entered a value it will use that value eve
--Precision: datatype number(p,s) vs number
An expr. with NULL always evaluates to NULL, except ||, which treats NULL as ''
Comparison to NULL is always FALSE
--All functions, except COUNT(*), ignores NULL values
--NULLS are displayed last with ORDERED BY in Oracle7
--From Oracle 7.0.16 you can order on col alias or position.
--Logical evaluation: TRUE takes precedence with OR, FALSE with AND, eg "TRUE OR NULL" is TRUE.
--There are a lot of questions about GROUP BY/ HAVING...
--Use WHER
Q. Whats is Row Chaining:
In Circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs , the data for the row is stored in a chain of data block (one or more) reserved for that
segment
Q. What is Mutation Table:
if a procedure that updates a column of table X is called in a database trigger of the same table
Q.whats is an Extent:
An Extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
Q. What is an Integrity Constraint
An integrity constraint is a declarative way to define a business rule for a column of a table.
Q.Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint ?
Ans: No
Q. what is the maximum number of CHECK constraints that can be defined on a column ?
Ans: n No of Check constraints
Q. What is an Optimizer ?
Ans: The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
Q.hat are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
pakcages,procedures,functions,triggers
Q Diff between Integrity Constraints and Database Triggers?
A declarative integrity constraint is a statement about the database that is always true. A constraint applies to existing data in the table and any statement that manipulates the table.
A trigger does not apply to data loaded before the definition of the trigger, therefore, it does not guarantee all data in a table conforms to the rules established by an associated trigger.
A trigger can be used to enforce transitional constraints where as a declarative integrity constraint cannot be used.
Q. what are the properties of the database triggers and form level triggers.
A> General
NAME: Of the Trigger
Sub Class Information:
Comments comments for the trigger
TriggerType PL/SQL (coding)
TriggerText Text(PL/SQL coding)
Fire-Enter-Query-Mode yes
Execution Hierarchy override(overrides the present level's trigger with previous/higher hierarchy level's trigger)
Display in keyboard Help yes (the available keys display at the run time)
'Keyboard Help' Text TESTING (SHIFT+F5 set the user defined keys which can be used at run time)
2. visual attributes and proerty class difference and which one overrides the other first if u place both
A> visual attribute overrides the property class
8. what message u get while attaching a PL/SQL library.
Ans>we get an alert message for removing the path or not
what happens if u say yes to the alert message (remove the path) ?
ofcourse the path is removed if we say yes where does its gonna store the app is stored in default folder forms60 and reports60 in case of d2k 6i which is orain\bin
11. different type of database triggers available
Ans> before insert row/statement Trigger
After insert row/statement Trigger
Before Update row/statement Trigger
After Update row/statement TRiggeer
Before Delelte row/statement Trigger
After Delete row/statement Trigger
14. call_form,new_form,open_form difference
Form Builder provides three separate built-ins capable of invoking forms:
CALL_FORM,NEW_FORM,OPEN_FORM. The standard built-ins used for most multiple-form workis
OPEN_FORM. Its syntax is show here,
open_form('form_name',activate,no_session,no_share_library_data,paramlist_id);
the first option ACTIVATE specfies that the newly opened form will immediately receive focus. the second option.
NO_SESSION,specifies that the newly opened form will share the database connection used by the calling form, as
opposed to opening its own database connection. The third option specifies that any libraries attached to the
newly opened form will not share data with matching libraries attached to other open forms.
the last argument specifies the internalID (or name ) of the parameter list you pass to the opened form.
CALL_FORM:
----------
If your application requires that you open a new form modally so that it is the only form that can receive
focus untill it is closed, use the built in CALL_FORM
syntax
CALL_FORM('form name',hide,no_replace,no_query_only,no_share_library_data,paramlist_id);
HIDE: causes the forms runtime program to make the calling form invisible to the user while the called form is active.
NO_REPLACE: tells the forms runtime program to continue using the default menu module of its own. ]
NO_QUERY_ONLY: specifies that users should be able to insert,update and delete records in the called form, as
oppossed to only being able to fetch them.(except select query)
NEW_FORM:
---------
if u want to open a new form and close the one that was active,use the built in NEW_FORM. Its Syntax
is shown here with each available option included and set to its default value:
syntax
-----
NEW_FORM ('form name',to_savepoint,no_query_only,no_share_library_data);
TO_SAVEPOINT: specifies that changes that have not been committed will be rolled back
to the caling form's last savepoint.
-------------------------------------------------------------------------------------------------------------------------------
Built-In CALL_FORM NEW_FORM OPEN_FORM
------------------------------------------------------------------------------------------------ ----------------------------
Purpose Opens additional form Closes calling form standard builtin used for
as modal windowe and opens new form multiple form form applications. Both the called and calling
standard builtin multiple are visible and can be
form applications access Independently
old->calling form will
not b visible and accessible
unless we close the called form
Parameters HIDE/NO_HIDE,
DO_REPLACE/NO_REPLACE
QUERY_ONLY/NO_QUERY_ONLY,
SHARE_LIBRARY_DATA/NO_SHARE
Calling Form Y N Y
Remains Opened
Calling Form N N/A Y
Accessible while
Called form is
Open?
Allows Seperate N N/A Y
DB Session?
Restricted
Procedure N Y Y
--------------------------------------------------------------------------------------------------------------------------------
16. different type of alerts
A> 3 types caution,stop and note with three buttons yes/no/cancel
ok/cancel, ok only
17. what code u write for restricting the user from deletion operation
Ans> we fire when-button-pressed in which we write code for restricting the user from not allowing him to delete operation
18. about oracle 11i
Ans> 11i is completely used for Oracle Applications
19. about ERP
Ans> EnterPrise Resource Planning
20. how do u restrict the user with different access levels
21. what kind packages u've created
A>. for the validation/numeric/nonnumeric/alphabetsonly/valid email check condition
with different procedures and functions to chek all the above validations.
22. steps in testing
23. what is DLL.
24. how many groups are present for matrix report.
-minimum 3 groups
25. can u see the code PL/SQL attached to the attached library if not ?
26. how many types of windows available.
A.> two types document window and the dialog window which can't be resized or
moved which can be used like alert and prepared like alert by setting the property
of dialogbox for the window object.
29. what happens to the synonym if we delete the data of physical table for which we've created a synonym
A> when we delete the data of physical table on which we've created a synonym the loss of data will b replicated in synonym too. if we drop the physical table on which we have a synonym the structure of synonym is also lost but the
the name of the synonym remains? why?
SYNTAX: CREATE SYNONYM
30. rollback (DCL) command doesnt work of DDL operations
31. why do u we use INSTEAD OF TRIGGERS FOR VIEWS
32. WHAT ARE THE CONDITIONS FOR THE INSTEAD OF TRIGGERS
33. why we wont the KEY-NEXT-ITEM trigger for emp child table of master detail relation
alter table emp
add
(
profit_sharing_indicator number(1,0) null
constraint between_0_and_1
check(profit_sharing_indicator between 0 and 1)
34. The Exception_INIT Pragma
----------------------------------------
Ans> This Exception is caught at the Compile time only and program execution is proceeded
like raise exception.it is a user defined error message which helps in defining userdesired
message where as RAISE_APPLICATION_ERROR(-20000(to -20999), '
A named exception can be associated with a particular error. This gives the ability to trap the error specially, rather than via an OTHERS handler.The association can be done via the EXCEPTION_INIT pragma. The EXCEPTION_INIT Pragma can be used as shown below.
Pragma Exception_INIT (
Declare
PrimException Exception;
Pragma Exception_Init(PrimException,-20000);
Begin
Insert into dept values(20,'SALES','CHICAGO');
IF SQL%ROWCOUNT = 0 THEN
RAISE PrimException;
END IF;
EXCEPTION
when primexception then
dbms_output.put_line ('Primary key Voilation');
END;
where -20000 is Internal oracle number we should give the oracle but not our own number
unlike in raise_application_error where we give our own error between -20000,-20999
remember we dont need to handle the raise_application_error in Exception Handler
36. What are the restrictions for the WHEN-VALIDATE-ITEM
Ans> WHEN-VALIDATE-ITEM-Trigger
-------------------------
-we cannot use any restricted builti-ins
-it doesnt fire in query mode
50 Q> what is DYNAMIC SQL
Ans> using this we can change object with in SQL statements at the run time ,
its a part of PL/SQL no relation with SQL OR SQL+
eg – 1
**impt 55 Q>How do u call a report and forms from stored procedure and how can u pass parameters
Ans we can call the Report and forms from a Stored Procedure using Ref Cursor and Table of Records
but we use REF CURSOR for calling the Report from Stored Procedure rather than Table of records
coz REF CURSOR has Query only but we cant perform any DML operations which is possible with
Table Of Records using that we can perform the DML operations so mostly we go for REF CURSOR
for drawing Reports from STORED PROCEDURES and Table OF RECORDS for Drawing Forms from
Stored procedure
ie if the STORED PROCEDURE is source for data block for form or Report instead of Table as
source we go for REF CURSOR (for report) and TABLE OF RECORDS (for FORMS)
eg -1
56 Q> How to get the stored procedures list on SQL> prompt
Ans>SQL> desc user_source;
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NOT NULL NUMBER
TEXT VARCHAR2(4000)
where type and name are the objects (stored procedure,table,view) name and type
select type,name from user_source;
*** Imp 57 Q > -Query record group, which is based on a SQL select statement.
This provides a functionality similar to a database view,with two added benefits:
The select statement that produces the record group can be dynamically created at runtime,
and you dont need to add a view to the database.
-NonQuery record group, which can also be created and populated dynamically at runtime but has no underlying select
statement.
- Static record group, whose structure and contents are defined at design time and cannot change as the application
runs.
Creating and modifying Record Groups Programmatically
Two types of records groups can be created programmatically : query record groups and nonquery record groups and nonquery record groups. There are two built-ins that create record groups: CREATE_GROUP and CREATE_GROUP_FORM_QUERY.
**-- NOTE: Record Group is like view which stores data and can be attached to LOV or anything normally it is
attached to LOV
Inline- Query
----------
eg-1 CREATE VIEW DEPARTMENT_SALARIES as
(
SELECT dname,
sum(sal) totalsalary
FROM
(
SELECT d.dname,e.sal
FROM dept d , emp e
WHERE d.deptno=e.deptno
)
GROUP BY dname
):
--built in used invoke a report from a form ie calling a Report or Graphics builder ie oracle
product only we use the following
run_product('Department_salaries',synchronous,runtime,filesystem, ' ', null);
--built in used invoke a report from a Report
Using the procedure of Package SRW.RUNREPORT()
58 Q> when we use run_product and run_report
Ans> when we are calling a report from form ie we use this whenever we call a report from any product
run_report is used in the reports only
Q-- In ADD_LIST_PARAMETER() we use y do we use
Ans> TEXT_PARAMETER ARGUMENT IS PASSED is value based
DATA_PARAMETER
OR
RECORD_PARAMETER argument record group we've to pass the record group name
NORMALIZATION:
--------------
Normalization theory is built around the concept of normal forms. Normalization reduces the redundancy
Redundancy is unnecessary repetition of data. It can cause problems with storage and retrieval of data.
During the process of normalization ,dependencies can be identified,which can cause problems during deletion
and updation. Normalization theory is based on the fundamental notion of Functional dependency. Normalization helps in
simplifying the structure of tables.
Suppose for an entity customer if attributes like cusno,name,address are required. Then it can be perceived
that for a particular cusno,only one name and address is possible . Hence the attribute name,address are said to be
functionally dependent on the attribute cusno,there are four levels of normalization.
First Normal Form
---------------
For easier understanding of the first normal form, consider an example . One employee has different
project codes. Hence the projcode is said to be functionally dependent on the attribute Ecode.
Now consider an unnormalized data that is represented
-----------------------------------------------------
Ecode Dept ProjCode Hours
-----------------------------------------------------
101 Accounts 20 90
40 101
41 60
343 Admin 56 50
76 60
898 Sales 20 48
45 67
------------------------------------------------------
A relational model does not permit or support such unnormalized tables. The data table must be present
atleast in first normal form , which appears as shown in the follwing figure
---------------------------------------------------------
Ecode Dept ProjCode Hours
---------------------------------------------------------
101 Accounts 20 90
101 accounts 40 101
101 accounts 41 60
343 admin 56 50
343 admin 76 60
898 sales 20 48
898 sales 45 67
---------------------------------------------------------
In the previous normal form it was noted that there was redundancy of data and
hence the table was decomposed without any loss of information.
The problem encountered here were:
-Insertion: Employee details cannot be recorded until the employer is assigned a project.
-Updation: The employee code and department is repeated. Hence if an employee is transfered
to another department.
These changes must be reflected every where. Any omission will lead to inconsistencies.
-Deletion: If an employee his project, his record will be deleted and details pertaining to
that employee lost.
From the table given below easier understanding is enabled.
Ecode Dept Ecode ProjCode Hours
101 Accounts 101 20 90
343 admin 101 40 101
898 sales 101 41 60
343 56 56
343 78 92
898 20 48
898 45 67
It should be noted that for a table to be in the second form it should also b in the first normal form
and every attribute in the record should functionally depend on the primary key.
Third Normal Form
---------------
A table is said to be in the third normal form if it is in second normal form
and every nonkey attribute is functionally dependent on just the primary key.
The primay key here is Ecode. The attribute dept-code is dependent
on dept. There is an indirect dependence on the primary key. Which has to be noticed.
It is illustrated in following table.
Ecode Dept Dept-code
101 Finance 909
303 sales 906
400 sales 906
500 admin 908
600 systems 901
777 finance 909
Even here it was noted that there were problems during insertion,updation and deletion.
The relation is thus reduced as shown
Ecode Dept Dept Dept-Code
101 Finance systems 900
303 sales sales 906
400 sales admin 908
500 admin finance 909
600 systems
777 finance
Each non-key attribute is wholly dependent only on the primary key. Even the third normal
form did not satisfy the needs.
Hence, a new form called the Boyce-Codd Normal Form was introduced.
The third normal form was not satisfactory for relations that had multiple candidate keys
Hence Boyce and codd introduced another form. Consider table
Cusno Cusname
100 Gajraj
121 Sharma
280 sinha
Here the candidate keys are cuscode, cusname. The attribute cuscode and cusname are unique for each row. Consider that another table that has few other details, as shown in table
-----------------------------------------------------------------------------------------------------------
Cuscode cusname itemcode qty
----------------------------------------------------------------------------------------------------------
100 GAJRAJ 19 4
100 GAJRAJ 16 2
121 SHARMA 29 6
280 SINHA 33 1
--------------------------------------------------------------------------------------------------------------
The attributes cuscode, and cusname of the above two tables can be used to link these tables.
These act as foreign key in the second table. These tables can be reduced in order by minimize redundancy.
------------------------------------------------ --------------------------------
cuscode itemcode qty cuscode cusname
------------------------------------------------ ---------------------------------
100 19 4 100 GAJRAJ
100 16 2 121 SHARMA
121 29 6 280 SINHA
280 33 1
--------------------------------------------------- ---------------------------------------
Q>
ROUND function rounds the value to the nearest decimal point after how many decimal places u want to
round it off
ROUND(DECIMALVALUE,PRECISION) from dual;
Eg:
1. ROUND(123.323,2)=123.32
note: since 2 digits after decimal placed are asked we place two digits after decimal point
and the third digit after the two digits(3 and 2 is 3) since the digit(third 3) after two digits
(here 3 and 2) < 5 no incrementation
2. SELECT ROUND(456.968,2) FROM DUAL =456.97
since 2 digits after COMMA ie after two should be placed we place two digits after decimal point
and the third digit after the two digits(9 and 6 is 8) since the digit(third 8) after two digits
(here 9 and 6) > 5 incrementation
ie third decimal digit >5
3. SELECT ROUND(456.968,2) FROM DUAL =456.968
*4. SELECT ROUND(456.968,1) FROM DUAL =457
*5. select round(456.12,1) from dual;= 456.1
since second digit 2 <5 therefore no incrementation
6. select round(456.67,1) from dual;= 456.7
since second digit 7>5 therefore place incremented value after decimal point
7.ROUND(45,1)=45
8.SELECT ROUND(45,-1) FROM DUAL=50
9.ROUND(-392,-1)=-390
10.ROUND(-1.12,0)=-1
11.ROUND(134,-1)=130
12.ROUND(-134,-1)=-130
Note: Since -1 is there after comma we replace that many right most digits with 0
13.ROUND(134,-2)=100
remember the the no of digits after comma should b less than or equal to the total no of digits
before comma ie -2 < 1,3,4 ie no of digits in 134 is 3
14.ROUND(134.345,-1)=130
Note: even though there is a decimal point if we have -ve value after comma immaterial to the
decimal point the right most value will be replaced with 0
15.ROUND(134.345,-2)=100
16.ROUND(134.345,-3)=000=0
Q> Trunc() function will jest truncates the value ie cut the values to the specified places
not nearer like round
Eg:
1.select trunc(456.968,2) from dual=456.96
2. select trunc(456.968,-2) from dual=400
2. select trunc(456.968,-2) from dual=400
decode(): interpreting column values based on condition
Eg:
ROLL NAME MARKS
1 A 0
2 B 65
3 C 78
0-FAIL
65-JUSTPASS
78-PASS
IF marks is 0 then
display fail
elsif marks is 40 then
display justpass
else
display pass
or in one SQL statement we write this as
select marks decode (marks,0,'fail',40,'justpass','pass')
NOTE: there are no fixed no of parameters for decode() function
** vimp> syntax DECODE(COLUMN,VALUE,TRUE,FALSE) each value returns either true or false what should be displayed if its true or falseq
min 3 parameters are required column name,value,true/false
we can't apply or in decode
Eg:
1. IF (deptno=10)
IF(desig='MRKTING')
display 'marketing'
ELSIF desig='PROD'
display 'production'
ELSE
display 'NONE OF THE ABOVE'
END IF
ELSIF(deptno=20)
IF(DESIG='MARKETING')
display 'MARKETING'
ENDIF
END IF
Ans> select decode(deptno,10,decode(desig,'MRKTING','marketing','PROD','production','NONE OF THE ABOVE'))
for every if
for elsif we can directly write the condtion and statement (displaying) like above
'PROD' ' production',... if we have any but here next we have ELSE part so write directly
else part after this
here value1 ie IF(desig='MRKTING') is true implies display 'marketing'
else if value1 is false ie not true then if value1 ie ELSIF desig='PROD' is true
implies display 'production' if value2 is also false ie all the values value1,value2.. value n are false
then comes to the else part ie ELSE then display 'NONE OF THE ABOVE'
NOTE: there is no restriction or parameters for decode() u can have as many conditions and values
as u desire syntax DECODE(COLUMN,VALUE,TRUE,FALSE)
Eg; 2. IF (deptno=10)
IF(desig='MRKTING')
display 'marketing'
ENDIF
select decode(deptno,10,decode(desig,'MRKTING','marketing')
Q> How to Delete Duplicate rows in a table
Ans> delete from t1 where rowid < ( select max(rowid) from t2
where t1.roll=t2.roll
and t1.name=t2.name
and t1.marks=t2.marks
)
Eg:
create table t1
(
sno number(8),
sname varchar2(10)
);
select * from t1;
SNO SNAME
----- ----------
10 yogi
10 yogi
10 yogi
10 yogi
Q How to Delete Duplicate Rows in a Table
DELETE from t1 where rowid < (select max(rowid) FROM t1
where t1.sno=t1.sno and t1.sname=t1.sname)
/
Note: AVG,SUM,VARIANCE,STDDEV functions can be used only with numeric data types.
imp** SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
Ans>TO_DATE('01-JAN-00')-TO_DATE('01-DEC-99')
-----------------------------------------
31
Q--SELECT TO_DATE('01-jan-00') + TO_DATE('01-dec-99')
Ans > Adding to Dates is Not allowed *
ERROR at line 1:
ORA-00975: date + date not allowed
Dear Yugander K,
Bobby (bobby_007_21@hotmail.com) wants you to read
the following article .
Normalizing an Existing Database
Bruce Szabo
03/06/2001
This article can be found online at the following location:
http://www.databasejournal.com/features/msaccess/article.php/10895_1442001_2
Denormalization
In the design process it became clear the database could be further
normalized. Some areas for normalization were the addresses, policies,
and options. Also, denormalization could have occured in other areas.
The following sections will cover areas where these two processes could
have occurred.
Further Normalization
Under the current database design it is possible to have the same
address, contact, email, web and phone information for each role in a
given firm. This would cause a lot of duplicate data. Knowing in most
cases, however that not all this information is repeated for each role
lead to the conclusion the database could be denormalized. For example,
There may be a returns department address but there is no contact for
the returns department. There could, however, be a customer service
agent or even multiple agents. For the agents it is possible only a
toll
free phone number will be kept. In this case a role is created for
agent1 or multiple agents and their name is kept in the contact table
while the toll free phone number is stored in the number table. In this
case address information could be added to the database but it may not
be required by the process the database supports.
The policies could also have all been stored in one table. Each policy
could have had a descriptive lookup type. It seemed as though it would
be easier to have three policy tables for programming purposes rather
than one table containing all the policies. When reporting information
the data will be able to be kept together by adding or removing tables
from the query. In the event all policies are needed a union query will
allow the tables to be combined.
The options tables could potentially have been combined to form a more
generic classification similar to how policies may have been further
normalized. It probably made less sense to further normalize the
options
as the data seems to fall nicely into the current tables.
Further Denormalization
The address, contact, email, web and phone information could have been
further denormalized. It would have been possble to combine these
tables
into one table. One of the major problems with this denormalization
would have been phone numbers. If another phone number type was added
it
would have required adding a field to the table. Combining the other
four tables may have presented less severe problems but would have
resulted in a number of blank fields for each record.
The policies could have been added to the respective options table but
this again would have meant modifing table structure to accomodate new
policies. Trying to avoid modifing table structure while accomodating
future data lead to the current table design.
Conclusions
One finds when normalizing and denormalizing there are pros and cons to
both approaches. While programming it becomes evident why many
databases
are denormalized. In some cases the original programming for a
denormalized database is easier. Developing programs that make use of
normalized data, however, leads to code that is reusable in many more
situations. This fact alone is enough to fight through the
normalization
and denormalization battle.
For another perspective, check out David's comments on the
normalization
of this database, found on the next page of this article.
DYNAMIC SQL
DBMS_SQL
PL/SQL lets you include Data Manipulation Language (DML) statements (SELECT,INSERT,UPDATE,DELETE). However, PL/SQL does not let you include DDL commands as statements. In other words ,you cannot have a PL/SQL block like this
BEGIN
CREATE TABLE IMPOSSIBLE_TO_DO (NOWAY NUMBER(3));
END;
However although this code would produce an error message, there are ways in which
you can execute DDL statements from within PL/SQL. These techniques offer an additional
advantage:
**The ability to dynamically define at run time any SQL statement(both DDL and DML) so
--------------------------------------------------------------------------------------
that the statement that executes can be created while the program executes, using parameters,
---------------------------------------------------------------------------------------------
variables and string functions such as concatenation to build unique statements that are
------------------------------------------------------------------------------------------
unknown at compilation time and can be defined based upon user input and other information
---------------------------------------------------------------------------------------------
available at run time. There are two primary ways to achieve this effect.
--------------------------------------------------------------------------
- The DBMS_SQL package is used to write Dynamic SQL in stored procedures and to parse DDL statements. Some of the procedures and functions of the package include
- OPEN_CURSOR
- PARSE
- BIND_VARIABLE
- FETCH_ROWS
- CLOSE_ROWS
Eg CREATE OR REPLCE PROCEDURE delete_all_rows
(p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)
IS
Cursor_name INTEGER;
BEGIN
Cursor_name:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, ‘DELETE FROM ‘ || p_tab_name, DBMS_SQL.NATIVE);
P_rows_del:=DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
Use Dynamic SQL to delete rows
VARIABLE deleted NUMBER
EXECUTE delete_all_rows (‘emp’, :deleted);
PRINT deleted
- A relatively new feature known as NATIVE DYNAMIC SQL
Using the EXECUTE IMMEDIATE statement dynamic SQL with better perfomance
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable,[,define_variable].. | record}]
[ USING [IN | OUT| IN OUT ] bind_argument];
- INTO is used for single row queries and specifies the variables or records into which column values are retrieved
- USING is used to hold all bind arguments.The default parameter mode is IN.
CREATE PROCEDURE del_rows
(p_table_name IN VARCHAR2,
p_rows_deld OUT NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE ‘delete from ‘ || p_table_name;
P_row_deld:= SQL%ROWCOUNT;
END;
VARIABLE deleted NUMBER
EXECUTE del_rows (‘test_employees’, :deleted)
PRINT deleted
Or
Being
Dbms_ouput.put_line(deleted)
END;
We will address DBMS_SQL first and then look at native dynamic SQL
The DBMS_SQL package consists of a few dozen procedures and functions as followes
--------------------------------------------------------------------------------------------
Procedure(P)
or Name Overloaded
Function (F)
--------------------------------------------------------------------------------------------
P BIND_ARRAY YES
P BIND_VARIABLE YES
P BIND_VARIABLE_CHAR YES
P CLOSE_CURSOR NO
P COLUMN_VALUE YES
P COLUMN_VALUE_CHAR YES
P COLUMN_VALUE_LONG NO
P COLUMN_VALUE_ROWID YES
P DEFINE_ARRAY YES
P DEFINE_COLUMN N
P DESCRIBE_COLUMNS NO
F EXECUTE NO
F EXECUTE_AND_FETCH NO
F FETCH_ROWS NO
F IS_OPEN NO
F OPEN_CURSOR NO
F PARSE NO
P VARIABLE_VALUE YES
------------------------------------------------------------------------------------------------
Obviously,DBMS_SQL is an involved package, but you dont need all of the procedures all of the
time. Generally , you use one set of procedure and function
Eg DBMS_SQL to execute a SELECT statement
PROCEDURE DO_QUERY ( pTable IN VARCHAR2,
pColumnA IN VARCHAR2,
pColumnB IN VARCHAR2
)
IS
VCursor NUMBER;
VSQLStatement VARCHAR2(400);
VResult NUMBER;
VColumnA VARCHAR2(30);
VColumnB VARCHAR2(30);
BEGIN
--Build and parse the query
vSQLStatement:= 'SELECT' || pColumnA || ',' || pColumnB || 'FROM' || pTable;
-- similar to explicit cursor declaration
-- declare cursor c1 as select pcolumna,pcolumnb from pTable
vCursor:= DBMS_SQL.OPEN_CURSOR
--like open c1
DBMS_SQL.PARSE(vCursor,vSQLStatement, DBMS_SQL.V7);
-- set up the columns and execute the query
DBMS_SQL.DEFINE_COLUMN(vCursor,1,vColumnA, 30);
-- defining the data type for the columns dynamically while executing this program
DBMS_SQL.DEFINE_COLUMN(vCursor,2,vColumnB, 30);
vResult:=DBMS_SQL.EXECUTE(vCursor);
--Fetch the rows and print the results
DBMS_OUTPUT.PUT_LINE('The Results');
DBMS_OUTPUT.PUT_LINE('------------');
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(vCursor) = 0;
-- EXIT when c1%notfound =0 or c1%rowcount=0
DBMS_SQL.COLUMN_VALUE(vCursor, 1, vColumnA);
DBMS_SQL.COLUMN_VALUE(vCursor, 2, vColumnB);
DBMS_OUTPUT.PUT_LINE(vColumnA || ' ' || vColumnB);
END LOOP;
--
DBMS_SQL.CLOSE_CURSOR(vCursor);
END DO_QUERY;
The above proecude accepts three incoming parameters table,columnA,ColumnB and builds
the string vSQLStatement as a SELECT statement for that table. It uses the DBMS_SQL.PARSE
procedure calls to associate query's column with locally declared variables.
DBMS_SQL.EXECUTE actually executes the query and the DBMS_SQL.FETCH_ROWS returns the a single
row of output its called inside loop generally , DBMS_SQL.COLUMN_VALUE to grab individual values, transfering each into appropriate local variables.
DBMS_SQL.DEFINE_COLUMN procedure is to associate local varaible in your PL/SQL block with columns
in a parsed but as of yet unexecuted- query. must be called once for each column
output
EXEC DO_QUERY('PORTS','PORT_ID','PORT_NAME');
SELECT PORT_ID,PORT_ID,PORT_NAME
The results
----------
1. miami
2. nassau
The following is an example of how to use DBMS_SQL to issue an UPDATE statement
PROCEDURE DO_UPDATE IS
vCursor NUMBER;
vSQLStatment varchar2(400);
vResult number;
BEGIN
--Build and parse the statement
vSQLStatement:= 'UPDATE PORTS' ||
'SET PORT_NAME= 'paradise' ||
'WHERE PORT_ID= 2';
vCursor:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(vCursor,vSQLStatement,DBMS_SQL.V7);
--Execute the query
vResult:= DBMS_SQL.EXECUTE(vCursor);
DBMS_OUTPUT.PUT_LINE(vResult || 'rows were affected');
DBMS_SQL.CLOSE_CURSOR(vCursor);
END DO_UPDATE;
DBMS_SQL.V7 which is fine for oracle 8i and higher applications
.
GENERAL FAQ’S
Q Diff bet function and procedure
: Functions return a single variable by value whereas procedures do not return any variable by value.
Rather they return multiple variables by passing variables by reference through their OUT parameter.
Q What is IN-VIEW in oracle ??
Ans> Subquery in from clause is IN-VIEW OR IN LINE VIEW
Q> : Can 2 functions have same name & input parameters but differ only by return datatype
Ans> No
Q> Can you pass parameters in packages ?
Ans> yes
Q >What are the constructs of a procedure, function or a package’
Ans> variables and constants,cursors,exceptions
Q. : Why Create or Replace and not Drop and recreate procedures ?
Ans> So that Grants are not dropped.
Q> : What are the various types of database triggers ?
Ans>There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
(3*2*2=12)
Q> What is the advantage of a stored procedure over a database trigger ?
Ans> We have control over the firing of a stored procedure but we have no control over the firing of a trigger
Q. What is the maximum no. of statements that can be specified in a trigger statement ?
Ans> one
Q. Can views be specified in a trigger statement ?
Ans> no
Q. What are the values of :new and old in Insert/Delete/Update Triggers ?
A. INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
Q. What are cascading triggers?
Ans> When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.
Q. OR TRANSFERABLE VIEWS in oracle
Ans>
Q. What is the maximum no of cascading triggers at a time?
Ans > 32
Q. What are mutating trigger s
Ans> A trigger giving a SELECT on the table on which the trigger is written
Q. What are constraining triggers ?
Ans> A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table
Q. An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
Ans> no
Q. Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
Q. Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING'
(Where x is a record of Number(4) and Char(15))
Q. Which symbol preceeds the path to the table in the remote database
Ans> @
Q. Are views automatically updated when base tables are updated ?
Ans> If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
Q. Is Sysdate a system variable or a system function?
Ans> System Function
Q. Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
Ans> 3
Q. : What are the disadvantages of SQL
Ans> Disadvantages of SQL are :
• Cannot drop a field
• Cannot rename a field
• Cannot manage memory
• Procedural Language option not provided
• Index on view or index on index not provided
• View updation problem
Q. What is the result of the following SQL :
Select 1 from dual
UNION
Select 'A' from dual;
Ans Error
Q. Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Ans> ANS :Yes, database trigger would fire.
Q. Can you alter synonym of view or view ?
Ans yes
Q. Can you create index on views
Ans no
Q . Diff bet synonym and view
Ans Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
Q. Alias is temporary and used with one query. Synonym is permanent and not used as
Ans>
Q. How to Create dynamic views
Ans> : SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.ROWNUM,4) = 0;
Q : Can one retrieve only rows X to Y from a table?
]. SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101
) WHERE RN between 91 and 100 ;
SELECT *
FROM tableX
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 7
MINUS
SELECT rowid FROM tableX
WHERE rownum < 5);
--------------------------------------------------------------------------------------------------------------------------------
PART II
GENERAL INTERVIEW QUESTIONS
1.What are the various types of Exceptions ?
User defined and Predefined Exceptions.
2.Can we define exceptions twice in same block ?
No.
3.What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not
return any variable by value.
Rather they return multiple variables by passing variables by reference
through their OUT parameter.
4.Can you have two functions with the same name in a PL/SQL block ?
Yes.
5.Can you have two stored functions with the same name ?
Yes.
6.Can you call a stored function in the constraint of a table ?
No.
7.What are the various types of parameter modes in a procedure ?
IN , OUT AND INOUT.
8.What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending
upon the no. of parameters or the data type of the parameters passed to
it.
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by
return datatype
No.
11.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are :
variables and constants
cursors
exceptions
12.Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.
13.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
14.What are the parts of a database trigger ?
The parts of a trigger are:
A triggering event or statement
A trigger restriction
A trigger action
15.What are the various types of database triggers ?
There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
(3*2*2=12)
16.What is the advantage of a stored procedure over a database trigger
?
We have control over the firing of a stored procedure but we have no
control over the firing of a trigger.
17.What is the maximum no. of statements that can be specified in a
trigger statement ?
One.
18.Can views be specified in a trigger statement ?
No
19.What are the values of :new and :old in Insert/Delete/Update
Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
20.What are cascading triggers? What is the maximum no of cascading
triggers at a time?
When a statement in a trigger body causes another trigger to be fired,
the triggers are said to be cascading. Max = 32.
21.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.
22.What are constraining triggers ?
A trigger giving an Insert/Update on a table having referential
integrity constraint on the triggering table.
23.Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
24.Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
25.Can you increase the size of datafiles ? How ?
No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause ----- Confirm !!).
26.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files,
etc.
27.What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical
Oracle structures e.g. Tables, Tablespaces, datafiles, etc
28.What are the advantages of clusters ?
Access time reduced for joins.
29.What are the disadvantages of clusters ?
The time for Insert increases.
30.Can Long/Long RAW be clustered ?
No.
31.Can null keys be entered in cluster index, normal index ?
Yes.
32.Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference
some other column of the same table and thus enforce self referential
integrity.
33.What are the min. extents allocated to a rollback extent ?
Two
34.What are the states of a rollback segment ? What is the difference
between partly available and needs recovery ?
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
35.What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.
36.An insert statement followed by a create table statement followed by
rollback ? Will the rows be inserted ?
No.
37.Can you define multiple savepoints ?
Yes.
38.Can you Rollback to any savepoint ?
Yes.
40.What is the maximum no. of columns a table can have ?
254.
41.What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a
variable. The && operator means that the value of this variable should be
the same as inputted by the user previously for this same variable.
If a transaction is very large, and the rollback segment is not able to
hold the rollback information, then will the transaction span across
different rollback segments or will it terminate ?
It will terminate (Please check ).
42.Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A
cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
43.What are the various types of RollBack Segments ?
Public Available to all instances
Private Available to specific instance
44.Can you use %RowCount as a parameter to a cursor ?
Yes
45.Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING'
(Where x is a record of Number(4) and Char(15))
Yes
46.Is the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
Yes
47.Is this for loop allowed :
For x in &Start..&End Loop
Yes
48.How many rows will the following SQL return :
Select * from emp Where rownum < 10;
9 rows
49.How many rows will the following SQL return :
Select * from emp Where rownum = 10;
No rows
50.Which symbol preceeds the path to the table in the remote database ?
@
51.Are views automatically updated when base tables are updated ?
Yes
52.Can a trigger written for a view ?
No
53.If all the values from a cursor have been fetched and another fetch
is issued, the output will be : error, last record or first record ?
Last Record
54.A table has the following data : [5, Null, 10]. What will the
average function return ?
7.5
55.Is Sysdate a system variable or a system function?
System Function
56.Consider a sequence whose currval is 1 and gets incremented by 1 by
using the nextval reference we get the next number 2. Suppose at this
point we issue an rollback and again issue a nextval. What will the
output be ?
3
56.Definition of relational DataBase by Dr. Codd (IBM)?
A Relational Database is a database where all data visible to the user
is organized strictly as tables of data values and where all database
operations work on these tables.
57.What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server configuration)
the database manager creates a separate process for each database user.
But in MTA the database manager can assign multiple users (multiple
user processes) to a single dispatcher (server process), a controlling
process that queues request for work thus reducing the databases memory
requirement and resources.
58.Which are initial RDBMS, Hierarchical & N/w database ?
RDBMS - R system
Hierarchical - IMS
N/W - DBTG
59.Difference between Oracle 6 and Oracle 7
ORACLE 7 ORACLE 6
Cost based optimizer • Rule based optimizer
Shared SQL Area • SQL area allocated for each user
Multi Threaded Server • Single Threaded Server
Hash Clusters • Only B-Tree indexing
Roll back Size Adjustment • No provision
Truncate command • No provision
Database Integrity
Constraints • Provision at Application Level
Stored procedures, functions
packages & triggers • No provision
Resource profile limit. It
prevents user from running
away with system resources • No provision
Distributed Database • Distributed Query
Table replication & snapshots• No provision
Client/Server Tech. • No provision
60.What is Functional Dependency
Given a relation R, attribute Y of R is functionally dependent on
attribute X of R if and only if each X-value has associated with it
precisely one -Y value in R
61.What is Auditing ?
The database has the ability to audit all actions that take place
within it.
a) Login attempts, b) Object Accesss, c) Database Action
Result of Greatest(1,NULL) or Least(1,NULL)
NULL
62.While designing in client/server what are the 2 imp. things to be
considered ?
Network Overhead (traffic), Speed and Load of client server
63.What are the disadvantages of SQL ?
Disadvantages of SQL are :
• Cannot drop a field
• Cannot rename a field
• Cannot manage memory
• Procedural Language option not provided
• Index on view or index on index not provided
• View updation problem
64.When to create indexes ?
To be created when table is queried for less than 2% or 4% to 25% of
the table rows.
65.How can you avoid indexes ?
TO make index access path unavailable
• Use FULL hint to optimizer for full table scan
• Use INDEX or AND-EQUAL hint to optimizer to use one index or set to
indexes instead of another.
• Use an expression in the Where Clause of the SQL.
66.What is the result of the following SQL :
Select 1 from dual
UNION
Select 'A' from dual;
Error
67.Can database trigger written on synonym of a table and if it can be
then what would be the effect if original table is accessed.
Yes, database trigger would fire.
68.Can you alter synonym of view or view ?
No
69.Can you create index on view
No.
70.What is the difference between a view and a synonym ?
Synonym is just a second name of table used for multiple link of
database. View can be created with many tables, and with virtual columns and
with conditions. But synonym can be on view.
71.What is the difference between alias and synonym ?
Alias is temporary and used with one query. Synonym is permanent and
not used as alias.
72.What is the effect of synonym and table name used in same Select
statement ?
Valid
73.What's the length of SQL integer ?
32 bit length
74.What is the difference between foreign key and reference key ?
Foreign key is the key i.e. attribute which refers to another table
primary key.
Reference key is the primary key of table referred by another table.
75.Can dual table be deleted, dropped or altered or updated or inserted
?
Yes
76.If content of dual is updated to some value computation takes place
or not ?
Yes
77.If any other table same as dual is created would it act similar to
dual?
Yes
78.For which relational operators in where clause, index is not used ?
<> , like '% ...' is NOT functions, field +constant, field || ''
79.Assume that there are multiple databases running on one machine. How
can you switch from one to another ?
Changing the ORACLE_SID
80.What are the advantages of Oracle ?
Portability : Oracle is ported to more platforms than any of its
competitors, running on more than 100 hardware platforms and 20 networking
protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends
more on R & D than most of its competitors earn in total revenue. This
market clout means that you are unlikely to be left in the lurch by
Oracle and there are always lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for
on-line backup and recovery and good software fault tolerence to disk
failure. You can also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is
quite good, even with large databases. Oracle can manage > 100GB
databases.
Multiple database support : Oracle has a superior ability to manage
multiple databases within the same transaction using a two-phase commit
protocol.
81.What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it.
Therefore, you must declare a subprogram before calling it. This declaration
at the start of a subprogram is called forward declaration. A forward
declaration consists of a subprogram specification terminated by a
semicolon.
82.What are actual and formal parameters ?
Actual Parameters : Subprograms pass information using parameters. The
variables or expressions referenced in the parameter list of a
subprogram call are actual parameters. For example, the following procedure
call lists two actual parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);
Formal Parameters : The variables declared in a subprogram
specification and referenced in the subprogram body are formal parameters. For
example, the following procedure declares two formal parameters named
emp_id and increase:
Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
current_salary REAL;
83.What are the types of Notation ?
Position, Named, Mixed and Restrictions.
84.What all important parameters of the init.ora are supposed to be
increased if you want to increase the SGA size ?
In our case, db_block_buffers was changed from 60 to 1000 (std values
are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std
values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std
values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096
(4K) {at the time of database creation}.
The initial SGA was around 4MB when the server RAM was 32MB and The new
SGA was around 13MB when the server RAM was increased to 128MB.
85.If I have an execute privilege on a procedure in another users
schema, can I execute his procedure even though I do not have privileges on
the tables within the procedure ?
Yes
86.What are various types of joins ?
Equijoins, Non-equijoins, self join, outer join
87.What is a package cursor ?
A package cursor is a cursor which you declare in the package
specification without an SQL statement. The SQL statement for the cursor is
attached dynamically at runtime from calling procedures.
88.If you insert a row in a table, then create another table and then
say Rollback. In this case will the row be inserted ?
Yes. Because Create table is a DDL which commits automatically as soon
as it is executed. The DDL commits the transaction even if the create
statement fails internally (eg table already exists error) and not
syntactically.
89.What are the various types of queries ?
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries
90.What is a transaction ?
A transaction is a set of SQL statements between any two COMMIT and
ROLLBACK statements.
91.What is implicit cursor and how is it used by Oracle ?
An implicit cursor is a cursor which is internally created by Oracle.
It is created by Oracle for each individual SQL.
92.Which of the following is not a schema object : Indexes, tables,
public synonyms, triggers and packages ?
Public synonyms
93.What is the difference between a view and a snapshot ?
94.What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language
includes object oriented programming techniques such as encapsulation,
function overloading, information hiding (all but inheritance), and so,
brings state-of-the-art programming to the Oracle database server and a
variety of Oracle tools.
95.Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus,
all your PL/SQL are send directly to the database engine for execution.
This makes it much more efficient as SQL statements are not stripped
off and send to the database individually.
96.Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K
and the maximum code size is 100K. You can run the following select
statement to query the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'
97.Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write
files. The directory you intend writing to has to be in your INIT.ORA
file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of
writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %s\n', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
98.How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for
PL/SQL programs to protect the source code. This is done via a
standalone utility that transforms the PL/SQL source code into portable binary
object code (somewhat larger than the original). This way you can
distribute software without having to worry about exposing your proprietary
algorithms and methods. SQL*Plus and SQL*DBA will still understand and
know how to execute such scripts. Just be careful, there is no "decode"
command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.yyy
99.Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a
procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic
SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL
AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
1. What is Referential Integrity rule?
Differentiate between
2. Delete & Truncate command.
3. Implicit Cursor & Explicit Cursor.
4. Ref. key & Foreign key.
5. Where & Having Clause.
6. What are various kinds of Integrity Constraints in Oracle?
7. What are various kind of joins?
8. What is Raise_Application_Error?
9. What are various kinds of exceptions in Oracle?
10. Normal Forms
Oracle Notes :
Oracle 8i -
It is a DB of internet computing , It changes the of information
managed and accessed to meet the demandof internet age.
-- Significant new feature for OLTP(Online trans Processing) and data
ware housing Appl.
-- To mange all types of datain web site.
-- iFS Internet file Syatem
-- interMedia to manage and access multimedia data,audio,video
-- Support to java(to install JVM on server)
-- Security enhancement(authentication and authorization,centralizing
user management)
Oracle 8(ORDBMS) -
-Parrallel enhancement ,faster connection
-Table partitioning , Connection inc to 30000 ,Table column upto 1000
-DB size inc from few tera byte to 10 tera. , Data file inc 65,533
-Support MTS,provides LOB
Oracle Start -
1. Oracle instance start -Allocates SGA and start BAckground processes.
2. Mount Oracle DB-Method of Associating DB with previous started
instance
3.Opening DB-To make available.
Normalization -
It's a technique thr. which we can design the DB.
During normalization dependancies can be identified which can cause pbs
during deletion & updation .It is used in simplifing the structure of
table.
1NF-Unnorma;ised data transfer to normalised form.
2NF-Functional dependancies can be find out & decompose the table
without loss of data.
3NF-Transist dependancies ,Every non key attrbute is functionally
dependant on just PK.
4NF(BCNF)-The relation which has multiple candidate keys ,then we have
to go for BCNF.
Denormalization-
At the same time when information is required from more than one table
at faster rate then it is wiser to add some sort of dependancies.
Rooling Forward -To reapply to Data file to all changes that are
recorded in Redo log file due to which datafile contains commited &
uncommited dat.
Forward Declaration-To declare variable and procedures before using it.
2- Tier Arch. Disadv-When Business Rule changes.
PL/SQL Record-To represent more than one row at time.
PL/SQL Table -To define single variable comprises several data element.
To delete define one more empty table and assign it.
Tablespace -
Profile-To control system resources ,memory ,diskspace and CPU time.
We can find rows effected by %rowcount.
Data Binding-Dividing the cursor in appl as per select stamt.
Trancate -Faster than delete ,doesn't fire ny DB trigger ,Allocate
space ,No roolback.
Defered Integrity constraints-When we refere PK in the same table where
we defined .
Cascading trigger-
Temporary Table-Delete operation table.
Log Table-to store information abt error.
Coordinity-
Err Trap -To trap error use SQLERRM,SQLCODE
Modularity-PL/SQL allows to create program module to improve software
reliability and to hide complexity
Positional and Named Notation -
The actual arguments are associaed with formal arguments by position
k/s Positional Notation.It's commonly used.
A Trigger doesn't accept argument & have same name as table or
procedure as it exist in seperate namespace.
How we ref FK in Sql -Join Condition.
Security/Lock-
Shared/exclusive -When 2 transaction wants to read/write from db at the
same time.
Dead- 1trans updates emp and dep
2 trans update dep and emp
TO add a not null column to a table which has already some records -
Alter table a
Add(b number default 1 not null)
Sequence- Start with,increment by,Cache/No cache,Order/No order,Max,Min
ER Dia.- Entity Relation Dia.
Set Transaction -To set a current transaction online offline
Oracle err-
ORA-06500 stiorage err
ORA-00923 from keyword not found
ORA-06501program err
ORA-00904 Invalid Col
ORA-00001Uk violated.
Dynamic Sql -Which uses late binding
File I/O-To read and write dat to and from text file thr. Oracle
procedure.
Joins-Equi,Non EQui,Self,inner joins,outer joins
Index-16 col per table.
Parsing-Syntax checking.
Optimization-Use of index (HINT)
Corelated Subquery -Which fires only once/ per row for entire stmt.
Simple Query--Which fires everytime for entire stmt
Packages- Encapsulation,Overloading,improve performance as I/O reduces.
PL/SQL Signature Method- To determine when remote dependant object get
invalid.
Object Previledge - On a particular object- I/U/D/Exec
System Previledge -Entire collection object -C/A/D
SGA Comprises -Data Buffer, Redo Log Buffer,Shared pool Buffer.
Shared Pool - Req to process unique SQL stmt submitted to DB.
It contains information such as parse tree and execusion plan .
PGA -A memry buffer that contains data and control information for a
server process.
Dedicated server - Handles request. for single user.
Multithresd Server-Handles request. for multiple user.
Background process -DBWR,LGWR,PMON,SMON,CKPT
DBWR-Writes modified data blocks from DB buffer to data file.
LGWR-
CKPT-Responsible to check DBWR and update control file to indicate most
recent CKPT.
SMON-Instance recovery at start up,Clean Temporary. Segment.
PMON-Responsible for process recovery and user process fails,Cleaning
up cache ,freeing resources which was using process.
Segment-Data/Index/Rollback/Temp
Data Dictionary -V$SESSION, information abt integrity constraints,space
allocated for schema object.
USER_TAB_COLUMNS gives you a list of tables as per Column.
EOD Procedure-
Mutating/Constraining Err/Table
Diff of where and group by
Connect,Allocate.Analyse Command.
Queries--
1. 3rd Max
select distinct sal
from emp a
where 3=(select count(distinct sal)
from emp b
where a.sal=2. Delete Duplicate rows
Delete Emp
where rowid not in(select max(rowid)
from emp
group by emp_no)
3. First 5 Max No
select sal
from (select abs(-sal) sal
from emp
group by -sal)
where rownum<6
Views--
-No Aggr function,group by,having
-U/D without PK but not Insert.
-Join -No DML
-No join-DML
Index -are used for row selection in where and order by only if
indexing on column
Oracle 9i features
Integration of SQL and PL/SQL parsers
Because of more consistent error-checking, you might find that some
invalid code is now found at compile time
instead of producing an error at runtime, or vice versa.
CASE statements and expressions
CASE Expressions, CASE Statement"
Inheritance and Dynamic Method Dispatch
Types can be declared in a supertype/subtype hierarchy, with subtypes
inheriting attributes and methods from
their supertypes. The subtypes can also add new attributes and methods,
and override existing methods. A call
to an object method executes the appropriate version of the method,
based on the type of the object.
Type Evolution
Attributes and methods can be added to and dropped from object types,
without the need to re-create the types
and corresponding data. This feature lets the type hierarchy adapt to
changes in the application, rather than
being planned out entirely in advance.
New Date/Time Types
The new datatype TIMESTAMP records time values including fractional
seconds. New datatypes TIMESTAMP WITH TIME ZONE
and TIMESTAMP WITH LOCAL TIME ZONE allow you to adjust date and time
values to account for time zone differences.
You can specify whether the time zone observes daylight savings time,
to account for anomalies when clocks shift forward
or backward. New datatypes INTERVAL DAY TO SECOND and INTERVAL YEAR TO
MONTH represent differences between
two date and time values, simplifying date arithmetic.
Table Functions and Cursor Expressions
You can query a set of returned rows like a table. Result sets can be
passed from one function to another, letting you
set up a sequence of transformations with no table to hold intermediate
results. Rows of the result set can be returned
a few at a time, reducing the memory overhead for producing large
result sets within a function.
Multilevel Collections
You can nest the collection types, for example to create a VARRAY of
PL/SQL tables, a VARRAY of VARRAYs, or a PL/SQL
table of PL/SQL tables. You can model complex data structures such as
multidimensional arrays in a natural way.
Better Integration for LOB Datatypes
You can operate on LOB types much like other similar types. You can use
character functions on CLOB and NCLOB types.
You can treat BLOB types as RAWs. Conversions between LOBs and other
types are much simpler, particularly
when converting from LONG to LOB types.
Enhancements to Bulk Operations
You can now perform bulk SQL operations, such as bulk fetches, using
native dynamic SQL (the EXECUTE IMMEDIATE statement).
You can perform bulk insert or update operations that continue despite
errors on some rows, then examine the problems after
the operation is complete.
i am using the command count(*), it will take more time (atleast 15
minutes) to execute the result.
How can i solve this
SQL> ANALYZE TABLE FAT_TABLE COMPUTE STATISTCS;
SQL> SELECT NUM_ROWS
FROM USER_TABLES
WHERE TABLE_NAME='FAT_TABLE';
You may need to schedule ANALYZE statement in a job.
Plain Text Attachment [ Save to my Yahoo! Briefcase | Download File ]
How many scroll bars can a block have
One
Two
Four
Any number
1
Which built_in can be used for setting title of forms
set_form_property
set_window_property
set_canvas_property
set_block_property
2
What is the datatype of global variables
Number
Character
Both 1& 2
None of the above
2
Why is dataparameter is used in forms
To pass data from forms to forms
To pass data from forms to reports
Both 1& 2
None of the above
2
Which of the following statements are true
LOV can exist without a RECORD GROUP
LOV can exist only with a RECORD GROUP
RECORD GROUP can exist without a LOV
LOV can be associated with multiple RECORD GROUP
2,3,4
Which command can execute a form abc.fmb from the COMMAND PROMPT
created by the user scott in the path c:\solix\
F45des c:\solix\abc.fmb scott/tiger@orcl
F45run c:\solix\abc.fmb scott/tiger@orcl
F45gen c:\solix\abc.fmx scott/tiger@orcl
F45run c:\solix\abc.fmx scott/tiger@orcl
4
Which type of canvas-view can be resized programmatically at runtime?
Horizontal Toolbar
Stacked Canvas
Content Canvas
Null Canvas
2
Which library should be attached to get bubble help for push buttons?
Autohint
Hint
Display_help
Bubble_help
2
In Forms4.5 when the built-in procedure Message(‘Hello’);
Message(‘Hello’); is executed it will display
A modal window
A modeless window
Both a modal and a modeless window
None
1
There is an occurance of a multiform transaction in the Order Entry
application. If an error occurs during default commit processing, where
does forms return the input focus?
Current form
Form that initiated the commit
Form in which the error occurred
First form opened for the transaction
2
Which built-in would you use to programmatically modify a coordination
type?
Set_form_property
Set_block_property
Set_relation_property
Set_application_property
3
"Which builtin menu subprogram can you use to disable a custom Insert
menu item when a user enters
this form from a query only menu item in that menu?
"
find_menu_item
Menu_parameter
Set_menu_item_property
Get_menu_item_property
3
When is the pl/sql interpreter displayed as a modeless window?
during runtime
during design time
during program creation
During forms generation
2
DECLARE
lc1 CHAR;
lc2 CHAR(20);
lv1 VARCHAR2(20);
BEGIN
lc2 := 'CAT';
lv1 := 'MOUSE';
DBMS_OUTPUT.PUT_LINE (NVL (LENGTH (lc1), -1));
DBMS_OUTPUT.PUT_LINE (NVL (LENGTH (lc2), -1));
DBMS_OUTPUT.PUT_LINE (NVL (LENGTH (lv1), -1));
1,3,5
-1,3,5
-1,20,5
1,20,5
1,20,20
5
What are the different objects that you cannot copy or reference in
object group?
Objects of different modules.
Objects from another object group
Object from independent block’s dependent items.
Program units
All the above
5
Give the sequence of execution of trigger fired on an item.
Post change
Key-next-item
When-validate-record
Post-next-item
2,1,4,3
When you create a table as ‘create table xyz as select * from abc’-
what all are copied from the table xyz?
All constraints
Only primary keys
Only foreign keys
Only not null constraints
4
What is maximum length of a table name
15
30
50
255
2
what is maximum size of VARCHAR2 data type in oracle 8i
255 bytes
2000 bytes
4000 bytes
None of the above
3
When a table is dropped , what happens to the view defined on that
table?
View is automatically dropped
View is not dropped
View becoms invalid
None of the above
3
What is range of user defined errors in PL/SQL
<-20000
-20000 to Any number
-20000 to -20999
None of the above
3
which data dictionary view has source code of stored procedures
user_procedures
user_source
user_objects
None of the above
2
How can we use DDL in forms
Cannot be used in forms
Through FORMS_DDL
Through form_triggers
None of the above
2
Which is the built in used for sending Operating system commands from
forms
Spool
Host
Command
Cannot be done in forms
2
Show_lov returns
Number
Varchar2
Boolean
None of the above
3
show_alert returns
Number
Varchar2
Boolean
None of the above
1
How many scroll bars can a block have
One
Two
Four
Any number
1
Which built_in can be used for setting title of forms
set_form_property
set_window_property
set_canvas_property
set_block_property
2
What is te datatype of global variables
Number
Character
Both 1& 2
None of the above
2
Why is dataparameter is used in forms
To pass data from forms to forms
To pass data from forms to reports
Both 1& 2
None of the above
2
What are the ways to specify a column alias
Naming the alias after the column specification separated by a space
Use of an 'as' word to specify the alias
a&b
None
3
One can read/write files from PL/SQL
No
Yes, USING UTL_FILE PACKAGE
YES, USING DBMS_OUTPUT PACKAGE
YES, USING DBMS_SQL PACKAGE
2
What does system monitor do?
performs instance recovery at instance startup useful for recovery from
system failure
performs recovery when user process fails and frees resources that
process was using
performs recovery when user process fails and frees resources that
process was using
None
2
A Procedure XYZ is written in program unit and attached library, when u
call this procedure what will happen
Error will occur
Library procedure will be called
Program unit procedure will be called
None of the above
3
What is system control statements
used to control the properties of current session enabling and
disabling roles and changing current
session enabling and disabling roles and changing
change properties of oracle instance
manage change by DML
None
2
What is program global area?
memory buffer that contains data and control information for server
process
stores the most recently used blocks of database data
is a stored memory region allocated by the oracle that contains data
and control information for one oracle Instance
All of the above
1
We have a total of ________ combination of Database triggers
4
6
12
None
3
Which of them are integrity Rules
Entity-Integrity rule
Foreign key Integrity rule
Business Integrity rules
All of the above
None of them
4
What are the various Master & Detail Relationships
Isolated, NonIsolated
Isolated, Cascading
NonIsolated, Cascading
Isolated, NoIsolated and Cascading
4
Which of the following is a Block Coordination properties
Immediate
Deffered with Auto Query
Deffered with No Auto Query
All of the Above
None of them
4
How do u implement the if statement in the select statement
using Where Clause
using Decode Statement
using Having clause
using Order by clause
None of them
2
How many minimum groups are required for a matrix report
2
6
4
5
ans 3
What are the table space created when database is created
User table space
Data table space
System table space
a & c
a, b & c
4
Every Oracle Database has ________ physical data files and each
datafile is associated with__________ database
many, one
many, many
one, many
one, one
1
Which of them is a Data block segment
Data segment
Index segment
Roll Back segment
All of the above
None of them
4
What are the 2 phase commit
Roll back phase & commit phase
Commit phase & prepare phase
Commit phase
Roll back phase, Commit phase & prepare phase
2
What are mutating tables
When a row has been inserted in the table
When a row has been updated in the table
When a row has been deleted in the table
When a row has been selected in the table
3
What is the difference of 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
A post query will fire only once but pre-query will fire for every row
tht is fetched
Apost query fires for every row that is fetched and also the pre-query
will fire for every row that is fetched
A Post Query is fired only once as so the Pre Query also is fired only
once
1
Which of them is an attributes of cursor
%Found
%Notfound
%isopen
%rowcount
All of the above
5
Triggers related to Isolated Master-Detail relationship
On check delete master, on clear details & on populated details
Per-delete, on-clear-details & on-populated details
On-clear details & on-populate details
On-check-delete-master & on-clear-details
3
Is forms 6i an object oriented tool
TRUE
FALSE
1
The sequence of firing in a text item are as follows
Pre-text, when-new-item, when validate, key-next & post-text
Pre-text, when-new-item, key-next, when-validate & post-text
Pre-text, key-next, when-new-item, when-validate & post-text
Pre-text, key-next, when-new-item, post-text & when-validate
2
Can you store pictures in database? How?
No
Yes, in long datatype
Yes, in long raw datatype
3
How many types of record groups are there
2
3
4
5
3
Can a button have icon and label at the same time
Yes
No
2
What is FORMS_MDI_WINDOW
Used for calling a form from another one
Used for setting the application window property of the form
Used for initializing a form
None of them
1
Can object group have a block
No, it is not possible
Yes, it can have block as well as program units
2
Can u have more than one layout in Report
Yes
No
1
Can u run the report without a parameter form
Yes
No
1
Which language allows for crozz platform portability and compatibility
PL/SQL
Java
Transact SQL
Visual Basic
Visual C++
3
Which one of the following SQL statements is used to created a trigger
Create table as trigger
Create view trigger
Table with trigger
Create trigger
Create object trigger
4
When designing a database table, how do you avoid missing column values
for
no-primary key columns
Use foreign key constraints
Use set constraints
Use unique constraints
Use primary key constraints
Use default and not null constraints
5
In Theory, how many foreign keys can you have in one table
One
Two
Three
Four
Unlimited
5
What type of trigger fires first—right after a SQL statement is issued
BETWEEN
DURING
IN
BEFORE
AFTER
4
When designing a database table, how do you ensure that column values
are valid when users are entering data into the table
Use PRIMARY KEY constraints
Use REDINDANCY constraints
Use CHECK Constraints
Use IDENTITY Constraints
Use stored procedures
3
In general, database stored procedures CANNOT perform which one of the
following
Insert data
Become nested
Call other stored procedures
Include cursors
Create views, triggers, or other procedures
5
What is an SQL cursor
A pointer for a table that is used to identify the current row and
column
A data set of rows or columns returned by a DML select statement
A mechanism that provides the ability to point to a specific row within
a result set
The subset of data that is returned for a select statement that
includes a Where clause
A SQL function used to specify the particular index level required for
a select statement join
2
Which one of the following is NOT a characteristic of row level locking
compared to block or page locking
It reduces contention
It increases performance of the RDBMS
It reduces a chance for deadlocks
It supoorts higher concurrency
It allows an increase in the number of locks
What is the difference between @ and @@
@ runs the script relative to the current directory and @@ runs the
script relative to the directory of the currenty script
@ runs the script relative to the directory of the current script,@@
runs the script relative to the current directory
None of the above
1
Can one bypass the Oracle login screen?
Yes, using PRE-LOGIN
Yes, Using ON-LOGON
Yes, Using POST-LOGON
NO
4
How can one iterate through items and records in a specified blocks
NEXT_RECORD
NEXT_ITEM
NEXT_FIELD
NEXT_BLOCK
None of them
1,3
How can u create a permanent definition of a substitution varioable
when the SQL statement uses them
DEFINE
OLD_VALUE
NEW_VALUE
SET
1
Which comand will perform variables substitution of & and && symbols
! (Exclamation)
Set
Host
None of the above
3
Can one execute dynamic SQL from Forms
DBMS_OUTPUT PACKAGAGE
FORMS_DDL
DBMS_SQL
All of the above
2,3
How to use DDL commands in PL?SQL
Execute immediate
FORMS_DDL
None of the above
A & B
1
How can I read/write OS Files from Forms
DBMS_UTIL PACKAGE
TEXT_IO package
FORMS_DDL PACKAGE
All of the above
2
HOW TO CREATE MORE THEN ONE TRIGGER IN A COMMAND FILE
SEPERATED BY ,
SEPERATED BY ;
SEPARETED BY SPACES
SEPERATED BY /
4
What is the default directory for sql files called from PL/SQL
ORACLE_HOME\BIN
ORACLE_HOME\SQL
ORACLE_HOME\NET
ORACLE_HOME\ADMIN
1
Can we define same exception twice in same block
Yes
No
2
Can you have two stored functions with the same name
Yes
No
1
Can you call a stored functions in the constraint of a table
Yes
No
2
Can views be specified in a trigger statement
Yes
No
2
What is the maximum no of cascading triggers at a time
4
8
16
32
Unlimited
4
Can check constraint be used for self referential integrity
Yes
No
1
How many rows will the following SQL return:
select * from emp where rownum = 10;
10
1
9
None
All rows
4
Which symbol preceeds the path to the table in the remote database
&
&&
$
#
@
5
Are views automatically updated when base tables are update
Yes
No
1
Can a trigger return for a view
Yes
No
2
If all the values from a cursor have been fetched and another fetch is
issued, the output will be
Error
Last record
First record
None of the above
2
A table has the following data : [ 5, null, 10 , null, 7]. what will
the count() return
5
4
3
1
0
3
Is sysdate is a
System variable
System function
Pre defined value
None of the above
2
Consider a sequence whose currval is 1 and gets incremented by 1 by
using the nextval reference we
get the next number 2. suppose at this point we issue an rollback and
again issue a nextval. What will the output be
3
2
1
None
1
Can dual table be
Altered
Dropped
Deleted
Inserted
All of the above
5
What is the result of the following SQL:
select 1 from dual union select 'A' from dual;
1
1'A'
A'
None of them
Error
5
Can database trigger written on synonym of a table
Yes
No
1
Can you alter synonym of view or view
Yes
No
2
If content of dual is updated to some value computation takes place or
not
Yes
No
1
Can you create index on view
Yes
No
2
If any other table same as dual is created would it act similar to dual
Yes
No
1
: WHAT IS THE ERR MESSAGE U GET WHEN U R INSERTING SAME ROW IN PRIMARY KEY
Ans UNIQUE CONSTRAINT(
.with out ln line query
----------------------
SELECT TD_MAIN_ACNT_CODE,TD_DIVN_CODE,DECODE('C',TD_DOC_DRCR_FLAG,SUM(NVL(TD_DOC_AMT,0)),0) CREDIT ,DECODE('D',TD_DOC_DRCR_FLAG,SUM(NVL(TD_DOC_AMT,0)),0) DEBIT
FROM FT_CUR_TRANS_DETAIL,FM_MAIN_ACCOUNT,FM_DIVISION
WHERE TD_MAIN_ACNT_CODE = MAIN_ACNT_CODE
AND MAIN_ACNT_CATG = 'BA'
AND TD_DIVN_CODE = DIVN_CODE
AND TD_DIVN_CODE IS NOT NULL
-- AND TD_DOC_DT BETWEEN TO_DATE(TO_CHAR(:P_FROM_DOC_DT,'DD-MON-YY')) AND TO_DATE(TO_CHAR(:P_TO_DOC_DT,'DD-MON-YY'))
GROUP BY TD_MAIN_ACNT_CODE,TD_DIVN_CODE,TD_DOC_DRCR_FLAG,TD_DOC_AMT
--,FM_DIVISION B,FM_MAIN_ACCOUNT C
OUTPUT
-------
TD_MAIN_ACNT_CODE TD_DIVN_CODE CREDIT DEBIT
----------------------------------------------
101 001 1200 0
101 001 25000 0
1011 001 12000 0
1011 001 0 12000
3888 002 2334 0
3888 002 0 2334
since we are using group by clause and since the grouping is done
on GROUP BY TD_MAIN_ACNT_CODE,TD_DIVN_CODE,TD_DOC_DRCR_FLAG,TD_DOC_AMT
The sum(debit) and sum of credit is displayed in two rows for each GROUP BY TD_MAIN_ACNT_CODE,TD_DIVN_CODE,TD_DOC_DRCR_FLAG,TD_DOC_AMT
sum of debit is displayed in one row and sum of credit is displayed in other row
for each grouped columns but we want both the sum of credit and debit to be displayed
in the same row so we go for INLINE QUERY in these situations
2.WITH In line Query
------------------
SELECT TD_DIVN_CODE,B.DIVN_NAME,TD_MAIN_ACNT_CODE,C.MAIN_ACNT_NAME,SUM(CREDIT),SUM(DEBIT)
FROM(SELECT TD_MAIN_ACNT_CODE,TD_DIVN_CODE,DECODE('C',TD_DOC_DRCR_FLAG,SUM(NVL(TD_DOC_AMT,0)),0) CREDIT ,DECODE('D',TD_DOC_DRCR_FLAG,SUM(NVL(TD_DOC_AMT,0)),0) DEBIT
FROM FT_CUR_TRANS_DETAIL,FM_MAIN_ACCOUNT,FM_DIVISION
WHERE TD_DIVN_CODE BETWEEN :P_FROM_DIVN_CODE AND :P_TO_DIVN_CODE
AND TD_MAIN_ACNT_CODE BETWEEN :P_FROM_MAIN_ACNT_CODE AND :P_TO_MAIN_ACNT_CODE
AND TD_MAIN_ACNT_CODE = MAIN_ACNT_CODE
AND MAIN_ACNT_CATG = 'BA'
AND TD_DIVN_CODE = DIVN_CODE
AND TD_DIVN_CODE IS NOT NULL
AND TD_DOC_DT BETWEEN TO_DATE(TO_CHAR(:P_FROM_DOC_DT,'DD-MON-YY')) AND TO_DATE(TO_CHAR(:P_TO_DOC_DT,'DD-MON-YY'))
GROUP BY TD_MAIN_ACNT_CODE,TD_DIVN_CODE,TD_DOC_DRCR_FLAG),FM_DIVISION B,FM_MAIN_ACCOUNT C
GROUP BY TD_MAIN_ACNT_CODE,TD_DIVN_CODE,B.DIVN_NAME,C.MAIN_ACNT_NAME
ORDER BY TD_DIVN_CODE
OUTPUT
------
TD_DIVN_CODE DIVN_NAME TD_MAIN_ACNT_CODE SUM(CREDIT) SUM(DEBIT)
----------------------------------------------------------------------------
001 109 363636 0 24400
001 109 01010 0 25000
001 FIN7 39467 24777 0
------------------------------------------------------------------------------
3.While building the Query in reports first put the parameters conditions and then the
other conditions like above ie column conditions for master detail and header and transactions
referential integrity conditions are checked at last so that the query is accessing is fast
coz oracle retrieves by cheking condtions from bottom to top
4.WHEN TO USE TO_CHAR() AND TO_DATE() FUNCTIONS
--------------------------------------------------
in the interface form the from and to date or DATE which we give wlll b character form
so while passing parameters from FORM to report ie in ADD_PARAMETER() procedure
we place TO_CHAR() before date at form or interface level for passing date to report
Eg: code written in PROGRAM UNIT
PROCEDURE L_Set_Report_Parameter (PARAM_ID IN OUT Paramlist) IS
rg_val number;
BEGIN
ADD_PARAMETER(PARAM_ID,'P_COMP_CODE',TEXT_PARAMETER,:GLOBAL.M_COMP_CODE);
ADD_PARAMETER(PARAM_ID,'P_DEST_TYPE',TEXT_PARAMETER,:m_dest_type);
ADD_PARAMETER(PARAM_ID,'P_FROM_DOC_DT',TEXT_PARAMETER,TO_CHAR(:TXTFROM_DOC_DT,'DD/MON/YYYY'));
ADD_PARAMETER(PARAM_ID,'P_TO_DOC_DT',TEXT_PARAMETER,TO_CHAR(:TXTTO_DOC_DT,'DD/MON/YYYY'));
ADD_PARAMETER(PARAM_ID,'P_FROM_HIREDATE',TEXT_PARAMETER,TO_CHAR(:TXTHIREDATE,'DD/MON/YYYY'));
ADD_PARAMETER(PARAM_ID,'P_TO_HIREDATE',TEXT_PARAMETER,TO_CHAR(:TXTHIREDATE,'DD/MON/YYYY'));
ADD_PARAMETER(PARAM_ID,'MAXIMIZE', TEXT_PARAMETER,'YES');
END;
BUT since at the report level or side when we compare the date in the condition
of SQL query ie in the WHERE condition since we compare with the DATABASE COLUMN VALUE
which will b in date format we have to convert this passed PARAMETER (P_HIREDATE) TO DATE with TO_DATE()
Eg: code written in Query part of Report Editor
SELECT ENAME,HIREDATE,DEPTNO,DNAME,TD_DOC_DT,TD_AMOUNT
FROM EMP ,DEPT,FT_CUR_TRANS_DETAIL
WHERE HIREDATE BETWEEN TO_DATE(:P_FROM_HIREDATE,'DD/MM/YY') AND TO_DATE (:P_TO_HIREDATE,'DD/MM/YY')
AND AND TD_DOC_DT BETWEEN TO_DATE(:P_FROM_DOC_DT,'DD/MM/YY') AND TO_DATE (:P_TO_DOC_DT,'DD/MM/YY')
AND EMP.DEPTNO=DEPT.DEPTNO
AND FT_CUR_TRANS_DETAIL.DEPTNO=DEPT.DEPTNO
Forms 6i Features
------------------
-HTTP server is connecting communication between client machine and the intermediate server 9iAS
-Wizards are powerful developing time reduces
-Java components is strong in Reports
-Better Development Reporting using API's
'
Blog Archive
Sunday, February 17, 2008
oracle interview questions
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment