Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 24 June 2012

Interview Preparation SQL/PLSQL/D2K Part2

What is ‘Pragma’ used in PL/SQL?
A pragma is a directive to the PL/SQL compiler. Pragmas pass information to the compiler; they are processed at compile time but do not execute. If you include a call to a built-in package in a SQL statement, you must include a RESTRICT REFERENCES pragma in your code. This pragma tells the compiler the purity level (freedom from side effects) of a packaged program.
Pragma keyword is used to give instructions to the compiler. There are 4 types of Pragmas:
A)     Exception_Init: - Tells the compiler to associate the specified error number with an identifier that has been declared an Exception in your current program or an accessible package.
B)      Restrict_References: - Tells the compiler the purity level of packaged program. The purity level is the degree to which a program does not read/write database tables and/or packaged variables.
C)      Serially_Reusable: - Tells the runtime engine that package data should not persist between references.  This is used to reduce per-user memory requirements when the package data is only needed for duration of call and not the duration of session.
D)      Autonomous_Transactions: - Tells the compiler that the function, procedure, top-level anonymous P/L SQL block, object method, or database trigger executes in its own transaction space.
Describe Early and Late Binding?
When you generate RPCs (remote procedure calls) using SQL*Module, you have a choice of early binding or late binding. Your choice of early or late binding is controlled by the BINDING option.
When you choose early binding, SQL*Module generates a call to the procedure stored in the database, and also uses a  time stamp that is associated with the call. The time stamp records the date and time (to the nearest second) that the stored procedure was last compiled. The time stamp is created by the Oracle database. If a host application calls the stored procedure through the interface procedure, and the time stamp recorded with the interface procedure is earlier than the time stamp on the stored procedure recorded in the database, an error is returned to the host application in the SQLCODE and/or SQLSTATE status parameter. The SQLCODE error is 4062 "time stamp of name has been changed".
The late binding option, on the other hand, does not use a time stamp. If your application calls a stored procedure that has been recompiled since SQL*Module generated the interface procedure, no error is returned to the application.
The advantages of late binding are
·         Greater flexibility
·         Changes in the stored procedure(s) are transparent to the user
·         Gives behavior similar to interactive SQL (for example, SQL*PLus)
The disadvantages of late binding are
·         There might be additional performance overhead at runtime, due to the necessity of compiling the PL/SQL anonymous block.
·         It is difficult to detect runtime PL/SQL compilation errors in the host application. For example, if the anonymous block that calls the late-bound procedure fails at runtime, there is no convenient way for the host application to determine the cause of the error.
·         The lack of time-stamp capability means that changes, perhaps radical changes, in the stored procedure could be made after the host application was built, and the application would have no way of detecting this.
What are the Back ground processes in Oracle and what are they?
This is one of the most frequently asked question. There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system. The various background processes in oracle are
Data Base Writer(DBWR): Database Writer Writes Modified blocks from Database buffer cache to Datafiles. This is required since the data is not written whenever a transaction is committed.
LogWriter(LGWR):  LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
System Monitor(SMON): The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure
Process Monitor(PMON): The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
CheckPoint(CKPT): At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
Archieves(ARCH): The Archiver copies online redo log files to archival storal when they are busy.
Recoveror(RECO): The Recoveror is used to resolve the distributed  transaction in network.
Dispatcher (Dnnn): The Dispatcher is useful in Multi Threaded Architecture
Lckn: We can have upto 10 lock processes for inter instance locking in parallel sql.
How many types of SQL Statements are there in Oracle?
There are basically 6 types of SQL statments.They are
Data Definition Language(DDL): The DDL statements define and maintain objects and drop objects.
Data Manipulation Language(DML): The DML statements manipulate database data.
Transaction Control Statements (TCL): Manage change by DML
Session Control: Used to control the properties of current session enabling and disabling roles and changing .e.g  Alter Statements, Set Role
System Control Statements: Change Properties of Oracle Instance .e.g:: Alter System
Embedded SQL: Incorporate DDL,DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open, Fetch, execute and close
What is a Transaction in Oracle?
A transaction is a  Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.
Key Words Used in Oracle?
The Key words that are used in Oracle are :
Commit: A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.
Rollback: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
SavePoint: For long transactions that contain many SQL statements, intermediate markers or save points are declared. Save points can be used to divide a transaction into smaller points.
Rolling Forward: Process of applying  redo log during recovery is called rolling forward.
Cursor: A cursor is a handle ( name or a pointer) for the memory associated with a specific statement.  A cursor is basically an area allocated by Oracle for executing the SQL Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
System Global Area(SGA): The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer.
Program Global Area (PGA): The PGA is a memory buffer  that contains data and control information for server process.
Database Buffer Cache: Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.
Redo log Buffer: Redo log Buffer of SGA stores all the redo log entries.
Redo Log Files: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
Process: A Process is a  thread of control or mechanism in Operating System that executes series of steps.
What are Procedure, functions and Packages?
Procedures  and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or  perform set of related tasks.
Procedures do not return values while Functions return at least one value
Packages: Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
Difference between Package Spec & Body
Packages provide a method of encapsulating and storing related procedures, functions and other package constructs as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).
Package specification contains declarations of public constructs where as the package body contains definitions of all those public constructs and declarations & definitions of private constructs.
What are the Packages provided by Oracle?
Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.

