FAQ
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:

Take first file

rename it

run procedure (pass it in)

move it into archive dir

delete it from original dir

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.

Search Discussions

  • Chet justice at Sep 8, 2009 at 10:11 pm
    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 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

    --
    http://www.freelists.org/webpage/oracle-l
  • Eugene Pipko at Sep 8, 2009 at 10:41 pm
    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
    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 > 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:

    Take first file

    rename it

    run procedure (pass it in)

    move it into archive dir

    delete it from original dir

    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 at Sep 8, 2009 at 10:45 pm
    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 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
    *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 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
  • Yechiel Adar at Sep 9, 2009 at 5:06 am
    In the first option I forgot to write the sqlplus command between the
    copy and the move.

    Adar Yechiel
    Rechovot, Israel

    Yechiel Adar wrote:
    I read the replies and I assume that all the files are in the same
    structure as you are reading them using the same external table.
    You have several options:
    1) If you can process the data from all the files in the same time AND
    have disk space
    you can use:
    copy a.txt+b.txt+c.txt target.txt
    move *.txt archive\*.old /Y -->move the files and rename them to
    old, overwrite existing files in archive.
    this will give you one big file, target.txt, that is concatenation
    of all the files and you can process that.
    2) Use the for command.
    for %i in (*.txt) do call process_file %1
    and create file call_process.cmd that do:
    copy %1 target.txt --> I prefer copy as the original file remains
    a is in case of problems, you can use rename.
    sqlplus whatever
    move %1 archive\%1 /Y --> move the file to archive directory and
    overwrite old file with that name
    exit
    Adar Yechiel
    Rechovot, Israel


    Eugene Pipko 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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Eugene Pipko at Sep 9, 2009 at 7:21 pm
    Thanks for your reply, I, however went with "alter table...." option. Easier and cleaner in my opinion.

    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: Yechiel Adar
    Sent: Tuesday, September 08, 2009 10:07 PM
    Cc: Eugene Pipko; 'Oracle L'
    Subject: Re: help with batch operation

    In the first option I forgot to write the sqlplus command between the copy and the move.

    Adar Yechiel

    Rechovot, Israel

    Yechiel Adar wrote:
    I read the replies and I assume that all the files are in the same structure as you are reading them using the same external table.
    You have several options:
    1) If you can process the data from all the files in the same time AND have disk space

    you can use:
    copy a.txt+b.txt+c.txt target.txt
    move *.txt archive\*.old /Y -->move the files and rename them to old, overwrite existing files in archive.
    this will give you one big file, target.txt, that is concatenation of all the files and you can process that.
    2) Use the for command.

    for %i in (*.txt) do call process_file %1
    and create file call_process.cmd that do:
    copy %1 target.txt --> I prefer copy as the original file remains a is in case of problems, you can use rename.
    sqlplus whatever
    move %1 archive\%1 /Y --> move the file to archive directory and overwrite old file with that name
    exit

    Adar Yechiel

    Rechovot, Israel

    Eugene Pipko 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:

    Take first file

    rename it

    run procedure (pass it in)

    move it into archive dir

    delete it from original dir

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 8, '09 at 9:45p
activeSep 9, '09 at 7:21p
posts6
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase