Invoking sql statements in shell scripts

Question

Hello members,

I'm working on the Solaris environment and the DB i'm using is Oracle 10g.

Skeleton of what I'm attempting;

Write a ksh script to perform the following. I have no idea how to include my sql query within a shell script and loop through the statements. Have therefore given a jist of what I'm attempting, below.

1. Copy file to be processed (one file at a time, from a list of 10 files in the folder ).

   for i in * do cp $i /home/temp

2 . Create a snapshot(n) table : Initialize n = 1

create table test insert account_no, balance from
records_all;

-- creates my snapshot table and inserts records in SQL

1. Checking if the table has been created successfully:

select count(*) from snapshot1 -- query out the number of records in the table -- always fixed, say at 400000

if( select count(*) from snapshot(n) = 400000 )
 echo " table creation successful.. proceed to the next step "
else
 echo " problem creating table, exiting the script .. "

1. If table creation is successful,

   echo " select max(value) from results_all " -- printing the max value to console

2. Process my files using the following jobs:

./runscript.ksh - READ -i $m ( m - initial value 001 ) ./runscript.ksh - WRITE -i $m ( m - initial value 001 -- same as READ process_id )

-- increment m by 1

1. Wait for success log

   tail -f log($m)* | -egrep "^SUCCESS"

2. looping to step1 to : Copy file 2 to temp folder; create snapshot(n+1) table

Exit when all the files have been copied for processing.

done -- End of Step 1

Pointers on getting me moving will be very valuable.

thanks,

Kris

Answer 1

Here's a pointer: don't try to do this in the shell. You could shoehorn this algorithm into the shell with a ton of heavily escaped expressions and pipes to and from your database client, but it would be difficult to read and difficult to maintain. Look into performing this task with some scripting or compiled language -- Python, Perl, R, Java, C++ -- with packaged support for Oracle databases.

Answer 2

As it happens I have just done something similar in real life. So here is a pure SQL solution.

First you'll need somebody with CREATE ANY DIRECTORY privilege (probably a DBA) to create a directory object:

create directory load_dir as '/home/temp'
/
grant read, write on directory load_dir to <your_user>
/

The following PL/SQL block opens the file and reads through it. For each line in the file it creates a table, counts from it and gets the maximum ID value.

declare
    fh_tabs utl_file.file_type;
    table_name varchar2(30);
    snapshot_name varchar2(30);
    stmt varchar2(4000) ;
    cnt_row pls_integer;
    cnt_tab pls_integer := 0;
    max_id pls_integer;
begin
    fh_tabs := utl_file.fopen('LOAD_DIR', 'file.name', 'R');
    << tables >>
    loop
        begin
            utl_file.get_line(fh_tabs, table_name);
            cnt_tab := cnt_tab + 1;
            snapshot_name := 'snapshot'||trim(to_char(cnt_tab));
            stmt := 'create table '||snapshot_name
                               ||' as select * from '||table_name;
            execute immediate stmt;
            execute immediate 'select count(*) from '
                               ||snapshot_name into cnt_row;
            dbms_output.put_line('New table '||snapshot_name||' has '
                               ||to_char(cnt_row)||' records.');
            execute immediate 'select max(id) from '
                               ||snapshot_name into max_id;
            dbms_output.put_line('Highest primary key = '||max_id);
        exception
            when no_data_found then
                utl_file.fclose(fh_tabs);
                dbms_output.put_line('End of file!. Tables created = '
                               ||to_char(cnt_tab));
                exit;
        end;
    end loop tables;
end;
/

I have assumed your file contains nothing but table names, and that you want the snapshot tables to have the same name, and that the ID column has the same name on all tables. The script can be modified to change these assumptions, but the additional data has to come from somewhere. It could belong to the input file, in which case you would need to tokenize the read lines.

Answer 3

There are twio options with this could be achieved.

1. Create a sql script file and execute it once the connection to the database is established properly and
2. Create a here document and execute it with the sqlplus by connecting to the database

1. Create a sql script file and execute it once the connection to the database is established properly

sql_file=sachin.sql
cat <<!SQL > $sql_file
    select $1 from dual;
    exit;
!SQL

sysdate=`sqlplus -s $ORACLE_LOGIN  @$sql_file 2>/dev/null  `
echo $sysdate
rm $sql_file

2 Create a here document and execute it with the sqlplus

print "your query;
COMMIT;" > SQLS
cmd='sqlplus ${ORALOGIN} < SQLS >> sachin.log
eval $cmd