1. The whole point of a function is to return a value (whether it’s a single, scalar value or a composite, such as a record or a collection). If you also return data back through the parameter list with OUT or IN OUT arguments, the purpose and usage of the function will be obscured.

2. A function that contains an OUT or IN OUT parameter cannot be called from within a SQL statement. Here's an example:

   

• Make sure the name of the program reflects what it does: In this case, the program doesn't return just the title; it returns several pieces of information about an excuse. Let's call it the "excuse_info" function.
• Pass everything in the RETURN clause: Rather than returning one value through the RETURN clause and another through the parameter list, let's pass everything back in the RETURN clause, using a composite structure—in this case, a record.

• Return a record or collection of values 
  
  Make sure to publish the structure of your record or collection (the TYPE statement) in a package specification so that developers can understand and use the function more easily. 
  
• Break up a single function into multiple functions, all returning scalar values
  
  With this approach, you can call the functions from within SQL statements. 
   
• Change a function into a procedure
  
  Unless you need to call a function to return this information, just change it to a procedure returning multiple pieces of information through the OUT arguments in the parameter list

Oracle introduced a significant enhancement to PL/SQL’s SQL-related capabilities with the FORALL statement and BULK COLLECT clause for queries. Together, these are referred to as bulk processing statements for PL/SQL. Why, you might wonder, would this be necessary? We all know that PL/SQL is tightly integrated with the underlying SQL engine in the Oracle database. PL/SQL is the database programming language of choice for Oracle—even though you can now use Java inside the database as well.

But this tight integration does not mean that there is no overhead associated with running SQL from a PL/SQL program. When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine, but passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary.

This transfer of control between the PL/SQL and SQL engines is called a context switch. Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades. As you can see, PL/SQL and SQL might be tightly integrated on the syntactic level, but "under the covers" the integration is not as tight as it could be.

With FORALL and BULK COLLECT, however, you can fine-tune the way these two engines communicate, effectively telling the PL/SQL engine to compress multiple context switches into a single switch, thereby improving the performance of your applications. 

=============================================================

Would you please settle a minor argument for me? We're trying to institute coding standards at my company.

 

I've been pushing for developers to put explicit declarations of private functions and procedures in their packages. My co-workers argue back that they are unnecessary. Other than giving us the ability to reorganize the source as we see fit, I can't find anything that states additional benefits of using them. However, as I've always used them, I feel uncomfortable not asking for them.

 

Here's an example of how I use them:

CREATE OR REPLACE PACKAGE BODY my_sample_package
IS
   --===========================================
   -- PRIVATE DECLARATIONS
   --===========================================
   FUNCTION my_private_function_f (p_parm1_in IN NUMBER)
      RETURN BOOLEAN;

   PROCEDURE my_private_proc_p (
       p_parm1_in IN NUMBER, p_parm2_in IN NUMBER);

   --===========================================
   -- PRIVATE SOURCE
   --===========================================
   FUNCTION my_private_function_f (p_parm1_in IN NUMBER)
      RETURN BOOLEAN
   IS
   BEGIN
      IF p_parm1_in > 0
      THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;
   END my_private_function_f;

   PROCEDURE my_private_proc_p (
       p_parm1_in IN NUMBER, p_parm2_in IN NUMBER)
   IS
      l_my_var   NUMBER;
   BEGIN
      IF p_parm1_in > 0
      THEN
         l_my_var := p_parm2_in / p_parm1_in;
      END IF;
   END;
  
   --===========================================
   -- PUBLIC SOURCE
   --===========================================
   -- Blah
   -- Blah
END my_sample_package;

Can you give me any reasons why or why not private declarations should be used?

=============================================================

In computer programming, a forward declaration is a declaration of an identifier (denoting an entity such as a type, a variable, or a function) for which the programmer has not yet given a complete definition.

You can modularize at any of thse levels:

• Schema-level procedure or function: defined outside of a package, callable by any schema with execute authority on that module. I generally recommend against these. Put all your code in packages; easier to find, manage, enhance.
• Packaged subprograms: this is the main method of modularization. You define a set of programs in the package specification that will be used by other subprograms in the appliation. You can also define private subprograms that can only be called by other subprograms in that package.
• Local or nested subprograms: a procedure or function that is defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is defined only within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside that enclosing block.

The following anonymous block, for example, declares a local  procedure:

Code reduction leads to higher code quality because you have fewer lines to test and fewer potential bugs. It takes less effort to maintain the code because there is less to maintain. And when you do have to make a change, you make it in one place in the local module, and the effects are felt immediately throughout the parent module.

 Improve the readability of your code

