Re: Creating table external using oracle 8..is it possible?

On Oct 6, 8:04 am, "sybrandb" <sybra..._at_gmail.com> wrote:
> andred..._at_hotmail.com wrote:
> > Hil All ,
>
> > I'm trying to create an table external using a input file in oracle 8
>
> > The command is :
>
> > CREATE TABLE os_pid (
> > pid number)
> > ORGANIZATION EXTERNAL
> > (TYPE oracle_loader
> > DEFAULT DIRECTORY data_dir
> > ACCESS PARAMETERS
> > (FIELDS TERMINATED BY ','
> > MISSING FIELD VALUES ARE NULL
> > (pid))
> > LOCATION ('pidlist.txt'))
> > REJECT LIMIT 0;
>
> > But i got the error message...
>
> > ORGANIZATION EXTERNAL
> > *
> > ERROR at line 3:
> > ORA-00922: missing or invalid option
>
> > Is it possible to create table external using oracle 8 , if not , exist
> > an alternative way?
>
> > Thank you!External tables are a new feature in 9i. No replacement exists in older
> versions, apart from sql*loader.
> Either use sql*loader or upgrade to something supported.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide Quoted text -- Show quoted text -

To simulate an external table back in version 8 you would have to create a real table, use sqlldr to load it, and then reference this real table in your SQL for what ever you needed to do. Since sqlldr had to be ran outside the db this generally meant you had to perform the task at a scheduled time after the file was loaded into the work table.

To run the load on demand from within the database would require using the external procedure feature to execute a shell script to perform the sqlldr step.

The use of utl_file might be an alternate for loading the data into a work table.

HTH -- Mark D Powell -- Received on Fri Oct 06 2006 - 08:45:53 CDT