CODEWITHSUNDEEP
oracle-databasestored-procedures
Ilesh Patel
Ilesh Patel

Reputation: 2155

Statement ignored in oracle procedure

I am trying to create procedure that return table. for that I have created Object and procedure as below. but it gives me compile time error,

CREATE TYPE INTERN_OBJECT AS OBJECT
( companyTitle VARCHAR2(100),
  schoolName VARCHAR2(30),
  RANK NUMBER(3),
  NUM_INTERNS NUMBER(3));

CREATE TYPE INTERN_TAB AS TABLE OF INTERN_OBJECT;

CREATE OR REPLACE FUNCTION INTERN_DATA_Function RETURN INTERN_TAB
IS
  r_INTERN_TAB INTERN_TAB;
  r_index number := 0;
BEGIN
  r_INTERN_TAB := INTERN_OBJECT();
  FOR r IN (select company.title AS title ,school.name AS name ,RANK AS RANK ,count(SID) AS NO_OF_INTERN from school join student on school.SCHID = student.SCHOOLID JOIN INTERN on student.SID = INTERN.SID JOIN COMPANY ON INTERN.CMPID = COMPANY.CMPID GROUP BY company.title,school.name,RANK ORDER BY count(SID) DESC)
  LOOP
          r_INTERN_TAB.extend;
      r_index := r_index + 1;  
      r_INTERN_TAB(r_index):= INTERN_TAB(r.title, r.name ,r.RANK , r.NO_OF_INTERN);
    END LOOP;
  return r_INTERN_TAB;
END;

when I hit run I got below error:

ERROR at line 11: PL/SQL: Statement ignored
1. CREATE OR REPLACE FUNCTION INTERN_DATA_Function RETURN INTERN_TAB
2. IS
3.   r_INTERN_TAB INTERN_TAB;

I don't know where is the probelm. please help me.

Upvotes: 0

Views: 34

Answers (1)

Boneist
Boneist

Reputation: 23578

You need to put a / after each statement, especially if you're running the above script in SQL*Plus:

CREATE TYPE INTERN_OBJECT AS OBJECT
( companyTitle VARCHAR2(100),
  schoolName VARCHAR2(30),
  RANK NUMBER(3),
  NUM_INTERNS NUMBER(3));
/

CREATE TYPE INTERN_TAB AS TABLE OF INTERN_OBJECT;
/

CREATE OR REPLACE FUNCTION INTERN_DATA_Function RETURN INTERN_TAB
IS
  r_INTERN_TAB INTERN_TAB;
  r_index number := 0;
BEGIN
  r_INTERN_TAB := INTERN_OBJECT();
  FOR r IN (select company.title AS title ,school.name AS name ,RANK AS RANK ,count(SID) AS NO_OF_INTERN from school join student on school.SCHID = student.SCHOOLID JOIN INTERN on student.SID = INTERN.SID JOIN COMPANY ON INTERN.CMPID = COMPANY.CMPID GROUP BY company.title,school.name,RANK ORDER BY count(SID) DESC)
  LOOP
          r_INTERN_TAB.extend;
      r_index := r_index + 1;  
      r_INTERN_TAB(r_index):= INTERN_TAB(r.title, r.name ,r.RANK , r.NO_OF_INTERN);
    END LOOP;
  return r_INTERN_TAB;
END;
/

Upvotes: 1

Related Questions