Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE Writeing in multiple text files (Oracle SQL Developer)
icon5.gif  UTL_FILE Writeing in multiple text files [message #647310] Tue, 26 January 2016 09:35 Go to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
Hi,

I've created a stored procedure which select 5 random records (Department ID, Department Name and Location ID) in departments table and select corresponding employee records (all details of the employee) in employees table for the department ID and write in a text file.

Attached the stored procedure.

My current output shows Department ID, Department Name, Location ID and below that the employee details. What I need is, I want to create 5 different text files for each department ID.

Can you please help me.

Thanks in advance.

create or replace PROCEDURE PROC_CREATE_FILES
IS
  V_WRITE_FILE UTL_FILE.FILE_TYPE;
  V_WRITE_LINE VARCHAR2(500);
  
BEGIN
  V_WRITE_FILE := UTL_FILE.FOPEN('MYDIR', 'DEPARTMENTNAME.TXT', 'W');
  FOR EMPREC IN
  (SELECT *
   FROM
    (SELECT DEPARTMENT_ID, DEPARTMENT_NAME, STREET_ADDRESS
    FROM DEPARTMENTS D
    JOIN LOCATIONS L USING (LOCATION_ID)
    ORDER BY DBMS_RANDOM.RANDOM)
  WHERE ROWNUM <= 5)
  LOOP
    V_WRITE_LINE := CHR(9)||CHR(9)||EMPREC.DEPARTMENT_ID||chr(9)||
    EMPREC.DEPARTMENT_NAME||CHR(9)||EMPREC.STREET_ADDRESS;
    UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
    
    BEGIN
      FOR EMPCUR IN
      (SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = EMPREC.DEPARTMENT_ID)
      LOOP
        V_WRITE_LINE := EMPCUR.EMPLOYEE_ID||chr(9)||EMPCUR.FIRST_NAME||chr(9)||
        EMPCUR.LAST_NAME||chr(9)||EMPCUR.SALARY||chr(9)|| EMPCUR.HIRE_DATE||
        chr(9)||EMPCUR.MANAGER_ID||CHR(9)||EMPCUR.DEPARTMENT_ID;
        
        UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
      END LOOP;
    END;
  END LOOP;
  UTL_FILE.FCLOSE(V_WRITE_FILE);
END;



[mod-edit: code from attached text file displayed inside post by bb]

[Updated on: Tue, 26 January 2016 10:44] by Moderator

Report message to a moderator

Re: UTL_FILE Writeing in multiple text files [message #647313 is a reply to message #647310] Tue, 26 January 2016 10:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So open the file inside the department loop
Re: UTL_FILE Writeing in multiple text files [message #647316 is a reply to message #647310] Tue, 26 January 2016 11:29 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
Can you please assist me?

Below is my code. It creates 4 files with department name. One file with 'NEW'. But none of the files contains employee details.

Here is my code.
create or replace PROCEDURE PROC_CREATE_FILES_NEW
IS
  V_WRITE_FILE UTL_FILE.FILE_TYPE;
  V_WRITE_LINE VARCHAR2(500);
  V_TEMP_FILE VARCHAR2(1000);
  DEPTNAME VARCHAR2(100) := 'NEW';
  
BEGIN
  FOR EMPREC IN
  (SELECT *
   FROM
    (SELECT DEPARTMENT_ID, DEPARTMENT_NAME, STREET_ADDRESS
    FROM DEPARTMENTS D
    JOIN LOCATIONS L USING (LOCATION_ID)
    ORDER BY DBMS_RANDOM.RANDOM)
  WHERE ROWNUM <= 5)
  LOOP
    V_WRITE_FILE := UTL_FILE.FOPEN('MYDIR', V_TEMP_FILE, 'W');
    IF EMPREC.DEPARTMENT_NAME <> DEPTNAME THEN
    V_TEMP_FILE := EMPREC.DEPARTMENT_NAME||'.TXT';
    
    V_WRITE_LINE := CHR(9)||CHR(9)||EMPREC.DEPARTMENT_ID||chr(9)||
    EMPREC.DEPARTMENT_NAME||CHR(9)||EMPREC.STREET_ADDRESS;
    UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
    
  
      FOR EMPCUR IN
      (SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = EMPREC.DEPARTMENT_ID)
      LOOP
        V_WRITE_LINE := EMPCUR.EMPLOYEE_ID||chr(9)||EMPCUR.FIRST_NAME||chr(9)||
        EMPCUR.LAST_NAME||chr(9)||EMPCUR.SALARY||chr(9)|| EMPCUR.HIRE_DATE||
        chr(9)||EMPCUR.MANAGER_ID||CHR(9)||EMPCUR.DEPARTMENT_ID;
        
        UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
      END LOOP;
      END IF;
  END LOOP;
  UTL_FILE.FCLOSE(V_WRITE_FILE);
END;

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Tue, 26 January 2016 11:40] by Moderator

Report message to a moderator

Re: UTL_FILE Writeing in multiple text files [message #647317 is a reply to message #647316] Tue, 26 January 2016 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Since we don't have your tables or data, we can't run, test, or debug posted code.
Re: UTL_FILE Writeing in multiple text files [message #647318 is a reply to message #647316] Tue, 26 January 2016 11:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
CREATE OR REPLACE PROCEDURE proc_create_files
AS
  v_write_file  UTL_FILE.FILE_TYPE;
  v_write_line  VARCHAR2(500);
BEGIN
  FOR emprec IN
    (SELECT *
     FROM
      (SELECT department_id, department_name, street_address
       FROM   departments d
       JOIN   locations l USING (location_id)
       ORDER  BY DBMS_RANDOM.RANDOM)
     WHERE  ROWNUM <= 5)
  LOOP
    v_write_file := UTL_FILE.FOPEN ('MYDIR', emprec.department_name || '.TXT', 'W');
    v_write_line := CHR(9) || CHR(9) || emprec.department_id || chr(9) ||
    emprec.department_name || CHR(9) || emprec.street_address;
    UTL_FILE.PUT_LINE (v_write_file, v_write_line);
    BEGIN
      FOR empcur IN
        (SELECT * FROM employees WHERE department_id = emprec.department_id)
      LOOP
        v_write_line := empcur.employee_id || CHR(9) || empcur.first_name || CHR(9) ||
        empcur.last_name || CHR(9) || empcur.salary || CHR(9) || empcur.hire_date ||
        CHR(9) || empcur.manager_id || CHR(9) || empcur.department_id;
        UTL_FILE.PUT_LINE (v_write_file, v_write_line);
      END LOOP;
    END;
    UTL_FILE.FCLOSE (v_write_file);
  END LOOP;
END;
/

Re: UTL_FILE Writeing in multiple text files [message #647319 is a reply to message #647317] Tue, 26 January 2016 11:51 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
Hi,

Thank you very much for suggesting me to open the file inside the Department loop. It worked.

Thanks again.
Re: UTL_FILE Writeing in multiple text files [message #647323 is a reply to message #647319] Tue, 26 January 2016 12:16 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
Thank You very much.

It is working for me.

I've one doubt, If I want to use aggregate functions here, how do i use it.

For example,

I want to sum the salary of the all the employees in the department and display at the of the file.
Re: UTL_FILE Writeing in multiple text files [message #647325 is a reply to message #647323] Tue, 26 January 2016 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can add a variable you initialize to 0 at the beginning of each department and add the salary of the employees in the employees loop and write the line before the END LOOP of the department (before FCLOSE).

[Updated on: Tue, 26 January 2016 12:30]

Report message to a moderator

Re: UTL_FILE Writeing in multiple text files [message #647326 is a reply to message #647325] Tue, 26 January 2016 12:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Or, instead of incrementing a variable, you can use an analytic function:

CREATE OR REPLACE PROCEDURE proc_create_files
AS
  v_write_file  UTL_FILE.FILE_TYPE;
  v_write_line  VARCHAR2(500);
BEGIN
  FOR emprec IN
    (SELECT *
     FROM
      (SELECT department_id, department_name, street_address
       FROM   departments d
       JOIN   locations l USING (location_id)
       ORDER  BY DBMS_RANDOM.RANDOM)
     WHERE  ROWNUM <= 5)
  LOOP
    v_write_file := UTL_FILE.FOPEN ('MYDIR', emprec.department_name || '.TXT', 'W');
    v_write_line := CHR(9) || CHR(9) || emprec.department_id || chr(9) ||
    emprec.department_name || CHR(9) || emprec.street_address;
    UTL_FILE.PUT_LINE (v_write_file, v_write_line);
    BEGIN
      FOR empcur IN
        (SELECT employees.*,
                SUM (salary) OVER (PARTITION BY department_id) sum_sal 
         FROM   employees 
         WHERE  department_id = emprec.department_id)
      LOOP
        v_write_line := empcur.employee_id || CHR(9) || empcur.first_name || CHR(9) ||
        empcur.last_name || CHR(9) || empcur.salary || CHR(9) || empcur.hire_date ||
        CHR(9) || empcur.manager_id || CHR(9) || empcur.department_id;
        UTL_FILE.PUT_LINE (v_write_file, v_write_line);
        v_write_line := CHR(9) || CHR(9) || CHR(9) || empcur.sum_sal;
      END LOOP;
    END;
    UTL_FILE.PUT_LINE (v_write_file, v_write_line);
    UTL_FILE.FCLOSE (v_write_file);
  END LOOP;
END;
/

Re: UTL_FILE Writeing in multiple text files [message #647327 is a reply to message #647326] Tue, 26 January 2016 12:45 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
You guys are very helpful. Thank You very much.

I am learning of lots of new concepts now.

I am very grateful to all of you guys.

Thanks again.
Re: UTL_FILE Writeing in multiple text files [message #647328 is a reply to message #647326] Tue, 26 January 2016 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As PL/SQL is used here, I wanted to not complicate the SQL part, otherwise you can do it like this:
SQL> select ename, sum(sal) sal
  2  from emp
  3  where deptno = 10
  4  group by grouping sets(deptno, ename)
  5  /
ENAME             SAL
---------- ----------
CLARK            2450
MILLER           1300
KING             5000
                 8750

Then no need of the sum specific line, it is included in the employees loop.

Re: UTL_FILE Writeing in multiple text files [message #647431 is a reply to message #647328] Wed, 27 January 2016 12:02 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
Thank You.

The below procedure print department details and corresponding employees with their sum of salary to a text file.

What If I want to print their first name and last name after "The sum of the salary for the department".

Do I need to use separate loop for printing employee details ? Here is my code below.

create or replace PROCEDURE PROC_CREATE_FILES_RANDOM
IS
  V_WRITE_FILE UTL_FILE.FILE_TYPE;
  V_WRITE_LINE1 VARCHAR2(500);
  V_WRITE_LINE  VARCHAR2(500);
  V_TEMP_FILE   VARCHAR2(1000);
  V_SUM_SALARY  VARCHAR2(100) := 0;
  V_ENAME VARCHAR2(50);
BEGIN
  FOR EMPREC IN
  (SELECT     *
  FROM
    (SELECT DEPARTMENT_ID,
      DEPARTMENT_NAME,
      STREET_ADDRESS
    FROM DEPARTMENTS D
    JOIN LOCATIONS L USING (LOCATION_ID))
  WHERE ROWNUM      <= 5
  AND DEPARTMENT_ID IN
    (SELECT DEPARTMENT_ID
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID
    HAVING COUNT(EMPLOYEE_ID) > 7))
  -- ORDER BY DBMS_RANDOM.RANDOM)
  LOOP
    V_TEMP_FILE  := EMPREC.DEPARTMENT_NAME||'.TXT';
    V_WRITE_FILE := UTL_FILE.FOPEN('MYDIR', V_TEMP_FILE, 'W');
    V_WRITE_LINE := CHR(9)||EMPREC.DEPARTMENT_ID||chr(9)|| EMPREC.DEPARTMENT_NAME||CHR(9)||EMPREC.STREET_ADDRESS;
    UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
    UTL_FILE.FFLUSH(V_WRITE_FILE);
    BEGIN
      FOR EMPCUR IN
      (SELECT E.*,
        SUM(salary) OVER (PARTITION BY DEPARTMENT_ID) AS SUM_SAL
      FROM EMPLOYEES E
      WHERE ROWNUM  <=5
      AND DEPARTMENT_ID = EMPREC.DEPARTMENT_ID
      ORDER BY DBMS_RANDOM.RANDOM)
      LOOP
        V_WRITE_LINE := EMPCUR.EMPLOYEE_ID||chr(9)||EMPCUR.SALARY;
        V_SUM_SALARY := 'The sum of salary for the department : '||EMPCUR.SUM_SAL;
        V_ENAME := EMPCUR.FIRST_NAME||' , '||EMPCUR.LAST_NAME;
        V_WRITE_LINE1 := CHR(9)||CHR(9)||V_SUM_SALARY;
        UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
      END LOOP;
        UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE1);
//This is where I got stuck.	
        UTL_FILE.PUT_LINE(V_WRITE_FILE, V_ENAME);
        UTL_FILE.FFLUSH(V_WRITE_FILE);
        
     END;
    UTL_FILE.FCLOSE(V_WRITE_FILE);
  END LOOP;
END;



[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Wed, 27 January 2016 14:29] by Moderator

Report message to a moderator

Re: UTL_FILE Writeing in multiple text files [message #647432 is a reply to message #647431] Wed, 27 January 2016 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again, Please read How to use [code] tags and make your code easier to read.

Compare your post and the ones above, which ones are easier and more understandable to read?

Re: UTL_FILE Writeing in multiple text files [message #647435 is a reply to message #647431] Wed, 27 January 2016 14:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Your new requirement is unclear. You need to provide a complete example that includes create table statements, insert statements for sample data, and the results that you want in text files based on that data. All this is stated in the forum guide that BlackSwan already referred you to.
Re: UTL_FILE Writeing in multiple text files [message #647440 is a reply to message #647435] Wed, 27 January 2016 14:48 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
I'm sorry my bad.

I got the needed result after going through some of the questionnaire in this forum.

I will post clearly next time.

Thanks.
Re: UTL_FILE Writeing in multiple text files [message #647500 is a reply to message #647440] Fri, 29 January 2016 13:43 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
These are my tables.

CREATE TABLE employees
    (
        employee_id    NUMBER(6) ,
        first_name     VARCHAR2(20) ,
        last_name      VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL ,
        email          VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL ,
        phone_number   VARCHAR2(20) ,
        hire_date      DATE CONSTRAINT emp_hire_date_nn NOT NULL ,
        job_id         VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL ,
        salary         NUMBER(8,2) ,
        commission_pct NUMBER(2,2) ,
        manager_id     NUMBER(6) ,
        department_id  NUMBER(4) ,
        CONSTRAINT emp_salary_min CHECK (salary > 0) ,
        CONSTRAINT emp_email_uk
	);		



			
CREATE TABLE departments
    (
        department_id                               NUMBER (4) NOT NULL ,
        department_name                             VARCHAR2 (30) NOT NULL ,
        CONSTRAINT dept_name_nn not NULL 
	manager_id NUMBER(6) 
	location_id NUMBER (4)
    ) ;
			 
			 
CREATE TABLE locations
  (
     location_id    NUMBER(4),
     street_address VARCHAR2(40),
     postal_code    VARCHAR2(12),
     city           VARCHAR2(30) CONSTRAINT loc_city_nn NOT NULL,
     state_province VARCHAR2(25),
     country_id     CHAR(2)
  ); 



The below are the data in the table. Employees table data attached in text file.

Departments table data:

INSERT INTO departments VALUES ( 10 , 'Administration' , 200 , 1700 ); 
INSERT INTO departments VALUES ( 20 , 'Marketing' , 201 , 1800 ); 
INSERT INTO departments VALUES ( 30 , 'Purchasing' , 114 , 1700 ); 
INSERT INTO departments VALUES ( 40 , 'Human Resources' , 203 , 2400 ); 
INSERT INTO departments VALUES ( 50 , 'Shipping' , 121 , 1500 ); 
INSERT INTO departments VALUES ( 60 , 'IT' , 103 , 1400 ); 
INSERT INTO departments VALUES ( 70 , 'Public Relations' , 204 , 2700 ); 
INSERT INTO departments VALUES ( 80 , 'Sales' , 145 , 2500 ); 
INSERT INTO departments VALUES ( 90 , 'Executive' , 100 , 1700 ); 
INSERT INTO departments VALUES ( 100 , 'Finance' , 108 , 1700 ); 
INSERT INTO departments VALUES ( 110 , 'Accounting' , 205 , 1700 ); 
INSERT INTO departments VALUES ( 120 , 'Treasury' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 130 , 'Corporate Tax' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 140 , 'Control And Credit' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 150 , 'Shareholder Services' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 160 , 'Benefits' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 170 , 'Manufacturing' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 180 , 'Construction' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 190 , 'Contracting' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 200 , 'Operations' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 210 , 'IT Support' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 220 , 'NOC' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 230 , 'IT Helpdesk' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 240 , 'Government Sales' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 250 , 'Retail Sales' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 260 , 'Recruiting' , NULL , 1700 ); 
INSERT INTO departments VALUES ( 270 , 'Payroll' , NULL , 1700 );


Locations table data:
INSERT INTO locations VALUES ( 1000 , '1297 Via Cola di Rie' , '00989' , 'Roma' , NULL , 'IT' ); 
INSERT INTO locations VALUES ( 1100 , '93091 Calle della Testa' , '10934' , 'Venice' , NULL , 'IT' ); 
INSERT INTO locations VALUES ( 1200 , '2017 Shinjuku-ku' , '1689' , 'Tokyo' , 'Tokyo Prefecture' , 'JP' ); 
INSERT INTO locations VALUES ( 1300 , '9450 Kamiya-cho' , '6823' , 'Hiroshima' , NULL , 'JP' ); 
INSERT INTO locations VALUES ( 1400 , '2014 Jabberwocky Rd' , '26192' , 'Southlake' , 'Texas' , 'US' ); 
INSERT INTO locations VALUES ( 1500 , '2011 Interiors Blvd' , '99236' , 'South San Francisco' , 'California' , 'US' ); 
INSERT INTO locations VALUES ( 1600 , '2007 Zagora St' , '50090' , 'South Brunswick' , 'New Jersey' , 'US' ); 
INSERT INTO locations VALUES ( 1700 , '2004 Charade Rd' , '98199' , 'Seattle' , 'Washington' , 'US' ); 
INSERT INTO locations VALUES ( 1800 , '147 Spadina Ave' , 'M5V 2L7' , 'Toronto' , 'Ontario' , 'CA' ); 
INSERT INTO locations VALUES ( 1900 , '6092 Boxwood St' , 'YSW 9T2' , 'Whitehorse' , 'Yukon' , 'CA' ); 
INSERT INTO locations VALUES ( 2000 , '40-5-12 Laogianggen' , '190518' , 'Beijing' , NULL , 'CN' ); 
INSERT INTO locations VALUES ( 2100 , '1298 Vileparle (E)' , '490231' , 'Bombay' , 'Maharashtra' , 'IN' ); 
INSERT INTO locations VALUES ( 2200 , '12-98 Victoria Street' , '2901' , 'Sydney' , 'New South Wales' , 'AU' ); 
INSERT INTO locations VALUES ( 2300 , '198 Clementi North' , '540198' , 'Singapore' , NULL , 'SG' ); 
INSERT INTO locations VALUES ( 2400 , '8204 Arthur St' , NULL , 'London' , NULL , 'UK' ); 
INSERT INTO locations VALUES ( 2500 , 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford' , 'Oxford' , 'UK' ); 
INSERT INTO locations VALUES ( 2600 , '9702 Chester Road' , '09629850293' , 'Stretford' , 'Manchester' , 'UK' ); 
INSERT INTO locations VALUES ( 2700 , 'Schwanthalerstr. 7031' , '80925' , 'Munich' , 'Bavaria' , 'DE' ); 
INSERT INTO locations VALUES ( 2800 , 'Rua Frei Caneca 1360 ' , '01307-002' , 'Sao Paulo' , 'Sao Paulo' , 'BR' ); 
INSERT INTO locations VALUES ( 2900 , '20 Rue des Corps-Saints' , '1730' , 'Geneva' , 'Geneve' , 'CH' ); 
INSERT INTO locations VALUES ( 3000 , 'Murtenstrasse 921' , '3095' , 'Bern' , 'BE' , 'CH' ); 
INSERT INTO locations VALUES ( 3100 , 'Pieter Breughelstraat 837' , '3029SK' , 'Utrecht' , 'Utrecht' , 'NL' ); 
INSERT INTO locations VALUES ( 3200 , 'Mariano Escobedo 9991' , '11932' , 'Mexico City' , 'Distrito Federal,' , 'MX' );


My requirement is,

I need to select any 5 random records of Department_ID, Department_Name and Street Address from Departments and Locations table and corresponding Employees for the Department_ID. This need to put into 5 separate text files for each department. The departments should have more than 5 employees

The last line should contain "The Sum of the Salary for the Department : " and the next line should contain first_name and last_name for all the employees displayed in the text file.

Example:
Department_ID, 		Department_Name 		Street Address
10		        Sales				123, ABC

Employee_ID, 		Salary
	1		100
	2		200
	3		300
	4		400
	5		500

The sum of the Salary for the department_ID : 1500

First_name||Last_name - First_name||Last_name - First_name||Last_name - First_name||Last_name - First_name||Last_name


I've created the below procedure which provides me output as same as above.

Here is my stored procedure.

CREATE OR replace PROCEDURE Proc_create_files_random
IS
  v_write_file  utl_file.file_type;
  v_write_line1 VARCHAR2(500);
  v_write_line  VARCHAR2(500);
  v_temp_file   VARCHAR2(1000);
  v_sum_salary  VARCHAR2(100) := 0;
  v_ename       VARCHAR2(50);
  v_trim_ename  VARCHAR2(100);
BEGIN
    FOR emprec IN (SELECT *
                   FROM   (SELECT department_id,
                                  department_name,
                                  street_address
                           FROM   departments D
                                  join locations L USING (location_id))
                   WHERE  ROWNUM <= 5
                          AND department_id IN (SELECT department_id
                                                FROM   employees
                                                GROUP  BY department_id
                                                HAVING Count(employee_id) > 7))
    -- ORDER BY DBMS_RANDOM.RANDOM)
    LOOP
        v_temp_file := emprec.department_name
                       ||'.TXT';

        v_write_file := utl_file.Fopen('MYDIR', v_temp_file, 'W');

        v_write_line := Chr(9)
                        ||emprec.department_id
                        ||Chr(9)
                        || emprec.department_name
                        ||Chr(9)
                        ||emprec.street_address;

        utl_file.Put_line(v_write_file, v_write_line);

        utl_file.Fflush(v_write_file);

        BEGIN
            FOR empcur IN (SELECT E.*,
                                  SUM(salary)
                                    over (
                                      PARTITION BY department_id) AS SUM_SAL
                           FROM   employees E
                           WHERE  ROWNUM <= 5
                                  AND department_id = emprec.department_id
                           ORDER  BY dbms_random.random) LOOP
                v_write_line := empcur.employee_id
                                ||Chr(9)
                                ||empcur.salary;

                v_sum_salary := 'The sum of salary for the department : '
                                ||empcur.sum_sal;

                v_write_line1 := Chr(9)
                                 ||Chr(9)
                                 ||v_sum_salary;

                utl_file.Put_line(v_write_file, v_write_line
                                                ||Chr(10));

                v_ename := ( empcur.first_name
                             ||','
                             ||empcur.last_name );
            END LOOP;

            utl_file.Put_line(v_write_file, v_write_line1);

            utl_file.Putf(v_write_file, v_ename);

            utl_file.Fflush(v_write_file);
        END;

        utl_file.Fclose(v_write_file);
    END LOOP;
END; 



But the V_ENAME is not printing for all the employees. It prints for the first record. If I call it within the loop, then it prints in between the lines.

My current output :

	80	Sales	Magdalen Centre, The Oxford Science Park
146	13500

149	10500

148	11000

145	14000

147	12000

		The sum of salary for the department : 61000
Alberto,Errazuriz


Can you please guide me ?
  • Attachment: Employees.txt
    (Size: 17.70KB, Downloaded 1007 times)
Re: UTL_FILE Writeing in multiple text files [message #647501 is a reply to message #647500] Fri, 29 January 2016 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And your Oracle version is?

Re: UTL_FILE Writeing in multiple text files [message #647502 is a reply to message #647501] Fri, 29 January 2016 14:50 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE	11.2.0.2.0	Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Re: UTL_FILE Writeing in multiple text files [message #647503 is a reply to message #647502] Fri, 29 January 2016 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
First make it work, then make it fancy.
If you can NOT make it work for single hard coded department, you are wasting your time trying to make it work for more than 1 department.
Re: UTL_FILE Writeing in multiple text files [message #647504 is a reply to message #647503] Fri, 29 January 2016 15:16 Go to previous messageGo to next message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
It is working for all the departments when I run the procedure. It creates text files of departments which is having more than 5 employees. Also it prints the Sum of the salary for that department as well.

My concern is,
If I need to print the First_Name and Last_name after the sum of salary. What would I need to do ?
Re: UTL_FILE Writeing in multiple text files [message #647512 is a reply to message #647504] Fri, 29 January 2016 21:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You need to make sure that your v_ename variable is big enough to hold all of the names for one department.

You need to initialize the v_ename variable to a blank string for each department before looping through the emp_cur.

You need to concatenate each first and last name to v_ename within the emp_cur loop.

You need to output the v_ename after the emp_cur loop for each department using put_line in the same manner that you output everything else, trimming any excess spaces and hyphen.

Please see the comment lines before each usage of v_ename in the corrected code below. I made a few other minor changes such as eliminating an unnecessary begin and end.

CREATE OR replace PROCEDURE Proc_create_files_random
IS
  v_temp_file    VARCHAR2(1000);
  v_write_file   utl_file.file_type;
  v_write_line   VARCHAR2(500);
  v_sum_salary   VARCHAR2(100) := 0;
  v_write_line1  VARCHAR2(500);
  -- make size of v_ename variable big enough to store all of the names for one department:
  v_ename        VARCHAR2(32767);
BEGIN
  FOR emprec IN 
    (SELECT *
     FROM   (SELECT department_id,
                    department_name,
                    street_address
             FROM   departments D
                    join locations L USING (location_id))
     WHERE  ROWNUM <= 5
     AND    department_id IN 
            (SELECT department_id
             FROM   employees
             GROUP  BY department_id
             HAVING Count(employee_id) > 7))
  LOOP
    v_temp_file := emprec.department_name || '.TXT';
    v_write_file := utl_file.Fopen ('MYDIR', v_temp_file, 'W');
    v_write_line := Chr(9)
      || emprec.department_id
      || Chr(9)
      || emprec.department_name
      || Chr(9)
      || emprec.street_address;
    utl_file.Put_line (v_write_file, v_write_line);
    utl_file.Fflush(v_write_file);
    -- initialize v_ename to empty string for each department:
    v_ename := '';
    FOR empcur IN 
      (SELECT E.*,
              SUM(salary) over (PARTITION BY department_id) AS SUM_SAL
       FROM   employees E
       WHERE  ROWNUM <= 5
       AND    department_id = emprec.department_id
       ORDER  BY dbms_random.random) 
    LOOP
      v_write_line := empcur.employee_id || Chr(9) || empcur.salary;
      v_sum_salary := 'The sum of salary for the department : ' || empcur.sum_sal;
      v_write_line1 := Chr(9) ||Chr(9) || v_sum_salary;
      utl_file.Put_line (v_write_file, v_write_line);
      -- concatenate each new first and last name to v_ename:
      v_ename := v_ename || ' - ' || empcur.first_name || ' ' || empcur.last_name;
    END LOOP;
    utl_file.Put_line (v_write_file, v_write_line1);
    -- output trimmed names:
    utl_file.Put_line (v_write_file, ltrim (v_ename, ' - '));
    utl_file.Fflush (v_write_file);
    utl_file.Fclose(v_write_file);
  END LOOP;
END; 
/

Re: UTL_FILE Writeing in multiple text files [message #647513 is a reply to message #647512] Fri, 29 January 2016 21:49 Go to previous message
thsankar
Messages: 11
Registered: January 2016
Location: Houston
Junior Member
Hi,

Thank you for the response.

It worked. Thanks again.
Previous Topic: How can I pass UserId/Password securely as a parameter using UTL_HTTP
Next Topic: Regarding row lock
Goto Forum:
  


Current Time: Thu Mar 28 13:19:18 CDT 2024