CODEWITHSUNDEEP
oracle-databaseoracle11g
A.Wen
A.Wen

Reputation: 223

Oracle SYS_REFCURSOR couldn't use as a return type

I need to extract and display all the years for all the records in db using member function in oracle 11g.

CREATE or replace TYPE BODY student_t AS 
MEMBER FUNCTION getYear RETURN SYS_REFCURSOR IS
yearDOB SYS_REFCURSOR;
BEGIN
    for c in (SELECT EXTRACT(YEAR FROM s.dob) c_year from student s)
    loop
        yearDOB := c.c_year; 
    end loop;
    return yearDOB; 
END;END;/

Since I need to return multiple values for the extract function I have declared a SYS_REFCURSOR type variable to return. But it will generate following errors.

7/1      PL/SQL: Statement ignored,
7/14     PLS-00382: expression is of wrong type

output after changed as following answer.

S.GETYEAR()

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

C_YEAR

  1993
  1995

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

C_YEAR

  1993
  1995

student type as follows.

create type student_t as object(
stno char(4),
dob date)/ 
alter type student_t add member function getYear RETURN SYS_REFCURSOR cascade;/

Upvotes: 1

Views: 105

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

I'm not sure what you are trying to achieve but I think you misunderstood concept of object in oracle. In my example i'm assuming
1) Table studens is create with this script create table student of student_t;

drop table student;

Create type spec and body. 

create or replace type student_t as object(
stno char(4),
dob date,
member function getYear return number

)
;

create or replace type body student_t as

    member function getYear return number is 
    begin 
            return  EXTRACT(YEAR FROM self.dob);
    end;

end;

Create table of studnets

create table student of student_t;

Populate tabel 

declare 
  v_student student_t;
begin 
for i in 0 .. 10 loop 
  insert into student values(student_t('ST'||+mod(i,3),to_date('01-01-'||to_char(2000+i),'dd-mm-yyyy')));
end loop;
commit;
end;

And the query.

select s.*,s.getYear() from student s;

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

It is much simpler:

yearDOB SYS_REFCURSOR;
BEGIN
    OPEN yearDOB for 
    SELECT EXTRACT(YEAR FROM s.dob) c_year 
    from student s;

    return yearDOB; 
END;

Upvotes: 1

Related Questions