JDBC getmetadata of an oracle procedure inside pac

问题:

In the oracle database there is a schema. Inside schema there is a package which contains different methods. How to retrieve the metadata of the procedure using getProcedureColumn() function in DatabaseMetaDataclass?

I have tried to get metadata using getProcedureColumns(catalog,schemaname,procedurename,columnnamepattern) it works fine when the procedure is located inside a schema. When a procedure is located inside a package in a schema it is not retrieving.

回答1:

This will print out all the column information for a specific procedure in a package. Change parameters with real values.

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            Connection conn =
              DriverManager.getConnection ("jdbc:oracle:thin:@<server>:<port>:<sid>", "<username>", "<password>");

            DatabaseMetaData metadata = conn.getMetaData();

            String packageName = "<your package name>";
            String schemaName = "<schema name>";
            String procedureName = "<procedure name>";

            ResultSet rs = metadata.getProcedureColumns(
                        packageName,
                        schemaName,
                        procedureName,
                    "%");

                while(rs.next()) {
                // get stored procedure metadata
                String procedureCatalog     = rs.getString(1);
                String procedureSchema      = rs.getString(2);
                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);
                }