Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE Writeing in multiple text files (Oracle SQL Developer)
UTL_FILE Writeing in multiple text files [message #647310] |
Tue, 26 January 2016 09:35 |
|
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 #647316 is a reply to message #647310] |
Tue, 26 January 2016 11:29 |
|
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 #647326 is a reply to message #647325] |
Tue, 26 January 2016 12:39 |
|
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 #647431 is a reply to message #647328] |
Wed, 27 January 2016 12:02 |
|
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 #647500 is a reply to message #647440] |
Fri, 29 January 2016 13:43 |
|
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 ?
|
|
|
|
|
|
|
Re: UTL_FILE Writeing in multiple text files [message #647512 is a reply to message #647504] |
Fri, 29 January 2016 21:32 |
|
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;
/
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 13:19:18 CDT 2024
|