LinuxQuestions.org - how to get result from both of these modules?

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - how to get result from both of these modules? (https://www.linuxquestions.org/questions/linux-newbie-8/how-to-get-result-from-both-of-these-modules-4175459855/)

how to get result from both of these modules?

Hallo All unix Guru's

I have written a small (static) pl/sql function which is inserting data into database. It is as follows:

Code:

sqlplus $CONNECT <<-EOF

        DECLARE

        Counter integer :=1;

        BEGIN

        WHILE Counter <= ${no_of_files} LOOP

        INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate,'pqrs','pqrs','pqrs','pqrs');

        Counter := Counter + 1;

        END LOOP;

        END;

        /

        commit;

        exit;

        EOF

However the above "insert" needs to be more of dynamic in nature, as such you can see that we want to insert data depending upon the variable value ${no_of_files}.

Now let me show you another module which is giving me the file(s)names which I want to insert.

Code:

N=0

for i in $(find . -path \*/waiting/* -type f -not -name "SS*" -mmin +120 -print) ; do

  

      testarray[$N]="$i"

      echo "$i"    

      

  let "N= $N + 1"

done

Sample Output from above command is as follows:

Code:

/d1/d2/d3/d4/waiting/abcd.txt

/d1/d2/d31/d42/waiting/pqrs.txt

/d1/d2/d32/d43/waiting/xyz.txt

Now the data insert which is given in the above pl/sql block has directly to do with this output. It going to be dynamic in nature.

Let us come back to mapping of how we need to insert data based on this output. I will go through the first insert.2 insert and 3rd insert so you would get an idea of design.
But these are very dynamic, meaning the insert depends on the output produced but yes the directory structure would remain same.

Code:

COL_DATE,SER_NAME,TYPE_FILE,FILEFILENAME,FILE_STATUS

sysdate,d3,d4,abcd.txt,waiting

sysdate,d31,d42,pqrs.txt,waiting

sysdate,d32,d3,xyz.txt,waiting

I believe this can definitely be implemented with help of magic like awk or anything but not able to implement it.

Any ideas/help would be greatly appreciated.

Using bash arrays

Code:

for line in $(<t1.t)

do

    rec=$(echo $line|sed 's:/: :g')

    a=($rec)

    echo "INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, '${a[2]}', '${a[3]}', '${a[4]}', '${a[5]}');"

done



# output

INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, 'd3', 'd4', 'waiting', 'abcd.txt');

INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, 'd31', 'd42', 'waiting', 'pqrs.txt');

INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, 'd32', 'd43', 'waiting', 'xyz.txt');

Just save output to a file eg my.sql, then iirc(?)

Code:

sqlplus user/pass @my.sql

I have got another way and it says success but there is no data in database !!

Have a look at the source code. Doesn't work.

Code:

                        if [[ ${no_of_files_stuck_input_directory} -eq 0 ]] ; then

                                echo -e "No files stuck in input directory\n" >> $LOG_FILE

                        else

                                echo -e "\nThere are ${no_of_files_stuck_input_directory} files stuck in input directory and their names are as follows:\n"

                                N=0;

                                for i in $(find ${BASE_DIRECTORY} -path \*/input/* -type f -not -wholename "*/restore/EAReturn*" -mmin +60 -print) ; do

                                input_files_array[$N]="$i"

                            #        echo "$i"

                                #        echo $file_name

                                        let "N= $N + 1"

                                done >> ${input_files}

                                for i in ${input_files_array[@]}

                                        do

                                                file_name=$i

                                                echo -e file_name[$i]

                                                service_name=$(echo $i|cut -d'/' -f4)

                                                echo -e service_name [$i]

                                                file_type=$(echo $i|cut -d'/' -f5)

                                                echo -e "file_type [$i]\n"

                                                stuck_type=$(echo $i|cut -d'/' -f6)

                                                echo -e "stuck_type [$i]\n"



#                                      sqlplus username/password@database <<-EOF

                                        sqlplus $CONNECT <<-EOF

--                                        DECLARE

--                                        Counter integer :=1;

--                                        BEGIN

--                                        WHILE Counter <= ${no_of_files_stuck_input_directory} LOOP

--                                        INSERT INTO stuck_files(COLLECTION_DATE,SERVICE_NAME,FILE_TYPE,FILENAME,STUCK_TYPE) VALUES (sysdate,'$service_name','$file_type','$file_name','$stuck_type');

--                                        Counter := Counter + 1;

--                                        END LOOP;

--                                        END;

--                                        /

--                                        commit;

                                        exit;

                                        EOF

                                        done

                        fi

Unfortunately I cannot see anything into the database !! why?

@chirsm01

Excellent stuff !the only thing which I am wondering is instead of having just abcd.txt as name of file which you have indicated with
'${a[4]}' If I want to have the entire name(absolute path) what needs to be modified?

I tried with something like this 'line[$i]' but that didn't work. :(

Looking fine now. All I did was instead of '${a[5]} I did was $line and it came through beautifully !!!