topic Re: Formatting v$sqltext.SQL_TEXT in Operating System - HP-UX

Formatting v$sqltext.SQL_TEXT

Nicolas Dumeige — Thu, 08 Apr 2004 04:35:00 GMT

Hello,

Does anybody have a method to get a human readable output on the SQL_TEXT field when looking at a running session this way :

SELECT
'EN COURS' REQ,
v$session.sid,
piece,
SQL_TEXT
FROM
v$SESSION,
v$SQLTEXT SQLTEXT
WHERE
v$SESSION.SQL_ADDRESS= SQLTEXT.ADDRESS AND
SQLTEXT.HASH_VALUE = v$SESSION.SQL_HASH_VALUE AND
v$SESSION.SID = &1
ORDER BY
v$session.sid,
v$session.username,
v$session.osuser,
piece
;

Thank for you help,

Nicolas

Re: Formatting v$sqltext.SQL_TEXT

Sanjay Kumar Suri — Thu, 08 Apr 2004 05:00:33 GMT

You can define a column characterstics as below before firing a SQL:

column sql_text heading "SQLTEXT" word_wrapped justify right format A(30)

Instead of word_wrapped, wrapped (default), truncated can also be used.

sks

Re: Formatting v$sqltext.SQL_TEXT

Hein van den Heuvel — Thu, 08 Apr 2004 09:02:43 GMT

I created a script for a similar need a while back. I expect you can either use it as is, or fix it up rapidly. Included below, and attached.
Enjoy,
Hein

usage: perl pretty.pl < tmp.lst

$x{"SELECT"}="\n";
$x{"INSERT"}="\n";
$x{"UPDATE"}="\n";
$x{"WHERE"}=" ";
$x{"FROM"}=" ";
$x{"INTO"}=" ";
$x{"AND"}=" ";
$x{"OR"}=" ";
$x{"GROUP"}=" ";
$x{"ORDER"}=" ";
$x{"newline"}=" ";
while (<>) {
if (length >= 64) {
$data=1;
$_ = substr($_,0,64);
s/$/ \(/g;
s/$/\) /g;
s/\s+/ /g;
s/^ //;
$buf .= $_;
} else {
if ($data==0) {
print;
next;
}
@words = split (/\s/,$buf);
foreach $word (@words) {
if (defined( $x{$word} )) {
print "$line\n";
$line = $x{$word}.$word;
} else {
if (length($line) > 57) {
print "$line\n";
$line = $x{"newline"}.$word;
} else {
$line = $line . " " . $word;
}
}
}
print "$line\n";
$line = "";
$buf = "";
}
}

Before:

SELECT SQL_TEXT FROM v$SESSION, v$SQLTEXT SQLTEXT WHERE v$SESSIO
N.SQL_ADDRESS= SQLTEXT.ADDRESS AND SQLTEXT.HASH_VALUE = v$SESSIO
N.SQL_HASH_VALUE AND v$SESSION.SID = 25 ORDER BY v$session.sid,
v$session.username, v$session.osuser, piece

After:

SELECT SQL_TEXT
FROM v$SESSION, v$SQLTEXT SQLTEXT
WHERE v$SESSION.SQL_ADDRESS= SQLTEXT.ADDRESS
AND SQLTEXT.HASH_VALUE = v$SESSION.SQL_HASH_VALUE
AND v$SESSION.SID = 25
ORDER BY v$session.sid, v$session.username, v$session.osuser,
piece

Re: Formatting v$sqltext.SQL_TEXT

Nicolas Dumeige — Thu, 08 Apr 2004 09:17:31 GMT

Sanjay : the format option is not enought

Hein :
1) The script attached is not the perl one
2) The script does a good job, but the last line of the SQL reques is not processed !

If I may suggest another improvement, the fields after the select might as well be displayed one per line.

Anyway, this is what I'm looking for.

Cheers

Nicolas

Re: Formatting v$sqltext.SQL_TEXT

Hein van den Heuvel — Thu, 08 Apr 2004 09:32:14 GMT

Ooops! I am surprised it works at all with the mangling of the spaces in the forum entry.

Let me try that attach again....

Hein.

Re: Formatting v$sqltext.SQL_TEXT

Nicolas Dumeige — Thu, 08 Apr 2004 11:00:32 GMT

Hein,

Even with the detached script, it doesn'process the last line.

Use the attached file if you want to see for your self.

Nicolas

Re: Formatting v$sqltext.SQL_TEXT

Hein van den Heuvel — Thu, 08 Apr 2004 12:27:58 GMT

The original script tried to handle multiple chunks of ugly-sql interspersed with text/data chunks, but it did not solve that problem good enough for your need (it was good enough for me back then! :-).

