Re: How do I parse information from a Column

Tim Gorman wrote:
>
> for something like that, don't bother with parsing the SQL_TEXT column, look
> at the numeric value in COMMAND_TYPE instead. what is stored there is the
> OCI "command-type" value which can be found in the OCI reference manual
> (http://docs.oracle.com for your version)...
>
> To save you some time:
>
> * command_type = 2 (insert)
> * command_type = 3 (select)
> * command_type = 6 (update)
> * command_type = 7 (delete)
>
> You're on your own for the 150 other types of SQL commands... :-)
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, May 30, 2002 11:08 AM
>
> > Hi,
> >
> > I am capturing data from v$sqlarea to a table to find out all the query
> activities on a database. Now I would like to parse sql_text column from a
> table to get only DML commands(SELECT, INSERT, UPDATE and DELETE) and also
> FROM clause to get table names. The example is:
> >
> > SELECT
> cuid,usid,cufirstname,culastname,cushipfirstname,cushiplastname,cushipaddres
> s1,cushipaddress2,cushiphomephone,cuemail,cushipcareof,cushipcity,cushipstat
> e,cushippostalcode,cuadcode,cubilltiid,cuaddress1,cuaddress2,cucity,custate,
> cupostalcode FROM tblCustinfo WHERE UsID=234563245
> >
> > Muqthar Ahmed
> > Database Administrator

I totally second Tim. Concerning the tables involved (since it seems to be your problem to) parsing SQL_TEXT is a bad idea. For one thing it only contains the 999 first characters of the query, a limit which is easily passed (however you can find the full text in V$SQLTEXT). You can also have complicated constructs (subqueries, in-line views), parsing with not be a piece of cake. A relatively easy way to do it is to run EXPLAIN and look for the tables referenced in PLAN_TABLE. Not 100% foolproof (if Oracle accesses an index but not the table), but it will be acurate in most cases.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).