Example #1
0
        public static ArrayList parseGrantSql(string p_sql, string p_filename)
        {
            SQLPlusScanner scanner = new SQLPlusScanner(new StringReader(p_sql), "parseGrantSql");
            string currGrantType = "";
            string currObjectClause = "";
            string currGrantor = "";
            string currTableName = "";
            string currAdminOption = "NO";
            string currGrantable = "NO";
            string currHierarchy = "NO";
            bool complete = false;
            StringBuilder grantPrivileges = new StringBuilder();
            StringBuilder grantees = new StringBuilder();

            try
            {
                string currentToken = scanner.getSkipComments();
                if (currentToken != "GRANT") throw new ArgumentException("Sql does not contain grant statement");

                while (true)
                {
                    currentToken = scanner.getSkipComments();
                    if (currentToken == "ON" || currentToken == "TO") break;
                    if (currentToken != "," && grantPrivileges.Length > 0) grantPrivileges.Append(" ");
                    grantPrivileges.Append(currentToken);
                }

                if (grantPrivileges.ToString().Trim() == "")
                    Log.Warning("GrantObject", "Incorrect grant, was expecting grant privileges: {0}", p_sql);

                if (currentToken == "ON") currGrantType = "OBJECT";
                else currGrantType = "SYSTEM";

                // read objectName
                if (currGrantType == "OBJECT")
                {
                    currentToken = scanner.getSkipComments();
                    if (currentToken == "DIRECTORY")
                    {
                        currObjectClause = currentToken;
                        currentToken = scanner.getSkipComments();
                    }
                    else if (currentToken == "JAVA")
                    {
                        currObjectClause = currentToken;
                        currentToken = scanner.getSkipComments();
                        if (currentToken != "SOURCE" || currentToken != "RESOURCE")
                            SQLMake.Util.Log.Warning("GrantObject", "Incorrect grant, was expecting keyword SOURCE|RESOURCE: {0}", p_sql);
                        currObjectClause += " " + currentToken;
                        currentToken = scanner.getSkipComments();
                    }

                    if (currentToken.IndexOf('.') > -1)
                    {
                        currGrantor = currentToken.Split('.')[0];
                        currTableName = currentToken.Split('.')[1];
                    }
                    else
                    {
                        currTableName = currentToken;
                    }
                    currentToken = scanner.getSkipComments();
                }

                if (currentToken != "TO")
                    Log.Warning("GrantObject", "Incorrect grant, was expecting keyword TO: {0}", p_sql);

                // read grantees part
                while (true)
                {
                    currentToken = scanner.getSkipComments();
                    grantees.Append(currentToken);
                    complete = true;
                    currentToken = scanner.getSkipComments();
                    if (currentToken != ",") break;
                    else grantees.Append(currentToken);
                }

                // read with admin clause
                if (currGrantType == "SYSTEM")
                {
                    currentToken += " " + scanner.getSkipComments() + " " + scanner.getSkipComments();
                    if (currentToken == "WITH ADMIN OPTION") currAdminOption = "YES";
                    else Log.Warning("GrantObject", "Incorrect grant, was expecting keyword WITH ADMIN OPTION: {0}", p_sql);
                }
                else
                {
                    currentToken += " " + scanner.getSkipComments() + " " + scanner.getSkipComments();
                    if (currentToken == "WITH GRANT OPTION") currGrantable = "YES";
                    else if (currentToken == "WITH HIERARCHY OPTION") currHierarchy = "YES";
                    else Log.Warning("GrantObject", "Incorrect grant, was expecting keyword WITH GRANT OPTION|WITH HIERARCHY OPTION: {0}", p_sql);

                    currentToken = scanner.getSkipComments();
                    currentToken += " " + scanner.getSkipComments() + " " + scanner.getSkipComments();
                    if (currentToken == "WITH GRANT OPTION") currGrantable = "YES";
                    else if (currentToken == "WITH HIERARCHY OPTION") currHierarchy = "YES";
                    else Log.Warning("GrantObject", "Incorrect grant, was expecting keyword WITH GRANT OPTION|WITH HIERARCHY OPTION: {0}", p_sql);
                }

                currentToken = scanner.getSkipComments();
                Log.Warning("GrantObject", "Incorrect grant, keywords found after expected end of grant clause: {0}", p_sql);
            }
            catch (EOFException)
            {
                if (!complete) Log.Warning("GrantObject", "Incorrect grant, premature termination of sql statement: {0}", p_sql);
            }
            catch (EOBException)
            {
                if (!complete) Log.Warning("GrantObject", "Incorrect grant, premature termination of sql statement: {0}", p_sql);
            }

            ArrayList resultSet = new ArrayList();
            if (complete)
            {
                foreach (string grantPrivilege in grantPrivileges.ToString().Split(','))
                {
                    foreach (string grantee in grantees.ToString().Split(','))
                    {
                        GrantObject grantObject = new GrantObject(p_filename, currGrantType, currObjectClause, replaceWithSandbox(grantee.Trim()), currTableName, replaceWithSandbox(currGrantor.Trim()), grantPrivilege, currAdminOption, currGrantable, currHierarchy);
                        // Console.WriteLine(grantObject.ToString());
                        resultSet.Add(grantObject);
                    }
                }
            }
            return resultSet;
        }
