RE: please help with SQL Trace and TKPROF

Your error reminds me of similar errors when trying to match a number and a NULL (instead of to_number(NULL)) in UNIONs or any set operation. I suspect the hash anti-join. Try an external join with a condition on NULL columns, although it should take a very similar path. Also, no UNUSED column? Can screw-up things, sometimes.

BTW not very convinced by your query on the data dictionary. I hope you have forgotten the condition on the different data types which should be different when putting it in the message - but that it was in what you run.

Not sure that a trace will be much help.

SF

>----- ------- Original Message ------- -----
>From: <rgaffuri_at_cox.net>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Fri, 16 May 2003 05:16:40
>
>Im getting an error and I think the only way to
>track it down is to use
>SQLTRACE and TKPROF. Im a total new to these
>utilities. Let me explain my
>problem and then maybe someone can point me in the
>correct directions. Here is
>my error.
>
>insert /*+ append */ into stage.tab1
> select *
> from master.tab1
> where (sec_no) NOT IN
> (SELECT /*+ HASH_AJ */ sec_no from stage.tab1)
>
>select *
> *
>ERROR at line 2:
>ORA-01722: invalid number
>
>These two tables are duplicates in structure. I ran
>a diff on DBA_TAB_COLUMNS
>as follows and got no rows returned.
>
>select a.column_name,a.data_type
>"Master",b.data_type "Stage"
>from dba_tab_columns a,dba_tab_columns b
>where a.column_name = b.column_name
>and a.table_name = 'TAB1'
>and b.table_name = 'TAB1'
>and a.owner = 'MASTER'
>and b.owner = 'STAGE'
>order by 2,3
>
>So my next step is to run a sqltrace. What kind of
>sqltrace event should I use?
>can I get the syntax for this? and syntax for
>running it in TKPROF? What should
>I look for?
>
>
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>--
>Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
>Fat City Network Services -- 858-538-5051
>http://www.fatcity.com
>San Diego, California -- Mailing list and
>web hosting services
>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).
>---------------------------------------------------
>------------------

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).