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

Popular posts from this blog

javascript - AngularJS custom datepicker directive -

javascript - jQuery date picker - Disable dates after the selection from the first date picker -