Example #2
0
        public static SynonymObject parseSynonymSql(string p_sql, string p_filename)
        {
            SQLPlusScanner scanner = new SQLPlusScanner(new StringReader(p_sql), "parseSynonymSql");
            string synonymSchema = "";
            string synonymName = "";
            string synonymTarget = "";
            bool isPublic = false;

            bool complete = false;

            try
            {
                string currentToken = scanner.getSkipComments();
                if (currentToken == "CREATE") currentToken = scanner.getSkipComments();
                if (currentToken == "OR")
                {
                    currentToken = scanner.getSkipComments();
                    if (currentToken != "REPLACE") throw new ArgumentException("REPLACE expected after CREATE OR tokens");
                    currentToken = scanner.getSkipComments();
                }

                isPublic = false;
                if (currentToken == "PUBLIC")
                {
                    isPublic = true;
                    currentToken = scanner.getSkipComments();
                }

                if (currentToken != "SYNONYM") throw new ArgumentException("Sql does not contain SYNONYM statement");
                currentToken = scanner.getSkipComments();

                // expecting schema.synonymName
                if (currentToken.IndexOf('.') > -1)
                {
                    synonymSchema = currentToken.Split('.')[0];
                    synonymName = currentToken.Split('.')[1];
                }
                else
                {
                    synonymSchema = "";
                    synonymName = currentToken;
                }

                currentToken = scanner.getSkipComments();
                // expecting FOR
                if (currentToken != "FOR") throw new ArgumentException("FOR statement expected in synonym statement");

                // target schema: schema.object@db_link
                // we have to convert schema to sandbox name
                currentToken = scanner.getSkipComments();
                if (currentToken.IndexOf('.') > -1)
                {
                    synonymTarget = replaceWithSandbox(currentToken.Split('.')[0].Trim()) + "." + currentToken.Split('.')[1];
                }
                else
                {
                    synonymTarget = currentToken;
                }

                complete = true;

                // this should throw exception as we should be at the end
                currentToken = scanner.getSkipComments();
            }
            catch (EOFException)
            {
                if (!complete) Log.Warning("SynonymObject", "Incorrect synonym, premature termination of sql statement: {0}", p_sql);
            }
            catch (EOBException)
            {
                if (!complete) Log.Warning("GrantObject", "Incorrect synonym, premature termination of sql statement: {0}", p_sql);
            }

            return new SynonymObject(p_filename, synonymSchema, synonymName, synonymTarget, isPublic);
        }
Example #3
0
        public static void Print(string p_dir, string p_include_list, SearchOption p_recurse_dir)
        {
            string[] files = new string[1];

            string[] include_list = p_include_list.Split(',');
            foreach (string filetype in include_list)
            {
                string[] matchedFiles = Directory.GetFiles(p_dir, filetype, p_recurse_dir);
                if (matchedFiles.Length > 0)
                {
                    int startPos = files.Length;
                    if (startPos == 1) startPos = 0; // compensate files array[1] declaration
                    Array.Resize(ref files, startPos + matchedFiles.Length);
                    Array.Copy(matchedFiles, 0, files, startPos, matchedFiles.Length);
                }
            }

            foreach (string filename in files)
            {
                //Console.WriteLine("xxx"+filename);
                StreamReader r = new StreamReader(filename, Encoding.GetEncoding(Settings.getEncoding(false)));
                SQLPlusScanner scanner = new SQLPlusScanner(r, filename);

                try
                {
                    do
                    {
                        try
                        {
                            do { scanner.get(); } while (true);
                        }
                        catch (EOBException)
                        {
                            if (scanner.currCommand.action != "REMARK") Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", filename, scanner.getModeDesc(), scanner.currCommand.action, scanner.currCommand.cmdName, scanner.currCommand.objectName, scanner.currCommand.alterType, scanner.currCommand.secondaryCmdName, scanner.currCommand.secondaryObjectName);
                            scanner.resetBlockType();
                        }
                    } while (true);
                }
                catch (EOFException)
                {
                }
            }
        }
