Delivering a large String Array as a VarChar2 into SQL

Question

Our application makes heavy use of SQL functions. One such function utilizes a large String variable of this sort:

public static boolean callSQL(Connection conn, String[] s)
    throws DatabaseException
{
    boolean result = false;
    String query = "begin ? := "+qualProc("callSQL")+"(?); end;";
cs.registerOutParameter(1, Types.INTEGER);
**cs.setString(2, GeneralUtils.join(s,","));**
**cs.execute();**
        if(cs.getInt(1) > 0)
            result = true;
    }catch(SQLException se){
        se.printStackTrace(); 
        throw new DatabaseException(se.getMessage());
    }finally{
        if(cs != null)
            try{ cs.close(); }catch(SQLException e){}
    }

    return result;
}

Note: qualProc is the name of our database. For security reasons I'm not using the real name here.

The application itself runs smoothly, but when it attempts to execute the SQL command, we get the following error:

ORA-06532: Subscript outside of limit ORA-06512: at "XXXX.XX", line XXX ORA-06512: at line X

In the package function itself, it uses the String like this:

  FUNCTION callSQL(s_tring IN VARCHAR2)
RETURN INTEGER IS

That string can (and has been) longer than the size limit for VarChar2 (32768 Bytes according to this answer https://stackoverflow.com/a/186436/2188082 ).

One option is to separate our Array into manageable chunks and run the SQL Function until it has processed the entire string, but ideally we would like to resolve this with a SQL package change - is there any way to make SQL accept a String that is too long for VarChar2?

possible duplicate of [What is the size limit for a varchar2 PL/SQL subprogram argument in Oracle?](http://stackoverflow.com/questions/186403/what-is-the-size-limit-for-a-varchar2-pl-sql-subprogram-argument-in-oracle) — Caleryn, Apr 24 '15 at 16:06
@Caleryn I'm more looking for a solution to this specific problem. I've edited the question to clarify that. — Zibbobz, Apr 24 '15 at 16:26

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

I recommend you review the response to 5198856, for sending array of String to PL / SQL.

On the other hand, you could modify the PL/SQL procedure that the parameter is a CLOB, not a VARCHAR2 whose limited to 32767 characters imposes a limitation that the CLOB type does not.

edited May 23 '17 at 11:51

Community

1
1

answered Apr 24 '15 at 18:14

AmbarJ2009

64
4

Looking at the answers linked, it doesn't appear to resolve the issue if the resulting VarChar2 is too large for the type to handle - but this is still a somewhat useful answer in suggesting a CLOB. Though I'd like to see if there are any answers that could preserve the use of VarChar2. – Zibbobz Apr 24 '15 at 18:21

Delivering a large String Array as a VarChar2 into SQL

1 Answers1