V$SQLAREA question about bind variables

[JMac]

Does the SQL_TEXT column show the statement as sent to the engine? What I'm after is: is this column a good method finding out whether or not user statements use bind variables?
If not - what is?

[ggnanaraj]

Yes, you can use sql_text...

If literals are used, parsing will be more, so you can use the following code to identify SQL statements receiving a lot of parse calls...

select rpad(sql_text,300), parse_calls, executions from v$sqlarea
where parse_calls > 100 and executions < 2*parse_calls

HTH.

[JMac]

Yes, thanks.
But - (a)if Bind variables are used in the original statement, do they appear in SQL_TEXT column?

e.g.
SELECT col1, col2, col3 FROM tableA WHERE col2 = :bindvar

or (b) does SQL_TEXT show the statement after the engine has resolved the bind variables into a 'literal' statement.

e.g.
SELECT col1, col2, col3 FROM tableA WHERE col2 = 10089

If (b)is the PARSE statistic the only way to discover if bind variables are being used?

[jovery]

The bind variables from the original statement are shown, not the value.

HTH

[JMac]

Thanks - thats the answer I wanted.

I'm looking at some statements passed into the Ora 8.1.5 DB from a VB6 application using OLEDB.

The statements appear with double quotes around each object name. Is this just the way the VB app constructs the string and send it to the database?

e.g.
SELECT "ORD_NO","ORD_ID","TRACKING_NO","NO_CARTONS" FROM "SCHEMA_OWNER"."SHIPMENTS"

[jovery]

Yes, I've come across several applications which do the same.

Regards