CODEWITHSUNDEEP
oracle-databaseplsqltoad
Rahul Gulwani
Rahul Gulwani

Reputation: 200

Warning: compiled but with compilation error in oracle

I am getting this warning in toad. so unable to use the procedure. i am creating a varray first.

CREATE or replace  TYPE notif_array AS VARRAY(100000) OF VARCHAR2(10);

Then i am creating a procedure.

CREATE OR REPLACE PROCEDURE get_notification_id
    ( personrole in varchar2, personid out notif_array )
is
begin
    DBMS_OUTPUT.PUT_LINE(personrole);

    select person_id into personid
    from   exp_role_person_mapping
    where  person_role = personrole;
exception
    when others then
        personid := null;
end;

then after that i am getting warning from toad 

Warning: compiled but with compilation errors

Upvotes: 1

Views: 5704

Answers (2)

Rahul Gulwani
Rahul Gulwani

Reputation: 200

just add "bulk collect" in select statement. Thanks to Ponder Stibbons

CREATE OR REPLACE PROCEDURE get_notification_id(personrole in varchar2,personid out notif_array)
is
begin
select person_id bulk collect  into personid from  exp_role_person_mapping where person_role=personrole;
exception when others then
personid:=null;
end;

Upvotes: 1

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

You need to change the way, in which you assign data to personid.

It is not a base data type, rather its a custom data type defined as per your requirement

CREATE OR REPLACE PROCEDURE get_notification_id(personrole in varchar2,personid out notif_array)
is
CURSOR cur_temp(per_role varchar2)
IS
   select person_id from  exp_role_person_mapping where person_role=per_role;
   index NUMBER := 1;
begin
   DBMS_OUTPUT.PUT_LINE(personrole);
   FOR datarecord in cur_temp(personrole)
   LOOP
       personid(index) := datarecord.person_id;
       index = index + 1;
   END LOOP;
exception when others then
   personid:=null;
end;

Upvotes: 1

Related Questions