accessing a PL/SLQ cursor from within a Java program

yes that exactly what I'm thinking about, I'll just take a look at the link and tehn return.

By they way, is that not the best way to do it?

cool so it's just return as a resultset, you just have to remember to set teh fetch size.
otherwise is just like executing normal queries?

>>is that not the best way to do it?

I can't think of a better way off the top of my head.

>>otherwise is just like executing normal queries

I guess so.  I'm not a java person.

thanks, neither can I  :-)

By the way, is there a simple way to do exception handling within PL/SQL when working with REF CURSER?

>> is there a simple way to do exception handling within PL/SQL

In what way?  You can handle the exceptions in the stored proc and possible raise your own custom exception but I'm not sure what you are looking for.  If you can explaint it a little better, I'll try to help.

Thanks
I'm relatively new to to REF CURSOR (well pl/sql in generel), but I was wondering what to return when using REF CURSOR. As an example this wouldn't work, since I need to return a REF cusor and not NULL:

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
           RETURN 'NO DATA FOUND';
   WHEN TOO_MANY_ROWS
   THEN
          RETURN NULL;
   WHEN OTHERS
   THEN
          RETURN NULL;

I have tested the java code with my REF CURSOR function (without exception handling within PL/SQL). The exception handling is created within java and it works fine, but I would also like to be able to do it in PL/SQL.

When I call the PL/SQL function with an invalid value I receive "ORA-01722: invalid number", just as i'm suppose to. But I think it would be best if I was able to do the exception handling from within PL/SQL since it would increase reusability.

Since the code stub you provided has a RETURN, I'm assuming a function and not a procedure.

You should be able to return null.  I'm not sure why this isn't working.  If you can provide a little more detail I'll attempt to assist you.

Since this is getting a little more complicated, I'll unaccept the answer to this question and let other experts assist.

CREATE OR REPLACE PACKAGE MHCI_poTypes
IS
      TYPE ref_cursor IS REF CURSOR;
      FUNCTION MHCI_PO_LINES(
        paramHEADER_ID VARCHAR, paramPROJECT_ID VARCHAR, paramTASK_ID VARCHAR) RETURN ref_cursor;
      FUNCTION MHCI_PO_HEADERS(
            paramHEADER_ID VARCHAR, paramPO_LINE_ID VARCHAR) RETURN ref_cursor;      
END MHCI_poTypes;

CREATE OR REPLACE package body MHCI_poTypes
IS
      FUNCTION MHCI_PO_LINES(
            paramHEADER_ID VARCHAR, paramPROJECT_ID VARCHAR, paramTASK_ID VARCHAR) RETURN ref_cursor
      IS
            csr_poLines_ref ref_cursor;
      BEGIN
            OPEN csr_poLines_ref FOR
                  SELECT
                     PAT.TASK_NUMBER TASK
                     ,POL.LINE_NUM DOC
                     ,'Missing' CP_Agr
                     ,'Missing' STATUS
                     ,POL.CREATION_DATE
                     ,(POLA.UNIT_PRICE * POLA.QUANTITY) REVISION_AMT
                     ,((POLL.QUANTITY - POLL.QUANTITY_CANCELLED) * POL.UNIT_PRICE) TOTAL_AMT
                     ,(POLL.QUANTITY_RECEIVED * POL.UNIT_PRICE) RECIEVED_AMT
                     ,(POLL.QUANTITY_BILLED * POL.UNIT_PRICE) INVOICE_AMOUNT
                     ,(POLL.QUANTITY_CANCELLED * POL.UNIT_PRICE) CANCELLED_AMT
                  FROM
                     PA_TASKS PAT
                     ,PO_LINES_ALL POL
                     ,PO_LINE_LOCATIONS_ALL POLL
                     ,po_lines_archive_ALL POLA
                  WHERE
                     POLA.PO_HEADER_ID = POL.PO_HEADER_ID
                     AND POLA.PO_LINE_ID = POL.PO_LINE_ID
                     AND POLL.PO_LINE_ID = POL.PO_LINE_ID
                     AND POL.PO_HEADER_ID  = paramHEADER_ID
                     AND PAT.PROJECT_ID = paramPROJECT_ID
                     AND PAT.TASK_ID = paramTASK_ID;
            RETURN csr_poLines_ref;
      END MHCI_PO_LINES;
      
      FUNCTION MHCI_PO_HEADERS(
            paramHEADER_ID VARCHAR, paramPO_LINE_ID VARCHAR) RETURN ref_cursor
      IS
            csr_poHeader_ref ref_cursor;
      BEGIN
            OPEN csr_poHeader_ref FOR
            SELECT
                  'Missing' BUYER
                  ,POH.SEGMENT1 DOC
                  ,POH.AUTHORIZATION_STATUS STATUS
                  ,POH.CREATION_DATE CREATION_DATE -- DD-MM-YYYY HH:MM:SS
                  ,'Missing' REVISION_AMT
                  ,((POLL.QUANTITY - POLL.QUANTITY_CANCELLED) * POL.UNIT_PRICE) TOTAL_AMT
                  ,(POLL.QUANTITY_RECEIVED * POL.UNIT_PRICE) RECIEVED_AMT
                  ,(POLL.QUANTITY_BILLED * POL.UNIT_PRICE) INVOICE_AMOUNT
                  ,(POLL.QUANTITY_CANCELLED * POL.UNIT_PRICE) CANCELLED_AMT
            FROM
                  PO_HEADERS_ALL POH,
                  PO_DISTRIBUTIONS_ALL POD,
                  PO_LINES_ALL POL,
                  PO_LINES_ARCHIVE_ALL POLA,
                  PO_LINE_LOCATIONS_ALL POLL
            WHERE
                  poh.po_header_id = pol.po_header_id
                  AND pol.po_line_id = poll.po_line_id
                  AND poll.po_line_id = pod.po_line_id
                  AND POH.PO_HEADER_ID = paramHEADER_ID
                  AND POLA.PO_LINE_ID = paramPO_LINE_ID;
            RETURN csr_poHeader_ref;
      END MHCI_PO_HEADERS;
