使用 ADO.NET 获取 Oracle 包内过程的存储过程元数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2857753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Obtain stored procedure metadata for a procedure within an Oracle package using ADO.NET
提问by alwayslearning
I am trying to obtain the stored procedure metadata (procedure name,parameter types,parameter names etc) for a procedure declared within an Oracle package, using the standard ADO.NET API - DbConnection.GetSchema call. I am using the ODP driver.
我正在尝试使用标准 ADO.NET API - DbConnection.GetSchema 调用获取在 Oracle 包中声明的过程的存储过程元数据（过程名称、参数类型、参数名称等）。我正在使用 ODP 驱动程序。
I see that the Package is listed in the 'Packages' and 'PackageBodies' metadata collections. The procedure parameter appears in the 'Arguments' and 'ProcedureParameters' collections. I do not see a way to get to the procedure information via the package metadata. Even if the procedure does not have any parameters there is a row in the 'ProcedureParameters' collection for this procedure.
我看到 Package 列在“Packages”和“PackageBodies”元数据集合中。过程参数出现在“Arguments”和“ProcedureParameters”集合中。我看不到通过包元数据获取过程信息的方法。即使该过程没有任何参数，该过程的“ProcedureParameters”集合中也有一行。
My question: To obtain the procedure metadata do I have to query the 'ProcedureParameters' collection and search for an entry with the required package name? I can then construct the procedure metadata based on the parameter information. Is there a shorter or quicker way to obtain the same information?
我的问题：要获取过程元数据，我是否必须查询“ProcedureParameters”集合并搜索具有所需包名称的条目？然后我可以根据参数信息构建过程元数据。是否有更短或更快捷的方法来获取相同的信息？
采纳答案by alwayslearning
With help from Bob I've used the following query to obtain a list of stored procedures defined within a package.
在 Bob 的帮助下，我使用以下查询来获取包中定义的存储过程列表。
SELECT a.OBJECT_NAME,p.PROCEDURE_NAME FROM SYS.ALL_OBJECTS a, SYS.ALL_PROCEDURES p WHERE a.OBJECT_NAME = p.OBJECT_NAME AND a.OBJECT_TYPE = 'PACKAGE' AND a.OWNER = '" + ownerName + "' AND p.PROCEDURE_NAME IS NOT NULL"
This returns all stored procedures for a particular user. I can then use the 'ProcedureParameters' collection to obtain the parameter information for them.
这将返回特定用户的所有存储过程。然后我可以使用“ProcedureParameters”集合来获取它们的参数信息。
NOTE: Do not query the SYS.DBA_PROCEDURES table. The user credentials you use to execute the query might not have 'select' privileges on that table.
注意：不要查询 SYS.DBA_PROCEDURES 表。您用于执行查询的用户凭据可能没有对该表的“选择”权限。
回答by Bob Jarvis - Reinstate Monica
I'm not sure how you'd get this using ADO.NET, but you can directly query the database to get this information as follows:
我不确定如何使用 ADO.NET 获取此信息，但您可以直接查询数据库以获取此信息，如下所示：
SELECT *
FROM SYS.DBA_PROCEDURES
WHERE OBJECT_TYPE = 'PACKAGE' AND
OBJECT_NAME = '<your package name here>' AND
PROCEDURE_NAME IS NOT NULL;
Once you've run the above query you'll have a result set which has, among other things, the PROCEDURE_NAME. Given the package name and the PROCEDURE_NAME, you can find parameter info using the following query:
运行上述查询后，您将获得一个结果集，其中包括 PROCEDURE_NAME。给定包名称和 PROCEDURE_NAME，您可以使用以下查询找到参数信息：
SELECT *
FROM SYS.ALL_ARGUMENTS
WHERE PACKAGE_NAME = '<your package name here>' AND
OBJECT_NAME = '<PROCEDURE_NAME from query above>';
Share and enjoy.
分享和享受。
