Esempio n. 1
0
        // get arguments for stand-alone stored procedures/functions
        private void GetProcedureArguments(MetaProcedure proc)
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }

            // GET ARGUMENTS FOR STAND-ALONE PROCEDURES/FUNCTIONS
            string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
                         "	POSITION, SEQUENCE, "+
                         "	IN_OUT AS DIRECTION, "+
                         "	DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE "+
                         "FROM SYS.ALL_ARGUMENTS " +
                         "WHERE OWNER = '" + proc.Owner + "' " +
                         "AND OBJECT_NAME = '" + proc.Name + "' " +
                         "AND PACKAGE_NAME IS NULL " +
                         "AND DATA_LEVEL = 0 AND DATA_TYPE IS NOT NULL " +
                         "ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";

            IDbCommand cmd = con.CreateCommand();

            cmd.CommandText = sql;
            IDataReader reader   = cmd.ExecuteReader();
            string      procType = "Procedures";

            while (reader.Read())
            {
                string procName = reader.GetString(0);
                string argName  = reader.GetString(2);

                if (argName.Equals("(RETURN)"))
                {
                    procType = "Functions";
                }
                else
                {
                    procType = "Procedures";
                }

                string direction = reader.GetString(5);
                if (argName.Equals("(RETURN)"))
                {
                    direction = String.Empty;
                }
                string dataType           = reader.GetString(6);
                MetaProcedureArgument arg = new MetaProcedureArgument(proc.Owner, procName, procType,
                                                                      argName, direction, dataType);

                proc.Arguments.Add(arg);
            }
            reader.Close();
            reader = null;
        }
Esempio n. 2
0
 public MetaProcedureArgument this[string name]
 {
     get {
         MetaProcedureArgument p = null;
         foreach (object o in list)
         {
             p = (MetaProcedureArgument)o;
             if (p.ArgumentName.Equals(name))
             {
                 return(p);
             }
         }
         throw new Exception("MetaProcedureArgument not found");
     }
 }
Esempio n. 3
0
		// get arguments for stand-alone stored procedures/functions
		private void GetProcedureArguments (MetaProcedure proc) 
		{
			if (con.State != ConnectionState.Open)
				con.Open();

			// GET ARGUMENTS FOR STAND-ALONE PROCEDURES/FUNCTIONS
			string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
				"	POSITION, SEQUENCE, " +
				"	IN_OUT AS DIRECTION, " +
				"	DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE " +
				"FROM SYS.ALL_ARGUMENTS " +
				"WHERE OWNER = '" + proc.Owner + "' " +
				"AND OBJECT_NAME = '" + proc.Name + "' " +
				"AND PACKAGE_NAME IS NULL " + 
				"AND DATA_LEVEL = 0 AND DATA_TYPE IS NOT NULL " +
				"ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";

			IDbCommand cmd = con.CreateCommand ();
			cmd.CommandText = sql;
			IDataReader reader = cmd.ExecuteReader ();
			string procType = "Procedures";
			while (reader.Read ()) {
				string procName = reader.GetString (0);
				string argName = reader.GetString (2);
 
				if (argName.Equals ("(RETURN)"))
					procType = "Functions";
				else
					procType = "Procedures";

				string direction = reader.GetString (5);
				if (argName.Equals ("(RETURN)"))
					direction = String.Empty;
				string dataType = reader.GetString (6);
				MetaProcedureArgument arg = new MetaProcedureArgument(proc.Owner, procName, procType,
					argName, direction, dataType);

				proc.Arguments.Add (arg);
			}
			reader.Close ();
			reader = null;
		}
Esempio n. 4
0
		// get procedures/functions for a given stored package and get the
		// arguments for each procedure/function
		private void GetPackageProcedures (MetaProcedure pkg) 
		{
			if (con.State != ConnectionState.Open)
				con.Open();

			// GET ARGUMENTS FOR PROCEDURES/FUNCTIONS FOR PACKAGES
			string sql = 
				"SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
				"	POSITION, SEQUENCE, " +
				"	IN_OUT AS DIRECTION, " +
				"	DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE " +
				"FROM SYS.ALL_ARGUMENTS " +
				"WHERE OWNER = '" + pkg.Owner + "' " +
				"AND PACKAGE_NAME = '" + pkg.Name + "' " +
				"AND DATA_LEVEL = 0 " +
				"ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";

			IDbCommand cmd = con.CreateCommand ();
			cmd.CommandText = sql;

			IDataReader reader = cmd.ExecuteReader ();

			// Notes:
			// 1. an Oracle stored package can overloaded functions or procedures
			// 2. stand-alone functions or procedures can not be overloaded
			// 3. a procedure with no arguments will still have one row - data_type will be null
			string previousProcName = "~";
			string previousOverload = "~";
			MetaProcedure proc = null;
			string procType = "Procedures";
			while (reader.Read ()) {
				string procName = reader.GetString (0);
				string argName = reader.GetString (2);

				string overload = String.Empty;
				if (!reader.IsDBNull (1))
					overload = reader.GetString (1);

				string direction = String.Empty;
				if (!reader.IsDBNull (5))
					direction = reader.GetString (5);
				
				string dataType = String.Empty;
				if (!reader.IsDBNull (6)) 
					dataType = reader.GetString (6);

				if (!procName.Equals (previousProcName) || !previousOverload.Equals (overload)) {
					if (argName.Equals ("(RETURN)") && (!dataType.Equals (String.Empty))) {
						procType = "Functions";
						direction = String.Empty;
					}
					else
						procType = "Procedures";

					proc = new MetaProcedure (String.Empty, procName, procType);
					pkg.Procedures.Add (proc);
					
					previousProcName = procName;
					previousOverload = overload;
				}

				if (!dataType.Equals (String.Empty)) {
					MetaProcedureArgument arg = new MetaProcedureArgument (pkg.Owner, procName, procType,
						argName, direction, dataType);
					proc.Arguments.Add (arg);
				}
			}
			reader.Close ();
			reader = null;
		}
Esempio n. 5
0
        // get procedures/functions for a given stored package and get the
        // arguments for each procedure/function
        private void GetPackageProcedures(MetaProcedure pkg)
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }

            // GET ARGUMENTS FOR PROCEDURES/FUNCTIONS FOR PACKAGES
            string sql =
                "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
                "	POSITION, SEQUENCE, "+
                "	IN_OUT AS DIRECTION, "+
                "	DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE "+
                "FROM SYS.ALL_ARGUMENTS " +
                "WHERE OWNER = '" + pkg.Owner + "' " +
                "AND PACKAGE_NAME = '" + pkg.Name + "' " +
                "AND DATA_LEVEL = 0 " +
                "ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";

            IDbCommand cmd = con.CreateCommand();

            cmd.CommandText = sql;

            IDataReader reader = cmd.ExecuteReader();

            // Notes:
            // 1. an Oracle stored package can overloaded functions or procedures
            // 2. stand-alone functions or procedures can not be overloaded
            // 3. a procedure with no arguments will still have one row - data_type will be null
            string        previousProcName = "~";
            string        previousOverload = "~";
            MetaProcedure proc             = null;
            string        procType         = "Procedures";

            while (reader.Read())
            {
                string procName = reader.GetString(0);
                string argName  = reader.GetString(2);

                string overload = String.Empty;
                if (!reader.IsDBNull(1))
                {
                    overload = reader.GetString(1);
                }

                string direction = String.Empty;
                if (!reader.IsDBNull(5))
                {
                    direction = reader.GetString(5);
                }

                string dataType = String.Empty;
                if (!reader.IsDBNull(6))
                {
                    dataType = reader.GetString(6);
                }

                if (!procName.Equals(previousProcName) || !previousOverload.Equals(overload))
                {
                    if (argName.Equals("(RETURN)") && (!dataType.Equals(String.Empty)))
                    {
                        procType  = "Functions";
                        direction = String.Empty;
                    }
                    else
                    {
                        procType = "Procedures";
                    }

                    proc = new MetaProcedure(String.Empty, procName, procType);
                    pkg.Procedures.Add(proc);

                    previousProcName = procName;
                    previousOverload = overload;
                }

                if (!dataType.Equals(String.Empty))
                {
                    MetaProcedureArgument arg = new MetaProcedureArgument(pkg.Owner, procName, procType,
                                                                          argName, direction, dataType);
                    proc.Arguments.Add(arg);
                }
            }
            reader.Close();
            reader = null;
        }