Here is a version that does work on the last line, and handles your long comma seperated liste nicer (imho).

I'm sure there is plenty wrong with it still (quoted strings, other key words,...) but it does the job on your examples.

Included and attached (for spacing!).

Hein.

(btw... did you assign 3 points to put me on 1000 exactly in the hpux section? :-).

#
%x = ("SELECT", "\n", "INSERT", "\n", "UPDATE", "\n",\
"WHERE", " ", "FROM", " ", "INTO", " ", "AND", " ",\
"OR", " ", "GROUP", " ", "ORDER", " ", "newline", " ");
while (<>) {
$_ = substr($_,0,64);
s/\,/\, /g; #Multi-space removed later.
s/$/ \(/g;
s/$/\) /g;
s/\s+/ /g;
s/ ,/,/g;
s/^ //;
$buf .= $_;
}

@words = split (/\s/,$buf);
foreach $word (@words) {
if (defined( $x{$word} )) {
print "$line\n";
$line = $x{$word}.$word;
} else {
if (length($line) > 57) {
print "$line\n";
$line = $x{"newline"}.$word;
} else {
$line = $line . " " . $word;
}
}
}
print "$line\n";

after:

INSERT
INTO tTEMPBAL0rst (NUMSEQCOG, APPLI_CPB, ACCOUNT, ALTACCT,
BUSINESS_UNIT, LEDGER_GROUP, FK_PERIOD, IND_EXLCVA,
CPT_NAT, JOURNAL_DATE, JRN_DATE, CODDIV, LIBDIV, CODACT,
LIBACT, GOP_MNE, GOP_LIB, PTF_MNE, PTF_LIBCRT, FK_STR,
CPT_PCI_S, CPT_PCI, FOREIGN_CURRENCY, IND_IAS, CDE_POS,
TYPE_RESULTAT, SOLDE_DBT, SOLDE_CRD, SOLDE) (SELECT
/*+ USE_NL(TMEURST) */ 40709, TM.APPLI_CBL, TM.ACCOUNT,
TM.ALTACCT, TM.BUSINESS_UNIT, TM.LEDGER_GROUP, TM.FK_PERIOD,
TM.IND_EXLCVA, TM.CPT_NAT, TM.JOURNAL_DATE, TM.JRN_DATE,
TSTRRST.CODDIV, TSTRRST.LIBDIV, TSTRRST.CODACT, TSTRRST.LIBACT,
TSTRRST.GOP_MNE, TSTRRST.GOP_LIB, TSTRRST.PTF_MNE,
TSTRRST.PTF_LIBCRT, TM.FK_STR, NVL (TM.CPT_PCR, -1),
TM.CPT_PCR, TM.FOREIGN_CURRENCY, TM.IND_IAS, TM.CDE_POS,
kRPTUTILrst.F_GET_TYPE_RESULTAT_RST ('BAL', 'D', TO_DATE
('15/03/2004', 'DD/MM/YYYY'), TO_DATE ('31/03/2004',
'DD/MM/YYYY'), TM.JRN_DATE), decode (TM.SENS_MVT, 'D',
TM.MNT_TRAN, 0) SOLDE_DBT, decode (TM.SENS_MVT, 'C',
TM.MNT_TRAN, 0) SOLDE_CRD, decode (TM.SENS_MVT, 'D',
TM.MNT_TRAN, 0) - decode (TM.SENS_MVT, 'C', TM.MNT_TRAN,
0) SOLDE
FROM TSTRRST, TMEURST TM WHERE TM.FK_STR=TSTRRST.PK_STR
AND TSTRRST.PTF_MNE in ('TRIND')
AND TM.BUSINESS_UNIT='G0001'
AND TM.LEDGER_GROUP='LEDGRP7003'
AND TM.IND_IAS='N'
AND TM.CDE_POS in ('N', 'S')
AND JRN_DATE BETWEEN TO_DATE ('01/03/2004', 'DD/MM/YYYY')
AND TO_DATE ('31/03/2004', 'DD/MM/YYYY')
AND JOURNAL_DATE BETWEEN TO_DATE ('29/02/2004', 'DD/MM/YYYY')
AND TO_DATE ('31/03/2004', 'DD/MM/YYYY') )

before:

