Last active
August 29, 2015 14:08
-
-
Save NekoTashi/0e1f63c9057f87248d5a to your computer and use it in GitHub Desktop.
Make a file that prints employees and departaments
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- AS SYS | |
| drop directory oraload; | |
| create directory oraload as "D:\oraload\"; | |
| grant execute on oraload to HR; | |
| grant read on directory oraload to hr; | |
| grant write on directory oraload to hr; | |
| -- AS HR | |
| select * from all_directories where directory_name = 'ORALOAD'; | |
| -- PL/SQL BLOCK | |
| DECLARE | |
| f utl_file.FILE_TYPE; | |
| line_dept_name VARCHAR2(400); | |
| line_data VARCHAR2(400); | |
| BEGIN | |
| f := utl_file.FOPEN('ORALOAD', 'something.txt', 'w'); | |
| FOR dept IN (select department_id, department_name from departments) LOOP | |
| line_dept_name := 'Departamento: ' || dept.department_id || ' - ' || dept.department_name; | |
| utl_file.put_line(f, line_dept_name); | |
| utl_file.put_line(f, ''); | |
| utl_file.put_line(f, '======================================================================='); | |
| utl_file.put_line(f, 'EMPID LAST_NAME FIRST_NAME SALARY COMM'); | |
| FOR row IN (select * from employees where department_id = dept.department_id) LOOP | |
| line_data := RPAD(row.employee_id, 14) || RPAD(row.last_name, 14) || RPAD(row.first_name, 20) || RPAD(row.salary, 17) || RPAD(row.commission_pct, 6); | |
| utl_file.put_line(f, line_data); | |
| END LOOP; | |
| utl_file.put_line(f, ''); | |
| utl_file.put_line(f, ''); | |
| END LOOP; | |
| utl_file.FCLOSE(f); | |
| END; | |
| / | |
| -- Results | |
| /* | |
| Departamento: 10 - Administration | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 200 Whalen Jennifer 4400 | |
| Departamento: 20 - Marketing | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 201 Hartstein Michael 13000 | |
| 202 Fay Pat 6000 | |
| Departamento: 30 - Purchasing | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 114 Raphaely Den 11000 | |
| 115 Khoo Alexander 3100 | |
| 116 Baida Shelli 2900 | |
| 117 Tobias Sigal 2800 | |
| 118 Himuro Guy 2600 | |
| 119 Colmenares Karen 2500 | |
| Departamento: 40 - Human Resources | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 203 Mavris Susan 6500 | |
| Departamento: 50 - Shipping | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 120 Weiss Matthew 8000 | |
| 121 Fripp Adam 8200 | |
| 122 Kaufling Payam 7900 | |
| 123 Vollman Shanta 6500 | |
| 124 Mourgos Kevin 5800 | |
| 125 Nayer Julia 3200 | |
| 126 Mikkilineni Irene 2700 | |
| 127 Landry James 2400 | |
| 128 Markle Steven 2200 | |
| 129 Bissot Laura 3300 | |
| 130 Atkinson Mozhe 2800 | |
| 131 Marlow James 2500 | |
| 132 Olson TJ 2100 | |
| 133 Mallin Jason 3300 | |
| 134 Rogers Michael 2900 | |
| 135 Gee Ki 2400 | |
| 136 Philtanker Hazel 2200 | |
| 137 Ladwig Renske 3600 | |
| 138 Stiles Stephen 3200 | |
| 139 Seo John 2700 | |
| 140 Patel Joshua 2500 | |
| 141 Rajs Trenna 3500 | |
| 142 Davies Curtis 3100 | |
| 143 Matos Randall 2600 | |
| 144 Vargas Peter 2500 | |
| 180 Taylor Winston 3200 | |
| 181 Fleaur Jean 3100 | |
| 182 Sullivan Martha 2500 | |
| 183 Geoni Girard 2800 | |
| 184 Sarchand Nandita 4200 | |
| 185 Bull Alexis 4100 | |
| 186 Dellinger Julia 3400 | |
| 187 Cabrio Anthony 3000 | |
| 188 Chung Kelly 3800 | |
| 189 Dilly Jennifer 3600 | |
| 190 Gates Timothy 2900 | |
| 191 Perkins Randall 2500 | |
| 192 Bell Sarah 4000 | |
| 193 Everett Britney 3900 | |
| 194 McCain Samuel 3200 | |
| 195 Jones Vance 2800 | |
| 196 Walsh Alana 3100 | |
| 197 Feeney Kevin 3000 | |
| 198 OConnell Donald 2600 | |
| 199 Grant Douglas 2600 | |
| Departamento: 60 - IT | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 103 Hunold Alexander 9000 | |
| 104 Ernst Bruce 6000 | |
| 105 Austin David 4800 | |
| 106 Pataballa Valli 4800 | |
| 107 Lorentz Diana 4200 | |
| Departamento: 70 - Public Relations | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 204 Baer Hermann 10000 | |
| Departamento: 80 - Sales | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 145 Russell John 14000 ,4 | |
| 146 Partners Karen 13500 ,3 | |
| 147 Errazuriz Alberto 12000 ,3 | |
| 148 Cambrault Gerald 11000 ,3 | |
| 149 Zlotkey Eleni 10500 ,2 | |
| 150 Tucker Peter 10000 ,3 | |
| 151 Bernstein David 9500 ,25 | |
| 152 Hall Peter 9000 ,25 | |
| 153 Olsen Christopher 8000 ,2 | |
| 154 Cambrault Nanette 7500 ,2 | |
| 155 Tuvault Oliver 7000 ,15 | |
| 156 King Janette 10000 ,35 | |
| 157 Sully Patrick 9500 ,35 | |
| 158 McEwen Allan 9000 ,35 | |
| 159 Smith Lindsey 8000 ,3 | |
| 160 Doran Louise 7500 ,3 | |
| 161 Sewall Sarath 7000 ,25 | |
| 162 Vishney Clara 10500 ,25 | |
| 163 Greene Danielle 9500 ,15 | |
| 164 Marvins Mattea 7200 ,1 | |
| 165 Lee David 6800 ,1 | |
| 166 Ande Sundar 6400 ,1 | |
| 167 Banda Amit 6200 ,1 | |
| 168 Ozer Lisa 11500 ,25 | |
| 169 Bloom Harrison 10000 ,2 | |
| 170 Fox Tayler 9600 ,2 | |
| 171 Smith William 7400 ,15 | |
| 172 Bates Elizabeth 7300 ,15 | |
| 173 Kumar Sundita 6100 ,1 | |
| 174 Abel Ellen 11000 ,3 | |
| 175 Hutton Alyssa 8800 ,25 | |
| 176 Taylor Jonathon 8600 ,2 | |
| 177 Livingston Jack 8400 ,2 | |
| 179 Johnson Charles 6200 ,1 | |
| Departamento: 90 - Executive | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 100 King Steven 24000 | |
| 101 Kochhar Neena 17000 | |
| 102 De Haan Lex 17000 | |
| Departamento: 100 - Finance | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 108 Greenberg Nancy 12000 | |
| 109 Faviet Daniel 9000 | |
| 110 Chen John 8200 | |
| 111 Sciarra Ismael 7700 | |
| 112 Urman Jose Manuel 7800 | |
| 113 Popp Luis 6900 | |
| Departamento: 110 - Accounting | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| 205 Higgins Shelley 12000 | |
| 206 Gietz William 8300 | |
| Departamento: 120 - Treasury | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 130 - Corporate Tax | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 140 - Control And Credit | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 150 - Shareholder Services | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 160 - Benefits | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 170 - Manufacturing | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 180 - Construction | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 190 - Contracting | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 200 - Operations | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 210 - IT Support | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 220 - NOC | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 230 - IT Helpdesk | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 240 - Government Sales | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 250 - Retail Sales | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 260 - Recruiting | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| Departamento: 270 - Payroll | |
| ======================================================================= | |
| EMPID LAST_NAME FIRST_NAME SALARY COMM | |
| */ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment