Thursday, October 14, 2010
Oracle FAQs
1. WHAT IS DATA OR INFORMATION?
Ans: The Matter that we feed into the Computer is called Data or Information.
2. WHAT IS DATABASE?
Ans: The Collection of Interrelated Data is called Data Base.
3. WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) PACKAGE?
Ans: The Collection of Interrelated Data and some Programs to access the Data is Called Data Base Management System (DBMS).
4. WHEN CAN WE SAY A DBMS PACKAGE AS RDBMS?
Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
5. WHAT IS ORDBMS?
Ans: Object (oriented) Relational Data Base Management System is one that can store data, the relationship of the data, and the behavior of the data (i.e., the way it interacts with other data).
6. NAME SOME CODD'S RULES.
Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it is to be considered truly relational. Out those, some are as follows
a) The rules stem from a single rule- the ‘zero rule’: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
b) Information Rule: Tabular Representation of Information.
c) Guaranteed Access Rule: Uniqueness of tuples for guaranteed accessibility.
d) Missing Information Rule: Systematic representation of missing information as NULL values.
e) Comprehensive Data Sub-Language Rule: QL to support Data definition, View definition, Data manipulation, Integrity, Authorization and Security.
7. WHAT ARE HIERARCHICAL, NETWORK, AND RELATIONAL DATABASE MODELS?
Ans: a) Hierarchical Model: The Hierarchical Model was introduced in the Information Management System (IMS) developed by IBM in 1968. In this data is organized as a tree structure. Each tree is made of nodes and branches. The nodes of the tree represent the record types and it is a collection of data attributes entity at that point. The topmost node in the structure is called the root. Nodes succeeding lower levels are called children.
b) Network Model: The Network Model, also called as the CODSYL database structure, is an improvement over the Hierarchical mode, in this model concept of parent and child is expanded to have multiple parent-child relationships, i.e. any child can be subordinate to many different parents (or nodes). Data is represented by collection of records, and relationships among data are represented by links. A link is an association between precisely two records. Many-to-many relationships can exists between the parent and child.
c) Relational Model: The Relational Database Model eliminates the need for explicit parent-child relationships. In RDBMS, data is organized in two-dimensional tables consisting of relational, i.e. no pointers are maintained between tables.
8. WHAT IS DATA MODELING?
Ans: Data Modeling describes relationship between the data objects. The relationships between the collections of data in a system may be graphically represented using data modeling.
9. DEFINE ENTITY, ATTRIBUTE AND RELATIONSHIP.
Ans: Entity: An Entity is a thing, which can be easily identified. An entity is any object, place, person, concept or activity about which an enterprise records data.
Attribute: An attribute is the property of a given entity.
Relationship: Relationship is an association among entities.
10. WHAT IS ER-MODELING?
Ans: The E-R modeling technique is the Top Down Approach. Entity relationship is technique for analysis and logical modeling of a system’s data requirements. It is the most widely used and has gained acceptance as the ideal database design. It uses three basic units: entities, their attributes and the relationship that exists between the entities. It uses a graphical notation for representing these.
11. WHAT IS NORMALIZATION?
Ans: Normalization is a step-by-step decomposition of complex records into simple records.
12. WHAT ARE VARIOUS NORMAL FORMS OF DATA?
Ans: The First Normal Form 1NF,
The Second Normal Form 2NF,
The Third Normal Form 3NF,
The Boyce and Codd Normal Form BC NF.
13. WHAT IS DENORMALIZATION?
Ans: The intentional introduction of redundancy to a table to improve performance is called DENORMALIZATION.
14. WHAT ARE 1-TIER, 2-TIER, 3-TIER OR N-TIER DATABASE ARCHITECTURES?
Ans: 1-Tier Database Architecture is based on single system, which acts as both server and client.
2-Tier Architecture is based on one server and client.
3-Tier Architecture is based on one server and client out that on client act as a remote system.
N-Tier Architecture is based on N no. Of servers and N no. Of clients.
15. WHAT ARE A TABLE, COLUMN, AND RECORD?
Ans: Table: A Table is a database object that holds your data. It is made up of many columns. Each of these columns has a data type associated with it.
Column: A column, referred to as an attribute, is similar to a field in the file system.
Record: A row, usually referred to as tuple, is similar to record in the file system.
16. WHAT IS DIFFERENCE BETWEEN A PROCEDURAL LANGUAGE AND A NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language NON-Procedural Language
A program in this implements a step-by-step algorithm to solve the problem. It contains what to do but not how to do
17.WHAT TYPE OF LANGUAGE "SQL" IS?
Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts what to do rather than how to do any process.
18. CLASSIFICATION OF SQL COMMANDS?
Ans:
DDL (Data Definition Language) DML (Data Manipulating Language) DCL (Data Control Language) DTL(Data Transaction Language)
Create Alter Drop Select Insert Update Delete Rollback Commit Grant Revoke
19. WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly whereas For DML commands autocommit is to be turned ON explicitly.
20. WHAT IS DIFFERENCE BETWEEN A TRANSACTION AND A QUERY?
Ans: A Transaction is unit of some commands where as Query is a single line request for the information from the database.
21. WHAT IS DIFFERENCE BETWEEN TRUNCATE AND DELETE COMMANDS?
Ans: Truncate Command will delete all the records where as Delete Command will delete specified or all the records depending only on the condition given.
22. WHAT IS DIFFERENCE BETWEEN UPDATE AND ALTER COMMANDS?
Ans: Alter command is used to modify the database objects where as the Update command is used to modify the values of a data base objects.
23. WHAT ARE COMMANDS OF TCL CATEGORY?
Ans: Grant and Revoke are the two commands belong to the TCL Category.
24. WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using this command we can delete only those records that are not really required.
25. WHAT ARE RULES FOR NAMING A TABLE OR COLUMN?
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character from your database character set and the characters $ and #. But these characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace. Objects in the name space must have different names.
7) A name can be enclosed in double quotes.
26. HOW MANY COLUMNS CAN A TABLE HAVE?
Ans: A Table can have 1000 columns.
27. WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values,
Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values,
Date data type for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.
28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB LONG
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type. 1) The maximum size is 2GB. 2) LONGs cannot. 3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table
29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store variable no. Of characters and while querying the table varchar2 trims the extra spaces from the column and fetches the rows that exactly match the criteria.
30. HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT DATE FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes Memory.
Default Date Format is: DD-MON-YY.
31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?
Ans:
Datatype Range
Char Varchar2 Number Float LONG, RAW, LONGRAW Large Objects (LOB’s) 2000 bytes 4000 bytes Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary precision 2 GB 4GB
32. HOW TO RENAME A COLUMN?
Ans: We can’t rename a Column of a table directly. So we follow the following steps.
To Rename a Column:
a) Alter the table specifying new column name to be given and data type.
b) Then copy the values in the column to be renamed into new column.
c) drop the old column.
33. HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a column
i. Truncate the table first.
ii. Alter the table column whose size is to be decreased using the same name and data type but new size.
34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.
35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.
36. WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?
Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can’t be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.
37. WHAT IS A COMPOSITE PRIMARY KEY?
Ans: A Primary key created on combination of columns is called Composite Primary Key.
38. WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE POSSIBLE PER COMPOSITE PRIMARY KEY?
Ans:
39. HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero nor a space and any mathematical calculation with NULL is always NULL.
40. WHAT IS NULL? A CONSTRAINT OR DEFAULT VALUE?
Ans: It is a default value.
41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using
References)is called as a child table(detail table) and a table which is being referred is called Parent (Master) Table .
47. HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate….
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified order(ascending,descending) by default it takes ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator.
58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest value from the set and compares a value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query produces any result then it evaluates it to TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once for each ‘Candidate row’ by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column value used in inner query refers to the column value present in the outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get query results.
There are four types of Joins namely
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining tables.
62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right) based on the requirement.
63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas
EQUI JOIN is made between different tables having common column.
64. WHAT ARE "SET" OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL"
OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will return even duplicate values.
66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER
FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.
69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an 'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE ()
FUNCTIONS?
Ans: Translate() is a superset of functionality provided by Replace().
71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas
Instr() return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC and the given date.
73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.
75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no physical data and manipulates the base table. View is saved as a select statement in the database and contains no physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based on more than one table) cannot be modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use like a view inside a SQL statement. It is not a schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be
Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick access to rows in a table. An Index increases the performance of the database.
82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers
to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a method of storing tables that are intimately related and are often joined together into the same area on the disk. When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or package.
86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas any user can reference the Public synonym.
87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTEOF [break] ON etc are SQL*PLUS REPORTING COMMANDS.
90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges.
Create, Select, Insert, Alter etc are Object Privileges.
91. WHAT FOR DCL COMMANDS ARE?
Ans: Commit, Rollback are DCL commands.
92. WHAT FOR GRANT COMMAND WITH "WITH GRANT OPTION"?
Ans: “With Grant Option” with Grant Command gives privileges to the user to grant privileges to other user(s) among the privileges he/she has.
93. HOW TO CHANGE PASSWORD OF A USER?
Ans: Using Password command or
Using ALTER USERIDENTIFIED BY COMAND.
94. WHAT IS A SCHEMA AND SCHEMA OBJECTS?
Ans: A schema is a collection of logical structures of data, or schema objects. A schema is owned by the database user and has the same name as that of user. Each user owns a single schema. Schema objects include following type of objects Clusters, Database Links, Functions, Indexes, Packages, Procedures, Sequences, Synonyms, Tables, Database Triggers, Views.
**95. HOW TO STARTUP AND SHUTDOWN ORACLE DATABASE?
Ans: Startup and Shutdown Oracle database can be done by only the administator. Startup is done by using STARTUP command and Shutdown is done by SHUTDOWN command
96. WHAT IS A SESSION?
Ans: The period between Login and Logoff on schema.
97. WHAT IS A CLIENT PROCESS? WHAT IS A SERVER PROCESS?
Ans: ref: 172 Q & A.
98. HOW TO MAKE EVERY DML OPERATION AS AUTO COMMIT?
Ans: By using SET AUTOCOMMIT ON command.
99. HOW TO DISPLAY DATA PAGE WISE IN SQL?
Ans: By using SET PAUSE ON command.
100. HOW TO CHANGE LINE SIZE, PAGE SIZE AND SQL PROMPT?
Ans: By using SET LINESIZE, SET PAGESIZE ,
SET SQLPROMPT.
101. HOW PL/SQL IS DIFFERENT FROM SQL?
Ans: SQL is non-procedural language whereas PL/SQL is procedural language that includes features and design of programming language.
102. WHAT IS ARCHITECTURE OF PL/SQL?
Ans:
103. WHAT IS A PL/SQL BLOCK?
Ans: DECLARE
BEGIN
EXCEPTION
END;
104. WHAT ARE DIFFERENT TYPES OF PL/SQL BLOCKS?
Ans: DECLARE BLOCK: In this block all the declarations of the variable used in the program is made. If no variables are used this block will become optional.
BEGIN BLOCK: In this block all the executable statements are placed.
This block is Mandatory.
EXCEPTION BLOCK: In this block all the exceptions are handled.
This block is also very optional.
END: Every begin must be ended with this END; statement.
105. WHAT ARE COMPOSITE DATA TYPES?
Ans: Records, Tables are two Composite data types.
106. WHAT IS SCOPE OF A VARIABLE IN PL/SQL BLOCK?
Ans: The visuability and accessibility of a variable within the block(s) is called scope of a variable.
107. WHAT IS A NESTED BLOCK?
Ans: A block within a block is called Nested Block.
108. WHAT IS A PL/SQL ENGINE?
Ans: The PL/SQL engine accepts any valid PL/SQL block as input, executes the procedural part of the statements and sends the SQL statements to the SQL statement executor in the Oracle server.
109. WHAT IS DEFAULT VALUE FOR A NUMERIC PL/SQL VARIABLE?
Ans: NULL
110. WHAT IS DIFFERENCE BETWEEN SIMPLE LOOP AND A FOR LOOP?
Ans: Simple requires declaration of variables used in it and exit condition but For Loop doesn’t require this.
111. WHAT IS A CURSOR? STEPS TO USE A CURSOR?
Ans: Cursor is Private SQL area in PL/SQL.
Declare the Cursor,
Open the Cursor,
Fetch values from SQL into the local Variables,
Close the Cursor.
112. HOW MANY TYPES OF CURSORS ARE SUPPORTED BY ORACLE?
Ans: There are two types of cursors namely Implicit Cursor, Explicit Cursor.
113. WHAT IS A CURSOR FOR LOOP?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.
114. WHAT ARE CURSOR ATTRIBUTES?
Ans: %Found
%NotFound
%IsOpen
%RowCount are the cursor attributes.
115. WHAT IS USE OF CURSOR WITH "FOR UPDATE OF" CLAUSE?
Ans: This Clause stop accessing of other users on the particular columns used by the cursor until the COMMIT is issued.
116. WHAT IS AN EXCEPTION? HOW IT IS DIFFERENT FROM ERROR?
Ans: Whenever an error occurs Exception raises.
Error is a bug whereas the Exception is a warning or error condition.
117. NAME SOME BUILT-IN EXCEPTIONS.
Ans: Too_Many_Rows
No_Data_Found
Zero_Divide
Not_Logged_On
Storage_Error
Value_Error etc.
118. HOW TO CREATE A USER-DEFINED EXCEPTION?
Ans: User-Defined Exception is created as follows:
DECLARE
EXCEPTION;
- - - - - - - - - ;
- - - - - - - - -;
BEGIN
- - - - - - - - -;
- - - - - - - - -;
RAISE;
EXCEPTION
WHENTHEN
- - - - - - - - -;
- - - - - - - - -;
END;
119. WHAT IS "OTHERS" EXCEPTION?
Ans: It is used to along with one or more exception handlers.
This will handle all the errors not already handled in the block.
120. WHAT IS SCOPE OF EXCEPTION HANDLING IN NESTED BLOCKS?
Ans: Exception scope will be with in that block in which exception handler is written.
121. WHAT IS A SUB-PROGRAM?
Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE INVOKED BY TAKING PARAMATERS.
122. WHAT ARE DIFFERENT TYPES OF SUB-PROGRAMS?
Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.
123. HOW A PROCEDURE IS DIFFERENT FROM A FUNCTION?
Ans: Function has return key word and returns a value whereas a Procedure doesn’t return any value.
124. WHAT ARE TYPES OF PARAMETERS THAT CAN BE PASSED TO FUNCTION OR PROCEDURE?
Ans: IN, IN OUT, OUT.
125. WHAT IS "IN OUT" PARAMETER?
Ans: A parameter, which gets value into the Procedure or Function and takes the value out of the Procedure or Function area, is called IN OUT parameter.
126. DOES ORACLE SUPPORTS PROCEDURE OVERLOADING?
Ans: NO.
127. WHAT IS A PACKAGE AND PACKAGE BODY?
Ans: Package is declarative part of the functions and procedures stored in that package and package body is the definition part of the functions and procedures of that package.
128. WHAT IS ADVANTAGE OF PACKAGE OVER PROCEDURE OR FUNCTION?
Ans: Packages provides Functions or Procedures Overloading facility and security to those Functions or Procedures.
129. IS IT POSSIBLE TO HAVE A PROCEDURE AND A FUNCTION WITH THE SAME NAME?
Ans: NO if it is out side a Package, YES if it is within a Package.
130. DOES ORACLE SUPPORTS RECURSIVE FUNCTION CALLS?
Ans: YES.
131. WHAT IS A TRIGGER? HOW IT IS DIFFERENT FROM A PROCEDURE?
Ans: Trigger: A Trigger is a stored PL/SQL program unit associated with a specific database table.
Procedure: A Procedure is to be explicitly called by the user whereas Triggers are automatically called implicitly by Oracle itself whenever event Occurs.
132. WHAT IS DIFFERENCE BETWEEN A TRIGGER AND A CONSTRAINT?
Ans: Constraints are always TRUE whereas Triggers are NOT always TRUE and Constraints has some limitations whereas Trigger has no limitations.
133. WHAT ARE DIFFERENT EVENTS FOR A TRIGGER AND THEIR SCOPES?
Ans: Insert, Update or Delete.
134. WHAT IS DIFFERENCE BETWEEN TABLE LEVEL AND ROW LEVEL TRIGGERS?
Ans: Table level Triggers execute once for each table based transaction whereas Row level Triggers will execute once FOR EACH ROW.
** 135. WHAT ARE AUTONOMOUS TRIGGERS?
Ans:
136. WHAT IS AN "INSTEAD OF" TRIGGER?
Ans: These Triggers are used with the Complex Views only to make possible of Insert, Update and Delete on those Views.
** 137. HOW MANY TRIGGERS CAN BE CONFIGURED ON A TABLE AND VIEW?
Ans:
138. WHAT IS "TABLE MUTATING" ERROR? HOW TO SOLVE IT?
Ans: ORA-04091: Table name is mutating, trigger/function may not see it
Cause : A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger.
Action : Rewrite the trigger or function so it does not read the table.
139. WHEN TO USE ":NEW" AND ":OLD" SPECIFIERS?
Ans: The prefix :old is used to refer to values already present in the table. The prefix :new is a correlation name that refers to the new value that is inserted / updated.
** 140. WHAT IS A CONDITIONAL TRIGGER?
Ans:
** 141. HOW TO CREATE A USER-DEFINED VARIABLE IN PL/SQL?
Ans:
142. HOW TO CREATE AN ARRAY VARIABLE IN PL/SQL?
Ans: Using CREATE [OR REPLACE] TYPE
AS VARRAY (size) OF ELEMENT_TYPE (NOT NULL) Command;
**143. HOW TO MAKE A USER-DEFINED DATA TYPE GLOBAL IN PL/SQL?
Ans:
144. HOW TO CREATE AN OBJECT IN ORACLE?
Ans: Using CREATE [OR REPLACE] TYPEAS OBJECT (ATTRIBUTE NAME DATA TYPE,..) Command
145. WHAT IS A TRANSIENT AND PERSISTENT OBJECT?
Ans: The Object created in a table is called Persistent Object.
Object created on execution of PL/SQL block is called Transient Object.
**146. WHAT IS A COLUMN OBJECT AND TABLE OBJECT?
Ans: A Column Object is only a Column of a table.
147. HOW TO GRANT PERMISSION ON AN OBJECT TO OTHER USER?
Ans: GRANTONTO .
148. WHAT IS A COLLECTION OF ORACLE?
Ans: Varray, Nested Table is a collection of Oracle.
149. WHAT IS DIFFERENCE BETWEEN VARRAY AND NESTED TABLE?
Ans: Varray has a fixed size.
Nested tables can carry any number of values.
150. HOW TO MODIFY CONTENTS OF A VARRAY IN ORACLE?
Ans: To modify a stored VARRAY it has to selected into a
PL/SQL variable and then inserted back into the table.
151. WHAT IS USE OF "THE" OPERATOR FOR NESTED TABLE?
Ans: THE operator allows nested tables to be manipulated using DML when it is stored in a Table.
152. WHICH PACKAGE IS USED FOR FILE INPUT/OUTPUT IN ORACLE?
Ans: UTL_FILE Package is used for File input/output in Oracle.
153. NAME SOME METHODS AND PROCEDURES OF FILE I/O PACKAGE?
Ans: FOPEN
FCLOSE
FFLUSH
IS_OPEN
GET_LINE
PUT_LINE
PUTF
NEW_LINE
**154. WHAT IS SQLJ? HOW IT IS DIFFERENT FROM JDBC CONNECTIVITY?
Ans: SQLJ is basically a Java program containing embedded static SQL statements that are compatible with Java design philosophy.
155. WHAT IS AN ITERATOR? Name some TYPES OF ITERATORS?
Ans: SQLJ Iterators are basically record groups generated during transaction, which requires manipulation of more than one records from one or more tables. There are two types Iterators namely Named Iterator and Positional Iterator.
** 156. WHAT ARE DIFFERENT STEPS TO WRITE A DYNAMIC SQL PROGRAM?
Ans:
Eg: char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
EXEC SQL EXECUTE readytogo;
157. WHAT IS TABLE PARTITIONING AND INDEX PARTITIONING?
Ans: Oracle8 allows tables and Indexes to be partitioned or broken up into smaller parts based on range of key values. Partitioning is a “divide and conquer” strategy that improves administration and performance in data warehouse and OLTP systems.
158. WHAT IS PARALLEL PROCESSING?
Ans:
159. WHAT IS PHYSICAL MEMORY STRUCTURE OF ORACLE?
Ans: The basic oracle memory structure associated with Oracle includes:
Software Code Areas
The System Global Area (SGA)
The Database Buffer Cache
The shared Pool
The Program Global Areas (PGA)
Stack Areas
Data Areas
Sort Areas
160. WHAT IS LOGICAL MEMORY STRUCTURE OF ORACLE?
DB_STG
STUDENT SYSTEM
EMP DEPT EMP_IND ….. ..
DATA DATA INDEX
Ans: Database
Tablespace
DB Object
Segment
Extends
161. WHAT IS SGA?
Ans: A System Global Area is a group of shared memory allocated by Oracle that contains data and control information for one Oracle database instance. IF the multiple users are concurrently connected to the same instance, the data in the instance’s SGA is “shared” among the users. Consequently, the SGA is often referred to as either the “system Global Area” or the “Shared Global Area”.
162. WHAT IS PGA?
Ans: The Program Global Area is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the configuration of Oracle.
163. WHAT IS AN ORACLE INSTANCE?
Ans: Every time a database is started, an SGA is allocated and Oracle background processes are started. The combination of these processes and memory buffers is called an Oracle instance.
164. WHAT ARE DIFFERENT ORACLE PROCESSES?
Ans: A process is a “thread of control” or a mechanism in an operating system that can be execute a series of steps. Some operating systems use terms jobs or task. A process normally has its own private memory area in which it runs. An Oracle database system has general types of process: User Processes and Oracle Processes.
**165. WHAT IS DIFFERENCE BETWEEN PMON AND SMON?
Ans: SMON (System Monitor) performs instance recovery at instance of startup. In a multiple instance system (one that uses the parallel server), SMON of one instance can also perform instance recovery other instance that have failed whereas The PMON (Process Monitor) performs process recovery when a user process fails.
**166. WHAT IS DIFFERENCE BETWEEN DATABASE AND TABLESPACE?
Ans:
167. WHAT IS JOB OF DATABASE WRITER (DBWR) PROCESS?
Ans: The Data Base Writer writes modified blocks from the database buffer cache to the data files.
168. WHAT IS JOB OF LOG WRITER (LGWR) PROC*SS?
Ans: The Log Writer writes redo log files to disk. Redo log data is generated in the redo log buffer of the SGA. As transactions commit and log buffer fills, LGWR writes redo entries into an online redo log file.
169. WHAT IS RECOVERER?
Ans: The Recover (RECO) is used to resolve distributed transactions that are pending due to network or system failure in a distributed database. At timed intervals, the local RECO attempts to concept to remote database and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
170. WHAT IS ARCHIVER?
Ans: The Archiver (ARCH) copies the online redo log files to archival storage when they are full. ARCH is active only when a
database’s redo log is used ARCHILOG mode.
** 171. WHAT IS A STORED QUERY?
Ans:
172. WHAT IS USER PROCESS AND SERVER PROCESS?
Ans: A User process is created and maintained to execute the software code of an application program (such as PRO * Program) or an ORACLE tool (such as SQL * DBA). The User process also manages the communication with server processes. User processes communication with the server processes through the program interface.
Other processes call ORACLE processes. In a dedicated server configuration, a server
Process handles requests for a single user process. A multithread server configuration allows many user processes to share a small number of server processes, minimizing the utilization of available system resources.
**173. WHAT IS A SELF REFERENTIAL INTEGRITY?
Ans:
174. WHAT IS A "RAISE" STATEMENT?
Ans: It is used to Raise Exceptions.
175. WHAT IS ROWID? HOW IT IS DIFFERENT FROM ROWNUM?
Ans: Rowid is the address of the row at where it is stored in the database. Rownum is count of records whereas Rowid is identification of the each row.
Ans: The Matter that we feed into the Computer is called Data or Information.
2. WHAT IS DATABASE?
Ans: The Collection of Interrelated Data is called Data Base.
3. WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) PACKAGE?
Ans: The Collection of Interrelated Data and some Programs to access the Data is Called Data Base Management System (DBMS).
4. WHEN CAN WE SAY A DBMS PACKAGE AS RDBMS?
Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
5. WHAT IS ORDBMS?
Ans: Object (oriented) Relational Data Base Management System is one that can store data, the relationship of the data, and the behavior of the data (i.e., the way it interacts with other data).
6. NAME SOME CODD'S RULES.
Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it is to be considered truly relational. Out those, some are as follows
a) The rules stem from a single rule- the ‘zero rule’: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
b) Information Rule: Tabular Representation of Information.
c) Guaranteed Access Rule: Uniqueness of tuples for guaranteed accessibility.
d) Missing Information Rule: Systematic representation of missing information as NULL values.
e) Comprehensive Data Sub-Language Rule: QL to support Data definition, View definition, Data manipulation, Integrity, Authorization and Security.
7. WHAT ARE HIERARCHICAL, NETWORK, AND RELATIONAL DATABASE MODELS?
Ans: a) Hierarchical Model: The Hierarchical Model was introduced in the Information Management System (IMS) developed by IBM in 1968. In this data is organized as a tree structure. Each tree is made of nodes and branches. The nodes of the tree represent the record types and it is a collection of data attributes entity at that point. The topmost node in the structure is called the root. Nodes succeeding lower levels are called children.
b) Network Model: The Network Model, also called as the CODSYL database structure, is an improvement over the Hierarchical mode, in this model concept of parent and child is expanded to have multiple parent-child relationships, i.e. any child can be subordinate to many different parents (or nodes). Data is represented by collection of records, and relationships among data are represented by links. A link is an association between precisely two records. Many-to-many relationships can exists between the parent and child.
c) Relational Model: The Relational Database Model eliminates the need for explicit parent-child relationships. In RDBMS, data is organized in two-dimensional tables consisting of relational, i.e. no pointers are maintained between tables.
8. WHAT IS DATA MODELING?
Ans: Data Modeling describes relationship between the data objects. The relationships between the collections of data in a system may be graphically represented using data modeling.
9. DEFINE ENTITY, ATTRIBUTE AND RELATIONSHIP.
Ans: Entity: An Entity is a thing, which can be easily identified. An entity is any object, place, person, concept or activity about which an enterprise records data.
Attribute: An attribute is the property of a given entity.
Relationship: Relationship is an association among entities.
10. WHAT IS ER-MODELING?
Ans: The E-R modeling technique is the Top Down Approach. Entity relationship is technique for analysis and logical modeling of a system’s data requirements. It is the most widely used and has gained acceptance as the ideal database design. It uses three basic units: entities, their attributes and the relationship that exists between the entities. It uses a graphical notation for representing these.
11. WHAT IS NORMALIZATION?
Ans: Normalization is a step-by-step decomposition of complex records into simple records.
12. WHAT ARE VARIOUS NORMAL FORMS OF DATA?
Ans: The First Normal Form 1NF,
The Second Normal Form 2NF,
The Third Normal Form 3NF,
The Boyce and Codd Normal Form BC NF.
13. WHAT IS DENORMALIZATION?
Ans: The intentional introduction of redundancy to a table to improve performance is called DENORMALIZATION.
14. WHAT ARE 1-TIER, 2-TIER, 3-TIER OR N-TIER DATABASE ARCHITECTURES?
Ans: 1-Tier Database Architecture is based on single system, which acts as both server and client.
2-Tier Architecture is based on one server and client.
3-Tier Architecture is based on one server and client out that on client act as a remote system.
N-Tier Architecture is based on N no. Of servers and N no. Of clients.
15. WHAT ARE A TABLE, COLUMN, AND RECORD?
Ans: Table: A Table is a database object that holds your data. It is made up of many columns. Each of these columns has a data type associated with it.
Column: A column, referred to as an attribute, is similar to a field in the file system.
Record: A row, usually referred to as tuple, is similar to record in the file system.
16. WHAT IS DIFFERENCE BETWEEN A PROCEDURAL LANGUAGE AND A NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language NON-Procedural Language
A program in this implements a step-by-step algorithm to solve the problem. It contains what to do but not how to do
17.WHAT TYPE OF LANGUAGE "SQL" IS?
Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts what to do rather than how to do any process.
18. CLASSIFICATION OF SQL COMMANDS?
Ans:
DDL (Data Definition Language) DML (Data Manipulating Language) DCL (Data Control Language) DTL(Data Transaction Language)
Create Alter Drop Select Insert Update Delete Rollback Commit Grant Revoke
19. WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly whereas For DML commands autocommit is to be turned ON explicitly.
20. WHAT IS DIFFERENCE BETWEEN A TRANSACTION AND A QUERY?
Ans: A Transaction is unit of some commands where as Query is a single line request for the information from the database.
21. WHAT IS DIFFERENCE BETWEEN TRUNCATE AND DELETE COMMANDS?
Ans: Truncate Command will delete all the records where as Delete Command will delete specified or all the records depending only on the condition given.
22. WHAT IS DIFFERENCE BETWEEN UPDATE AND ALTER COMMANDS?
Ans: Alter command is used to modify the database objects where as the Update command is used to modify the values of a data base objects.
23. WHAT ARE COMMANDS OF TCL CATEGORY?
Ans: Grant and Revoke are the two commands belong to the TCL Category.
24. WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using this command we can delete only those records that are not really required.
25. WHAT ARE RULES FOR NAMING A TABLE OR COLUMN?
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character from your database character set and the characters $ and #. But these characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace. Objects in the name space must have different names.
7) A name can be enclosed in double quotes.
26. HOW MANY COLUMNS CAN A TABLE HAVE?
Ans: A Table can have 1000 columns.
27. WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values,
Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values,
Date data type for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.
28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB LONG
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type. 1) The maximum size is 2GB. 2) LONGs cannot. 3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table
29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store variable no. Of characters and while querying the table varchar2 trims the extra spaces from the column and fetches the rows that exactly match the criteria.
30. HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT DATE FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes Memory.
Default Date Format is: DD-MON-YY.
31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?
Ans:
Datatype Range
Char Varchar2 Number Float LONG, RAW, LONGRAW Large Objects (LOB’s) 2000 bytes 4000 bytes Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary precision 2 GB 4GB
32. HOW TO RENAME A COLUMN?
Ans: We can’t rename a Column of a table directly. So we follow the following steps.
To Rename a Column:
a) Alter the table specifying new column name to be given and data type.
b) Then copy the values in the column to be renamed into new column.
c) drop the old column.
33. HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a column
i. Truncate the table first.
ii. Alter the table column whose size is to be decreased using the same name and data type but new size.
34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.
35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.
36. WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?
Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can’t be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.
37. WHAT IS A COMPOSITE PRIMARY KEY?
Ans: A Primary key created on combination of columns is called Composite Primary Key.
38. WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE POSSIBLE PER COMPOSITE PRIMARY KEY?
Ans:
39. HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero nor a space and any mathematical calculation with NULL is always NULL.
40. WHAT IS NULL? A CONSTRAINT OR DEFAULT VALUE?
Ans: It is a default value.
41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using
References)is called as a child table(detail table) and a table which is being referred is called Parent (Master) Table .
47. HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate….
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified order(ascending,descending) by default it takes ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator.
58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest value from the set and compares a value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query produces any result then it evaluates it to TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once for each ‘Candidate row’ by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column value used in inner query refers to the column value present in the outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get query results.
There are four types of Joins namely
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining tables.
62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right) based on the requirement.
63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas
EQUI JOIN is made between different tables having common column.
64. WHAT ARE "SET" OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL"
OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will return even duplicate values.
66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER
FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.
69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an 'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE ()
FUNCTIONS?
Ans: Translate() is a superset of functionality provided by Replace().
71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas
Instr() return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC and the given date.
73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.
75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no physical data and manipulates the base table. View is saved as a select statement in the database and contains no physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based on more than one table) cannot be modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use like a view inside a SQL statement. It is not a schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be
Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick access to rows in a table. An Index increases the performance of the database.
82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers
to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a method of storing tables that are intimately related and are often joined together into the same area on the disk. When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or package.
86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas any user can reference the Public synonym.
87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTE
90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges.
Create, Select, Insert, Alter etc are Object Privileges.
91. WHAT FOR DCL COMMANDS ARE?
Ans: Commit, Rollback are DCL commands.
92. WHAT FOR GRANT COMMAND WITH "WITH GRANT OPTION"?
Ans: “With Grant Option” with Grant Command gives privileges to the user to grant privileges to other user(s) among the privileges he/she has.
93. HOW TO CHANGE PASSWORD OF A USER?
Ans: Using Password command or
Using ALTER USER
94. WHAT IS A SCHEMA AND SCHEMA OBJECTS?
Ans: A schema is a collection of logical structures of data, or schema objects. A schema is owned by the database user and has the same name as that of user. Each user owns a single schema. Schema objects include following type of objects Clusters, Database Links, Functions, Indexes, Packages, Procedures, Sequences, Synonyms, Tables, Database Triggers, Views.
**95. HOW TO STARTUP AND SHUTDOWN ORACLE DATABASE?
Ans: Startup and Shutdown Oracle database can be done by only the administator. Startup is done by using STARTUP command and Shutdown is done by SHUTDOWN command
96. WHAT IS A SESSION?
Ans: The period between Login and Logoff on schema.
97. WHAT IS A CLIENT PROCESS? WHAT IS A SERVER PROCESS?
Ans: ref: 172 Q & A.
98. HOW TO MAKE EVERY DML OPERATION AS AUTO COMMIT?
Ans: By using SET AUTOCOMMIT ON command.
99. HOW TO DISPLAY DATA PAGE WISE IN SQL?
Ans: By using SET PAUSE ON command.
100. HOW TO CHANGE LINE SIZE, PAGE SIZE AND SQL PROMPT?
Ans: By using SET LINESIZE
SET SQLPROMPT
101. HOW PL/SQL IS DIFFERENT FROM SQL?
Ans: SQL is non-procedural language whereas PL/SQL is procedural language that includes features and design of programming language.
102. WHAT IS ARCHITECTURE OF PL/SQL?
Ans:
103. WHAT IS A PL/SQL BLOCK?
Ans: DECLARE
BEGIN
EXCEPTION
END;
104. WHAT ARE DIFFERENT TYPES OF PL/SQL BLOCKS?
Ans: DECLARE BLOCK: In this block all the declarations of the variable used in the program is made. If no variables are used this block will become optional.
BEGIN BLOCK: In this block all the executable statements are placed.
This block is Mandatory.
EXCEPTION BLOCK: In this block all the exceptions are handled.
This block is also very optional.
END: Every begin must be ended with this END; statement.
105. WHAT ARE COMPOSITE DATA TYPES?
Ans: Records, Tables are two Composite data types.
106. WHAT IS SCOPE OF A VARIABLE IN PL/SQL BLOCK?
Ans: The visuability and accessibility of a variable within the block(s) is called scope of a variable.
107. WHAT IS A NESTED BLOCK?
Ans: A block within a block is called Nested Block.
108. WHAT IS A PL/SQL ENGINE?
Ans: The PL/SQL engine accepts any valid PL/SQL block as input, executes the procedural part of the statements and sends the SQL statements to the SQL statement executor in the Oracle server.
109. WHAT IS DEFAULT VALUE FOR A NUMERIC PL/SQL VARIABLE?
Ans: NULL
110. WHAT IS DIFFERENCE BETWEEN SIMPLE LOOP AND A FOR LOOP?
Ans: Simple requires declaration of variables used in it and exit condition but For Loop doesn’t require this.
111. WHAT IS A CURSOR? STEPS TO USE A CURSOR?
Ans: Cursor is Private SQL area in PL/SQL.
Declare the Cursor,
Open the Cursor,
Fetch values from SQL into the local Variables,
Close the Cursor.
112. HOW MANY TYPES OF CURSORS ARE SUPPORTED BY ORACLE?
Ans: There are two types of cursors namely Implicit Cursor, Explicit Cursor.
113. WHAT IS A CURSOR FOR LOOP?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.
114. WHAT ARE CURSOR ATTRIBUTES?
Ans: %Found
%NotFound
%IsOpen
%RowCount are the cursor attributes.
115. WHAT IS USE OF CURSOR WITH "FOR UPDATE OF" CLAUSE?
Ans: This Clause stop accessing of other users on the particular columns used by the cursor until the COMMIT is issued.
116. WHAT IS AN EXCEPTION? HOW IT IS DIFFERENT FROM ERROR?
Ans: Whenever an error occurs Exception raises.
Error is a bug whereas the Exception is a warning or error condition.
117. NAME SOME BUILT-IN EXCEPTIONS.
Ans: Too_Many_Rows
No_Data_Found
Zero_Divide
Not_Logged_On
Storage_Error
Value_Error etc.
118. HOW TO CREATE A USER-DEFINED EXCEPTION?
Ans: User-Defined Exception is created as follows:
DECLARE
- - - - - - - - - ;
- - - - - - - - -;
BEGIN
- - - - - - - - -;
- - - - - - - - -;
RAISE
EXCEPTION
WHEN
- - - - - - - - -;
- - - - - - - - -;
END;
119. WHAT IS "OTHERS" EXCEPTION?
Ans: It is used to along with one or more exception handlers.
This will handle all the errors not already handled in the block.
120. WHAT IS SCOPE OF EXCEPTION HANDLING IN NESTED BLOCKS?
Ans: Exception scope will be with in that block in which exception handler is written.
121. WHAT IS A SUB-PROGRAM?
Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE INVOKED BY TAKING PARAMATERS.
122. WHAT ARE DIFFERENT TYPES OF SUB-PROGRAMS?
Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.
123. HOW A PROCEDURE IS DIFFERENT FROM A FUNCTION?
Ans: Function has return key word and returns a value whereas a Procedure doesn’t return any value.
124. WHAT ARE TYPES OF PARAMETERS THAT CAN BE PASSED TO FUNCTION OR PROCEDURE?
Ans: IN, IN OUT, OUT.
125. WHAT IS "IN OUT" PARAMETER?
Ans: A parameter, which gets value into the Procedure or Function and takes the value out of the Procedure or Function area, is called IN OUT parameter.
126. DOES ORACLE SUPPORTS PROCEDURE OVERLOADING?
Ans: NO.
127. WHAT IS A PACKAGE AND PACKAGE BODY?
Ans: Package is declarative part of the functions and procedures stored in that package and package body is the definition part of the functions and procedures of that package.
128. WHAT IS ADVANTAGE OF PACKAGE OVER PROCEDURE OR FUNCTION?
Ans: Packages provides Functions or Procedures Overloading facility and security to those Functions or Procedures.
129. IS IT POSSIBLE TO HAVE A PROCEDURE AND A FUNCTION WITH THE SAME NAME?
Ans: NO if it is out side a Package, YES if it is within a Package.
130. DOES ORACLE SUPPORTS RECURSIVE FUNCTION CALLS?
Ans: YES.
131. WHAT IS A TRIGGER? HOW IT IS DIFFERENT FROM A PROCEDURE?
Ans: Trigger: A Trigger is a stored PL/SQL program unit associated with a specific database table.
Procedure: A Procedure is to be explicitly called by the user whereas Triggers are automatically called implicitly by Oracle itself whenever event Occurs.
132. WHAT IS DIFFERENCE BETWEEN A TRIGGER AND A CONSTRAINT?
Ans: Constraints are always TRUE whereas Triggers are NOT always TRUE and Constraints has some limitations whereas Trigger has no limitations.
133. WHAT ARE DIFFERENT EVENTS FOR A TRIGGER AND THEIR SCOPES?
Ans: Insert, Update or Delete.
134. WHAT IS DIFFERENCE BETWEEN TABLE LEVEL AND ROW LEVEL TRIGGERS?
Ans: Table level Triggers execute once for each table based transaction whereas Row level Triggers will execute once FOR EACH ROW.
** 135. WHAT ARE AUTONOMOUS TRIGGERS?
Ans:
136. WHAT IS AN "INSTEAD OF" TRIGGER?
Ans: These Triggers are used with the Complex Views only to make possible of Insert, Update and Delete on those Views.
** 137. HOW MANY TRIGGERS CAN BE CONFIGURED ON A TABLE AND VIEW?
Ans:
138. WHAT IS "TABLE MUTATING" ERROR? HOW TO SOLVE IT?
Ans: ORA-04091: Table name is mutating, trigger/function may not see it
Cause : A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger.
Action : Rewrite the trigger or function so it does not read the table.
139. WHEN TO USE ":NEW" AND ":OLD" SPECIFIERS?
Ans: The prefix :old is used to refer to values already present in the table. The prefix :new is a correlation name that refers to the new value that is inserted / updated.
** 140. WHAT IS A CONDITIONAL TRIGGER?
Ans:
** 141. HOW TO CREATE A USER-DEFINED VARIABLE IN PL/SQL?
Ans:
142. HOW TO CREATE AN ARRAY VARIABLE IN PL/SQL?
Ans: Using CREATE [OR REPLACE] TYPE
AS VARRAY (size) OF ELEMENT_TYPE (NOT NULL) Command;
**143. HOW TO MAKE A USER-DEFINED DATA TYPE GLOBAL IN PL/SQL?
Ans:
144. HOW TO CREATE AN OBJECT IN ORACLE?
Ans: Using CREATE [OR REPLACE] TYPE
145. WHAT IS A TRANSIENT AND PERSISTENT OBJECT?
Ans: The Object created in a table is called Persistent Object.
Object created on execution of PL/SQL block is called Transient Object.
**146. WHAT IS A COLUMN OBJECT AND TABLE OBJECT?
Ans: A Column Object is only a Column of a table.
147. HOW TO GRANT PERMISSION ON AN OBJECT TO OTHER USER?
Ans: GRANT
148. WHAT IS A COLLECTION OF ORACLE?
Ans: Varray, Nested Table is a collection of Oracle.
149. WHAT IS DIFFERENCE BETWEEN VARRAY AND NESTED TABLE?
Ans: Varray has a fixed size.
Nested tables can carry any number of values.
150. HOW TO MODIFY CONTENTS OF A VARRAY IN ORACLE?
Ans: To modify a stored VARRAY it has to selected into a
PL/SQL variable and then inserted back into the table.
151. WHAT IS USE OF "THE" OPERATOR FOR NESTED TABLE?
Ans: THE operator allows nested tables to be manipulated using DML when it is stored in a Table.
152. WHICH PACKAGE IS USED FOR FILE INPUT/OUTPUT IN ORACLE?
Ans: UTL_FILE Package is used for File input/output in Oracle.
153. NAME SOME METHODS AND PROCEDURES OF FILE I/O PACKAGE?
Ans: FOPEN
FCLOSE
FFLUSH
IS_OPEN
GET_LINE
PUT_LINE
PUTF
NEW_LINE
**154. WHAT IS SQLJ? HOW IT IS DIFFERENT FROM JDBC CONNECTIVITY?
Ans: SQLJ is basically a Java program containing embedded static SQL statements that are compatible with Java design philosophy.
155. WHAT IS AN ITERATOR? Name some TYPES OF ITERATORS?
Ans: SQLJ Iterators are basically record groups generated during transaction, which requires manipulation of more than one records from one or more tables. There are two types Iterators namely Named Iterator and Positional Iterator.
** 156. WHAT ARE DIFFERENT STEPS TO WRITE A DYNAMIC SQL PROGRAM?
Ans:
Eg: char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
EXEC SQL EXECUTE readytogo;
157. WHAT IS TABLE PARTITIONING AND INDEX PARTITIONING?
Ans: Oracle8 allows tables and Indexes to be partitioned or broken up into smaller parts based on range of key values. Partitioning is a “divide and conquer” strategy that improves administration and performance in data warehouse and OLTP systems.
158. WHAT IS PARALLEL PROCESSING?
Ans:
159. WHAT IS PHYSICAL MEMORY STRUCTURE OF ORACLE?
Ans: The basic oracle memory structure associated with Oracle includes:
Software Code Areas
The System Global Area (SGA)
The Database Buffer Cache
The shared Pool
The Program Global Areas (PGA)
Stack Areas
Data Areas
Sort Areas
160. WHAT IS LOGICAL MEMORY STRUCTURE OF ORACLE?
DB_STG
STUDENT SYSTEM
EMP DEPT EMP_IND ….. ..
DATA DATA INDEX
Ans: Database
Tablespace
DB Object
Segment
Extends
161. WHAT IS SGA?
Ans: A System Global Area is a group of shared memory allocated by Oracle that contains data and control information for one Oracle database instance. IF the multiple users are concurrently connected to the same instance, the data in the instance’s SGA is “shared” among the users. Consequently, the SGA is often referred to as either the “system Global Area” or the “Shared Global Area”.
162. WHAT IS PGA?
Ans: The Program Global Area is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the configuration of Oracle.
163. WHAT IS AN ORACLE INSTANCE?
Ans: Every time a database is started, an SGA is allocated and Oracle background processes are started. The combination of these processes and memory buffers is called an Oracle instance.
164. WHAT ARE DIFFERENT ORACLE PROCESSES?
Ans: A process is a “thread of control” or a mechanism in an operating system that can be execute a series of steps. Some operating systems use terms jobs or task. A process normally has its own private memory area in which it runs. An Oracle database system has general types of process: User Processes and Oracle Processes.
**165. WHAT IS DIFFERENCE BETWEEN PMON AND SMON?
Ans: SMON (System Monitor) performs instance recovery at instance of startup. In a multiple instance system (one that uses the parallel server), SMON of one instance can also perform instance recovery other instance that have failed whereas The PMON (Process Monitor) performs process recovery when a user process fails.
**166. WHAT IS DIFFERENCE BETWEEN DATABASE AND TABLESPACE?
Ans:
167. WHAT IS JOB OF DATABASE WRITER (DBWR) PROCESS?
Ans: The Data Base Writer writes modified blocks from the database buffer cache to the data files.
168. WHAT IS JOB OF LOG WRITER (LGWR) PROC*SS?
Ans: The Log Writer writes redo log files to disk. Redo log data is generated in the redo log buffer of the SGA. As transactions commit and log buffer fills, LGWR writes redo entries into an online redo log file.
169. WHAT IS RECOVERER?
Ans: The Recover (RECO) is used to resolve distributed transactions that are pending due to network or system failure in a distributed database. At timed intervals, the local RECO attempts to concept to remote database and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
170. WHAT IS ARCHIVER?
Ans: The Archiver (ARCH) copies the online redo log files to archival storage when they are full. ARCH is active only when a
database’s redo log is used ARCHILOG mode.
** 171. WHAT IS A STORED QUERY?
Ans:
172. WHAT IS USER PROCESS AND SERVER PROCESS?
Ans: A User process is created and maintained to execute the software code of an application program (such as PRO * Program) or an ORACLE tool (such as SQL * DBA). The User process also manages the communication with server processes. User processes communication with the server processes through the program interface.
Other processes call ORACLE processes. In a dedicated server configuration, a server
Process handles requests for a single user process. A multithread server configuration allows many user processes to share a small number of server processes, minimizing the utilization of available system resources.
**173. WHAT IS A SELF REFERENTIAL INTEGRITY?
Ans:
174. WHAT IS A "RAISE" STATEMENT?
Ans: It is used to Raise Exceptions.
175. WHAT IS ROWID? HOW IT IS DIFFERENT FROM ROWNUM?
Ans: Rowid is the address of the row at where it is stored in the database. Rownum is count of records whereas Rowid is identification of the each row.
Reports FAQs
1) What is ANCHOR?
A) These are used to determine the vertical and horizontal positioning of the child object relative to its position.
Tool which we will use in the layout to destroy the field horizontally or vertically whenever field gets null value.
When we have multiple fields in layout if any one of the field gets null value the next field should gets automatically adjusted in that place that time we will use anchor.
2) What is frame?
A) Frame is the place in the layout model where we place an object to display only once in the report output.
3) What is repeating frame?
A) It is also a place in the layout editor where we place an object to display repeatedly
In report output.
4) What are action, format and validation triggers?
A) ACTION TRIGGERS: action triggers are pl/sql procedures executed when button is selected
EX: calling a report
FORMAT TRIGGERS: format triggers are pl/sql functions which we will use to display layout object in the report.
The return type is always true or false.
EX: no data found we will write a validation trigger for this boiler plate text.
VLIDTION TRIGGERS: we will use to validate the lov values in the parameter form.
5) What are Report Triggers?
A) Report triggers are 5 types. They are
1) Before parameter form.
2) After parameter form.
3) Before report trigger.
4) Between pages.
5) After Report trigger
6) What is the difference between after parameter form and before report trigger?
A) After parameter for will fire in Parsed time.
Before report trigger will fire in the execution time.
7) What are Bind and Lexical parameters?
A Bind parameter: Bind parameter is a variable which we will use to pass the vlue.
‘:’ before any variable in a query is called as bind variable.
Lexical parameters: Lexical parameter is a parameter which we will use inside of a query. These parameters we can use any where inside of query.
EX: select, from, where, order by
8) What are system parameters?
A) There are around 78 system parameters. Some of them are
1) Background
2) Currency
3) Copies
4) Decimal
5) Desformat
6) Desname
7) Destype
8) Mode
9) Orientation
10) Destintion
9) What are formula, summary and placeholder columns?
A) Formula column: It is a pl/sql block which will calculate a value based on our logic and it will return one value.
Summary column: It is a pl/sql block which we will use to print the result of aggregate functions like sum, avg and count either at page level or at report level.
Placeholder column: It is a column which will have the data type and value. It works like global variable in reports.
If we want to return more than one value then we will use placeholder column in formula column.
10) How to run a report from a report?
A) We will place button in the repeating frame of layout editor. And in that button code we will write code as follows
Srw.run_report (‘path of .rdf file’, paramform = no, dept = “‘||:deptno||’”’);
Save the report and generate it.
11) What is user exit?
A) User exit is an program which will transfer the control from report execution to another third generation language it will the required data & it will complete the remaining report execution process.
12) What is srw.do_sql, srw.message, srw.referene.srw.program_abort?
A) srw.do_sql: used to execute DDL commands in reports.
Syntax: srw.do_sql (‘create table tname’);
Srw.message: used to display message in reports.
Srw.reference: used to refer the variables in formula columns.
Srw.program_abort: This exception stops report execution and raises the following exceptions.
Rep-1419 pl/sql program aborted
13) How to execute DDL commands in reports?
A) We will execute the DDL commands in reports by using the following user exit.
Srw.do_sql (‘create table tname ()’);
14) How to change the layout dynamically?
A) A lay out can be changed dynamically by passing the parameters.
15) How to implement lexical parameters in reports?
A) & before any parameter is called as lexical parameters. We can use these parameters in any class of the query
Ex: select, where, order by
16) What is the report global variable?
A) Place holder column is the report global variable. We can return more than one value by using place holder column.
17) What is matrix report?
A) Display information row ,column and cell format
18) The report output is 10 pages then how many times between pages report trigger will fire?
A) It will fire 8 times. Between pages report trigger will not fire for 1st and last page.
19) Report will not have any parameters then before and after parameters will fire or not?
A) yes
A) These are used to determine the vertical and horizontal positioning of the child object relative to its position.
Tool which we will use in the layout to destroy the field horizontally or vertically whenever field gets null value.
When we have multiple fields in layout if any one of the field gets null value the next field should gets automatically adjusted in that place that time we will use anchor.
2) What is frame?
A) Frame is the place in the layout model where we place an object to display only once in the report output.
3) What is repeating frame?
A) It is also a place in the layout editor where we place an object to display repeatedly
In report output.
4) What are action, format and validation triggers?
A) ACTION TRIGGERS: action triggers are pl/sql procedures executed when button is selected
EX: calling a report
FORMAT TRIGGERS: format triggers are pl/sql functions which we will use to display layout object in the report.
The return type is always true or false.
EX: no data found we will write a validation trigger for this boiler plate text.
VLIDTION TRIGGERS: we will use to validate the lov values in the parameter form.
5) What are Report Triggers?
A) Report triggers are 5 types. They are
1) Before parameter form.
2) After parameter form.
3) Before report trigger.
4) Between pages.
5) After Report trigger
6) What is the difference between after parameter form and before report trigger?
A) After parameter for will fire in Parsed time.
Before report trigger will fire in the execution time.
7) What are Bind and Lexical parameters?
A Bind parameter: Bind parameter is a variable which we will use to pass the vlue.
‘:’ before any variable in a query is called as bind variable.
Lexical parameters: Lexical parameter is a parameter which we will use inside of a query. These parameters we can use any where inside of query.
EX: select, from, where, order by
8) What are system parameters?
A) There are around 78 system parameters. Some of them are
1) Background
2) Currency
3) Copies
4) Decimal
5) Desformat
6) Desname
7) Destype
8) Mode
9) Orientation
10) Destintion
9) What are formula, summary and placeholder columns?
A) Formula column: It is a pl/sql block which will calculate a value based on our logic and it will return one value.
Summary column: It is a pl/sql block which we will use to print the result of aggregate functions like sum, avg and count either at page level or at report level.
Placeholder column: It is a column which will have the data type and value. It works like global variable in reports.
If we want to return more than one value then we will use placeholder column in formula column.
10) How to run a report from a report?
A) We will place button in the repeating frame of layout editor. And in that button code we will write code as follows
Srw.run_report (‘path of .rdf file’, paramform = no, dept = “‘||:deptno||’”’);
Save the report and generate it.
11) What is user exit?
A) User exit is an program which will transfer the control from report execution to another third generation language it will the required data & it will complete the remaining report execution process.
12) What is srw.do_sql, srw.message, srw.referene.srw.program_abort?
A) srw.do_sql: used to execute DDL commands in reports.
Syntax: srw.do_sql (‘create table tname’);
Srw.message: used to display message in reports.
Srw.reference: used to refer the variables in formula columns.
Srw.program_abort: This exception stops report execution and raises the following exceptions.
Rep-1419 pl/sql program aborted
13) How to execute DDL commands in reports?
A) We will execute the DDL commands in reports by using the following user exit.
Srw.do_sql (‘create table tname ()’);
14) How to change the layout dynamically?
A) A lay out can be changed dynamically by passing the parameters.
15) How to implement lexical parameters in reports?
A) & before any parameter is called as lexical parameters. We can use these parameters in any class of the query
Ex: select, where, order by
16) What is the report global variable?
A) Place holder column is the report global variable. We can return more than one value by using place holder column.
17) What is matrix report?
A) Display information row ,column and cell format
18) The report output is 10 pages then how many times between pages report trigger will fire?
A) It will fire 8 times. Between pages report trigger will not fire for 1st and last page.
19) Report will not have any parameters then before and after parameters will fire or not?
A) yes
Oracle Purchasing Tables
ORACLE PURCHASING TABLES
segment1 - is the system–assigned number you use to identify in forms and reports.
Table Name Columns
PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID, PREPARER_ID, SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG
stores information about requisition headers. You need one row for each requisition header you
create. Each row contains the requisition number, preparer, status, and description.SEGMENT1 is the number you use to identify the requisition in forms and reports(unique).
PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID,REQUISITION_HEADER_ID,
LINE_NUM,LINE_TYPE_ID,CATEGORY_ID,
ITEM_DESCRIPTION,UNIT_MEAS_LOOKUP_CODE ,
UNIT_PRICE, QUANTITY, DELIVER_TO_LOCATION_ID,
TO_PERSON_ID, SOURCE_TYPE_CODE
stores information about requisition lines.line number, item number, item category, item description,
need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.
LINE_LOCATION_ID - purchase order shipment line on which you placed the requisition. it is null if you
have not placed the requisition line on a purchase order.
BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement
or catalog quotation line information for the requisition line.
PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you
exploded or multisourced this requisition line.
PO_HEADERS_ALL PO_HEADER_ID, AGENT_ID, TYPE_LOOKUP_CODE,
SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG
information for your purchasing documents.There are six types of documents that use PO_HEADERS_ALL
RFQs, Quotations, Standard purchase orders, Planned purchase orders, Blanket purchase orders, Contracts
can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using
PO_HEADER_ID.BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders.
if we use copy document Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID.
PO_LINES_ALL PO_LINE_ID, PO_HEADER_ID, LINE_TYPE_ID, LINE_NUM
stores current information about each purchase order line. CONTRACT_NUM reference a contract
purchase order from a standard purchase order line.
PO_VENDORS VENDOR_ID, VENDOR_NAME, SEGMENT1,
SUMMARY_FLAG, ENABLED_FLAG
information about your suppliers.purchasing, receiving, payment, accounting, tax, classification, and general information.
PO_VENDOR_SITES_ALL VENDOR_SITE_ID, VENDOR_ID, VENDOR_SITE_CODE
information about your supplier sites.a row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this
information to store supplier address information.
PO_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID, PO_HEADER_ID, PO_LINE_ID,LINE_LOCATION_ID, SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,QUANTITY_ORDERED,
DISTRIBUTION_NUM
contains accounting distribution information fora purchase order shipment line.You need one row for
each distribution line you attach to a purchase order shipment.
There are four types of documents using distributions in Oracle Purchasing:
Standard Purchase Orders, Planned Purchase Orders, Planned Purchase Order Releases, Blanket Purchase Order Releases
includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution.
PO_RELEASES_ALL PO_RELEASE_ID, PO_HEADER_ID, RELEASE_NUM,
AGENT_ID, RELEASE_DATE
contains information about blanket and planned purchase order releases. You need one row for each release you issue
for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment
PO_VENDOR_CONTACTS VENDOR_CONTACT_ID, VENDOR_SITE_ID
stores information about contacts for a supplier site. You need one row for each supplier contact you define.
Each row includes the contact name and site.
PO_ACTION_HISTORY OBJECT_ID, OBJECT_TYPE_CODE , OBJECT_SUB_TYPE_CODE, SEQUENCE_NUM
information about the approval and control history of your purchasing documents. There is one record in
this table for each approval or control action an employee takes on a purchase order, purchase agreement, release, or requisition.
stores object_id -- Document header identifier,OBJECT_TYPE_CODE --- Document type, OBJECT_SUB_TYPE_CODE --Document subtype SEQUENCE_NUM --Sequence of the approval or control action for a document
PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID, REQUISITION_LINE_ID, SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,REQ_LINE_QUANTITY,
DISTRIBUTION_NUM
stores information about the accounting distributions associated with each requisition line.
PO_LINE_LOCATIONS_ALL LINE_LOCATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, PO_HEADER_ID, PO_LINE_ID, SHIPMENT_TYPE
contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line. There are seven types of documents that use shipment schedules:
RFQs,Quotations,Standard purchase orders,Planned purchase orders,Planned purchase order releases,Blanket purchase orders, Blanket purchase order releases
Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses
this information to record delivery schedule information for purchase orders, and price break information for
blanket purchase orders, quotations and RFQs.
Pl/Sql FAQs
PL/SQL DOCUMENTATION
1.EXPLAIN PL/SQL, PL/SQLBLOCK?
PL/SQL is a procedural language that has both interactive SQL and procedural language constructs such as iterations, conditional branching.
PL/SQL block is a block-structured language. Each block is supposed to perform one logical unit of job.
2.MAIN BLOCKS OF PL/SQL?
PL/SQL block is having 3 parts
· Declaration part
· Executable part
· Exception handling part
Declaration part: PL/SQL enforces the declaration of variables before their use in executable portion.
All variables (or) constants have to be declared in this block.
Syntax: variable name data type;
Executable part: this is main section of the block, all the procedural and SQL statements are defined here.
Exception handling part: this is used for responding to runtime errors encountered by a program.
MANDATORY BLOCKS IN PL/SQL: executable block in PL/SQL is the mandatory block other two blocks declarative and exception blocks are optional blocks.
3.WHAT IS NAMED BLOCK?
Named blocks are the blocks that have a name associated with 3 types
· Labeled blocks: blocks with a label that gives the block name.
· Sub program: consists of procedures and functions.
· Triggers: consists of pl/sql block that is associated with an event that occurs in the database.
4.WHAT IS ANONYMOUS BLOCK?
These blocks are generally constructed dynamically and execute only once.
Block is often issued from a client program to call a sub program in the database.
5.EXPLAIN % TYPE, %ROWTYPE IN PL/SQL?
% TYPE: it is used to give data type of predefined variable and database column.
Ex: declare
Item code number (10);
I code item code% type;
%ROWTYPE: it is used to provide record data type to a variable.
The variable can stop row of the table (or) row fetched from the cursor.
6.DATA TYPES IN PL/SQL?
· Scalar data type: number, character, Boolean, date/time.
· Composite data type: table, record.
· Reference data type: ref cursor.
· Lob types: (large objects) Bfile (variable stores locator of the file)
BLob (for storing large raw data like graphics or sound data)
Clob(stores location, which provides location of data)
7.EXPLIAIN PL/SQL TABLES, VARRAYS, NESTED TABLES?
PL/SQL TABLES: these are temporary array like objects used in pl/sql block.
These can have one column & a primary key.
These are declared in the declarative part of any block, sub program
Or package.
Syntax: STEP1: type is table of
STEP2:
NESTED TABLES: similarly to PL/SQL block along with adding the ability to store
nested tables within a database table will be there.
Syntax: type table name is table of table type;
VARRAYS: this are implemented differently, elements are inserted into varray starting at index1 upto maximum length declared in varray.
Syntax: type type-name is varry(max-size) of element-type(not null);
8.CAN WE PASS PL/SQL TABLE AS APARAMETER TO ANOTHER PROCEDURE OR NOT, IF SOHOW WILL IT BE PASSED? GIVE THE SYNTAX?
9.EXPLAIN AUTONOMOUS TRANSACTION, RESTRICT-REFERENCE AND EXCEPTION-INIT?
AUTONOMOUS TRANSACTION: it is used when ever in a transaction with in another transaction should be committed or rollback irrespective of parent transaction commit or rollback.
RESTRICT-REFERENCE: it is used to assert the purity level for the user-defined functions.
Syntax: PRAGMA RESTRICT-REFERENCES (function-name, [rnds], [wnds], [rnps], [wnps])
EXCEPTION-INIT: used for associating a named exception with in a particular oracle error
Syntax: PRAGMA EXCEPTION-INIT (exception-name, oracle error number)
10.WHAT IS PRAGMA?
Pragmas are compiler directives, it serves as instructions to the pl/sql compiler.
The compiler will act on the pragma during the compilation of the block.
11.WHAT IS EXCEPTION HANDLING IN PL/SQL& TYPES OF EXCEPTION?
Exception handling is used to handle the errors according to users way and functions
It will be used to generate error messages and replacing default messages.
These are 2 types standard & user defined exceptions.
STANDARD EXCEPTIONS 0R BUILT IN EXCEPTIONS: -
Oracle package standard had defined exceptions for certain common errors
Some of them are:
· Too-many-rows (ora-01422)
· No-data-found (ora-1403)
· Value-error (ora-06502)
· Zero-divide (ora-01476)
· Invalid-number (ora-01722)
· Dup-value-on-index (ora-00001)
· Program-error (ora-06501)
USER DEFINED EXCEPTIONS: The user defines these exceptions and these are used to take care of abnormal conditions that are application specific.
12.EXPLAIN SQL CODE & SQLERRM?
These are functions, which return error code and error message of the recent error.
SQL CODE: it returns error code as negative number.
For NO-DATA-FOUND it returns ‘+100’.
SQL ERRRM: it returns length of the messages in 512 characters, which includes code, message, tablename&column name.
Syntax: ercode: =sqlcode;
Er msg: =sqlerrm;
Insert into error table values (errcode, ermsg);
13.WHAT IS RAISE-APPLICATION-ERROR?
This is a procedure used to generate user-defined errors.
Syntax: raise-application-error (errorcode, errormessage, true/false);
Error code- (range is –20000 to-20999).
Error message (length is 2048 bytes).
True/false- true indicates error is put in stack
False is mentioned then the error replace all the previous errors.
14. What is a cursor?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
15. What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
16.For Update Of Clause: -when declaring the cursor itself we will be mentioning for update clause then we can update the records inside of the cursor.
17.Where Current Of Clause: -
18.PROCEDURE: -A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. it may or may not return a value.
Procedures are made up of
· Declaration part
· Executable part
· Exceptional part
Here declarative part and executable part are mandatory while exceptional part is optional.
Syntax: -CREATE OR REPLACE PROCEDURE procedure name {IN, OUT, INOUT}
{IS, AS}
Variable declaration;
Constant declaration;
Begin
PL/SQL subprogram body;
Exception
Exception block;
End;
19.FUNCTION: - A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. It returns a value.
Functions having
Declaration block
Executable block
Exception block
Syntax: - CREATE OR REPLACE FUNCTION function name {IN}
Return data type {IS, AS}
Variable declaration;
Constant declaration;
Begin
PL/SQL subprogram body;
Exception
Exception block;
End;
20.difference between procedure and function?
· Procedures may or may not return a value but function should return a value
· Procedures we cannot use inside of select statement
Functions are used inside of select statement
21.PARAMETER MODES (IN, OUT, INOUT)
IN: - when we pass the parameter in IN mode that will work like a constant inside a procedure.
OUT: -this is used to return a value.
INOUT: -this will be using in both way.
22.ACTUAL PARAMETERS: -while calling the procedure will pass the values this will be calling it as actual parameters
The procedure declaration variables will be receiving these values called FORMAL parameters
23.PROCEDURE OVERLOADING: -multiple procedures that are declared with the same name are called overloading procedures.
24.FUNCTION OVERLOADING: - multiple functions that are declared with the same name are called overloading functions.
25.PROCEDURE, FUNCTION FORWARD DECLARATION: -
26.PACKAGE: -A package is an oracle object, which holds other objects with in it
these objects may be
· 
Procedures
Procedures· Functions
· Cursors , which are logically related.
· Variables
· Constants
27.COMPONENTS OF PACKAGE: -
A package has usually two components
Specification
Body
A package specification declares the types, memory variables, constants, exceptions, cursors and subprograms that are a variable for use.
A package body fully defines cursors, procedures and thus implements the specification.
28.package body with out specification is possible or not?
29.can we define cursor inside with out package? If so how to call the cursor?
30.what is cursor variable?
31. We created specification and body, if we delete specification whether the body will present or not?
32.we have package body and specification inside of the package we are writing procedure to insert some thing to a table, if we delete the table, the above package will valid or not?
33.we have package and we have grants to execute that package inside of that we have table, here we don’t have privileges to this table? Whether this table will execute or not?
34.TRIGGERS:
Trigger is a pl/sql block, which will fire automatically whenever some event occurs like insert, update and delete.
Types of triggers: -
Event
Insert
Update level
Delete
Row level
Time statement level
Before
After
Row level triggers: -it will fire for each row
Statement level triggers: -it will fire only once for the whole statements.
35.can we use DLL command inside of trigger? If not then what is the alternative.
36.TRIGGER PREDICATES?
These are 3 types
· Inserting
· Updating
· Deleting
Whenever we want to do inserting inside of the trigger that time we will be using inserting predicate trigger.llly for updating and deleting.
37. : NEW and :OLD
Both will be used in triggers to get the new and old values.
In case of update both NEW and OLD are valid.
In case of delete only OLD is valid.
In case of insert only NEW is valid.
Both: NEW and :OLD will work only for row level triggers.
Subscribe to:
Posts (Atom)