Difference between Procedures and Functions?
A procedure is a subprogram that performs a specific action
Procedure Does and Does not return the Value.
Procedure we can use (In, Out, InOut Parameter)
You cannot use the procedure in select Statement.
Execute as a PL/SQL statement
No RETURN clause in the header
Can return none, one, or many values
A function is a subprogram that computes a value
Invoke as part of an expression
Must contain a RETURN clause in the header
Must return a single value
Must contain at least one RETURN statement
Always return the Value.
Function you cannot use the (In, Out, InOut Parameter)
You can use the Function the in select Statement.
What are Database Triggers and Stored Procedures?
Database Triggers: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT’s are useful for implementing complex business rules which cannot be enforced using the integrity rules. We are having the trigger as Before or After Trigger and Statement or Row level Trigger. Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards. Some of the new database triggers are: Startup, Shutdown, Logon, Logoff, Alter, Create, and Drop
Stored Procedures: Stored Procedures are Procedures that are stored in Compiled form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
How many Integrity Rules are there and what are they?
There are Three Integrity Rules. They are as follows:
Entity Integrity Rule: The Entity Integrity Rule  enforces that the Primary key cannot be Null.
Foreign Key Integrity Rule: The Foreign key denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
What are the Various Master and Detail Relationships?
The various Master and Detail Relationship are
a) Non-Isolated: The Master cannot be deleted when a child is existing
b) Isolated: The Master can be deleted when the child is existing
c) Cascading: The child gets deleted when the Master is deleted.
What are the Various Block Coordination Properties?
The various Block Coordination Properties are
a) Immediate (Default Setting): The Detail records are shown when the Master Records are shown.
b) Differed with Auto Query: Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deferred with No Auto Query: The operator must navigate to the detail block and explicitly  execute a query
What are the Different Optimization Techniques?
The Various Optimization techniques are
Execute Plan: we can see the plan of the query and change it accordingly based on the indexes
Optimizer_hint: For Example
set_item_property ('DeptBlock', OPTIMIZER_HINT, 'FIRST_ROWS');
Select /*+ First_Rows */ Deptno, Dname, Loc, Rowid from dept where (Deptno > 25)
Optimize_SQL: By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements. This slow downs the processing because for every time the SQL must be parsed whenever they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
Optimize_Tp: By setting the Optimize_Tp= No, Oracle Forms assigns separate cursor only for each query SELECT  statement. All other SQL statements reuse the cursor. For Example:
f45run module = my_firstform userid = scott/tiger optimize_Tp = No
How do you implement the “If” statement in the  “Select” Statement?
We can implement the if statement in the select statement by using the Decode statement. For example:
Select   DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done .
What is SQL Code & SQLERRM?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
How many types of Exceptions are there?
There are 2 types of exceptions. They are
System Exceptions: for Example When no_data_found, When too_many_rows
User Defined Exceptions: For Example My_exception exception When My_exception then
What are the inline and the precompiler directives?
The inline and precompiled directives detect the values directly
How do you use the same LOV for 2 columns?
We can use the same LOV for 2 columns by passing the return values  in global values and using the global values in the code
What is the difference between Static and Dynamic LOV?
The static LOV contains the predetermined values  while the dynamic LOV contains values that come at run time
What are snap shots and views?
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated
What are the OOPS concepts in Oracle?
Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categories the properties by setting the visual attributes and then attach the property classes for the Objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects
What is the difference between candidate key, unique key and primary key?
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the  distinct rows in the table.
Define Artificial (Derived) Primary Key
A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
What is concurrency?
Concurrency is  allowing simultaneous access of same data by different users.  Locks useful for accessing the database are:
Exclusive: The exclusive lock is useful for locking the row when an insert, update or delete is being done. This lock should not be applied  when we do only select from the row.
Share lock: We can do the table as Share_Lock as many share_locks can be put on the same resource.
Privileges and Grants: Privileges  are the right to execute a  particular type of SQL statements. For Example Right to Connect, Right to create, Right to resource where as Grants are given to the objects so that the object might be accessed accordingly. The grant has to be given by the owner of the object.
What is a 2 Phase Commit?
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
Prepare Phase: Global coordinator asks participants to prepare
Commit  Phase: Commit all participants to coordinator to Prepared, Read only or abort Reply
What is the difference between  deleting and truncating of tables?
Deleting   a table will not remove the rows from the table but entry is there in  the database dictionary and it can be retrieved but truncating a table deletes it completely and it cannot be retrieved or in other worlds DELETE is a DML operation and TRUNCATE is a DDL statements.
What are mutating tables?
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
What is Tree-Structured Queries?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
  Connect by prior EMPNO = MGR Start with MGR is NULL;
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
Select lpad(' ', LEVEL * 2) || ENAME ........
One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult, as one has to do the top-level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
What are Codd Rules?
Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only RDBMS to satisfy the maximum number of rules.
What is Normalization/De-normalization Process?
Normalization:  It's the process of efficiently organizing data in a database.  There are two goals of the normalization process:  eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table).  Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
1.        Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2.        Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3.        Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
4.        Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5.        Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
1NF: A table is said to be in 1st Normal Form when the attributes are atomic
2NF: A table is said to be in 2nd Normal Form when all the candidate keys are dependant on the primary key
3NF: A table is said to be third Normal form when it is not dependant transitively
De-Normalization: De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply De-normalization in the process of deriving a physical data model from a logical form.
What is the Difference between a post query and a pre query?
A post query will fire for every row that is fetched but the pre query will fire only once.
How you will delete the Duplicate rows in the table?
We can delete the duplicate rows in the table by using the Rowid
How can you disable database trigger?
ALTER TABLE ‘Table_name’ [DISABLE all_trigger]
What are pseudo columns? Name them?
A pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but you cannot insert, update, or delete their values. For Example: CURRVAL , NEXTVAL , LEVEL, ROWID, ROWNUM
The ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. For Example: To select top 5 rows
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
  FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
