PL/SQL: How to create type from cursor

Can I define a type from a cursor?

Yes, you can define your own type that is based on cursor_name%rowtype record type(basically it will be a synonym in this situation), using subtype keyword, not the type one.

Here is an example:

set serveroutput on;declare  cursor c1 is    select 1 as col1         , 2 as col2         , 3 as col3     from dual;  subtype mytype is c1%rowtype;  l_myvar mytype;begin  open c1;  fetch c1 into l_myvar;  dbms_output.put(to_char(l_myvar.col1) || ' : ');  dbms_output.put(to_char(l_myvar.col2) || ' : ');  dbms_output.put_line(to_char(l_myvar.col3));  close c1;end;

Result:

anonymous block completed1 : 2 : 3

I think that what you want to do here is to use a ROWTYPE variable, as follows:

DECLARE  CURSOR c1 IS    SELECT t.COL1, t.COL2, t.COL3      FROM MY_TABLE t      WHERE t.ADD_DATE >= TRUNC(SYSDATE, 'MONTH');  c1Row  c1%ROWTYPE;BEGIN  OPEN c1;  LOOP    FETCH c1 INTO c1Row;    EXIT WHEN c1%NOTFOUND;    DBMS_OUTPUT.PUT_LINE('COL1=' || c1Row.COL1 ||                         '  COL2=' || c1Row.COL2 ||                         '  COL3=' || c1Row.COL3);  END LOOP;  CLOSE c1;END;

Reference here

Share and enjoy.