O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Expert

Q:

14-23.

You can’t simply add a second argument with a default value in this case; the earlier calls to calc.totals in the Oracle Forms applications don’t compile, because they haven’t passed values for all the arguments. You must instead rely on overloading:

PACKAGE calc
IS
   PROCEDURE totals (
      department_id_in IN department.department_id%TYPE);

   PROCEDURE totals (
      department_id_in IN department.department_id%TYPE,
      section_id_in IN INTEGER);
END calc;

Inside an Oracle Forms application, a developer can now call calc.totals in one of two ways, as shown in these examples:

calc.totals (:emp.deptno);
calc.totals (my_dept_var, :sections.section_id);

Existing calls to calc.totals do not need to be changed; new calls to the calc.totals procedure providing a section will unambiguously be routed to the “second” implementation of the totals procedure.

Q:

14-24.

To remove the redundancies, you can use a local procedure (defined in the declaration section) to centralize all common code elements:

PROCEDURE calc_percentages (total_in IN NUMBER)
IS
   FUNCTION formatted_pct  (val_in IN NUMBER)
      RETURN VARCHAR2 IS
   BEGIN
      RETURN TO_CHAR ((val_in/total_in) * 100, '$999,999');
   END;
BEGIN
   food_sales_stg := formatted_pct  (sales.food_sales);
   service_sales_stg := formatted_pct  (sales.service_sales);
   toy_sales_stg := formatted_pct  (sales.toy_sales);
END;

Q:

14-25.

This is a poorly designed function for a number of reasons:

  • The function returns two different pieces of information: the company name as the RETURN value ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required