Difference between RANK () and DENSE_RANK () with an example?
Another way to perform a top-N query uses the new Oracle 8i feature called "analytic functions.
SELECT Empno, Ename, Job, Mgr, Sal,
The difference between RANK () and DENSE_RANK () is that RANK () leaves gaps in the ranking sequence when there are ties. In our case, Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK () but only fourth place using RANK (). The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.
When ‘Where Current of’ clause is used?
This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by  cursor name. The cursor must be FOR UPDATE and must be open and positioned on a row.
If the cursor is not open, the CURRENT OF clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.
WHERE CURRENT is used as a reference to the current row when using a cursor to UPDATE or DELETE the current row.
How many columns can table have?
The number of columns  in a table can range from 1 to 254.
Is space acquired in blocks or extents?
In “extents” .
What is clustered index?
In an indexed cluster, rows are stored  together based on their cluster key values. Can not apply for HASH.
What are the data types supported by oracle (INTERNAL)?
Varchar2, Number, Char, MLSLABEL.
What are Various Cursor Attributes?
SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.
SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.
SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.
SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.
What is Ref Cursor
A Ref Cursor is a pointer to a server side cursor variable. The stored procedure returns a reference to a cursor that is open and populated by a select statement to be used as a block Data source REF cursors hold cursors in the same way that VARCHAR2 variables hold strings.  This is an added feature that comes with PL/SQL v2.2.  A REF cursor allows a cursor to be opened on the server and passed to the client as a unit rather than one row at a time.  One can use a Ref cursor as a target of assignments and can be passed as parameters to the Program Units.  Ref cursors are opened with an OPEN FOR statement and in all other ways, they are the same as regular cursors.

Can you use select in FROM clause of SQL select?


  1. Searching for the Ultimate Dating Site? Create an account to find your perfect match.