Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Formatting v$sqltext.SQL_TEXT

SOLVED
Go to solution
Nicolas Dumeige
Esteemed Contributor

Formatting v$sqltext.SQL_TEXT

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
All different, all Unix
13 REPLIES
Sanjay Kumar Suri
Honored Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Hein van den Heuvel
Honored Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
Esteemed Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
All different, all Unix
Hein van den Heuvel
Honored Contributor

Re: Formatting v$sqltext.SQL_TEXT


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
Esteemed Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
All different, all Unix
Hein van den Heuvel
Honored Contributor

Re: Formatting v$sqltext.SQL_TEXT


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
Esteemed Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
All different, all Unix
Michael Schulte zur Sur
Honored Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
Honored Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
Esteemed Contributor

Re: Formatting v$sqltext.SQL_TEXT

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
All different, all Unix
Nicolas Dumeige
Esteemed Contributor

Re: Formatting v$sqltext.SQL_TEXT

Hein, are you there ?
All different, all Unix
Hein van den Heuvel
Honored Contributor
Solution

Re: Formatting v$sqltext.SQL_TEXT

ack

Hein.
(hein at hp dot com)
Nicolas Dumeige
Esteemed Contributor

Re: Formatting v$sqltext.SQL_TEXT

Hein,

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

Cheers

Nicolas
All different, all Unix