--PROGRAM TO READ DATA FROM FILE BY USING utl_file----

HI frndz,,here we c how to read file data in plsql........
--In SHORT THIS IS A FILE HANDLING---
---THIS IS SAME AS .NET ,,c,FILE HANDLING----
--PROGRAM TO READ DATA FROM FILE BY USING utl_file----
------------------------------------------------------------------------------------------------------------------------------------
/*first create a diretctory name as My_DIR(giv any name)  */
CREATE OR REPLACE DIRECTORY My_DIR AS '/u01/dbtst/db/tech_st/11.1.0/appsutil/outbound/TST_pn-vm000001';  (run this script)
/*after creating directory assign some permition to tht directory so any user can access tht directory*/
GRANT read, write ON DIRECTORY ROHIT_DIR TO PUBLIC; ( run this script)
then put your file in that directoy wich u want to read... (here we use "test.txt" file)

----------------------------------------------------------------------------------------------
here is the plsql code to read file data wich is present in directory "My_DIR".
 ------------------------------------------------------------------------------------------------
set serveroutput on
DECLARE
------here we create one object of utl_file.file_type, it name as "handle"(step 1)----

handle  utl_file.file_type;
line  VARCHAR2(250);
BEGIN

--here we open the file in read mode on wich we want to work and assign tht to the our object "handle"(step 2)--
  handle := utl_file.fopen('My_DIR', 'test.txt', 'R');
  LOOP
    BEGIN
  
    --we use utl_file.get_line() function to read file line by line and save tht line data in variable "line" wich is declared above(step 3)--
      utl_file.get_line(handle, line);
      dbms_output.put_line(line);
      --utl_file.get_line() funcion used to write the read data()--
     
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
 
  --after all operation close the file(step 4)--
  utl_file.fclose(handle);
END fopen;
/

create a query to List all the Customers and Customer sites specific to each Operating Unit...

Hi frndz if u want to find customer details of each operating unit in oracle apps then u can use following query..
----------------------------------------------------------------------------------------------------------------------   
select  distinct
         -- ,b.org_id
         --a.organization_id
         -- ,c.party_id
         a.name                     as OPERATING_UNIT
        ,d.party_name               AS CUSTOMER_NAME
        ,d.party_number             AS CUSTOMER_NUMBER
        ,c.PARTY_SITE_NAME          AS SITE_NAME
        ,d.ADDRESS1                 AS ADDRESS
        ,d.CITY
        ,d.STATE
        ,d.POSTAL_CODE
        ,d.STATUS
from
          HR_OPERATING_UNITS a
         ,HZ_CUST_ACCT_SITES_ALL b
         ,HZ_PARTY_SITES c
         ,hz_parties d
where
         a.organization_id=b.org_id
    and  b.PARTY_SITE_ID= c.PARTY_SITE_ID
    and  c.party_id=d.party_id
    and  c.party_site_name is not null
order by
         d.party_name              
         ,d.party_number
        ,d.status;

Create a simple cursor

Cursor is nothing but th pointer..
stpes to create and use the cursor
1) DECLARE THE CURSOR.
2)OPEN THE CURSOR
3)FETCH THE CURSOR
4)CLOSE THE CURSOR.

Ex---create a cursor to disply the all name of emplpoee form emp table..


set serveroutput on
-------declare cursor-------------declare
cursor emp_cur
IS select ename from emp;
emp_rec emp_cur%rowtype;
begin
---------open the cursor----------open emp_cur;
----------Fetch the cursor--------fetch emp_cur into emp_rec;
dbms_output.put_line('NAME=>'||emp_rec.ename);
-----------close the cursor---------
close emp_cur;
end;
/
--------------------------------

Query to find any Highest no.

Here i write a simple query to find any highest no from a list ..

for example--
Table_name---EMP

ename     salary
1            10,000
2             2000
3            5000
4            6000
5            7000

if u want to find 2nd highest salary from  emp table then...
-------------------------------------------------------------------
select min(sal)
           from ( select sal from emp order by sal desc)
  where rownum<=:2;
----------------------------------------------------------------------
output will show lyk this
SAL
7000.
----------------------------------------------------------------------
Detail explanation--

1)..1st u can sort the all record in descending order
at this stage SAL  look lyk this
10000
7000
6000
5000
2000

2) then u get th "highest" salary at 1st row,,,2nd hieghest at 2nd row, 3rdhieghest at 3rd row,onwords...

3)then select only 2nd row ...coz it disply 2nd hieghest salary....



IF U WANT TO FIND 3RD HIEGHEST SALARY THEN INSTEAD OF ROWNUM:=2 U CAN PUT ROWNUM:=3 IN QUERY ......

But Problem will occure If there are same value in two rows..

EX-
 salary
10000
7000
7000

5000
2000
 in above table2nd and 3rd column contain same value...
and if u need to show both row
then u can use following query-----
-------------------------------------------------------------------------- 
select sal
    from emp
    where sal=(
                       select min(sal)
                            from ( select sal from emp order by sal desc)
                               where rownum<=:2
                     );
-----------------------------------------------------------------------------------------------





hi frndz i hop this query will help u if any one ask u to find any heighest salary......

this is comman question asked for fresher during technical interview.....