Re: Program line ?

From: Paul Cluiss <paul_cluiss_at_intervoice.com>
Date: Fri, 11 Feb 2000 12:32:23 -0600
Message-ID: <1E5BA0F10B51492B.16D4F5AA5AE94DF1.30E50A611C57DBF6_at_lp.airnews.net>

[Quoted] According to my knowledge, yes. See Thomas Kyte's web pages for details. I've done it and it works --- PL/SQL block name + line number

Oh, twist my arm here is the code:

---

/*
This procedure extracts the filename and linenumber from the given callstack.
*/
PROCEDURE grabPositionInfo (

    callstack IN VARCHAR2,
    filename OUT VARCHAR2,
    linenum OUT PLS_INTEGER
)
IS
  nPos1 PLS_INTEGER;
  nPos2 PLS_INTEGER;
  line VARCHAR2(256);
  linenumStr VARCHAR2(256);
BEGIN

    nPos1 := INSTR(callstack, CHR(10), 1, 3);
    nPos2 := INSTR(callstack, CHR(10), 1, 4);
    line := SUBSTR(callstack, nPos1 +1, nPos2 - nPos1 -1);

    nPos1 := INSTR(line, ' ');
    linenumStr := LTRIM(SUBSTR(line, nPos1));     nPos1 := INSTR(linenumStr, ' ');
    linenumStr := SUBSTR(linenumStr, 1, nPos1 -1);     nPos1 := INSTR(line, ' ', -1);
    filename := SUBSTR(line, nPos1 +1);
    linenum := TO_NUMBER(linenumStr);

EXCEPTION
    WHEN OTHERS THEN
    BEGIN

        DBMS_OUTPUT.PUT_LINE('Error!  :  ' || SQLERRM());
        RETURN;

    END;
END grabPositionInfo;

---

You need to call it similarly to this:

    callstack VARCHAR2(512) := DBMS_UTILITY.FORMAT_CALL_STACK;     filename VARCHAR2(512);
    linenum PLS_INTEGER;
    grabPositionInfo(callstack, filename, linenum);

---

Hope this helps.

Paul Cluiss
Richardson, Texas

"M. Armaghan Saqib" wrote:

> According to my knowledge; No.
>
> Creating a global variable and then setting it after every (say) 10
> lines and then using this in your PLSQL error handler might be of some
> help.
>
> regards,
> M. Armaghan Saqib
> +---------------------------------------------------------------
> | 1. SQL PlusPlus => Add power to SQL Plus command line
> | 2. SQL Link for XL => Integrate Oracle with XL
> | 3. Oracle CBT with sample GL Accounting System
> | Download free: http://www.geocities.com/armaghan/
> +---------------------------------------------------------------
> Elrond <elrond_at_icn.pl> wrote in message
> news:880t2l$ocq$1_at_helios.man.lublin.pl...
> > Is there a way to obtain a line in procedure, where was an error ?
> > I have a procedure, and in ON-ERROR trigger i want to now which line
> of this
> > procedure was wrong ?
> > sincerelly
> > Maciej Myrcha
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Feb 11 2000 - 19:32:23 CET