From oracle-l-bounce@freelists.org Tue Oct 25 13:45:42 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9PIjgH7005643 for ; Tue, 25 Oct 2005 13:45:42 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9PIjXvX005599 for ; Tue, 25 Oct 2005 13:45:34 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 45EEB20F255; Tue, 25 Oct 2005 13:00:41 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26564-01; Tue, 25 Oct 2005 13:00:41 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6D36020F242; Tue, 25 Oct 2005 13:00:40 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ldvZUeDkkwpli/kH0Ddd6NY/h1FCnxWcXbvQI3n6sAylh/oPx4oWqc5drSkDoPpRonUO/3JpfwK7nwRZ2mGWCXOgELYbOGN8ZuOC7slBazvTZeeVYGeFfNS35sNIZcvztEgnpyN41weLNu72m5CDLzFNTz7f44Rf5svtt9MByb0= Message-ID: Date: Tue, 25 Oct 2005 10:58:34 -0700 From: Jared Still To: raja4list@yahoo.com Subject: Re: better sql code Cc: oracle list In-Reply-To: <20051025171600.39007.qmail@web31404.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5302_21254019.1130263114070" References: <20051025171600.39007.qmail@web31404.mail.mud.yahoo.com> X-archive-position: 27541 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jkstill@gmail.com Precedence: normal Reply-To: jkstill@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.7 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 ------=_Part_5302_21254019.1130263114070 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Comments inline: On 10/25/05, raja rao wrote: > > Hi All, > can someone give me a better code for the below: > ... > For this purpose, we have develped the code like this. > we will collect the acct# numbers into a flat file (filename is list) and > for each line will fire theupdate statement like below. > cat list|while read line > do > sqlplus -s < ${connect_string} > @update.sql ${line} > EOF > done > # list is a file which contains the acct# to be updated into main table. > update.sql contains teh below code: > This is a fairly inefficient method. If you are using Oracle 9i+, then you can use external tables, bulk binds and FORALL. Below the sig is a prototype to test with. Modify as needed for your system= , and read the docs on this for options you may need. -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist # Create a directory in the database - the filesystem directory must exist. SQL> create or replace directory forall_dir as '/u02/oradir' / # create a text file for testing $> ls -1 ~/tmp >| /u02/oradir/loadtest.txt # create an external table and a test table drop table loadtest; create table loadtest( filename varchar2(50) ) organization external ( type oracle_loader default directory forall_dir access parameters ( records delimited by newline badfile 'loadtest.bad' discardfile 'loadtest.dis' logfile 'loadtest.log' fields terminated by "," optionally enclosed by '"' ( filename char ) ) location ('loadtest.txt') ) reject limit unlimited / drop table newfiles; create table newfiles ( filename varchar2(50), date_added date ) / create index newfiles_fname on newfiles(filename); # load the table, then update it declare type t_type is table of varchar2(50) index by binary_integer; v_file_table t_type; i integer; v_now date :=3D sysdate; begin -- get the filenames from the file select filename bulk collect into v_file_table from loadtest; -- insert into table forall i in 1..v_file_table.count insert into newfiles(filename) values(v_file_table(i)); -- use an update to set the date added forall i in 1..v_file_table.count update newfiles set date_added =3D v_now where filename =3D v_file_table(i); commit; end; / ------=_Part_5302_21254019.1130263114070 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Comments inline:

On 10/25/05, raja rao <raja4list@yaho= o.com> wrote:
Hi All,
 
can someone give me a better code for the below:
...
For this purpose, we have develped the code like this.
we will collect the acct# numbers into a flat file (filename is list) = and
for each line will fire theupdate statement like below.
 
cat list|while read line
do
sqlplus -s <<EOF
${connect_= string}
@update.sql ${line}
EOF
done
# list is a file which contains the acct# to be updated into main tabl= e.
update.sql contains teh below code:

This is a fairly inefficient method.

If you are using Oracle 9i+, then you can use external tables, bulk binds a= nd FORALL.
Below the sig is a prototype to test with.  Modify as needed for your = system, and read
the docs on this for options you may need.

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangel= ist


# Create a directory in the database - the filesystem directory must exist.=
SQL> create or replace directory forall_dir
as '/u02/oradir'
/

# create a text file for testing
$> ls -1  ~/tmp >| /u02/oradir/loadtest.txt

# create an external table and a test table
drop table loadtest;
 
create  table loadtest(
        filename  varchar2(50)
)
organization external
(
        type oracle_loader
        default directory forall_dir
        access parameters
        (
            &nb= sp;   records delimited by newline
            &nb= sp;   badfile 'loadtest.bad'
            &nb= sp;   discardfile 'loadtest.dis'
            &nb= sp;   logfile 'loadtest.log'
            &nb= sp;   fields terminated by ","  optionally enclosed by '"'             &nb= sp;   (
            &nb= sp;           filename   char
            &nb= sp;   )
        )
        location ('loadtest.txt')
)
reject limit unlimited
/
 
 
drop table newfiles;
 
create table newfiles (
        filename varchar2(50),
        date_added date
)
/
 
create index newfiles_fname on newfiles(filename);

# load the table, then update it
declare
        type t_type is table of varchar2= (50) index by binary_integer;
        v_file_table t_type;
        i integer;
        v_now date :=3D sysdate;
begin
        -- get the filenames from the fi= le
        select filename bulk collect int= o v_file_table from loadtest;
 
        -- insert into table
        forall i in 1..v_file_table.coun= t
            &nb= sp;   insert into newfiles(filename) values(v_file_table(i));
 
        -- use an update to set the date= added
        forall i in 1..v_file_table.coun= t
            &nb= sp;   update newfiles set date_added =3D v_now
            &nb= sp;   where filename =3D v_file_table(i);
 
        commit;
 
end;
/




------=_Part_5302_21254019.1130263114070-- -- http://www.freelists.org/webpage/oracle-l