Re: better sql code
- From: Jared Still <jkstill@xxxxxxxxx>
- To: raja4list@xxxxxxxxx
- Date: Tue, 25 Oct 2005 10:58:34 -0700
Comments inline:
On 10/25/05, raja rao <raja4list@xxxxxxxxx> 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;
/
Other related posts:
