Suggestions? - tracing SQL and bind variables in Oracle 7

We are developing an application in Powerbuilder, with an Oracle7 server on a Sun unix platform, connected by SQL*net.

To assist in the debugging of the application, I need to be able to view the SQL statements received by the server from the remote application, as well as the SQL statement sent by the application.

PowerBuilder (version 3.0) lets us see the outgoing SQL with the dw_sql_preview function. I have had some limited success in viewing the SQL within Oracle also, but would appreciate any hints and suggestions from anyone out there.

First, I tried SQL*DBA 'monitor SQL'; it only shows the first 60(?) characters of the SQL statement, which in many cases is several hundred characters long.

Next, I tried the following query:

	select user_name, piece,s.sql_text
	from v$open_cursor o, v$sqltext s
	where o.address = s.address
	and o.hash_value = s.hash_value
	and user_name like upper('%&user%')
	order by s.address, s.hash_value, piece

This allows me to see the full text of an SQL statement, but I still have two problems:
1: open cursors are not very persistent - if I don't run this query soon enough, the cursor appears to be closed, and the SQL statement is no longer found. Does anyone have another way of doing this?

2: even when I can see the SQL statement that I want, in many cases it uses bind variables, which appear by name in the retrieved text - I can't see the value assigned to the bind variables for that query, which is limiting.

For example, I see an SQL statement like:

   select name, shoe_size from users where name = :u_name - I have no idea what the value of :u_name was when the query was executed.

If anyone can suggest queries, or monitoring tools or techniques that would help me in this, I'd be most grateful.

Thanks in advance,

...PJ
(Patrick Johnson: tspj_at_ucs.ubc.ca) Received on Fri Dec 17 1993 - 01:23:46 CET