Re: better sql code

Comments inline:

On 10/25/05, raja rao <raja4list_at_yahoo.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 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 := 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 = v_now
where filename = v_file_table(i);

commit;

end;
/

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