Oracle Practice SQL Queries 04:

Oracle Practice SQL Queries 04:
    
51) Display those employees whose manager name is Jones?

        SELECT e.ename superior, e1.ename subordinate
          FROM emp e, e1
         WHERE e.empno = e1.mgr AND e.ename = 'JONES'

52) Display those employees whose salary is more than 3000 after giving 20% increment?

        SELECT ename, sal, (sal + (sal * 0.20))
          FROM emp
         WHERE (sal + (sal * 0.20)) > 3000;
        
53) Display all employees with their department names?
    Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno


54) Display ename who are working in sales department?

        SELECT e.ename, d.dname
          FROM emp e, dept d
         WHERE e.deptno = d.deptno AND d.dname = 'SALES'


55) Display employee name, dept name, salary, and commission for those sal in between 2000 to 5000 while location is Chicago?

        SELECT e.ename, d.dname, e.sal, e.comm
          FROM emp e, dept d
         WHERE e.deptno = d.deptno AND sal BETWEEN 2000 AND 5000
        
56) Display those employees whose salary is greater than his manager’s salary?
             SELECT e.ename, e.sal, e1.ename, e1.sal
              FROM emp e, e1
             WHERE e.mgr = e1.empno AND e.sal > e1.sal
            
57) Display those employees who are working in the same dept where his manager is work?

        SELECT e.ename, e.deptno, e1.ename, e1.deptno
          FROM emp e, e1
         WHERE e.mgr = e1.empno AND e.deptno = e1.deptno
        
58) Display those employees who are not working under any Manager?
        Select ename from emp where mgr is null;
       
       
59) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?

                SELECT ename, grade, deptno, sal
                  FROM emp, salgrade
                 WHERE (grade, sal) IN (SELECT grade, sal
                                          FROM salgrade, emp
                                         WHERE sal BETWEEN losal AND hisal)
                   AND grade ! = 4
                   AND deptno IN (10, 30)
                   AND hiredate < '31-Dec-82'
                  
60) Update the salary of each employee by 10% increment that are not eligible for commission?
            Update emp set sal= (sal+(sal*0.10)) where comm is null

61) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
            SELECT e.ename, e.hiredate, d.loc
              FROM emp e, dept d
             WHERE e.deptno = d.deptno
               AND hiredate < '31-Dec-82'
               AND d.loc IN ('NEW YORK', 'CHICAGO')
              
62) Display employee name, job, deptname, and loc for all who are working as manager?

            SELECT e.ename, e.job, d.dname, d.loc
              FROM emp e, dept d
             WHERE e.deptno = d.deptno AND e.empno IN (SELECT mgr
                                                         FROM emp
                                                        WHERE mgr IS NOT NULL)

63) Display those employees whose manager name is Jones and also display their manager name?

                    SELECT e.ename sub, e1.ename
                      FROM emp e, emp e1
                     WHERE e.mgr = e1.empno AND e1.ename = 'JONES'
                    
64) Display name and salary of ford if his salary is equal to hisal of his grade?
            Select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;
            OR
            Select grade, sal, hisal from emp, salgrade where ename='FORD' and sal between losal and hisal;
            OR
             SELECT ename, sal, hisal, grade
              FROM emp, salgrade
             WHERE ename = 'FORD' AND (grade, sal) IN (SELECT grade, hisal
                                                         FROM salgrade, emp
                                                        WHERE sal BETWEEN losal AND hisal);
                                           
                                           
65) Display employee name, job, deptname, his manager name, his grade and make an under department wise?

                SELECT   e.ename sub, e1.ename sup, e.job, d.dname, grade
                    FROM emp e1, salgrade, dept d
                   WHERE e.mgr = e1.empno
                     AND e.sal BETWEEN losal AND hisal
                     AND e.deptno = d.deptno
                GROUP BY d.deptno, e.ename, e1.ename, e.job, d.dname, grade
                OR
                
                SELECT e.ename sub, e1.ename sup, e.job, d.dname, grade
                  FROM emp e, e1, salgrade, dept d
                 WHERE e.mgr = e1.empno
                   AND e.sal BETWEEN losal AND hisal
                   AND e.deptno = d.deptno
                  
66) List out all the employee names, job, salary, grade and deptname for every one in a company except ‘CLERK’. Sort on salary display the highest salary?

            SELECT   e.ename, e.job, e.sal, d.dname, grade
                FROM emp e, salgrade, dept d
               WHERE (e.deptno = d.deptno AND e.sal BETWEEN losal AND hisal)
            ORDER BY e.sal DESC
           
           
67) Display employee name, job and his manager. Display also employees who are with out managers?

        Select e.ename, e1.ename, e.job, e.sal, d.dname from emp e, emp e1, dept d where e.mgr=e1.empno (+) and e.deptno=d.deptno

68) Display Top 5 employee of a Company?

69) Display the names of those employees who are getting the highest salary?
            Select ename, sal from emp where sal in (select max (sal) from emp)

70) Display those employees whose salary is equal to average of maximum and minimum?
            Select * from emp where sal=(select (max (sal)+min (sal))/2 from emp)

Oracle Practice SQL Queries 03:

 Oracle Practice SQL Queries 03:  
        
31) Find the first occurance of character a from the following string Computer Maintenance Corporation?
                select lstr('Computer Maintenance Corporation','a' ) from dual;

32) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)
                select translate('Alliens','A','B') from Dual;

33) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
                select ename ,replace(job,'MANAGER','BOSS') from emp;

34) Display empno,ename,deptno from emp table. Instead of display department numbers display the related department name(Use decode function)?

                SELECT empno, ename, deptno,
                       DECODE (deptno,
                               10, 'ACCOUNTING',
                               20, 'RESEARCH',
                               30, 'SALES',
                               'OPERATIONS'
                              ) dname
                  FROM emp;
                 
                 
35) Display your Age in Days?
            select sysdate-to_date('30-jul-1977') from dual;
           
36) Display your Age in Months?

            select months_between(sysdate,to_date('30-jul-1977')) from dual;

37) Display current date as 15th August Friday Nineteen Nienty Seven?

            select To_char(sysdate,'ddth Month Day year') from dual;

39) Scott has joined the company on 13th August ninteen ninety?
            select empno,ename,to_char(Hiredate,'Day ddth Month year') from emp;

40) Find the nearest Saturday after Current date?
            select next_day(sysdate,'Saturday') from dual;
           
           
41) Display the current time?
            select To_Char(sysdate,'HH:MI:SS') from dual;
           
           
42) Display the date three months before the Current date?

            select Add_months(sysdate,-3) from dual;
           
43) Display the common jobs from department number 10 and 20?

            select job from emp where job in (select job from emp where deptno=20) and deptno=10;
           
44) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?

            select Distinct job from emp where deptno in(10,20);
           
45) Display the jobs which are unique to department 10?
            select job from emp where deptno=10;
           
46) Display the details of those employees who do not have any person working under him?

            SELECT empno, ename, job
              FROM emp
             WHERE empno NOT IN (SELECT mgr
                                   FROM emp
                                  WHERE mgr IS NOT NULL);
                                 
47) Display the details of those employees who are in sales department and grade is 3?

                SELECT e.ename, d.dname, grade
                  FROM emp e, dept d, salgrade
                 WHERE e.deptno = d.deptno AND dname = 'SALES' AND grade = 3
                
48) Display thoes who are not managers?
                select ename from emp where job!='MANAGER';
               
49) Display those employees whose name contains not less than 4 characters?
                Select ename from emp where length (ename)>=4
               
50) Display those department whose name start with 'S' while location name ends with 'K'?
                Select e.ename, d.loc from emp e, dept d where d.loc like ('%K') and enamelike ('S%');

Oracle Practice SQL Queries 02:

Last Post Oracle Practice SQL Queries 01:

11) Display the various jobs along with total number of employees in each job. The output should contain only those jobs with more than three employees?

SELECT job, COUNT (*)
FROM emp
GROUP BY job
HAVING COUNT (*) > 3;


12) Display the name of employees who earn Highest Salary?

SELECT ename, sal
FROM emp
WHERE sal >= (SELECT MAX (sal)
FROM emp);


13) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?

SELECT ename, empno
FROM emp
WHERE sal = (SELECT MAX (sal)
FROM emp
WHERE job = 'CLERK') AND job = 'CLERK';


14) Display the names of salesman who earns a salary more than the Highest Salary of the Clerk?

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX (sal)
FROM emp
WHERE job = 'CLERK') AND job = 'SALESMAN';


15) Display the names of clerks who earn a salary more than the lowest Salary of any Salesman?

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MIN (sal)
FROM emp
WHERE job = 'SALESMAN') AND job = 'CLERK';


16) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?

SELECT ename, sal
FROM emp
WHERE sal > ALL (SELECT sal
FROM emp
WHERE ename = 'JONES' OR ename = 'SCOTT');


17) Display the names of employees who earn Highest salary in their respective departments?

SELECT ename, sal, deptno
FROM emp
WHERE sal IN (SELECT MAX (sal)
FROM emp
GROUP BY deptno);

18) Display the names of employees who earn Highest salaries in their respective job Groups?

SELECT ename, job
FROM emp
WHERE sal IN (SELECT MAX (sal)
FROM emp
GROUP BY job);

19)Display employee names who are working in Accounting department?

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname = 'ACCOUNTING';

20) Display the employee names who are Working in Chicago?

SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO';


21) Display the job groups having Total Salary greater than the maximum salary for Managers?

SELECT job, SUM (sal)
FROM emp
GROUP BY job
HAVING SUM (sal) > (SELECT MAX (sal)
FROM emp
WHERE job = 'MANAGER');


22) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?

SELECT ename, deptno
FROM emp
WHERE sal > ANY (SELECT MIN (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno) AND deptno = 10;


23) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?

SELECT ename, deptno
FROM emp
WHERE sal > ALL (SELECT MAX (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno) AND deptno = 10;

24) Display the names of Employees in Upper Case?
select upper(ename) from emp;

25) Display the names of employees in Lower Case?

select Lower(ename) from emp;

26) Display the names of employees in Proper case?
select InitCap(ename)from emp;

27) Find the length of your name using Appropriate Function?
select lentgh('RAMA') from dual;

28) Display the length of all the employee names?
select length(ename) from emp;

29) Display the name of employee Concatinate with Employee Number?
select ename' 'empno from emp;

30) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
select substr('Oracle',3,2) from dual;

For More Check Oracle Practice SQL Queries 03:

Oracle Practice SQL Queries 01:

Oracle Practice SQL Queries 01:


1) Display the name of employees along with their annual salary (sal*12) the name of the employee earning highest annual salary should appear first?

                    SELECT   ename, sal, sal * 12 "Annual Salary"
                        FROM emp
                    ORDER BY "Annual Salary" DESC;

2) Display name, salary, Hra, pf, da, TotalSalary for each employee. The out put should be in the order of total salary, hra 15% of salary, DA 10% of salary .pf 5% salary Total Salary will be (salary+hra+da)-pf?

                    SELECT   ename, sal sa, sal * 0.15 hra, sal * 0.10 da, sal * 5 / 100 pf,
                             sal + (sal * 0.15) + (sal * 0.10) - (sal * .05) totalsalary
                        FROM emp
                    ORDER BY totalsalary DESC;

3) Display Department numbers and total number of employees working in each Department?

                    SELECT   deptno, COUNT (*)
                        FROM emp
                    GROUP BY deptno;

4) Display the various jobs and total number of employees working in each job group?

                    SELECT   job, COUNT (*)
                        FROM emp
                    GROUP BY job;

5) Display department numbers and Total Salary for each Department?

                SELECT   deptno, SUM (sal)
                    FROM emp
                GROUP BY deptno;

6) Display department numbers and Maximum Salary from each Department?

            SELECT   deptno, MAX (sal)
                FROM emp
            GROUP BY deptno;

