mardi 13 février 2018

read a file and find if a string exists and return the tablename

I am trying to read a file, if the file has 'alter table' then it has to search for 'modify' if modify exists then it has to return the table name. For that i wrote the below code and it worked.

filename='modify_table.sql'
bol1="false"
File.foreach(filename).with_index do |line, line_num|

#convert all characters to upper case
 if ( line =~ /[a-z]/ )
 line = line.upcase
 end
  if (line =~ /ALTER TABLE/) 

    location = line.index("ALTER TABLE") + 11
    subline = line[location..-1]
    sublineParts = subline.split(" ")
    tableName = sublineParts[0]

    bol1 = line.include?("MODIFY")
    if (bol1)
    puts " found modify column on #{tableName}"
  else
    puts " no modify found"
  end
end
end

My file contains:

begin
BEGIN EXECUTE IMMEDIATE 'alter table schemaname.tablename 
modify (
abc              VARCHAR2(200 BYTE),
xyz               VARCHAR2(200 BYTE)
)'; EXCEPTION when others then if (SQLCODE != -01430 and SQLCODE != -942) then RAISE; end if; END;
end;
/

if alter and modify are on the same line my code works. In the above file, both are in diff line. so the code i wrote returns no modify found even if there is a modify in the file. Can someone help me how I could read the next line and find modify

Aucun commentaire:

Enregistrer un commentaire