The use of oracle custom objects

紫云流星

A type of list nsrlist is defined in oracle:
create or replace type nsrlist is varray (2000) of varchar2 (20)

The stored procedure body is as follows:
create or replace package body TESTVARRAY is
procedure testSS (nsrs out nsrlist) is
   nsrarray nsrlist: = nsrlist ('a1', 'a2', 'a3');
   begin
      nsrs: = nsrarray;
   end;
begin
   null;
end TESTVARRAY;

This is called in the java code:
Connection conn = session.connection ();
CallableStatement proc = null;
try
{
   proc = conn.prepareCall ({call TESTVARRAY.testSS (?)});
   proc.registerOutParameter (1, OracleTypes.ARRAY, "NSRLIST");
   proc.execute ();
   ResultSet rs = null;
   Array simpleArray = proc.getArray (1);
   rs = simpleArray.getResultSet ();
   while (rs.next ())
   {
      System.out.println (rs.getString (2));
   }
} catch (SQLException e)
{
   e.printStackTrace ();
}

The output of the console becomes
0x6131
0x6132
0x6133

What is going on?

whbilly

procedure testSS (nsrs out nsrlist) is
Just the out parameter, what about the in parameter?

How is your java code in?

proc = conn.prepareCall ({call TESTVARRAY.testSS (?)});

紫云流星

It can take no parameters. After the call, the returned result should be:
a1
a2
a3
But now the result becomes:
0x6131
0x6132
0x6133

紫云流星

The problem has not been solved yet. At present, the temporary table is used to solve this problem, that is, insert the query result into the temporary table, then query the temporary table, and return a cursor, so that the terminal will not be garbled. The only problem is performance. .