7) Display various jobs and Total Salary for each job?
   
                SELECT   job, SUM (sal)
                    FROM emp
                GROUP BY job;

8)Display each job along with min of salary being paid in each job group?

                SELECT   job, MIN (sal)
                    FROM emp
                GROUP BY job;
               
9) Display the department Number with more than three employees in each department?

                SELECT   deptno, COUNT (*)
                    FROM emp
                GROUP BY deptno
                  HAVING COUNT (*) > 3;

10) Display various jobs along with total salary for each of the job where total salary is greater than 40000?
                
                SELECT   job, SUM (sal)
                    FROM emp
                GROUP BY job
                  HAVING SUM (sal) > 40000;

For more Please Check  Oracle Practice SQL Queries 02:

Oracle apps’s technical FAQs 6

(ERP) Oracle Applications 11i,Oracle apps’s technical FAQs 5:

56) What are triggers?

---- triggers are similar to procedures, in that they are the named pl/sql blocks with declarative, executable and exception-handling sections, how ever a procedure is executed explicitly from another block via a procedure call, which can also pass arguments.

---- A trigger is executed implicitly when ever a particular event task places. And is nothing but a event.

---- The triggering event is a DML (insert, update, delete) operations on a data base table

----- fires whenever a data event(such as DML) or system event(such as login or shutdown) occurs on a schema or database

Trigger timing : 1) before

2) after

3) instead of ( this is used for views)

Triggering events : 1) insert

2)update

3) delete

Trigger type : 1) statement level

2) row level.

Firing sequence of database triggers

1) before statement trigger

2) before row trigger

3) after row trigger

4) after statement trigger

Ex:

1) Create or replace trigger secure_emp

Before

Insert on emp

Begin

If (to_char(sysdate,’dy’) in(‘sat’,’sun’)) or

To_char(sysdate,’hh24:mi’)

Not between ’08:00’ and ’18:00’)

Then raise_application_error(-20500,’u can insert in the office timings’)

End if;

End;

Ex :- 2) write a program to all transitions with name smith?

Create or replace

Trigger trigger_name

Before insert or update or delete

On emp

For each row

When (old.ename =’smith’ or

New.ename =’smith’)

Begin

Raise_application_error(-20003,’smith’);

End;

57) Difference between triggers and procedures?

Defined with create trigger	Defined with create procedure
The data dictionary contains source code in the user_triggers.	Data dictionary contains source code in user_source
Implicitly invoked	Explicitly invoked
Commit, save point and rollback are not allowed(TCL)	Those are allowed

58) What are LOCKS?

-- Is to reduce concurrency

1) share lock

---it allows the other users for only reading not to insert or update or delete.

2) exclusive lock

--- only one user can have the privileges of insert or update and delete of particular object

--- others can only read.

3) update lock

----multiple user can read, update delete

Lock levels :

1) table level 2) table space 3) data base level.

58) What is template.fmb?

a) The TEMPLATE form is the required starting point for all development of new

Forms.

b) The TEMPLATE form includes platform–independent attachments of several

Libraries.

APPSCORE :- It contains package and procedures that are required of all forms to support the MENUS ,TOOLBARS.

APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.

FNDSQF :- it contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.

CUSTOM :- it allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom ( such as moving to another form and querying up specific records)

59) What are ad-hoc reports?

Ans.: Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a

particular purpose. For example, ad hoc tax codes or an ad hoc database query

60) What is responsibility in Oracle Applications?

Is collection of menus, request security groups and data groups

Menus: collection of forms is nothing but menus

Request security groups: collection of programs.

Data groups: is a group of modules to be made accessible by the user through

Responsibility

Navigations are : 

System admin --> security --> define

Security--> user --> define

61) What are different execution methods of executables in Oracle Applications Concurrent program?

FlexRpt The execution file is wrnitten using the FlexReport API.

FlexSql The execution file is written using the FlexSql API.

Host The execution file is a host script.

Oracle Reports The execution file is an Oracle Reports file.

PL/SQL Stored Procedure The execution file is a stored procedure.

SQL*Loader The execution file is a SQL script.