Example #4
0
        public static void BasicStats(string p_dir, SearchOption p_recurse_dir)
        {
            // Read configuration
            string configSqlFileList = SQLMake.Util.Settings.getSqlFileList(true);

            // disable variable substitution as we are just scanning through files
            Settings.overrideSqlPlusVariableSubstitutionFlag(false);

            int dmlModeCount = 0; // insert, update, delete, select
            int ddlModeCount = 0; // all other SQL
            int tabColCommentsModeCount = 0; //
            int plsqlModeCount = 0;
            int sqlPlusModeCount = 0;
            int unknownModeCount = 0;
            int numberOfScripts = 0;

            string[] files = FolderSearch.Search(p_dir, Settings.getRecurseFlag(true), Settings.getSqlFileList(true), Settings.getIgnoreDirList(true), Settings.getIgnoreFileList(true));

            foreach (string filename in files)
            {
                //Console.WriteLine(filename);
                numberOfScripts++;
                StreamReader r = new StreamReader(filename, Encoding.GetEncoding(Settings.getEncoding(false)));
                SQLPlusScanner scanner = new SQLPlusScanner(r, filename);

                try
                {
                    do
                    {
                        try
                        {
                            do { scanner.get(); } while (true);
                        }
                        catch (EOBException)
                        {
                            switch (scanner.currCommand.cmdType)
                            {
                                case CommandTypes.Sql:
                                    if (scanner.currCommand.action == "SELECT" ||
                                        scanner.currCommand.action == "INSERT" ||
                                        scanner.currCommand.action == "UPDATE" ||
                                        scanner.currCommand.action == "DELETE") dmlModeCount++;
                                    else if (scanner.currCommand.action == "COMMENT") tabColCommentsModeCount++;
                                    else ddlModeCount++;
                                    break;
                                case CommandTypes.Plsql:
                                    plsqlModeCount++;
                                    break;
                                case CommandTypes.SqlPlus:
                                    sqlPlusModeCount++;
                                    break;
                                case CommandTypes.Unknown:
                                    //Console.WriteLine("Unknown BLOCK found in file {0} from line {1} to {2}", filename, scanner.startLineIndex + 1, scanner.currLineIndex + 1);
                                    //Console.WriteLine(scanner.getCurrentLine());
                                    unknownModeCount++;
                                    break;
                            }
                            //Console.WriteLine("   mode {1}, action {0} {2}, plsql:{3}, sql:{4}, sqlplus:{5}", scanner.action, scanner.getModeDesc(), scanner.currSubMode, plsqlModeCount, ddlModeCount, sqlPlusModeCount);

                            scanner.resetBlockType();
                        }
                    } while (true);
                }
                catch (EOFException)
                {
                    if (scanner.currCommand.cmdType != CommandTypes.Unknown)
                    {
                        Log.Warning("stat", "Last {0} in file {1} not correctly terminated", scanner.getModeDesc(), filename);
                        Console.WriteLine("Last {0} in file {1} not correctly terminated", scanner.getModeDesc(), filename);
                    }
                }
            }
            Console.WriteLine("Number of scripts: {0}", numberOfScripts);
            Console.WriteLine("Number of DDL commands: {0}", ddlModeCount);
            Console.WriteLine("Number of table/column comments: {0}", tabColCommentsModeCount);
            Console.WriteLine("Number of DML commands: {0}", dmlModeCount);
            Console.WriteLine("Number of PLSQL commands: {0}", plsqlModeCount);
            Console.WriteLine("Number of SQLPlus commands: {0}", sqlPlusModeCount);
            Console.WriteLine("Number of Unknown commands: {0}", unknownModeCount);
        }
