Re: How many rows?

From: Jared Still <jkstill_at_gmail.com>
Date: Sat, 11 Nov 2006 08:29:48 -0800
Message-ID: <bf46380611110829x5913e484uac68baaaec0b1813@mail.gmail.com>

On 11/10/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com> wrote:
>
> Ok, this is a weird one.
>
> Oracle 9.2.0.6 64-bit on Sparc-Solaris 9.
>
> I have an external table. If I select * from table, I get 3,371 rows. If
> I select count(*), I get 3,631??
>
> My guess is that count(*) just counts rows, but doesn't care if they will
> be rejected, whereas 'select *' actually outputs them, so, filters out rows
> that don't fit the correct format?
>

Mark,

Have you setup discard and bad files?

As per doc:

CREATE TABLE dept_external (

   deptno     NUMBER(6),
   dname      VARCHAR2(20),
   loc        VARCHAR2(25)

)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY admin
 ACCESS PARAMETERS
 (
 *RECORDS DELIMITED BY newline

* *BADFILE 'ulcase1.bad'
* *DISCARDFILE 'ulcase1.dis'
* *LOGFILE 'ulcase1.log'
* *SKIP 20
* *FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
* *(
* *deptno     INTEGER EXTERNAL(6),
* *dname      CHAR(20),
* *loc        CHAR(25)
*  )

 )
 LOCATION ('ulcase1.ctl')
)
REJECT LIMIT UNLIMITED; Try doing a SELECT and checking the badfile contents.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Nov 11 2006 - 10:29:48 CST