Looping through records in db and processing them in UNIX

Shell Programming and Scripting
1

Hi,

I want to read multiple records from oracle database table and then based on each record I need to do some file modifications in UNIX.

I'm not sure how do I process each record from DB in UNIX.Below is the code snippet.

sqlplus user/pwd@DEV
for i IN (select * from table)
loop
-- for every record I need to switch to UNIX and move the files to some directory.
end loop

Thanks.

2

assume your read the filename from table as column fname
If you were to execute the host command hundreds of times it would be VERY slow.
Write to a file instead, then run all the commands afterwards, plus this lets you check
your commands for errors before they mess up.

> ./destfile.shl

# here document for sqlplus code:
sqlplus user/pwd@DEV <<-EOF
set serverout on size 1000000
spool destfile.shl
DECLARE
  fname varchar2(256):=NULL;
BEGIN
dbms_output.enable(1000000);
for i IN (select fname from table)  --or whatever you need to do to get a resultset
loop
    dbms_output.put_line( '/usr/bin/mv '||fname||  ' /destination/some/directory ');
-- for every record I need to switch to UNIX and move the files to some directory.
-- no you do not
end loop
END;
/

EOF

# now execute all your commands
# READ destfile.shl for errors first then run destfile.shl
#
# ./destfile.shl  uncomment for production.
3

You could also do this in the shell if you run the SQL and capture the output. You could do this with a file or a pipe to get it input a shell loop. It may be simpler to understand but may not run as efficiently as the all-in-one SQL solution suggested above.

Do you want to consider it?

Robin