SQL*Plus The execution file is a SQL*Plus script.

SQL*Report The execution file is a SQL*Report script.

Spawned The execution file is a C or Pro*C program.

Immediate The execution file is a program written to run as a subroutine of the concurrent manager. We recommend against defining new immediate concurrent programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead.

Composite Datatypes :

– PL/SQL TABLES

– PL/SQL RECORDS

- Nested TABLE

- VARRAY

What is the sequence of functions – group by,having,orderby in a select statements ?

Select…..

Group by…

Having…

Orderby..

Difference between User and Super User?

User : login user or front end user

Super user : it has full access of particular module

Oracle apps’s technical FAQs 5

(ERP) Oracle Applications 11i,Release 12 - Technical (eBussiness Suite):

52) what is REF Cursor?

To execute a multi-row query, oracle opens an unnamed work area that stores processing information, to access the information, an explicit, which names the work area or, a cursor variable, which points to the work area.

where as a cursor always refers to the same query work area, a cursor variable can refer to a different work areas, cursor variable area like ‘c’ or ‘pascal’ pointers, which hold the memory location(address) of some object instead of the object itself.

So, declaring a cursor variable creates a pointers, not an object.

32) Can u define exceptions twice in same block?

No

33) Can you have two functions with the same name in a pl/sql block?

Yes

34) Can you have two stored functions with in the same name?

Yes

35) Can function be overload?

Yes

36) What is the maximum number of statements that can be specified in a trigger statement?

One.

32) Stored procedure?

Stored procedure is a sequence of statements that perform specific function.

53) What is procedure?

---- is a named pl/sql block to perform a specific task.

---- A procedure may have DML statements.

---- It may or may not return a value.

---- Procedure can return more than one value.

Example for procedure

1) To accept the year as a parameter and list emp belong to the year?

Create or replace

Procedure empy(y number) is

Cursor emp_cursor is

Select * from emp where to_char(hiredate,’yyyy’)=’y’;

Emp_record emp%rowtype;

Begin

For emp_record in emp_cursor loop

Print (emp_record.empno);

Print (emp_record.ename);

Print (emp_record.sal);

End loop;

End;

Output :

var empx number;

Begin

:empx := ‘1234’;

End;

Exec empy(:empx);

Print empy;

54) What is function?

---- is a named pl/sql block to perform a specific task, is mainly used for calculation purpose.

---- A function is called as part of an exception.

---- Every function should return a value

Example for function

Create or replace

Function get_sal(p_id in emp.emp_no% type)

Return number

Is

v_sal emp.sal%type :=0;

Begin

Select salary into v_salary

From emp

Where emp_no = p_id;

Return v_salary

End get_sal;

End;

Output :

var g_sal number;

Exec :g_sal := get_sal(99);

Print g_salary;

9.Can functions be overloaded ?

Yes.

10.Can 2 functions have same name & input parameters but differ only by return datatype

No.

55) What is the package?

---- Group logically related pl/sql types, items and subprograms.

1) package specification

2) package body

Advantages of a package:

· Modularity

· Easier Application Design

· Information Hiding

· Overloading

You cannot overload:

•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their total number is same).

•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (number and decimal belong to the same family)

•Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)

•Two functions that differ only in return type, even if the types are in different families.

56) What is FORWARD DECLARATION in Packages?

PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon. You can use forward declarations to do the following:

• Define subprograms in logical or alphabetical order.

• Define mutually recursive subprograms.(both calling each other).

• Group subprograms in a package

Example of forward Declaration:

CREATE OR REPLACE PACKAGE BODY forward_pack

IS

PROCEDURE calc_rating(. . .); -- forward declaration

PROCEDURE award_bonus(. . .)

IS -- subprograms defined

BEGIN -- in alphabetical order

calc_rating(. . .);

. . .

END;

PROCEDURE calc_rating(. . .)

IS

BEGIN

. . .

END;

END forward_pack;

Oracle apps’s technical FAQs 4

(ERP) Oracle Applications 11i,Release 12 - Technical (eBussiness Suite):

