Formatting v$sqltext.SQL_TEXT

[Nicolas Dumeige]

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

[Sanjay Kumar Suri]

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

[Hein van den Heuvel]

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

[Nicolas Dumeige]

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

[Hein van den Heuvel]

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.

[Nicolas Dumeige]

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

[Hein van den Heuvel]

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') )

[Nicolas Dumeige]

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

[Michael Schulte zur Sur]

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

[Michael Schulte zur Sur]

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

[Nicolas Dumeige]

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

[Nicolas Dumeige]

Hein, are you there ?

[Hein van den Heuvel · Accepted solution]

ack

Hein.
(hein at hp dot com)

[Nicolas Dumeige]

Hein,

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

Cheers

Nicolas