StoredProcedureCall 1x Varchar output 1x Cursor output

Question

My question is maybe a little bit confusing. I have the problem that I'm calling an procedure with StoredProcedureCall from Java e.g.:

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

resset is my result as a Cursor - as you can see - this works without any problems while the procedure looks like:

 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 a output parameter named "p_data" with an own select call

create or replace PROCEDURE TEST (p_year IN NUMBER, 
                p_rel IN VARCHAR2,
                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, '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;

And that's where I got stuck.

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

"call.addNamedOutputArgument("p_data");"

but that was totally wrong.

maybe also good to know is how I handle the result I get back 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'm not able to handle the single string to get it as a parameter to create a html/excel file after and that's the problem I have to handle.

Don't know Java so not posting an answer, some thoughts though: Your 'Select Into' query has put the 'month' field into a new table called p_data; 'Select Into' will not create a resultset/return and data, only perform this action. To retrieve the data from P_data you will need a separate select statement (select month from p_data.) You have also said that P_Data is a varchar2 datatype, when it is actually a table.. so I don't think that's right. — JeffUK, Dec 01 '15 at 11:27
[this](http://dreamand.me/java/java-jee7-jpa-stored-procedure-example/) can help — George Vassilev, Dec 24 '15 at 09:03

score 0 · Answer 1 · answered Jan 20 '16 at 14:47

I think you are close. you need to get the value from the resultset

try something like

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");

check out these snippets

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

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

score 0 · Answer 2 · answered Jun 27 '17 at 05:20

Your 'Select Into' query has insert 'month' field into a new table p_data. 'Select Into' will not return data. You need to write separate select query to fetch data from table p_data: Once you are done writing like this in stored procedure. Try calling the procedure in the way the above person told.

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");

Hope this will work for you

StoredProcedureCall 1x Varchar output 1x Cursor output

2 Answers2