Java – storedprocedurecall 1x varchar output 1x cursor output

My question may be a little confusing I have a problem. I'm calling storedprocedurecall in Java, for example:

StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("test");
    call.addNamedArgument("p_year");
    call.addNamedArgument("p_rel");
    call.useNamedCursorOutputAsResultSet("p_resset");

Resset is my result as cursor - as you can see - when the program looks like this, there is no problem:

create or replace PROCEDURE TEST (p_year IN NUMBER,p_rel IN VARCHAR2,p_resset OUT SYS_REFCURSOR) AS
    BEGIN
       OPEN p_resset FOR
           SELECT NVL (s.KA,'Summe') ka,COUNT (s.AZ) az
             FROM table1 s,table2 w
            WHERE s.year= w.year
              AND w.relevant = p_rel 
              AND s.year = p_year
END;

Now I've added an output parameter called "p_data" with my own select call

create or replace PROCEDURE TEST (p_year IN NUMBER,p_data OUT VARCHAR2,p_resset OUT SYS_REFCURSOR) AS
    BEGIN
       SELECT month
       INTO p_data
         FROM month_table b
        WHERE month_nr = (SELECT MAX (month)
                            FROM instruction
                           WHERE year= b.year)
          AND year= p_year;

OPEN p_resset FOR
           SELECT NVL (s.KA,table2 w
            WHERE s.year= w.year
              AND w.relevant = p_rel 
              AND s.year = p_year
END;

This is where I got stuck

I don't know how to call a single string, which is the result of the new select statement (month) I tried to add

"call.addNamedOutputArgument("p_data");"

But that's completely wrong

Maybe I'm also glad to know how I handle the results from the first call:

DataReadQuery query = new DataReadQuery(); 
                query.setCall(call);
                query.addArgument("p_year");
                query.addArgument("p_rel");


                @SuppressWarnings("rawtypes")
                List args = new ArrayList();
                args.add(dbyear);
                args.add(relevation);

                @SuppressWarnings("rawtypes")
                List result=  (List) s.executeQuery(query,args);

    for (int i = 0; i < ergebnis.size(); i++){
    testDto record = new testDto();
    ArrayRecord ar=(ArrayRecord) ergebnis.get(i);

    record.setKa((ar.get("ka")).toString());
    record.setAz((ar.get("az")).toString()); 

    System.out.println("cursor : " + ergebnis.get(i));  

    result.add(ergebnis);
}

But as I said, I can't handle a single string and create an HTML / Excel file with it as a parameter, which is what I have to deal with

Solution

I think you're close. You need to get values from the result set

Try something similar

StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("test");
    call.addNamedArgument("p_year");
    call.addNamedArgument("p_rel");
    call.addNamedOutputArgument("p_data");
    call.useNamedCursorOutputAsResultSet("p_resset");


    @SuppressWarnings("rawtypes")
    List result=  (List) s.executeQuery(query,args);
    DatabaseRecord record = (DatabaseRecord)results.get(0);
    String data = (String) record.get("p_data");

Look at these clips

http://www.ctmmc.net/how-to-call-stored-procedures

http://blog.yenlo.com/nl/calling-oracle-stored-procedures-from-eclipselink-with-multiple-out-parameters