public static string getPLSQLFromFile(string fileName) { // Console.WriteLine("Reading " + fileName + "..."); // string fileName = @"G:\ucg\Database\croo\plsql\BL_ATRIBUTFIZICKOGLICA.pkb"; StreamReader r = new StreamReader(fileName, Encoding.GetEncoding(Settings.getEncoding(false))); SQLPlusScanner scanner = new SQLPlusScanner(r, fileName); String token = null; CommandTypes mode = CommandTypes.Unknown; do { try { token = scanner.get(); mode = scanner.currCommand.cmdType; } // this is needed to filter out any SQLPlus commands like PROMPT // or line&block comments catch (EOBException) { mode = scanner.currCommand.cmdType; scanner.resetBlockType(); } } while (mode == CommandTypes.SqlPlus); // Console.WriteLine(token); while (token.ToUpper() != "FUNCTION" && token.ToUpper() != "LIBRARY" && token.ToUpper() != "PACKAGE" && token.ToUpper() != "PROCEDURE" && token.ToUpper() != "TRIGGER" && token.ToUpper() != "TYPE" && token.ToUpper() != "VIEW") { token = scanner.get(); // Console.WriteLine(token); } StringBuilder sb = new StringBuilder(); string currLine = null; try { currLine = scanner.getLineFromStartOfLastToken(); while (currLine != null) { if (sb.Length > 0) sb.Append("\n"); sb.Append(currLine.TrimEnd(null)); currLine = scanner.getLine(); } } catch (EOBException) { } // Example of plsql file, why this was added // Blank lines between end of stored procedure and block terminator // ... // end; // // // / // text += "\n"; r.Close(); //string fileName1 = @"c:\temp\" + Path.GetFileName(fileName) + ".sqlmake"; //System.IO.File.WriteAllText(fileName1, text); return sb.ToString().TrimEnd(); }
public static string extractPlsql(string text, string fileName) { StringReader r = new StringReader(text); SQLPlusScanner scanner = new SQLPlusScanner(r, fileName); String token = null; CommandTypes mode = CommandTypes.Unknown; do { try { token = scanner.get(); mode = scanner.currCommand.cmdType; } // this is needed to filter out any SQLPlus commands like PROMPT // or line&block comments catch (EOBException) { mode = scanner.currCommand.cmdType; scanner.resetBlockType(); } } while (mode == CommandTypes.SqlPlus); // Console.WriteLine(token); while (token.ToUpper() != "FUNCTION" && token.ToUpper() != "LIBRARY" && token.ToUpper() != "PACKAGE" && token.ToUpper() != "PROCEDURE" && token.ToUpper() != "TRIGGER" && token.ToUpper() != "TYPE" && token.ToUpper() != "VIEW") { token = scanner.get(); // Console.WriteLine(token); } // with views we use only select statement starting after keyword AS if (token.ToUpper() == "VIEW") { while (token.ToUpper() != "AS" ) { token = scanner.get(); } token = scanner.get(); } StringBuilder sb = new StringBuilder(); string currLine = null; try { currLine = scanner.getLineFromStartOfLastToken(); while (currLine != null) { if (sb.Length > 0) sb.Append("\n"); sb.Append(currLine.TrimEnd(null)); currLine = scanner.getLine(); } } catch (EOFException) { } // Example of plsql file, why this was added // Blank lines between end of stored procedure and block terminator // ... // end; // // // / // text += "\n"; r.Close(); //string fileName1 = @"c:\temp\" + Path.GetFileName(fileName) + ".sqlmake"; //System.IO.File.WriteAllText(fileName1, text); return sb.ToString().TrimEnd(); }