Oracle pl/sql code line(row) to variable

Question

So is it possible to get current code line?

1,2,3,4 v_variable :=(get_line or something???) and in variable are value 4?
5,6,7,8,9 v_variable :=(get_line or something???) and in variable are value 9?

I just want to find easiest way to catch bugs Thanks.

1,2,3,4, code lines...

`find easiest way to catch bugs` You need to elaborate a little bit more on your situation. What kind of bug you want to catch and under what circumstances. If you do not hide the line number on which exception is raised, by re-raising it in the exception section calling `raise` or `raise_application_error()` function, oracle will provide you with exact line number. — Nick Krasnov, Dec 09 '13 at 13:10
when others raise_application_error(-20001, l_cur_cl||SQLERRM) thats the way, because not in all times oracle provide line number... — Strauteka, Dec 09 '13 at 14:20

score 10 · Accepted Answer · answered Dec 09 '13 at 12:47

10

So is it possible to get current code line?

Yes, it's possible. Starting from Oracle 10g the $$PLSQL_LINE inquiry directive can be used to return number of a line in the code where $$PLSQL_LINE appears:

SQL> declare
  2    l_cur_cl pls_integer;
  3  begin
  4    l_cur_cl := $$PLSQL_LINE;
  5    dbms_output.put_line('Line #: '|| to_char(l_cur_cl) || chr(13)
  6                         || 'Current line #:  '|| to_char($$PLSQL_LINE));
  7  end;
  8  /

Output:

Line #: 4
Current line #:  6
PL/SQL procedure successfully completed

answered Dec 09 '13 at 12:47

Nick Krasnov

26,886
6
61
78

Wouldnt this require him to print/store line numbers after every line in order to help him track a bug ? – Adarsh Dec 09 '13 at 12:53
@Adarsh In order to be able to use that line number later on, yes, it'll require to store the directive's result in a variable. But this approach won't be a good choice for exception tracking(if that's the OPs final goal). You either will have to put `$$PLSQL_LINE` before a statement that might raise an exception on the same line, or previous one. – Nick Krasnov Dec 09 '13 at 14:09

Adarsh · Answer 2 · 2013-12-09T15:08:59.660

2

The oracle exception handling system helps you do exactly what you need. Instead of manually getting line numbers, you can raise and handle user defined exceptions or system exceptions and in the exception blocks use the following code to fetch the lin number where the error occurred.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Read this article for more information and examples.

http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html

edited Dec 09 '13 at 15:08

answered Dec 09 '13 at 12:51

Adarsh

3,613
2
21
37

Yes, when it comes to tracing exceptions, this, without any doubts, is a preferable way to do it. The small correction, though. If we need to get a line number, which raises an exception, in the exception section of a PL/SQL block, calling `DBMS_UTILITY.FORMAT_ERROR_BACKTRACE` will be a better choice. – Nick Krasnov Dec 09 '13 at 13:18
@NicholasKrasnov Thanks for pointing that out. I provided the link for DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and typed in DBMS_UTILITY.FORMAT_ERROR_STACK. – Adarsh Dec 09 '13 at 15:08

score 0 · Answer 3 · edited May 23 '17 at 12:29

I just want to find easiest way to catch bugs

The easiest way to find bugs is called unit testing. Apply rigorously with test driven development and after a while you won't have many bugs at all !

Internet is not short of instructions nor Stack Overflow short of questions about both subjects. Start e.g. with:

Oracle pl/sql code line(row) to variable

3 Answers3