Q-1

Inline view

An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used simplify complexqueries by removing join operations and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.

This feature is commonly referred to in the MSSQL community as a derived table, and in the Postgres community simply refers to it as asubselect (subselects are inline views + subqueries in Oracle nomenclature).

[edit]Examples

Example inline view:

SELECT *

  FROM (select deptno, count(*) emp_count

          from emp

         group by deptno) emp,

       dept

 WHERE dept.deptno = emp.deptno;

Another good example of an inline view is:

SELECT a.last_name, a.salary, a.department_id, b.maxsal

  FROM employees a,

       (SELECT department_id, max(salary)maxsal

          FROM employees

      GROUP BY department_id) b

 WHERE a.department_id = b.department_id

   AND a.salary < b.maxsal;

The above query display the employees who earn the highest salary in each department.

 

2 How many types of database triggers

http://www.geekinterview.com/question_details/4651

trigger details

http://ist.marshall.edu/ist480adbp/plsql_triggers.html

 

3 copy(name_in('PARAMETER.p_username'),'txt_username');

 

4 Lexical paramteres

Lexical parameters are used to substitute multiple values 

at runtime and are identified by a preceding '&'. Lexical s 

can consist of as little a one line where clause to an 

entire select statement

Lexical parameter is a feature used for building a dynamic 

 query.

Example: An example of a lexical parameter usage in a 

select statement is as follows

select *

from   emp

where  &L_EMP; (Report can assign a value as :L_EMP:= 

emp.dept = 20 and salary > 20000)

 

5 report from report

6 same procedure in database , libraries, and program units .

7 in exceptions , when others  after no_data_found , what is the output.

8 Autonomous Transactions

Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only commited data can be shared by both transactions.

The following types of PL/SQL blocks can be defined as autonomous transactions:

• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.

The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not commited.

http://www.oracle-base.com/articles/misc/AutonomousTransactions.php

9  tow tire and three tire architecture

10 Primary key Vs Unique que

11. ref cursors

        http://www.oradev.com/ref_cursor.jsp

http://beginner-sql-tutorial.com/sql-index.htm

12) what is the default BOOlEAN value

Ans) NULL

13) What are the Debugging types in PL/SQL

Ans) It is possible to debug PL/SQL Code in Oracle with DBMS_DEBUG

dbr.sql	This starts the debugger
dbe.sql	This starts the debuggee
cont.sql	Continues after a breakpoint was hit
abort.sql	Aborts a debugging session
s.sql	Steps to the next executable line
breakpoints.sql	Shows the breakpoints
delbp.sql	Deleteas a breakpoint
is_r.sql	Is debugee running?
si.sql	Steps into a function
so.sql	Steps out of a function
var.sql	Prints a variable's value
brl.sql	Sets a breakpoint

The debugee and the debugger

In order to debug PL/SQL with dbms_debug, two sessions are needed: a debugee session and a debugger session. The debugee session is the session in which the pl/sql code to be debugged is run while the debugger session controls the debugee, that is, the debugger sets breakpoints, clears them, continues the programm and queries variable's values.

Starting the debugee

The debugee is started with dbe:

 SQL> @dbe

 Session altered.

 START_DEBUGEE

----------------------------------------------------

0009000A0001

The string that is printed (0009000A0001 in this case) will be used when the debugger will be started.

Now, an anonymous PL/SQl is executed in the debugee session:

 SQL> declare

   2    v_result integer;

  3  begin

  4    select pkg_dbgd.tst_1(4) into v_result from dual;

  5  end;

  6  /

After typing this in, the session seems to hang. That is because the debugee sessions waits for commands from the debugger session.

Starting the debugger

Now, an another session (called the debugger session) is attached to the debugee with dbr:

 SQL> @dbr

Enter value for debugee_id: 0009000A0001

 Runtime Info

Prg Name:

Line:

 Terminated:    0

Breakpoint:

Stackdepth

 Interpr depth: 1

Reason:        Interpreter is starting.

  Namespace:  Unknown namespace

  Name:

   owner:

  dblink:

  Line#:

   lib unit:

  entrypoint:

 PL/SQL procedure successfully completed.

The debugee won't be touched until the end, all we do now is done in the debugger session.

14) what arethe UTL_File Exception

able 168-1 UTL_FILE Package Exceptions

Exception Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The requested file rename operation failed.

 

Procedures in UTL_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

 

4) How many types of record groups

Ans) Query Record Groups

       NonQuery Record Groups

       State Record Groups

 

5) what is the max length of record group

6) what is the mutating table or state

7) what r the transaction triggers in forms

Ans) PRE-INSERT, ON-INSERT, POST-INSERT

     PRE-UPDATE, ON-UPDATE, POST-UPDATE

     PRE-DELETE, ON-DELETE, POST-DELETE

8) Explain about UTL_FILE package in PL/SQL

9) What is Autonomos Transaction

10) Ref cursors - dynamic or static

11)

xample: Correlated subquery in a WHERE Clause

Suppose that you want a list of all the employees whose education levels are higher than the average education levels in their respective departments. To get this information, SQL must search the CORPDATA.EMPLOYEE table. For each employee in the table, SQL needs to compare the employee's education level to the average education level for the employee's department. In the subquery, you tell SQL to calculate the average education level for the department number in the current row. For example:

  SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL

   FROM CORPDATA.EMPLOYEE X

   WHERE EDLEVEL >

      (SELECT AVG(EDLEVEL)

         FROM CORPDATA.EMPLOYEE

         WHERE WORKDEPT = X.WORKDEPT)

A correlated subquery looks like an uncorrelated one, except for the presence of one or more correlated references. In the example, the single correlated reference is the occurrence of X.WORKDEPT in the subselect's FROM clause. Here, the qualifier X is the correlation name defined in the FROM clause of the outer SELECT statement. In that clause, X is introduced as the correlation name of the table CORPDATA.EMPLOYEE.

Now, consider what happens when the subquery is executed for a given row of CORPDATA.EMPLOYEE. Before it is executed, the occurrence of X.WORKDEPT is replaced with the value of the WORKDEPT column for that row. Suppose, for example, that the row is for CHRISTINE I HAAS. Her work department is A00, which is the value of WORKDEPT for this row. The subquery executed for this row is:

  (SELECT AVG(EDLEVEL)

     FROM CORPDATA.EMPLOYEE

     WHERE WORKDEPT = 'A00')

Thus, for the row considered, the subquery produces the average education level of Christine's department. This is then compared in the outer statement to Christine's own education level. For some other row for which WORKDEPT has a different value, that value appears in the subquery in place of A00. For example, for the row for MICHAEL L THOMPSON, this value is B01, and the subquery for his row delivers the average education level for department B01.

The result table produced by the query has the following values:

Table 15. Result set for previous query
EMPNO	LASTNAME	WORKDEPT