Re: how do u get SQL text when exception occurs in PL/SQL?

From: Utilisateur1 <nlatrach.capgemini_at_wanadoo.fr>
Date: Mon, 15 Jul 2002 20:33:10 +0200
Message-ID: <agv40v$s2l$1@wanadoo.fr>

Hi,
You can use v$session and v$sqltext, two views that belong to the sys schemaShow SQL .

---

COLUMN userID FORMAT A14
COLUMN sql_text FORMAT A65

BREAK ON userID SKIP 2

SET pagesize 0

SELECT s.schemaname||'('||TO_CHAR (s.sid)||')' userID,t.sql_text FROM sys.v$session s,sys.v$sqltext t
WHERE t.address = s.sql_address
ORDER BY s.sid,t.piece
/

Nour

"julio" <julio33_at_whomail.com> a écrit dans le message news: 3d32cf72$0$55644$45beb828_at_newscene.com...
>
> We have several pl/sql procedures that we coded. we have an exception
> handling section that gets sqlcode and SQLERRM. What we would like to do
is
> get the SQL text that actually caused the problem. So if we have an
insert
> that is trying to put in a value to large into a column we get an sqlcode
and
> sqlerrrm but we don't have the exact sql statement that did it.
>
> Is that possible, well pretty much anything is possible, but is it doable
> without a ton of coding/recoding?
>
> Julio and Colin and Lyse
>
> See my babies at
>
> http://www.geocities.com/colin_and_lyse/colin_lyse.html
Received on Mon Jul 15 2002 - 13:33:10 CDT