Example #5
0
        // Find all dependencies between sql script files
        // Who is calling who
        /// ///////////////////////////////////////////////////////////////////
        public static void Run(string callerScript, string targetScript, string baseDir, int level)
        {
            Console.WriteLine("REM "+" ".PadLeft(level * 3) + Path.GetFileName(targetScript) + "(" + Path.GetDirectoryName(targetScript) + ")");
            String normalisedTargetScript = "";

            if (File.Exists(targetScript))
                normalisedTargetScript = targetScript;
            else if (File.Exists(targetScript + ".sql"))
                normalisedTargetScript = targetScript + ".sql";
            else
            {
                Log.Error("Crawler", "SP2-0310: unable to open file \"{0}\"", targetScript);
                return;
            }

            StreamReader r = new StreamReader(normalisedTargetScript, Encoding.GetEncoding(Settings.getEncoding(false)));
            SQLPlusScanner scanner = new SQLPlusScanner(r, targetScript);

            String token = null;

            try
            {
                // loop through all SQLPlus, SQL and PL/SQL commands in script
                do
                {
                    try
                    {
                        token = scanner.get();

                        // Is this SQLPlus command one of: start, @, @@
                        // this is indicated by scanner mode SQLPlusStartMode
                        if (scanner.currCommand.cmdType == CommandTypes.SqlPlusStart)
                        {
                            string scannerAction = scanner.currCommand.action;

                            // We have found token start, @ or @@
                            // All text until end of the line is considered as a target
                            String target = null;
                            try
                            {
                                do { target += " " + scanner.get(); } while (true);
                            }
                            catch (EOBException)
                            {
                                scanner.resetBlockType();
                            }

                            if (scannerAction != "@@") Run(targetScript, baseDir + "\\" + target.Trim(), baseDir, level + 1);
                            else Run(targetScript, Path.GetDirectoryName(targetScript) + "\\" + target.Trim(), baseDir, level + 1);

                        }
                        else if (scanner.currCommand.cmdType == CommandTypes.SqlPlus && scanner.currCommand.action == "DEFINE")
                        {
                            string define_variable_name = scanner.get();
                            scanner.get(); // skip equal sign
                            string define_variable_content = scanner.get();

                            // variable content can be optionally enclosed in 'xyz'
                            if (define_variable_content.StartsWith("'") && define_variable_content.EndsWith("'"))
                                define_variable_content = define_variable_content.Substring(1, define_variable_content.Length - 2);

                            Settings.addSqlplusVariable(define_variable_name.ToUpper(), define_variable_content);
                        }
                        // This command is not one of: start, @, @@
                        // Skip it
                        else
                        {
                            try
                            {
                                do { scanner.get(); } while (true);
                            }
                            catch (EOBException)
                            {
                                Console.WriteLine(scanner.currBlockText);
                                scanner.resetBlockType();
                            }
                        }
                    }
                    catch (EOBException)
                    {
                        scanner.resetBlockType();
                    }
                } while (true);
            }
            catch (EOFException)
            {
            }
        }
