Application Maintenance

Contact Us or call 1-877-932-8228
Application Maintenance

Application Maintenance

The fact that Oracle stores subprograms in the database has a number of interesting implications. Subprograms are covered by Oracle security and access to them can be controlled using standard Oracle Database functionality. As mentioned above a dropped object might be recoverable if it exists in the user's recyclebin. However, this storage is not a replacement for a standard version control system such as Subversion, CVS, Source Safe, or Git. These products provide an array of features that allow identification of every change made to a file over time. The storage of objects in the database should not replace ordinary coding practices that are standard and well accepted regardless of the technology involved.

In many environments, developers never deploy code to production databases. In such cases, PL/SQL subprograms must be coded in coherent scripts that contain all dependent objects and can be run using a standard process across the organization. This is sometimes a surprise to junior programmers who believe that their work is complete once they have successfully created working PL/SQL code in a development environment. In order to maintain a stable and efficient development process clear guidelines and communication between DBAs and developers is essential for projects to succeed.

Code Sample:

Stored-Procedures-and-Functions/Demos/debugging_subprograms.sql
CREATE OR REPLACE PROCEDURE my_proc
IS
BEGIN
  dbms_output.put_line('In: ' || $$PLSQL_UNIT);
  dbms_output.put_line('First line number: ' || $$PLSQL_LINE);
  dbms_output.put_line('Second line number: ' || $$PLSQL_LINE);
  dbms_output.put_line('Third line number: ' || $$PLSQL_LINE);
  RAISE VALUE_ERROR;
 EXCEPTION
    WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/

Code Explanation

Debugging subprograms is an extensive topic. There are a number of Oracle tools as well as third party tools that can assist in this work. This example demonstrates a few features added in Oracle 10g that can be helpful when constructing PL/SQL code that facilitates efficient troubleshooting and support. The $$PLSQL_UNIT returns the program unit executing the code, and the $$PLSQL_LINE returns the line number being executed. The DBMS_UTILITY.format_error_backtrace outputs the call stack at the point where the exception was raised.

Challenge

  1. Create a function called format_phone. It will format the following string 123.456.7890 so that it looks like a U.S. phone number (123) 456-7890. The function will be expanded upon in later lessons.
  2. Add two variables of type VARCHAR2 and length 50. The first variable will hold an unformatted phone number and the second a formatted phone number.
  3. Initialize the first variable to 123.456.7890.
  4. Add PL/SQL code to format the string.
  5. Return the formatted string.
  6. Compile, test and debug your function.
  7. For an additional challenge, see if you can come up with a solution that requires even less code. (Hint: using the regexp_replace function can eliminate several lines of code).
  8. Compile, test and debug your function.

Challenge Solution:

Stored-Procedures-and-Functions/Solutions/phone_number_function1.sql
create or replace function format_phone
return varchar2
is 
  v_phone_number VARCHAR2(50) := '123.456.7890';
  v_format_phone_no VARCHAR2(50);
begin

  v_format_phone_no := '(' || v_phone_number;
  
  v_format_phone_no := replace(v_format_phone_no, '.', ') ');
  
  v_format_phone_no := substr(v_format_phone_no, 1, 9) ||
                              '-' ||
                              substr(v_format_phone_no, 12);
  
  return v_format_phone_no;

END;
/

-- test it
begin
  dbms_output.put_line(format_phone());
end;
/

Code Explanation

This solution uses the built in replace and substring functions to format the phone number.

Next