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.
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; /
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.
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; /
This solution uses the built in replace and substring functions to format the phone number.