Java : Retrieve Column names from ResultSet

Question

我想从调用存储过程返回的 ResultSet 中获取列名称。

cstmt = c.prepareCall("CALL MyProcedure(?,?,?)");
ArrayDescriptor descriptionArrayString = ArrayDescriptor.createDescriptor("TYPE_TABLEAU_CHAINES", c);
ARRAY arrayNumfics = new ARRAY(descriptionArrayString, c, numfics.toArray());
cstmt.setArray(1, arrayNumfics);
cstmt.setDouble(2, offreId);
cstmt.registerOutParameter(3, java.sql.Types.ARRAY, "TYPE_TABLEAU_OBJ");         

cstmt.executeUpdate();

java.sql.Array arrayObjects = cstmt.getArray(3);
ResultSet rs = arrayObjects .getResultSet();
int i = 0;

while (rs.next()) {
    STRUCT structure = ((STRUCT) rs.getObject(2));

    if(i == 0) {
        StructDescriptor descriptor = structure.getDescriptor();
        ResultSetMetaData metadata  = descriptor.getMetaData();
        int numAttrs = descriptor.getLength(); 

        System.out.println("descriptor length = " + numAttrs);
        System.out.println("Number of columns = " + metadata.getColumnCount());

        for (int j = 1; j <= metadata.getColumnCount(); j++) {
            String name = metadata.getColumnName(j);

            System.out.println("Column Name " + j + " = " + name);
        }
    }

    Object elements[] = structure.getAttributes();

    System.out.println("obj[0] = " + elements[0]);
    System.out.println("obj[1] = " + elements[1]);
    System.out.println("obj[2] = " + elements[2]);

    i++;
}

这是显示的内容:

descriptor length = 15
Number of columns = 15

但是当我使用 j = 1 调用metadata.getColumnName(j) 时，出现此错误:

java.sql.SQLException: ORA-01403 No Data Found

<小时/>

存储过程

PROCEDURE myprocedure (param1 IN TYPE_TABLEAU_CHAINES, param2 IN NUMBER,
param3 OUT TYPE_TABLEAU_OBJ) AS

TYPE myRecord IS RECORD (
name1 VARCHAR2(11),
name2 VARCHAR2(6),
name3 VARCHAR2(11),
name4 VARCHAR2(11),
name5 VARCHAR2(11),
name6 DATE,
name7 VARCHAR2(13),
name8 VARCHAR2(2),
name9 VARCHAR2(4),
name10 VARCHAR2(32),
name11 VARCHAR2(32),
name12 VARCHAR2(15),
name13 VARCHAR2(38),
name14 VARCHAR2(4),
name15 VARCHAR2(3)
);

TYPE typeCursor IS REF CURSOR;

mysigREC myRecord ;
mysigOBJ TYPE_MYOBJECT;
i NUMBER := 1;
queryString VARCHAR2(10000) := '';
myCursor typeCursor;

BEGIN
    mysigOBJ := TYPE_TABLEAU_SIGNALEMENTS();
    queryString := 'SELECT name1, name2, name3, name4, name5, name6, name7,
    name8, name9, name10, name11, name12, name13, name14, name15 FROM
    TSIGGRC WHERE name22 = ''VALUE''';

    OPEN myCursor FOR queryString;
    LOOP
        FETCH myCursor INTO mysigREC ;
        EXIT WHEN myCursor%NOTFOUND;
        mysigOBJ := new TYPE_MYOBJECT();
        mysigOBJ .set_name1(mysigREC .name1);
        mysigOBJ .set_name2(mysigREC .name2);
        mysigOBJ .set_name3(mysigREC .name3);
        mysigOBJ .set_name4(mysigREC .name4);
        mysigOBJ .set_name5(mysigREC .name5);
        mysigOBJ .set_name6(mysigREC .name6);
        mysigOBJ .set_name7(mysigREC .name7);
        mysigOBJ .set_name8(mysigREC .name8);
        mysigOBJ .set_name9(mysigREC .name9);
        mysigOBJ .set_name10(mysigREC .name10);
        mysigOBJ .set_name11(mysigREC .name11);
        mysigOBJ .set_name12(mysigREC .name12);
        mysigOBJ .set_name13(mysigREC .name13);
        mysigOBJ .set_name14(mysigREC .name14);
        mysigOBJ .set_name15(mysigREC .name15);

        param3 .EXTEND();
        param3 (i) := mysigOBJ ;
        i := i + 1;
    END LOOP;

END myProcedure;

Answer 1

好的，我找到了。

我之前更改了 PLSQL 类型 TYPE_MYOBJECT，并且没有再次向应用程序的用户授予授权。这就是为什么它显示“未找到数据”的原因。

只是做:

grant execute on TYPE_MYOBJ to USER_OF_APPLICATION;

解决了问题。