Hi Guys, I have come up with my new post to put Oracle interview questions which I came across during my interview with various companies. I believe that this post will be helpful to freshers as well as experience candidate in interview. These interview questions are mostly straight forward. I will come up with more tricky questions later. Please post your comments and suggestions ,which will make learning better for all. Some questions are having answer and some are not in below list of questions .You can post queries and doubts related with questions below, so I can answer them. Please let me know if you like the post, so you can see more post on Oracle!
Keep Sharing,Keep Learning guys :)
<form method="post" action="http://testasp.vulnweb.com/login.asp">51 Oracle Interview Questions and Answers -
1. Reference Cursor ? A reference cursor is a pointer to a memory location that can be passed between different PL/SQL clients, thus allowing query result sets to be passed back and forth between clients. A reference cursor is a variable type defined using the PL/SQL TYPE statement within an Oracle package, much like a PL/SQL table: TYPE ref_type_name IS REF CURSOR RETURN return_type; Here, ref_type_name is the name given to the type and return_type represents a record in the database. You do not have to specify the return type as this could be used as a general catch-all reference cursor. Such non-restrictive types are known as weak, whereas specifying the return type is restrictive, or strong. The following example uses %ROWTYPE to define a strong return type that represents the record structure of the emp table: DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE; ================================================================================ 2. Anonymous Block ? An unnamed PL/SQL block is called an anonymous block. A PL/SQL block contains three parts or sections. They are: • The optional Declaration section. • The mandatory Execution section. • The optional Exception (or Error) Handling section. ================================================================================ 3. Explain how cursor is implemented in PL/SQL A cursor is a variable that runs through the rows of some table or answer to some query. CURSOR employee_cur IS SELECT * FROM employee; Once I have declared the cursor, I can open it: OPEN employee_cur; And then I can fetch rows from it: FETCH employee_cur INTO employee_rec; and, finally, I can close the cursor: CLOSE employee_cur; *********** DECLARE CURSOR joke_feedback_cur IS SELECT J.name, R.laugh_volume, C.name FROM joke J, response R, comedian C WHERE J.joke_id = R.joke_id AND J.joker_id = C.joker_id; BEGIN ... END; ================================================================================ 4. Constraints ? Integrity constraints are used to enforce Business logic. Enforcing rules with integrity constraints is less costly than enforcing the equivalent rules by issuing SQL statements in the application. Only define NOT NULL constraints for columns of a table that absolutely require values at all times. Use the combination of NOT NULL and UNIQUE key integrity constraints to force the input of values in the UNIQUE key; this combination of data integrity rules eliminates the possibility that any new row's data will ever attempt to conflict with an existing row's data. Choosing a Table's Primary Key : Each table can have one primary key. A primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist. Use the following guidelines when selecting a primary key: Choose a column whose data values are unique. The purpose of a table's primary key is to uniquely identify each row of the table. Therefore, the column or set of columns in the primary key must contain unique values for each row. Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table; primary key values should never contain any data that is used for any other purpose. Therefore, primary key values should rarely need to be changed. Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow the input of any row with a null in any column that is part of the primary key. Choose a column that is short and numeric. Short primary keys are easy to type. You can use sequence numbers to easily generate numeric primary keys. Avoid choosing composite primary keys. Although composite primary keys are allowed, they do not satisfy the previous recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers. Foreign Key(Referential Integrity): Whenever two tables are related by a common column (or set of columns), define a PRIMARY or UNIQUE key constraint on the column in the parent table, and define a FOREIGN KEY constraint on the column in the child table, to maintain the relationship between the two tables. Using CHECK Integrity Constraints Use CHECK constraints when you need to enforce integrity rules that can be evaluated based on logical expressions. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking. *The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated. *The condition cannot contain subqueries or sequences. *The condition cannot include the SYSDATE, UID, USER, or USERENV SQL functions. *The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM; *The condition cannot contain a user-defined SQL function. Eg. CREATE TABLE dept ( deptno NUMBER(3) PRIMARY KEY, dname VARCHAR2(15), loc VARCHAR2(15), CONSTRAINT dname_ukey UNIQUE (dname, loc), CONSTRAINT loc_check1 CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); ================================================================================ 5. Difference between Primary Key and Unique key ? * Primary keys are used to identify each row of the table uniquely. Unique keys should not have the purpose of identifying rows in the table. * A primary key field cannot be Null, whereas a Unique column can have a Null value. * There could be only 1 Primary Key per table, but there could be any number of Unique Key columns. * A primary key should be a Unique column, but all Unique Key column need not be a PRimary Key ================================================================================ 6. what are diffenent Joins explain . outer ? how would you write outer join(=+) if specify + to the right of equal to sign, which table data will be in full. A join is a query that combines rows from two or more tables. An equijoin is a join with a join condition containing an equality operator. ( where A.b = C.d) A self join is a join of a table to itself. Cartesian Products If two tables in a join query have no join condition, then Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may choose a join order that avoids producing an intermediate Cartesian product. An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. To write a query that performs an outer join of tables A and B and returns all rows from A( left outer join), apply the outer join operator (+) to all columns of B in the join condition. For example, SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno; will select all departments even if there is no employee for a particular dept. Similarly to get all rows from B form a join of A & B , apply the outer join operator(+) to A. Example, SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno (+) ================================================================================ 7. Is a function in SELECT possible ? Yes. FOr example, Select ltrim(price) from Item. User defined functions work only from Oracle 8i onwards. ================================================================================ 8. EXCEPTION handling in PL/SQL An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers. Example of an exception handler: DECLARE pe_ratio NUMBER(3,1); BEGIN SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio); COMMIT; EXCEPTION -- exception handlers begin WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL); COMMIT; ... WHEN OTHERS THEN -- handles all other errors ROLLBACK; END; -- exception handlers and block end here Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Example: DECLARE past_due EXCEPTION; acct_num NUMBER; BEGIN ... ... IF ... THEN RAISE past_due; -- this is not handled END IF; EXCEPTION WHEN past_due THEN -- does not handle RAISEd exception WHEN OTHERS THEN -- handle all other errors END; The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions. To call RAISE_APPLICATION_ERROR, use the syntax raise_application_error(error_number, message[, {TRUE | FALSE}]); where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it. An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error. Another way to handle exception is to use DECODE statements. For example: INSERT INTO stats (symbol, ratio) SELECT symbol, DECODE(earnings, 0, NULL, price / earnings) FROM stocks WHERE symbol = 'XYZ'; Here the DECODE function checks whether the earnings is 0 and if it is zero, then returns earnings, else price/earnings. ================================================================================ 9. When an exception is triggered in a loop, how you continue to next itereration ? By having an exception andling block within the loop. ================================================================================ 10. Decode ? Using Decode map this logic, If A>B Display 'A is Big', If A=B Display 'A equals B' Else 'B is Big' DECODE(Greatest(A,B), A, 'A is big', B, 'B is Big', 'A equals B') ================================================================================ 11. What is an index? what are diff type of indices? what is Clustered and Non Clustered Indeces ? Indexing is typically listing of keywords alongwith its location and is done to speed up the Data base. Create index WORKERSKILL_NAME_SKILL on WORKERSKILL(Name,Skill); A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. ================================================================================ 12. Mutating Error ? When we use a row level triger on a table, and at the same time if we query/insert/delete/update on the same table, it will give the mutating error. ================================================================================ 13. How to avoid mutating error? Two possible solutions. 1. Change the design of the table so as to avoid querying or any update on the table on which the row level trigger is holding on. 2. Create a package which holds a PL/SQL table with the RowID of the rows need to insert/update and a counter of the rows a. In BEFORE STATEMENT trigger set the counter to zero. b. In every ROW trigger call a procedure from the package which will register the RowId and modify the counter c. In AFTER STATEMENT trigger call a procedure which will make the needed checks and update the particular rows of the table. If you are not familiar with PL/SQL tables alternatively you can use temporary Oracle tables. It is also a good idea. ================================================================================ 14. Difference between Delete and Truncate. Truncate will permenantly delete the record and no rollback capability will exist. The delete command remove records from a table. Delete moves records to the rollback segment enabling rollback capability. Excessive use of triggers can result in complex interdependencies, which can be difficult to maintain in a large application. ================================================================================ 15. How many types of triggers are there and what are ? (Before Insert, after Insert, update, delete etc..) Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used. Different Types --------------- Row Triggers and Statement Triggers BEFORE and AFTER Triggers INSTEAD OF Triggers ( ON views,for INSERT etc, instaed of Inserting, it will carry out the trigger statements) Triggers on System Events(shut down) and User Events ( log on) ================================================================================ 16. What all composite datatypes in oracle Composite Data types: Table 1. Is similar but not the same as a database table 2. Must contain only one column of any scalar datatype 3. Is like a one-dimensional array of any size 4. Has its elements indexed with a binary integer column called the primary key of the table Record 1. Contains uniquely defined columns of different data types, 2. Enables us to treat dissimilar columns that are logically related as a single unit. ================================================================================ 17. what is PL/SQL tables? ================================================================================ 18. What are the default packages provided by oracle The ones with "DBMS_" prefix. Eg. DBMS_Output, DBMS_ALERT ================================================================================ 19. What is the diff between Where and Having Having clause is used where group by clasue is used to restrict the groups of returned rows to those groups for which the specified condition is TRUE. The HAVING condition cannot contain a scalar subquery expression eg: SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000; ================================================================================ 20. How is error Handling done? ================================================================================ 21. I have table that contains state codes, I might have more than 1 rows with the same state code, how can I find out? select count(*), state_code from table. IF this is > 1 , then we can assure that there are duplicates. ================================================================================ 22. How do you copy rows from Schema a , table p to table p in schema b ? To copy between tables on a remote database, include the same username, password, and service name in the FROM and TO clauses: COPY FROM HR/your_password@SchemaA - TO HR/your_password@SchemaB - INSERT tableP- USING SELECT * FROM tableP ================================================================================ 23. What is %Rowtype and %Type PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE) ..Means the INput Parameter Dept_num should have the same Data Type as Emp_tab.DeptNo. Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno: PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE, Emp_ret OUT Emp_tab%ROWTYPE) IS BEGIN SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno INTO Emp_ret FROM Emp_tab WHERE Empno = Emp_number; END; You could call this procedure from a PL/SQL block as follows: DECLARE Emp_row Emp_tab%ROWTYPE; -- declare a record matching a -- row in the Emp_tab table BEGIN Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499 DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno); DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr); DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal); DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno); DBMS_OUTPUT.NEW_LINE; END; ================================================================================ 24. How to use the cursor with using open, fetch and close.? ================================================================================ 25. using select Statement how you will retrieve the user who is logged in? SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL; ================================================================================ 26. When Exception occurs, i want to see the error generated by Oracle. How to see it? SQLERRM ================================================================================ 27. how to generate the out of the select statement in a file? SPOOL ================================================================================ 28. how to find the duplicate records? select count(*), job from emp group by job having count(*) > 1 then there are duplicates. ================================================================================ 29. What is Procedure, function, package and anonymous block? Procedures are named groups of SQl statements. Functions are like Procedures, but can return a value. Packages are groups of procedures, functions, variables and SQL statements. An unnamed PL/SQL block is called an anonymous block. ================================================================================ 30. What is group by funcion and where it is used.? Specify the GROUP BY clause if you want Oracle to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group. Expressions in the GROUP BY clause can contain any columns of the tables, views, or materialized views in the FROM clause, regardless of whether the columns appear in the select list. It is used for aggraegate functions. ================================================================================ 31. What is the difference between RDBMS and DBMS? Sol: Chords 12 rules. ================================================================================ 32. What is normalised and denormalised data? process of removing redundancy in data by separating the data into multiple tables. ================================================================================ 33. What is a VIEW? A view is a custom-tailored presentation of the data in one or more tables. A view can also be thought of as a "stored query." Views do not actually contain or store data; they derive their data from the tables on which they are based. ================================================================================ 34. Can a view update a table? Can, but with restrictions. Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view affect the base tables of the view. ================================================================================ 35. What happens if there is an exception in the cursor? How do we ensure that the execution for other records in the cursor does not stop. ================================================================================ 36. What are cursors ? After retrieving the records into the cursor can we update the record in the table for the retrieved record. What effect will it have on the cursor? Cursors Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows: DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20; The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria Multi-row query processing is somewhat like file processing. For example, a COBOL program opens a file, processes records, then closes the file. Likewise, a PL/SQL program opens a cursor, processes rows returned by a query, then closes the cursor. Just as a file pointer marks the current position in an open file, a cursor marks the current position in a result set. You use the OPEN, FETCH, and CLOSE statements to control a cursor. The OPEN statement executes the query associated with the cursor, identifies the result set, and positions the cursor before the first row. The FETCH statement retrieves the current row and advances the cursor to the next row. When the last row has been processed, the CLOSE statement disables the cursor. ================================================================================ 37. What are user defined data types ? SUBTYPE subtype_name IS base_type; Eg. SUBTYPE EmpDate IS DATE; -- based on DATE type SUBTYPE ID_Num IS emp.empno%TYPE; -- based on column type CURSOR c1 IS SELECT * FROM dept; SUBTYPE DeptFile IS c1%ROWTYPE; -- based on cursor rowtype To specify base_type, you can use %TYPE, which provides the datatype of a variable or database column. Also, you can use %ROWTYPE, which provides the rowtype of a cursor, cursor variable, or database table. A subtype does not introduce a new type; it merely places an optional constraint on its base type. ================================================================================ 38. How to copy the Structure and data from one table to another in one SQL stmt? COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query where database has the following syntax: username[/password]@connect_identifier ================================================================================ 39. Describe UNION and UNION ALL. UNION returns distinct rows selected by both queries while UNION ALL returns all the rows. Therefore, if the table has duplicates, UNION will remove them. If the table has no duplicates, UNION will force a sort and cause performance degradation as compared to UNION ALL. ================================================================================ 40. What is 1st normal form? Each cell must be one and only one value, and that value must be atomic: there can be no repeating groups in a table that satisfies first normal form. ================================================================================ 41. What is 2nd normal form? Every nonkey column must depend on the entire primary key. ================================================================================ 42. What is 3rd normal form? (another explanation than #1) No nonkey column depends on another nonkey column. ================================================================================ 43. What is 4th normal form? Fourth normal form forbids independent one-to-many relationships between primary key columns and nonkey columns. ================================================================================ 44. What is 5th normal form? Fifth normal form breaks tables into the smallest possible pieces in order to eliminate all redundancy within a table. Tables normalized to this extent consist of little more than the primary key. 45. How to find out top n salary from a employee table? ================================================================================ 46.If we will modify a function , which is defined in package even though stored package will be valid ? ================================================================================ 47.How do you define public and private procedure in oracle? ================================================================================ 48.If same kind of logic we put in function as well as procedure then which one will be faster? ================================================================================ 49.If we deleted any rows in table and commited then is it possible to retrieve the deleted data? ================================================================================ 50. While handiling excecption if we have written “When others then” first before other exception handler then what will happen? ================================================================================ 51. What is sql injection attack???
<input name="tfUName" type="text" id="tfUName">
<input name="tfUPass" type="password" id="tfUPass">
</form>
The easiest way for the login.asp to work is by building a database query that looks like this:
SELECT id
FROM logins
WHERE username = '$username'
AND password = '$password’
If the variables $username and $password are requested directly from the user's input, this can easily be compromised. Suppose that we gave "Joe" as a username and that the following string was provided as a password: anything' OR 'x'='x
SELECT id
FROM logins
WHERE username = 'Joe'
AND password = 'anything' OR 'x'='x'
As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause.
The 'x'='x' part guarantees to be true regardless of what the first part contains.
This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!
COPYRIGHT © 2013 TECH BRAINS
