Debugging a Pipelined Function in Oracle

A pipelined function is often used to replace a multi-step function when working with large Oracle databases. Mass amounts of information that are typical in databases often result in a traditional multi-step function bogging down the system, resulting in slow performance. Since the release of Oracle 9i, developers can now replace the functions with a pipelined function. A pipeline function is capable of producing end data more rapidly by producing each line of data immediately after it is compiled; whereas, traditional functions common before Oracle 9i compiled all lines of data before any of it was sent to the output. Pipelined functions are the answer to many developers’ problems, but they are still subject to the same programming bugs as their older counterparts. The debugging process is simple because most code compilers come with a debugger feature.

Instructions

Things You'll Need

• Script compiler

1. • 1
     Launch your compiler of choice. When the compiler is open, click the "File” menu and choose “Open.” Navigate to the folder where the pipeline function is located. Find the function’s file and double-click it to open the file in your compilers navigator window.
   • 2
     Type the line of code "DBMS_OUTPUT.PUT_LINE(‘ ‘);" periodically throughout the body of the code. The line is case sensitive and must be entered exactly to function correctly. Depending on the length of the function, you may have to enter the line many times. To pinpoint the bugs easily, input the line after every other statement within the script of the pipeline function. Do not type the code in the middle of existing code; give it its own line. In some cases, your compiler may feature a shortcut that inserts the code automatically. Other compilers allow the code to be inserted wherever the cursor is located by hitting the keyboard shortcut of "Ctrl" and "D."
   • 3
     Click on the debug icon on the compilers toolbar. Alternatively, you may hover over the script of the pipeline function and right-click. Select the debug option from the menu that will appear. This will compile the script. For each DBMS_OUTPUT you entered in the functions script, an output line will appear in the output buffer window that is typically on the bottom of the compiler window.
   • 4
     Look at the lines of output in the output buffer window. The lines will all begin with the "DBMS_ OUTPUT.PUT_LINE" followed by the data text from that statement, which will be enclosed in parenthesis. This allows you to localize any bugs. When an error is found, the output line will be red. Double-click the red text to automatically be taken to the line of code in which the error is present. Correct the bug in the navigator menu.
   • 5
     Remove all lines of "DBMS_OUTPUT.PUT_LINE(‘ ‘)" from the functions script by manually scrolling through and deleting them. Click the “File” menu and select “Save” to save the changes to your pipeline function, which is now debugged.