INSERT INTO tTEMPBAL0rst (NUMSEQCOG,APPLI_CPB,ACCOUNT,ALTACCT,BU
SINESS_UNIT,LEDGER_GROUP,FK_PERIOD,IND_EXLCVA,CPT_NAT,JOURNAL_DA
TE,JRN_DATE,CODDIV,LIBDIV,CODACT,LIBACT,GOP_MNE,GOP_LIB,PTF_MNE,
PTF_LIBCRT,FK_STR,CPT_PCI_S,CPT_PCI,FOREIGN_CURRENCY,IND_IAS,CDE
_POS,TYPE_RESULTAT,SOLDE_DBT,SOLDE_CRD,SOLDE) (SELECT /*+ USE_NL
(TMEURST) */ 40709, TM.APPLI_CBL, TM.ACCOUNT, TM.ALTACCT, TM.BUS
INESS_UNIT, TM.LEDGER_GROUP, TM.FK_PERIOD, TM.IND_EXLCVA, TM.CPT
_NAT, TM.JOURNAL_DATE, TM.JRN_DATE, TSTRRST.CODDIV, TSTRRST.LIBD
IV, TSTRRST.CODACT, TSTRRST.LIBACT, TSTRRST.GOP_MNE, TSTRRST.GOP
_LIB, TSTRRST.PTF_MNE, TSTRRST.PTF_LIBCRT, TM.FK_STR, NVL(TM.CPT
_PCR,-1), TM.CPT_PCR, TM.FOREIGN_CURRENCY , TM.IND_IAS , TM.CDE_
POS , kRPTUTILrst.F_GET_TYPE_RESULTAT_RST('BAL', 'D', TO_DATE('1
5/03/2004','DD/MM/YYYY'), TO_DATE('31/03/2004','DD/MM/YYYY'), TM
.JRN_DATE), decode(TM.SENS_MVT,'D',TM.MNT_TRAN ,0) SOLDE_DBT, de
code(TM.SENS_MVT,'C',TM.MNT_TRAN ,0) SOLDE_CRD, decode(TM.SENS_M
VT,'D',TM.MNT_TRAN ,0) - decode(TM.SENS_MVT,'C',TM.MNT_TRAN ,0)
SOLDE FROM TSTRRST, TMEURST TM WHERE TM.FK_STR=TSTRRST.PK_STR AN
D TSTRRST.PTF_MNE in ('TRIND') AND TM.BUSINESS_UNIT='G0001' AND
TM.LEDGER_GROUP='LEDGRP7003' AND TM.IND_IAS='N' AND TM.CDE_POS
in ('N','S') AND JRN_DATE BETWEEN TO_DATE('01/03/2004','DD/MM/Y
YYY') AND TO_DATE('31/03/2004','DD/MM/YYYY') AND JOURNAL_DATE B
ETWEEN TO_DATE('29/02/2004','DD/MM/YYYY') AND TO_DATE('31/03/200
4','DD/MM/YYYY') )

Re: Formatting v$sqltext.SQL_TEXT

Nicolas Dumeige — Fri, 09 Apr 2004 04:31:51 GMT

Hein,

Thanks for the timle you took to improve you perl script.
It's a nice output indeed !

Fort the 3 pts, there was no special thinking on it, I felt the correction was at leat worth some points.

The 10 pts you get now are well deserved !

Cheers

Nicolas

Re: Formatting v$sqltext.SQL_TEXT

Michael Schulte zur Sur — Fri, 09 Apr 2004 06:15:50 GMT

Hi Nicolas,

perhaps you overlooked a small detail.
A ten is made of a 1 and a 0 and not only of
a 0!
You have given Hein 0 points for the last post!

greetings,

Michael

Re: Formatting v$sqltext.SQL_TEXT

Michael Schulte zur Sur — Fri, 09 Apr 2004 06:16:01 GMT

Hi Nicolas,

perhaps you overlooked a small detail.
A ten is made of a 1 and a 0 and not only of
a 0! ;-)
You have given Hein 0 points for the last post!

greetings,

Michael

Re: Formatting v$sqltext.SQL_TEXT

Nicolas Dumeige — Fri, 09 Apr 2004 07:00:02 GMT

Oops ! My wheelmouse has scroll both the frame and the point combo box.

Hein, all appologies.
Please say hello to get your 10 pts.

Nicolas

Re: Formatting v$sqltext.SQL_TEXT

Nicolas Dumeige — Tue, 13 Apr 2004 03:21:20 GMT

Hein, are you there ?

Re: Formatting v$sqltext.SQL_TEXT

Hein van den Heuvel — Tue, 13 Apr 2004 16:18:08 GMT

ack

Hein.
(hein at hp dot com)

Re: Formatting v$sqltext.SQL_TEXT

Nicolas Dumeige — Wed, 14 Apr 2004 04:10:36 GMT

Hein,

I like you personal quote.
Can I say the script is not flawles but keep my own shoes ?

Cheers

Nicolas