oracle Java：从过程中读取元数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3854192/
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
Java: Read metadata from procedure
提问by Adnan
I am looking for a way to get metadata from an Oracle store procedure, such as input/output parameters and their types.
我正在寻找一种从 Oracle 存储过程获取元数据的方法，例如输入/输出参数及其类型。
I did try DESCbut it is not working:
我确实尝试过，DESC但它不起作用：
stmt = conn.createStatement();
ResultSet rs1 = stmt.executeQuery("desc pack.procname");
while ( rs1.next() ) {
System.out.println(rs1.getString(1));
}
Any ideas on what approach to use to get the input/output parameters?
关于使用什么方法来获取输入/输出参数的任何想法？
Thanx for your time.
谢谢你的时间。
回答by mithlesh
Use the below code block to fix the issue.
使用下面的代码块来解决这个问题。
DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet rs = dbMetaData.getProcedureColumns(conn.getCatalog(),
null,
"procedureNamePattern",
"columnNamePattern");
while(rs.next()) {
// get stored procedure metadata
String procedureCatalog = rs.getString(1);
String procedureSchema = rs.getString(2);
String procedureName = rs.getString(3);
String columnName = rs.getString(4);
short columnReturn = rs.getShort(5);
int columnDataType = rs.getInt(6);
String columnReturnTypeName = rs.getString(7);
int columnPrecision = rs.getInt(8);
int columnByteLength = rs.getInt(9);
short columnScale = rs.getShort(10);
short columnRadix = rs.getShort(11);
short columnNullable = rs.getShort(12);
String columnRemarks = rs.getString(13);
System.out.println("stored Procedure name="+procedureName);
System.out.println("procedureCatalog=" + procedureCatalog);
System.out.println("procedureSchema=" + procedureSchema);
System.out.println("procedureName=" + procedureName);
System.out.println("columnName=" + columnName);
System.out.println("columnReturn=" + columnReturn);
System.out.println("columnDataType=" + columnDataType);
System.out.println("columnReturnTypeName=" + columnReturnTypeName);
System.out.println("columnPrecision=" + columnPrecision);
System.out.println("columnByteLength=" + columnByteLength);
System.out.println("columnScale=" + columnScale);
System.out.println("columnRadix=" + columnRadix);
System.out.println("columnNullable=" + columnNullable);
System.out.println("columnRemarks=" + columnRemarks);
}
回答by Justin Cave
Since you are using JDBC, my preference would be to use the JDBC metadata API to retrieve this information rather than querying the Oracle data dictionary directly. DatabaseMetaData.getProcedureColumnsis the generic JDBC method to get the parameters for a procedure.
由于您使用的是 JDBC，因此我更喜欢使用 JDBC 元数据 API 来检索此信息，而不是直接查询 Oracle 数据字典。 DatabaseMetaData.getProcedureColumns是用于获取过程参数的通用 JDBC 方法。
回答by Codo
Try the following statement:
试试下面的语句：
select *
from user_arguments
where package_name = 'PACK' and object_name = 'PROCNAME';
Depending on the schema the package belongs to, you might need to use the view _ALL_ARGUMENTS_ or _DBA_ARGUMENTS_ instead.
根据包所属的架构，您可能需要改用视图 _ALL_ARGUMENTS_ 或 _DBA_ARGUMENTS_。
回答by Sualeh Fatehi
If you want to avoid using JDBC directly, you can use my open source SchemaCrawlerAPI, which creates plain old Java objects from database metadata.
如果你想避免直接使用 JDBC，你可以使用我的开源SchemaCrawlerAPI，它从数据库元数据创建普通的旧 Java 对象。
