Monday, October 24, 2016

creating a function for finding ancestor in oracle from parent-child table

when we have a table as

parent        child

A            B
B            C
C           D


then here the ancestor of (D) is to be found as
C
B
A

Thus finding such result from the table can be easily done in database through the use of hierarchial query

SELECT parent FROM ancestor START WITH child = 'D' CONNECT BY PRIOR parent = child;

this query can directly qive us the result as oracle has the feature of hierarchical query . So The function in pl/sql can be written as:

create or replace FUNCTION find_ancestor(in_cname IN varchar2) 
return varchar2

AS 
    stat varchar2(50);


CURSOR anc_cur IS 
SELECT parent FROM ancestor START WITH child = in_cname CONNECT BY PRIOR parent = child;
anc_rec anc_cur%ROWTYPE;
indx number := 0;

BEGIN
open anc_cur;

dbms_output.put_line('-------ancestors  of ----------' || in_cname);
loop
fetch anc_cur into anc_rec;

if anc_cur%NOTFOUND
then
exit;
else
   -- return anc_rec.parent;
dbms_output.put_line(''||anc_rec.parent);


end if;
end loop;
close anc_cur;
stat := '-----success---------';
return stat;


END find_ancestor;

/

now the result can be obtained with query  as 
select find_ancestor('D') from dual;


now we can also write the function without using this in built query but using recursion and cursor as:

create or replace FUNCTION find_ancestor(in_cname IN varchar2) 
return varchar2

AS 
    stat varchar2(50);


CURSOR anc_cur IS 
SELECT parent FROM ancestor  where child = in_cname;
anc_rec anc_cur%ROWTYPE;
indx number := 0;

BEGIN
open anc_cur;

-- dbms_output.put_line('-------ancestors  of ----------' || in_cname);
loop
fetch anc_cur into anc_rec;

if anc_cur%NOTFOUND
then

            -- dbms_output.put_line('inside cursor exit');
exit;
else

         stat:= find_ancestor(anc_rec.parent);
   -- return anc_rec.parent;
dbms_output.put_line(''||anc_rec.parent);



end if;
end loop;

close anc_cur;
    stat:= '----success---';

return stat;


END find_ancestor;
/

here the same query can bewritten and the result is same but here we have used recursion to find the parent.

Python script to run the bash script file

we can run the  script file or executable file from the progamming just simply by ./script_file name . Sometimes we may reach in to the situation that we need to run through the python program. so inorder to run the script from python file can be easily done through  subprocess  of python.

The scenario of this program is at a certain location, I have several folder  like A,B,C, D and so on and each folder has the executable files. I need to run each of them. And to do so I can go to each folder and run the executable, however this is tedious. thus This is the program which runs the executable files from the each folder.


import os
import subprocess
rootdir = '/home/laxmi/Desktop/assembler/assignment_grad/assign3/grade'

for subdir, dirs, files in os.walk(rootdir):
    for file in dirs:
        path = os.path.join(subdir, file)
        # print file
        # abspath = os.path.abspath(__file__)
        # print abspath
        # dname = os.path.dirname(abspath)
        # print dname
        os.chdir(path)
        print " --------entering folder------"
        print "%s"%path
        subprocess.call('./asm64 *.asm',shell=True)