Announcement Announcement Module
Collapse
No announcement yet.
Problem with Arrays in StoredProcedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Andreas Senft
    started a topic Problem with Arrays in StoredProcedure

    Problem with Arrays in StoredProcedure

    Again I am experimenting with stored procedures (Oracle 9i).

    Based on informations from other threads, I created a simple StoredProcedure. However, handling of an array parameter seems to be faulty.

    The stored proc:
    Code:
        PROCEDURE test(o_table OUT MSG_TABLE, i_val IN NUMBER)
        IS
        BEGIN
            o_table := MSG_TABLE();
            
            FOR i IN 1..i_val LOOP
                o_table.EXTEND;
                o_table(o_table.LAST) := 'Test' || i;
            END LOOP;
        END;
    The array type
    Code:
        TYPE msg_table IS TABLE OF VARCHAR2(1024);
    The StoredProcedure subclass
    Code:
        class TestProc extends StoredProcedure {
            
            private static final String SQL = "test";
    
            public TestProc(JdbcTemplate tpl) {
              setJdbcTemplate(tpl);
              init();
            }
    
            public TestProc(DataSource ds) {
                setDataSource(ds);
                init();
            }
    
            private void init() {
                setFunction(false);
                setSql(SQL);
    
                declareParameter(new SqlOutParameter("o_table", Types.ARRAY, "MSG_TABLE"));
                declareParameter(new SqlParameter("i_val", Types.INTEGER));
                compile();
            }
            
            public String[] execute() {
                Map<String, Object> params = new HashMap<String,Object>();
                params.put("i_val", new Integer(5));
                Map result = execute(params);
                
                Array array = (Array) result.get("o_table");
                String[] retArray = null;
                try {
                  retArray = (String[]) array.getArray();
                } catch (SQLException e) {
                  throw new TypeMismatchDataAccessException("Failed to get array", e);
                }
                
                return retArray;
            }
        }
    The invocation works and retrieves an array of the correct size. However, all entries are "???" strings.

    Is this a bug in my JDBC driver, or did I something wrong?

    Thanks for hints,
    Andreas

    [edit]
    When invoked from a database client, the stored procedure works as expected. So there should be nothing wrong with that.
    Last edited by Andreas Senft; Jul 6th, 2006, 12:53 AM.

  • Andreas Senft
    replied
    Solved, but...

    ...still strange.

    It works, when I change the definition of the collection type MSG_TABLE to
    Code:
    TYPE msg_table IS TABLE OF NVARCHAR2(1024)
    In this case that is not a problem as I can use NVARCHAR2 here. But if anyone has an idea how to enable usage of "normal" VARCHAR2 in such a scenario I would be glad to hear it!
    Using VARCHAR2 as output parameter (not inside an ARRAY) does work fine.

    Andreas

    Leave a comment:


  • Andreas Senft
    replied
    Update: I also tried a different jdbc driver with no avail. I also managed to invoke the stored procedure successfully from a jdbc-based database client.

    Could there be an issue with language/characterset settings of the database? If yes, what could be done in that case?
    My client is WinXP, the database is on a W2k machine.

    Any ideas?
    Andreas
    Last edited by Andreas Senft; Jul 6th, 2006, 01:54 AM.

    Leave a comment:

Working...
X