Wednesday, November 27, 2013

How to print line numbers in PL/SQL

Introduction

Any one who wants to be a good programmer must also acquire good debugging skills. Oracle PL/SQL database programming offers several easy code debugging options through the DBMS_UTILITY package. This article provides some naunces about printing line numbers in PL/SQL code.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

One can print the exact line number that caused the PL/SQL error by using the supplied DBMS_FORMAT.FORMAT_ERROR_BACKTRACE procedure.  It can be called directly in the dbms_output.put_line procedure.  An example is shown below. 


How to print line numbers in SQL*Developer

Many Oracle developers use Oracle SQL*Developer IDE (Integrated Development Environment) for working on SQL and PL/SQL code. One can let SQL*Developer display the line numbers by using the  TOOLS => Preferences => Code Editor => Line Gutter option



PL/SQL anonymous blocks are always counted from the DECLARE section

One common mistake that PL/SQL programmers do is to assume that the line number shown by the DBMS_FORMAT.FORMAT_ERROR_BACKTRACE is counted from the first line of the code. In the above example, the 1st line number is for the line that contains "set serveroutput on". The PL/SQL code starts only from DECLARE section (line 2). So for the PL/SQL compiler, line 1 is what shown as line 2 in the above screenshot. 

The error occurred in PL/SQL code line 7 v_name := 'DBA UNIVERSITY'; (which is line 8 for SQL*Developer). So when debugging anonymous PL/SQL code, one should always count the 1st line from the line that contains DECLARE.

Conclusion

Hope this helps !  We are always looking for your participation in this blog. Please feel free to provide more tips by commenting below to help other readers.


Wednesday, November 13, 2013

BEST JOBS in America - Database Administrator

CNNMoney.com has released the BEST JOBS in AMERICA report for 2013 today. Database Administrator (DBA) is now the 14th best job in america with a 30% job growth forecast until 2023. 

Here is the LINK for more information. Another interesting statistic in this report that among all the IT/computer best jobs in this list, Database Administrator job has the highest job growth forecast for the next 10 years until 2023

This is the 5th time in a row that Database Administrator has been among the best jobs in America by CNN.com 

Are you interested in becoming a database administrator ? DBA University can help you. We are an Oracle Education Partner and we are offering online training courses in Oracle Database Administration training and Microsoft SQL Server Database  Administration training with affordable cost.   If you are interested, you can register online  for a FREE DEMO session or sign up for one of our upcoming training courses.

Registration - http://www.dbauniversity.com/

Student Video Reviews - http://video.dbauniversity.com/reviews/