Couldn't create String Oracle ARRAY of varchar

excuse me for my bad english (stupid french people!! :p )

I create 2 types with:

create or replace TYPE LISTE_ENQUETE AS TABLE OF VARCHAR2(500);

and

create or replace TYPE LISTE_IDE_ENQUETE AS TABLE OF VARCHAR2(500);

and I use them in my stored procedure PS_MAJ_QUALITE2.sql:

create or replace PROCEDURE PS_MAJ_QUALITE2 (
                   w_LISTE IN LISTE_ENQUETE,
                   w_PART IN VARCHAR2,
                   w_TYPE IN VARCHAR2,
                   w_ID_ENQ IN VARCHAR2,
                   w_CATEGORIE IN VARCHAR2,
                   w_LISTE_IDE IN LISTE_IDE_ENQUETE
                   )
IS.......

My Java code is:

private void appelerPS_MAJ_QUALITE2(String partieEnquete, Connection c, String[]tabEntete, String type, String categorie, String ideEnquete, String[]tabIde) throws TechnicalException {
    String sReq = "{call ps_MAJ_QUALITE2(?,?,?,?,?,?)}";
    CallableStatement cstmt = null;

    try {

        java.sql.Array sqlArray = null;
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("LISTE_ENQUETE", c);
        sqlArray = new oracle.sql.ARRAY(arrayDescriptor, c, tabEntete);

        java.sql.Array sqlArrayIde = null;
        ArrayDescriptor arrayDescriptor2 = ArrayDescriptor.createDescriptor("LISTE_IDE_ENQUETE", c);
        sqlArrayIde = new oracle.sql.ARRAY(arrayDescriptor2, c, tabIde);

        // Préparation de l'appel à la procédure stockée
        cstmt = c.prepareCall(sReq);
        cstmt.clearParameters();
        // alimentation du code traitement :
        cstmt.setArray(1, sqlArray);
        cstmt.setString(2, partieEnquete);
        cstmt.setString(3, type);
        cstmt.setString(4, ideEnquete);
        cstmt.setString(5, categorie);
        cstmt.setArray(6, sqlArrayIde);

        cstmt.execute();

    } catch (SQLException e) {...}
.......
}

My String[] tabEntete and String[]tabIde tabs have the good informations but when I'm looking sqlArray and sqlArrayIde contents, I have: ["???", "???", "???"].

and when I try to insert sqlArray's data or sqlArrayIde'data I have the error:

Error executing the stored procedure PS_MAJ_QUALITE, exception message :  ORA-01400: cannot insert NULL into ("WFGA_PM_ASS1"."REPONSE_FORMULAIRE_QUALITE"."IDE_REPONSE")

Could you please help me to solve this problem. Thank you very much for your help.

Yohann

回答1:

(answering with Yo hann's own solution, as it helped me too)

Use NVARCHAR2 for your new type instead of VARCHAR2.

回答2:

As confirmed by the OP, the problem was use of NVARCHAR2 instead of VARCHAR2.