Even if you do not repeat sections of code within a module, you still may want to pull out a set of related statements and package them into a local module. This can make it easier to follow the logic of the main body of the parent module.

When making use of lots of nested subprograms, your executable section becomes small and transparent in meaning. I will run through an example and then leave it to my readers to respond. The reason I say this is that I wrote this posting in response to a developer, who wrote to me as follows:

PROCEDURE distribute_calls (
   department_id_in IN employees.department_id%TYPE
)
IS
BEGIN
   WHILE (calls_still_unhandled ())
   LOOP
      FOR emp_rec IN support_dept_cur (department_id_in)
      LOOP
         IF call_analysis.current_caseload (emp_rec.employee_id)             call_analysis.avg_caseload_for_dept (department_id_in)
         THEN
            assign_next_open_call_to (emp_rec.employee_id, l_case_id);
            notify_customer (l_case_id);
         END IF;
      END LOOP;
   END LOOP;
END distribute_calls;

PROCEDURE distribute_calls (

   department_id_in IN employees.department_id%TYPE

)

IS

   l_case_id   cases.ID%TYPE;

 

   CURSOR support_dept_cur (

      department_id_in IN employees.department_id%TYPE

   )

   IS

      SELECT *

        FROM employees

       WHERE department_id = department_id_in;

 

   FUNCTION calls_still_unhandled

      RETURN BOOLEAN

   IS

   BEGIN

     

          RETURN NULL;

   END calls_still_unhandled;

 

   PROCEDURE assign_next_open_call_to (

      employee_id_in IN employees.employee_id%TYPE

    , case_id_out OUT cases.ID%TYPE

   )

   IS

   BEGIN

     NULL;

   END assign_next_open_call_to;

 

   PROCEDURE notify_customer (case_id_in IN cases.ID%TYPE)

   IS

   BEGIN

      NULL;

   END notify_customer;

BEGIN

   WHILE (calls_still_unhandled ())

   LOOP

      FOR emp_rec IN support_dept_cur (department_id_in)

      LOOP

         IF call_analysis.current_caseload (emp_rec.employee_id)

                    call_analysis.avg_caseload_for_dept (department_id_in)

         THEN

            assign_next_open_call_to (emp_rec.employee_id, l_case_id);

            notify_customer (l_case_id);

         END IF;

      END LOOP;

   END LOOP;

END distribute_calls;

One of my developers just noticed a strange behavior in all versions of Oracle 10g that we could test (10.2.0.3, 10.2.0.5). Here is the scenario:

• Use the REPLACE function with a CLOB variable
• Replace "T_" with "ZZ"; the variables contains a string "T(", as well as several occurrences of "T_".
•  In the result, Oracle replaces both "T_" and "T(" with "ZZ"!

The underscore character is clearly being interpreted as a wild-card!

DECLARE

   v_clob   CLOB:='T_T(a number)';

BEGIN

   v_clob   :=REPLACE(v_clob,'T_','ZZ');

   DBMS_OUTPUT.put_line(v_clob);

END;

Result on 10g: "ZZZZa number)"

Bug 4598943

Database Version - 9.2.0.6

Fixed in Product Version- 11.0.0.0.0

Workaround: None

One of my developers just noticed a strange behavior in all versions of Oracle 10g that we could test (10.2.0.3, 10.2.0.5). Here is the scenario:

• Use the REPLACE function with a CLOB variable
• Replace "T_" with "ZZ"; the variables contains a string "T(", as well as several occurrences of "T_".
•  In the result, Oracle replaces both "T_" and "T(" with "ZZ"!

The underscore character is clearly being interpreted as a wild-card!

DECLARE

   v_clob   CLOB:='T_T(a number)';

BEGIN

   v_clob   :=REPLACE(v_clob,'T_','ZZ');

   DBMS_OUTPUT.put_line(v_clob);

END;

Result on 10g: "ZZZZa number)"

Bug 4598943

Database Version - 9.2.0.6

Fixed in Product Version- 11.0.0.0.0

Workaround: None

DECLARE
   l_char   CHAR (1);

   CURSOR x
   IS
      SELECT dummy INTO l_char FROM DUAL;
BEGIN
   OPEN x;

   FETCH x INTO l_char;

   CLOSE x;
END;

DECLARE
   l_char   CHAR (1);

   CURSOR x
   IS
      SELECT dummy INTO l_char FROM DUAL;
BEGIN
   OPEN x;

   FETCH x INTO l_char;

   CLOSE x;
END;