Re: help with batch operation

From: chet justice <chet.justice_at_gmail.com>
Date: Tue, 8 Sep 2009 18:45:30 -0400
Message-ID: <8311a5b60909081545h119b66cav2b6c1bba6dbf08e0_at_mail.gmail.com>

That is correct, assuming that vNew_loc is just a file name. The LOCATION keyword threw me off initially as the location was not changing, just the file name.

On Tue, Sep 8, 2009 at 6:41 PM, Eugene Pipko <eugene.pipko_at_unionbay.com>wrote:

> Thanks Chet,
>
> I actually like the first approach, where I can alter table location.
>
> So, is this how it would be done?
>
> Procedure alter_table_p (vNew_loc in varchar2)
>
> Is
>
> Begin
>
> Execute immediate ‘alter table ORIG_TABLE location (:x)’
> using vNew_loc;
>
> End;
>
>
>
> Regards,
>
>
>
> Eugene Pipko
>
> Seattle Pacific Industries
>
> office: 253.872.5243
>
> cell: 206.304.7726
>
> P Please consider the environment before printing this e-mail.
>
>
>
> *From:* chet justice [mailto:chet.justice_at_gmail.com]
> *Sent:* Tuesday, September 08, 2009 3:11 PM
> *To:* Eugene Pipko
> *Cc:* Oracle L
> *Subject:* Re: help with batch operation
>
>
>
> The file name can change, but you have to use DDL to do so:
>
> ALTER TABLE table_name LOCATION ( 'new_file_name.csv' );
>
> I found that out the hard way...at least it was in dev. I did something like this previously for a DW environment.
>
>
> Part I <http://www.oraclenerd.com/2008/04/validating-process.html> and Part II <http://www.oraclenerd.com/2008/05/validating-process-part-ii.html>
>
>
> Essentially what you need to do, is create a simple java class that can read the contents of your directory,
>
> wrap that up in a PL/SQL Stored procedure then loop through the directory contents.
>
>
> I think your best option, to avoid DDL, is to rename the file in the directory using a combination of Java (directory contents)
>
> and UTL_FILE to rename the file to some "common" name you choose. You can utilize the same methodology to move
>
> the files after you have completed processing.
>
>
> Hope this helps.
>
>
> chet
>
>
> On Tue, Sep 8, 2009 at 5:45 PM, Eugene Pipko <eugene.pipko_at_unionbay.com> wrote:
>
> Hi all,
>
> Running Oracle 9.2 on Windows.
>
> I know this is Oracle forum, but it’s somewhat related. This question is
> for those of you who run Oracle on Windows.
>
> I have several “.txt” files in a directory (all different names) and
> looking for the possibility to do the following using .BAT file:
>
> 1. Take first file
>
> 2. rename it
>
> 3. run procedure (pass it in)
>
> 4. move it into archive dir
>
> 5. delete it from original dir
>
> 6. GOTO 1
>
>
>
> Reason I have to do all this is because in step 3 I am using external table
> to read the “.txt” file and it has to have a unique file name.
>
>
>
> Regards,
>
>
>
> Eugene Pipko
>
> Seattle Pacific Industries
>
> office: 253.872.5243
>
> cell: 206.304.7726
>
> P Please consider the environment before printing this e-mail.
>
>
>
>
>
>
> --
> chet justice
> www.oraclenerd.com
>

-- 
chet justice
www.oraclenerd.com

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

Received on Tue Sep 08 2009 - 17:45:30 CDT