CODEWITHSUNDEEP
oracle-databaseplsqluser-defined-types
Anirudh S
Anirudh S

Reputation: 41

PLSQL Getting expression is of wrong type

I'm a newbee to PLSQL and I'm facing issues while created PLSQL function.It says expression is of wrong type.I need some help. Here is my function

CREATE OR REPLACE TYPE HOTGROUPTYPE AS OBJECT (
  IMEI           VARCHAR2(255),
  LACCI  VARCHAR2(255),
  FRAUD_TYPE VARCHAR2(255),
  MSISDN VARCHAR2(255)
);
/

CREATE OR REPLACE TYPE HOTGROUPTYPE_tab IS TABLE OF HOTGROUPTYPE;
/

CREATE OR REPLACE FUNCTION get_tab_tf (p_count IN NUMBER, p_days IN NUMBER) RETURN HOTGROUPTYPE_tab 
AS
l_tab  HOTGROUPTYPE_tab:=HOTGROUPTYPE_tab();
BEGIN
  for i in (select IMEI,LACCI,FRAUD_TYPE,MSISDN 
  from fms_fraud_master_tbl 
  where request_type='BARRING' 
  and rownum<2)
  loop
  l_tab.extend;
  l_tab(l_tab.last) := i.IMEI;
  end loop;

  RETURN l_tab;
END;
/

And I'm getting this error when the execute the function

11/3     PL/SQL: Statement ignored
11/28    PLS-00382: expression is of wrong type

Upvotes: 0

Views: 388

Answers (2)

APC
APC

Reputation: 146349

The neatest way to populate a collection from a query is to use bulk collect:

CREATE OR REPLACE FUNCTION get_tab_tf (p_count IN NUMBER, p_days IN NUMBER) RETURN HOTGROUPTYPE_tab 
AS
  l_tab  HOTGROUPTYPE_tab:=HOTGROUPTYPE_tab();
BEGIN

  select HOTGROUPTYPE( IMEI,LACCI,FRAUD_TYPE,MSISDN )
  bulk collect into l_tab
  from fms_fraud_master_tbl 
  where request_type='BARRING' ;

  RETURN l_tab;

END;
/

Upvotes: 0

Popeye
Popeye

Reputation: 35930

You are not assigning the values to your table type properly. l_tab(<index>) must be assigned the variable of type HOTGROUPTYPE.

You must use this:

l_tab(l_tab.last) := HOTGROUPTYPE(i.IMEI, i.LACCI,i.FRAUD_TYPE,i.MSISDN );

Upvotes: 1

Related Questions