trouble passing array to stored procedure in spring HELP

[Darvesh Niz]

I dont know why the hell this is so complicated, i am trying to pass a simple array of Strings to an oracle stored procedure from Spring in my package here it is

In my package decleration

  TYPE t_att_name IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;  

i have the method declared

  procedure insert_att_arr(
    p_Att_Name in t_att_name,
    rsuId in number,
    forecast out number
);  

then in my stored procedure i have

     
    procedure insert_att_arr(
        p_Att_Name in t_att_name,
        rsuId in number,
        forecast out number
    ) is
    begin

    insert into emp values (1,'Rashid');
    forecast := 5556;
    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error ' || TO_CHAR(SQLCODE)|| ': '||SQLERRM);
        RAISE;
    end;   

i purposely did not use the array variable, i want to make it sure i want the stored proc atleast without any error.

Now coming to the java side, there are so many people who are having issues with this how to pass some say use array descriptor some say do this, this is what i was trying

  
public class SimpleSPArray {

   public static void main(String[] args) throws Exception
    {
        new SimpleSPArray().execute();
    }

    public void execute() throws Exception
    {
        DataSource ds = new DriverManagerDataSource(
            "oracle.jdbc.driver.OracleDriver",
            "jdbc:oracle:thin:@XXXX",
            "XXX", "XXX");
        //Object object = returnStringArray(ds);
        SimpleSPArray.DemoStoredProcedure proc = new SimpleSPArray.DemoStoredProcedure(ds);
        Map params = new HashMap();
        params.put("p_Att_Name",new ObtainStringArrayFromJava().returnStringArray(ds) );
        params.put("rsuid",new Integer(701));
        Map results = proc.execute(params);
        printMap(results);
    }

    private class DemoStoredProcedure extends StoredProcedure
    {
        public static final String SQL = "evdb.dct_2_2.insert_att";

        public DemoStoredProcedure(DataSource ds)
        {
            setDataSource(ds);
            setSql(SQL);
            //setFunction(true);
            declareParameter(new SqlParameter("p_Att_Name",   oracle.jdbc.OracleTypes.ARRAY));
            declareParameter(new SqlParameter("rsuid",Types.INTEGER));
            declareParameter(new SqlOutParameter("forecast", Types.INTEGER));
            compile();
        }
    }

    private static void printMap(Map results) {
        for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) {
            System.out.println(it.next());
        }
        String test = (String)results.get("somereturn");
    }

    public class ObtainStringArrayFromJava  {

        public oracle.sql.ARRAY[]  returnStringArray(DataSource ds) throws
        java.sql.SQLException, IOException {
            oracle.sql.ARRAY[] vReturnArray = null;
        int arrayLength = 10;
        String[] vTestArray = new String[arrayLength];

        for (int i=0; i<arrayLength; i++) {
        vTestArray[i] = "Java Entry " + (i+1);
        }

        Connection conn = ds.getConnection();
        ArrayDescriptor desc =
        ArrayDescriptor.createDescriptor("evdb.dct_2_2.t_att_name",conn);
        vReturnArray[0] = new ARRAY(desc,conn,vTestArray);
            return vReturnArray;
        }
    }

  

i get an exception saying java.sql.SQLException: invalid name pattern: evdb.dct_2_2.t_att_name

Can anyone please tell me whats the easiest way to pass this array.

Thanks
Rashid

>

[Mark Spritzler]

Personally, I don't use Stored Procedures anymore, especially for these types of reasons. But soon Hibernate in version 4 will have full support for stored procedures like these.

Anyway, the only thing that I think I notice is that in the execute method you are setting the parameter's value to an array of Oracle ARRAYs, I would think you would just set it to a single instance of Oracle's ARRAY object.

And it goes to show how Oracle just decides to do things their own way, rather than follow standards. I mean a class named "ARRAY" all caps. That totally goes against the standard naming of classes.

Anyway, maybe you want your returnStringArray method to just return a single oracle.sql.ARRAY class, instead of ARRAY[]

Mark

[Darvesh Niz]

FYI
The error i am getting is at the line where i do
ArrayDescriptor.createDescriptor("evdb.dct_2_2.t_att_name",conn);

i tried ddifferent things like removing the schema name adding the schema name but still the same issue cant help it.


Exception in thread "main" java.sql.SQLException: invalid name pattern: EVDB.t_att_name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:412)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:327)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1300)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:156)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:113)
at com.edmunds.evdb.SimpleSPArray$ObtainStringArrayFromJava.returnStringArray(SimpleSPArray.java:112)
at com.edmunds.evdb.SimpleSPArray.execute(SimpleSPArray.java:47)
at com.edmunds.evdb.SimpleSPArray.main(SimpleSPArray.java:34)

[Joe ONeil]

I got it to work by hacking I just passed the datasource into the execute. I think spring closes the connection and by passing the data source the connection stays open it is really strange

This Spring stored procedure passes in an array of tab delimited strings the procedure validates the strings if there are errors in validation an error is added to the error array and the array is passed back



public class JdbcUpLoadPOLinesDAOImpl extends StoredProcedure implements JdbcUploadPOLinesDAO {

private static final String UPLOAD_PO_LINES = "necxadm.po_upload_lines";
private static final String p_po_line_array= "p_po_line_array";

public JdbcUpLoadPOLinesDAOImpl(DataSource dataSource) {
super(dataSource, UPLOAD_PO_LINES);
final String[] errorList=new String[50];
declareParameter(new SqlParameter("poid", Types.BIGINT));
declareParameter(new SqlParameter("insert_user",Types.BIGINT));
declareParameter(new SqlParameter("version",Types.VARCHAR));
declareParameter(new SqlParameter("PO_LINES", Types.ARRAY,"STRING_ARRAY"));
declareParameter(new SqlOutParameter("results",Types.VARCHAR));
declareParameter(new SqlOutParameter("ERRORS", Types.ARRAY, "STRING_ARRAY", new SqlReturnType() {
public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
Connection con = cs.getConnection();
Map typeMap = con.getTypeMap();
typeMap.put(typeName, errorList);
Object o = cs.getObject(paramIndex);
return o;
}
}));
compile();
}

public Map upLoadLines(DriverManagerDataSource ds ,Long poid,Long loginUser,String version,String[] poLines) {
Map inParameters = new HashMap();
final String[] temp = poLines;
inParameters.put( "poid", poid );
inParameters.put( "insert_user", loginUser );
inParameters.put( "version", version );
inParameters.put("PO_LINES", new AbstractSqlTypeValue() {

protected Object createTypeValue(Connection con, int type, String typeName)
throws SQLException {
if( con.isClosed()){
System.out.println("connection is closed");
}else{
System.out.println("connection is open");
}
ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
return new ARRAY(desc, con, temp);
}
});

Map out = execute( inParameters ); // Call on parent class


System.out.println("results="+out.get("results"));
ARRAY simpleArray=(ARRAY )out.get("ERRORS");
Object[] obj;
try {
obj = (Object[]) simpleArray.getArray(out);
System.out.println("obj[0]"+obj[0]);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


return out;
}

This is the the outpput and it is what I expected
[STDOUT] obj[0]Header: Invalid Label for 'Part Number': xPart Number