OUT cursor in Procedure,

[dainova]

Hi, all
What are the rules to use OUT Cursor with Procedure, I started to do something like below, but still have error "need declare a cursor", can find any info anywhere.

create or replace Procedure p_RYBA_GetALL (in_custom IN NUMBER, out_cursorINFO OUT CURSOR_INFO) AS
TYPE CT IS REF CURSOR RETURN tc_INFO.id_comp%TYPE, tc_INFO.id_name%TYPE , tc_INFO.cust_profile%TYPE;
CURSOR_INFO CT;
BEGIN
OPEN out_cursorINFO FOR
SELECT id_comp, id_name, cust_profile FROM tc_INFO
WHERE id_comp = in_custom;
END ;


Tx
V

 

[Dagon]

For starters, you should be using the cursor type (CT) in the procedure declaration, not the cursor itself. What you're doing is akin to saying;

procedure myproc (input_number lvar_num)
lvar_num number;
...

The other problem is that CT is only declared internally to the procedure whereas the declaration of the procedure itself is external. You have two possible solutions:

a) Use the generic type SYS_REFCURSOR instead.
b) Change to a package and declare CT in the package specification. You should then be able to use CT in the declaration of the procedure e.g.

create or replace package xxx as
    type CT is ref cursor;
    Procedure  p_RYBA_GetALL (in_custom IN NUMBER,  out_cursorINFO OUT CT);
end;

For Oracle-related work, contact me through Linked-In.