plsql - oracle - write package output results to a file on client side -
so have this:
create or replace package my_first_package procedure employee_analysis (p_id in number := 100, /*default formal parameter no arguments invokation */ p_percent in number := 0.01); /* -||- */ end my_first_package; create or replace package body my_first_package procedure employee_analysis (p_id in number := 100, p_percent in number := 0.01) cursor c_city (select l.city employees e inner join departments d on (e.department_id = d.department_id) inner join locations l on (l.location_id = d.location_id) e.employee_id = p_id); cursor c_manager (select e1.last_name employees e1 inner join employees e2 on (e1.employee_id = e2.manager_id) e2.employee_id = p_id); cursor c_department_name (select department_name employees e inner join departments d on (e.department_id = d.department_id) e.employee_id = p_id); /*-----------------------------------------------------------------------------*/ v_annual_sal number(9,2); v_monthly_sal number(9,2); v_last_name varchar2(10); v_deptno number(3); v_length number(2); v_tenure number(5); v_job_id varchar2(20); v_hire_date date; v_city varchar(25); v_commission_pct number(2,2); v_phone_number varchar2(20); v_manager varchar2(20); v_comm_calc number(10,2); v_email varchar2(10); v_department varchar2(20); v_count number(4); v_old_salary number(9,2); v_new_salary number(9,2); v_lname varchar2(10); v_phone_number_format varchar2(25); v_phone_number_length number(3); v_tax number(8,4); v_sum_sal_departments number; begin dbms_output.put_line('welcome summary of employee based on unique id'); dbms_output.put_line('============================================================'); /*-----------------------------------------------------------------------------*/ select salary, last_name, department_id, trunc(months_between(sysdate,hire_date),0), job_id, hire_date, commission_pct, phone_number, email, length(phone_number) v_monthly_sal, v_last_name, v_deptno, v_tenure, v_job_id, v_hire_date, v_commission_pct, v_phone_number, v_email, v_phone_number_length employees employee_id = p_id; /*-----------------------------------------------------------------------------*/ v_count := sql%rowcount; dbms_output.put_line(v_count||' employee found...'); /*-----------------------------------------------------------------------------*/ v_annual_sal := v_monthly_sal * 12; v_length := length(v_last_name); dbms_output.put_line('employee:-> ' || v_last_name || ' ,and name contains: ' || v_length ||' chars'); dbms_output.put_line(q'[belong's department: ]' || v_deptno); /*-----------------------------------------------------------------------------*/ if (v_monthly_sal < v_annual_sal) dbms_output.put_line('has annual salary of:-> ' || v_annual_sal); else dbms_output.put_line('something wrong in formula!'); end if; /*-------------------------------------------------------------------------------*/ if v_commission_pct null dbms_output.put_line('no commission added annual salary!'); else dbms_output.put_line('commission percentage salary is:-> '|| v_commission_pct ||'%'); v_comm_calc := (v_annual_sal * v_commission_pct) + v_annual_sal; dbms_output.put_line('and calculated annual salary is:-> ' ||v_comm_calc); end if; /*-------------------------------------------------------------------------------*/ dbms_output.put_line('working for:-> '|| v_tenure || ' months '|| v_job_id); dbms_output.put_line('started in:-> '|| v_hire_date); /*-------------------------------------------------------------------------------*/ if v_phone_number_length = 12 v_phone_number_format := '(' || substr(v_phone_number,1,3) || ')' || '-' || substr(v_phone_number,5,3) || '-' || substr(v_phone_number,9,4); dbms_output.put_line('phone number:-> '|| v_phone_number_format); elsif v_phone_number_length = 18 v_phone_number_format := '(' || substr(v_phone_number,1,3) || ')' || '-' || substr(v_phone_number,5,2) || '-' || substr(v_phone_number,8,4) || '-' || substr(v_phone_number,13,6); dbms_output.put_line('phone number:-> '|| v_phone_number_format); else dbms_output.put_line('phone number digits not in range, check length of phone numbers table'); end if; /*-------------------------------------------------------------------------------*/ dbms_output.put_line('email:-> '||v_email); /*-------------------------------------------------------------------------------*/ open c_city; fetch c_city v_city; if c_city%found dbms_output.put_line('location:-> '||v_city); else dbms_output.put_line('employee location unknown'); end if; close c_city; /*-------------------------------------------------------------------------------*/ open c_manager; fetch c_manager v_manager; if c_manager%found dbms_output.put_line('is in eyes of manager:-> '||v_manager); else dbms_output.put_line('slave '||v_last_name||' free!'); end if; close c_manager; /*-------------------------------------------------------------------------------*/ open c_department_name; fetch c_department_name v_department; if c_department_name%found dbms_output.put_line('department name:-> '||v_department); else dbms_output.put_line('employee ' ||v_last_name||' belongs no department!'); end if; /*--------------------------------------------------------------------------------*/ dbms_output.put_line('checking current employee id:-> '|| p_id ||'..'); if (check_sal2(p_id) null) dbms_output.put_line('the function returned null due exception, therefore employee not exist!'); elsif (check_sal2(p_id)) dbms_output.put_line('employees salary > average of department '||v_deptno||' belongs.'); else dbms_output.put_line('salary < average of department '||v_deptno||', belongs.'); end if; /*--------------------------------------------------------------------------------*/ select salary v_old_salary employees employee_id = p_id; dbms_output.put_line('before raise of ' || p_percent || ' %, salary was:-> '|| v_old_salary); /*--------------------------------------------------------------------------------*/ if (p_percent > 0.01) dbms_output.put_line('maximum increase allowance moment 0.01 %, no increase in salary made'); elsif (p_percent < 0.01) dbms_output.put_line('minimum percent allowance moment 0.01 %, no increase in salary made'); else update employees set salary = salary * (1 + p_percent/100) employee_id = p_id; select last_name, salary v_lname, v_new_salary employees employee_id = p_id; dbms_output.put_line('after raise of ' || p_percent || ' %, salary is:-> '|| v_new_salary); end if; /*--------------------------------------------------------------------------------*/ dbms_output.put_line('==========================================================='); dbms_output.put_line('==========================================================='); dbms_output.put_line('==========================================================='); in (select sum(salary) "sumy", department_id employees group department_id) loop if i.department_id null dbms_output.put_line('unknown department '|| i.department_id ||' earns:-> '|| i.sumy); else dbms_output.put_line('department '|| i.department_id ||' earns:-> '|| i.sumy); end if; end loop; j in (select sum(sumy) "dep_sum" (select sum(salary) "sumy" employees group department_id)) loop v_sum_sal_departments := j.dep_sum; dbms_output.put_line('total income on departments:-> '|| j.dep_sum); end loop; select taxes_pkg.tax(salary) v_tax employees employee_id = p_id; dbms_output.put_line('salary after 0.08 % tax withdrawal:-> '|| v_tax); k in (select last_name employees salary = (select max(salary) employees)) loop dbms_output.put_line('employee highest salary is:-> '||k.last_name); end loop; k in (select last_name, hire_date employees hire_date = (select max(hire_date) employees)) loop dbms_output.put_line('our newest employees are:-> ' || k.last_name); end loop; m in (select last_name employees hire_date = (select min(hire_date) employees)) loop dbms_output.put_line('our oldest employees are:-> '||m.last_name); end loop; /*--------------------------------------------------------------------------------*/ exception when no_data_found dbms_output.put_line('employee id:-> ' || p_id || ' not exist, check data tables!'); when others dbms_output.put_line('unknown propagation'); end employee_analysis; end my_first_package;
i seached called utl_file package exporting data oracle database , on, didn't understand , didn't found looking for. want is, how can "export" of output screen after running useless package above? there way modify package add features , stuff it? possible? thanks...
Comments
Post a Comment