Discrepency between ABAP SQL and SQL displayed via st05

[Former Member]

Hi all:

Within some standard SAP code there is an SQL statement that is being built dynamically. One of the fields within the 'where' clause of this SQL statement is stored within the database as a character (char 32). Within the code, they are using a num field to build the where clause - however they are explicitly adding single quotes such that oracle doesn't need to do the type conversion.

So - within the abap debugger, the select statement ultimately looks like this (not showing all 32 chars here).

select * from estvh where recnroot in ('0000000001', '0000000002',....,'0000000XX').

The issue is that when I look at this through st05 - the single quotes have been removed from the select statement, i.e.

select * from estvh where recnroot in (0000000001, 0000000002,....,0000000XX).

My question - is that I'm not sure if the quotes are being removed as a result of the ST05 tool itself - i.e. they aren't really being removed at the DB level and ST05 just displays the data without the quotes - or if there is something happening between the ABAP sql statement and the actual SQ statement that is executed on the DB. I feel if the quotes are being removed - it forces oracle to do the type conversion - and causes a performance penalty.

If anyone has familiarity with this or has seen something similar - let me know.

Thanks!

[Former Member]

To add to my previous post - if you check the database while this query is running, use v$sqlarea:

select sql_text from v$sqlarea where users_executing > 0;

[Former Member]

Hi,

How does the SQL statement look when you look up the Oracle session in ST04? You could also look at the execution plan there. If this is an Oracle database, you can look at V$SQL, V$SQLTEXT or v$sqltext_with_newlines. Here's a code sample for v$sqltext_with_newlines:

set serveroutput on size 1000000

declare

v_stmt varchar2(16000);

v_sql_text v$sqltext_with_newlines.sql_text%type;

v_sid v$session.sid%type;

begin

for r in (

select

sql_text,s.sid

from

v$sqltext_with_newlines t,

v$session s

where

s.sql_address=t.address

order by s.sid, piece) loop

v_sid := nvl(v_sid,r.sid);

if v_sid <> r.sid then

dbms_output.put_line(v_sid);

<a href='oru_10028.html'>put_line</a>(v_stmt,100);

v_sid := r.sid;

v_stmt := r.sql_text;

else

v_stmt := v_stmt || r.sql_text;

end if;

end loop;

dbms_output.put_line(v_sid);

dbms_output.put_line(v_stmt,100);

end;

[lbreddemann]

Hi there,

this is just a display discrepancy of ST05.

See my blog [MaxDB ST05-Trace Fallacy - when sometimes the trace is wrong...|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/19012] [original link is broken] [original link is broken] [original link is broken]; on it.

What's important here is to realize the data type of the columns for which the values are given in the statement.

For character types you will always have the ' ' around the data.

regards,

Lars