33) FLEX FIELDS?

Used to capture the additional business information.

DFF	KFF
Additional	Unique Info, Mandatory
Captured in attribute prefixed columns	Segment prefixed
Not reported on standard reports	Is reported on standard reports
To provide expansion space on your form With the help of []. [] Represents descriptive Flex field. FLEX FILED : DESCRIPTIVE : REGIGSTER	Used for entering and displaying key information For example Oracle General uses a key Flex field called Accounting Flex field to uniquely identifies a general account. FLEX FILED : KEY : REGIGSTER

Oracle Applications KEY FLEX FIELDS

1) GL :- ACCOUNTING

2) AR :- SALES TAX LOCATION, TERRITORY,

3) AP :- BANK DETAILS, COST ALLOCATION, PEOPLE GROUP

Oracle Applications DESCRIPTIVE FLEX FIELDS (Partial)

1) GL :- daily rates

2) AR :- credit history, information

3) PA :- bank branch, payment terms, site address,

34) What are the requests groups?

a) Single request: - this allows you to submit an individual request.

b) Request set : - this allows you to submit a pre-defined set of requests.

35) Sys Admin Module?

a) Define Custom Users, b) Define Login Users, c) Register oracle DB users,

d) Define Concurrent Programs, e) Register Concurrent Executables, f) Setting Profile Option Values, g) Define Request Types.

36) AOL?

a) Registering tables. b) Registering views c) Registering db sequences

d) Registering profile options e) Registering lookups and lookup codes

f) Registering forms g) Registering Form and Non-Form functions i) registering

Menus and sub-menus. j) Registering DFF and KFF. k) Libraries

37) What r the type Models in the system parameters of the report?

1) Bit map 2) Character mode

38) .What is SRW Package? (Sql Report Writer)

The Report builder Built in package know as SRW Package This package extends reports ,Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exist, to format width of the columns, to page break the column, to set the colors

Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views , etc.,

SRW.SET_FIELD_NUM

SRW. SET_FILED_CHAR

SRW. SET FILED _DATE

37) Difference between Bind and Lexical parameters?

BIND VARIABLE :

-- are used to replace a single value in sql, pl/sql

-- bind variable may be used to replace expressions in select, where, group, order

by, having, connect by, start with cause of queries.

-- bind reference may not be referenced in FROM clause (or) in place of

reserved words or clauses.

LEXICAL REFERENCE:

-- you can use lexical reference to replace the clauses appearing AFTER select,

from, group by, having, connect by, start with.

-- you can’t make lexical reference in a pl/sql statmetns.

38) Matrix Report: Simple, Group above, Nested

Simple Matrix Report : 4 groups

1.Cross Product Group

2. Row and Column Group

3. Cell Group

4. Cell column is the source of a cross product summary that

becomes the cell content.

Frames: 1.Repeating frame for rows(down direction)

2.Repeating frame for columns(Across )

3.Matrix object the intersection of the two repeating frames

39) what is Flex mode and Confine mode?

Confine mode

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders "stretch" when child objects are moved against them.

Off: parent borders remain fixed when child objects are moved against

them.

40) What is Place holder Columns?

A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object.

You can set the value of a placeholder column is in a Before Report trigger.

Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

23) What is Formula Column?

A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

24) What is Summary columns?

A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

50) What is cursor?

A Cursor is a pointer, which works on active set, I.e. which points to only one row at a time in the context area’s ACTIVE SET. A cursor is a construct of pl/sql, used to process multiple rows using a pl/sql block.

28) Types of cursors?

1) Implicit: declared for all DML and pl/sql statements.

By default it selects one row only.

2) Explicit: Declared and named by the programmer.

Use explicit cursor to individually process each row returned by a

Multiple statements, is called ACTIVE SET.

Allows the programmer to manually control explicit cursor in the

Pl/sql block

a) declare: create a named sql area

b)Open: identify the active set.

c) Fetch: load the current row in to variables.

d)Close: release the active set.

CURSOR ATTRIBUTES

a) %is open: evaluates to true if the cursor is open.