END MHCI_poTypes;

that's the package without the exception handling. Could you show me an example how to do the eception handling then it would really cool :-)

I'm still not sure what you want to do with the internal exception handling.  Assuming you can return a NULL from the function, then what.  All you will know is that 'something' happened.

Also, what version of Oracle (please provide all 4 number.  ex./ 10.2.0.3).

Are you done with this question?  If not, why did you accept an answer?

sorry, no I would like you to show the exception handling

The Oracle Database server I'm using is 10g...

I've unaccepted the answer so others can participate.

Back to the question:
I can't help you with exception handling until you explain what it is you want to do.  I need specifics not just: "I want to handle exceptions in PL/SQL".

I would like my java program to be able to receive a meningfull error message from a PL/SQL function.
 I only been using exception handling in simple PL/SQL functions using static cursors, howevery I don't know how to do it using REF CURSOR.
 
Two simple examples should do the trick,
1) One using a predefined exception like TOO_MANY_ROWS
2) One using a custom defined exceptoin

By the way I don't need any java code, just the  PL/SQL code.
Thanks :-)

By the way just use a "Hello world" example since the code I have i working fine (I just need to add the exception handling :-)

I think I'm starting to understand.  I'm slow at times.

You can trap no_data_found and the custom 'Too many Rows' but the only way I can think of requires an additional execution of the statement.

See if this helps:
-----------------------------------------
drop table tab1;
create table tab1 (
      col1 number
);

insert into tab1 values('1');
insert into tab1 values('2');
insert into tab1 values('2');
commit;



create or replace function junk( someParameter varchar2 ) return sys_refcursor
is
      myCur      sys_refcursor;
      junkNumber number;
      maxRows number := 1;
begin

      --perform some data checks
      begin
            junkNumber := to_number(someParameter);

            exception
                  when others then
                        raise_application_error(-20101, 'Parameter not a number: ' || someParameter);
      end;

      --check for too many rows or NO rows
      begin
            select count(*) into junkNumber from tab1 where col1 = to_number(someParameter);

            case
                  when junkNumber = 0 then
                        raise_application_error(-20102, 'No rows found for: ' || someParameter);
                  when junkNumber > maxRows then
                        raise_application_error(-20103, 'Too many rows found for: ' || someParameter);
                  else
                        null;
            end case;
      end;

      open  myCur for 'select sysdate from tab1 where col1 = to_number(:x)' using someParameter;

      return myCur;

end;
/

show errors

-- no rows found
select junk('0') from dual;

-- too many rows found
select junk('2') from dual;

-- not a valid number
select junk('Y') from dual;

-- Just right.....
select junk('1') from dual;

Is  raise_application_error(...) a build in PL/SQL function, if so then it should solve my problem right?

Yes.  It's part of PL/SQL.

the code I posted should run as-is.