Example #6
0
        public static Object[] Load(string filename)
        {
            int sqlModeCount = 0;
            int plsqlModeCount = 0;
            int sqlPlusModeCount = 0;
            int unknownModeCount = 0;
            int seqInFile = 0;
            string commandType = "";
            string objectType = "";
            string action = "";

            StreamReader r = new StreamReader(filename, Encoding.GetEncoding(Settings.getEncoding(false)));
            SQLPlusScanner scanner = new SQLPlusScanner(r, filename);

            ArrayList sqlObjectList = new ArrayList();

            try
            {
                do
                {
                    try
                    {
                        //Console.WriteLine(filename);
                        do {
                            scanner.get();
                            if ((scanner.currCommand.cmdType == CommandTypes.Plsql ||
                                scanner.currCommand.cmdType == CommandTypes.WrappedPlsql)
                                && scanner.currCommand.objectName != "") break;
                           } while (true);

                        if (scanner.currCommand.cmdType == CommandTypes.Plsql ||
                            scanner.currCommand.cmdType == CommandTypes.WrappedPlsql)
                        {
                            do { scanner.getLine(); } while (true);
                        }

                    }
                    catch (EOBException)
                    {
                        commandType = "Unknown";
                        seqInFile++;
                        action = scanner.currCommand.action; // CREATE, ALTER, DROP, INSERT, ...
                        objectType = scanner.currCommand.cmdName; //TABLE, PACKAGE, TRIGGER, INDEX, ...

                        switch (scanner.currCommand.cmdType)
                        {
                            case CommandTypes.Sql:
                                sqlModeCount++;
                                commandType = "SQL";
                                if (action == "ALTER" && objectType == "TABLE" && scanner.currCommand.alterType == "ADD CONSTRAINT")
                                {
                                    action = "CREATE";
                                    objectType = scanner.currCommand.secondaryCmdName;
                                    if (objectType != "CHECK") objectType += " KEY";
                                }
                                if (action == "COMMENT")
                                {
                                    action = "CREATE";
                                    objectType = "COMMENT";
                                }

                                //Console.WriteLine("[{6},{7}] action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType, scanner.blockStartLine+1, scanner.currLineIndex+1);
                                //Console.WriteLine(scanner.currBlock.ToString().Trim());
                                break;
                            case CommandTypes.Plsql:
                                commandType = "PLSQL";
                                plsqlModeCount++;
                                //Console.WriteLine("[{6},{7}] action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType, scanner.blockStartLine+1, scanner.currLineIndex+1);
                                break;
                            case CommandTypes.WrappedPlsql:
                                commandType = "PLSQL";
                                plsqlModeCount++;
                                //Console.WriteLine("[{6},{7}] action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType, scanner.blockStartLine+1, scanner.currLineIndex+1);
                                break;
                            case CommandTypes.SqlPlus:
                                commandType = "SQLPlus";
                                //Console.WriteLine("action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType);
                                sqlPlusModeCount++;
                                break;
                            case CommandTypes.SqlPlusStart:
                                commandType = "SQLPlus";
                                //Console.WriteLine("action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType);
                                sqlPlusModeCount++;
                                break;
                            case CommandTypes.Unknown:
                                commandType = "Unknown";
                                //Console.WriteLine("Unknown BLOCK found in file {0} from line {1} to {2}", filename, scanner.blockStartLine + 1, scanner.currLineIndex + 1);
                                //Console.WriteLine(scanner.getCurrentLine());
                                unknownModeCount++;
                                break;
                            default:
                                throw new NotImplementedException();
                        }
                        sqlObjectList.Add(new SqlObject(commandType,
                                                        scanner.currCommand.objectName,
                                                        scanner.currCommand.secondaryObjectName,
                                                        action,
                                                        objectType,
                                                        filename,
                                                        seqInFile,
                                                        scanner.blockStartLine + 1,
                                                        scanner.currLineIndex + 1,
                                                        scanner.currBlockText.ToString().Trim()));
                        scanner.resetBlockType();
                    }
                } while (true);
            }
            catch (EOFException)
            {
                if (scanner.currCommand.cmdType != CommandTypes.Unknown)
                {
                    Log.ExitError(String.Format("Last {0} in file {1} not correctly terminated", scanner.getModeDesc(), filename));
                }
            }

            //foreach (SqlObject s in sqlObjectList)
            //{
            //    Console.WriteLine("[{0},{1}] action {2}, objectType {3}, objectName {4}, secondaryObjectName {5}", s.lineStart, s.lineEnd, s.action, s.objectType, s.objectName, s.secondaryObjectName);
            //}

            return sqlObjectList.ToArray();
        }
Example #7
0
        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();
        }
Example #8
0
        public static string getObjectNameFromFile(string fileName, ref string plsqlObjectType)
        {
            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);

            // This is cheap but perhaps not exactly correct
            // way to skip "create or replace"
            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();
            }
            plsqlObjectType = token.ToUpper();

            token = scanner.getSkipComments();

            if ((plsqlObjectType == "PACKAGE" || plsqlObjectType == "TYPE")
                && token.ToUpper() == "BODY")
            {
                plsqlObjectType += " BODY";
                token = scanner.getSkipComments();
            }

            // now the token contains plsql object name
            string plsqlObjectName = token;

            r.Close();
            r.Dispose();

            return plsqlObjectName;
        }
Example #9
0
        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();
        }