b) %not found: evaluates to true if the most recent fetch does not return a row

c) %found: evaluates to true if the most recent fetch returns a row.

d) %row count: evaluates to the total number of rows returned to far.

Example for cursor:

1) Declare

Vno emp.empno%type;

Vname emp.ename %type;

Cursor emp_cursor is

Select empno,ename

From emp;

Begin

Open cursor;

For I in 1..10 loop

Fetch emp_cursor into vno,vname;

Dbms_output.putline(to_char(vno) ||’ ‘||vname);

End if;

E nd;

2) Begin

Open emp_cursor;

Loop

Fetch when emp_cursor % rowcount >10 or

Emp_curor % not found;

Bdms_output_put_line(to_char(vno)||’ ‘|| vname);

End loop;

Close emp_cursor;

End;

CURSOR FOR LOOP

A) cursor for loop is a short cut to process explicit cursors

B) it has higher performance

C) cursor for loop requires only the declaration of the cursor, remaining things like opening, fetching and close are automatically take by the cursor for loop

Example:

1) Declare

Cursor emp_cursor is

Select empno,ename

From emp;

Begin

For emp_record in emp_cursor loop

Dbms_output.putline(emp_record.empno);

Dbms_output.putline(emp_record.ename)

End loop

End;

Can we create a cursor without declaring it?

Yes – by using cursor for loop using subqueries.

BEGIN

FOR emp_record IN ( SELECT empno, ename

FROM emp) LOOP

-- implicit open and implicit fetch occur

IF emp_record.empno = 7839 THEN

...

END LOOP; -- implicit close occurs

END;

a) for update clause:

1) use explicit locking to deny access for the duration of a transaction

2) lock the rows before update or delete

Ex : select …….

From…….

For update[ of column ref] [no_wait]

b) where current of clause?

1) use cursor to update or delete the current row

Where current of <>

29) Attribute data types?

1) %type 2) %row type.

30) Exception Handilings?

Is a mechanism provided by pl/sql to detect runtime errors and process them with out halting the program abnormally

1) pre-defined

2) user-defined.

PRE-DEFINED:

1) cursor_already_open--------attempted to open an already open cursor.

2) Dup_val_on_index --------attempted to insert a duplicate values.

3) Invalid_cursor -------- illegal cursor operation occurred.

4) Invalid_number -------- conversion of character string to number fails.

5) Login_denied ---------loging on to oracle with an invalid user name

and password.

6) program_error -------- pl/sql has an internal problem.

7) storage_error -------- pl/sql ran out of memory or memory is

corrupted.

8) to_many_row ---------single row select returned more than one row.

9) value_error -------- arithmetic,conversion,truncation or size

constraint error occurred.

10) zero_devided -------- attempted to divided by zero.

USER-DEFINED:

Declare : name the exception

Raise : explicitly raise the exception by using the raise statements

Reference: exception handing section.

The Raise_Application_Error_Procedure:

n You can use this procedure to issue user-defined error messages from stored sub programs.

n You can report errors to your applications and avoid returning unhandled exceptions.

Raise_Application_Error(error_number,message[,{true/false}]

Error number è between -20000 to -20999

pragma exception_init?

It tells the compiler to associate an exception with an oracle error. To get an error message of a specific oracle error.

Ex: pragma exception_init(exception name, oracle error number)

Example for Exceptions?

1) Check the record is exist or not?

Declare

E emp% rowtype

Begin

e.empno := &empno;

select * into e from emp where empno =e.empno;

Dbms_output.putline(‘empno’ || e.empno);

Exception

When no_data_found then

Dbms_output.putline(e.empno ||’doest exist’);

End;

2) User defined exceptions?

Define p_dept_desc =’gvreddy’

Define p_dept_number =1236

Declare

E_invalid_dept exception;

Begin

Update departments

Set dept_name=’&p_dept_desc’

Where dept_id =’&p_dept_number’;

If sql% not found then

Raise e_invalid_departments;

End if;

Commit;

Exception

When e_invalid_departments then

Dbms_output.putline(‘no such dept’);

End;