OraFAQ Forum: SQL & PL/SQL » Invalid Datatype error for record type

Home » SQL & PL/SQL » SQL & PL/SQL » Invalid Datatype error for record type (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Invalid Datatype error for record type [message #665290]

Tue, 29 August 2017 00:43

Rotan
Messages: 1
Registered: August 2017

Junior Member

I have a type declared in my package spec

TYPE product_type IS RECORD (      
product_id  NUMBER,      
Product_name      VARCHAR2(50),      
product_stat      VARCHAR2(50),      
product_type      VARCHAR2(50),      
product_uom       NUMBER);

I have a function which returns this type in the package body

FUNCTION product_info_typ(p_att1 IN VARCHAR2,p_att2 IN VARCHAR2)
 RETURN product_type IS

  CURSOR c_prod IS
    SELECT product_id,
      Product_name,
           product_stat,
           product_type,
           product_uom 
    FROM prod_table
   WHERE attribute1       = p_att1 
AND  attribute2       = p_att2 ;

  l_prod_typ     product_type;
 BEGIN
   l_prod_typ := NULL;
   OPEN c_prod ;
   FETCH c_prod INTO     l_prod_typ.product_id,
                         l_prod_typ.Product_name,
                         l_prod_typ.product_stat,
                         l_prod_typ.product_type,
                         l_prod_typ.product_uom ;
   CLOSE c_prod ;
  RETURN l_prod_typ;
   END product_info_typ;

I want to call this function in a select query of a cursor. But it throws error ----- ORA-00902: invalid datatype.
Any ideas how to overcome this. What am I doing wrong?

Report message to a moderator

Re: Invalid Datatype error for record type [message #665294 is a reply to message #665290]

Tue, 29 August 2017 03:44

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you want help with code that calls a function you really need to post the code that calls the function.

Report message to a moderator

Re: Invalid Datatype error for record type [message #665296 is a reply to message #665294]

Tue, 29 August 2017 05:14

fixxxer
Messages: 45
Registered: August 2014

Member

Just out of interest, why define it as a cursor, with open fetch close? Why not just do a select into?

I compiled your code as it is at the minute, and ran it with the below stub, worked fine:

SET SERVEROUTPUT ON;

ALTER SESSION SET CURRENT_SCHEMA = fixxxer;

DECLARE
  p_att1    VARCHAR2(200);
  p_att2    VARCHAR2(200);
  v_return  TEST_TYPE_PKG.PRODUCT_TYPE;
BEGIN
  p_att1 := 'fixxxer';
  p_att2 := 'fixxxer';

  v_return := TEST_TYPE_PKG.PRODUCT_INFO_TYP(p_att1 => p_att1,
                                             p_att2 => p_att2);
  
  DBMS_OUTPUT.PUT_LINE(v_return.product_name);
END;

Report message to a moderator

Re: Invalid Datatype error for record type [message #665316 is a reply to message #665290]

Tue, 29 August 2017 14:50

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

RECORD is PL/SQL, not SQL data type - you can't call function returning record from SQL. Create SQL object type:

CREATE OR REPLCAE
TYPE product_type IS OBJECT (      
product_id  NUMBER,      
Product_name      VARCHAR2(50),      
product_stat      VARCHAR2(50),      
product_type      VARCHAR2(50),      
product_uom       NUMBER);

And use instead.

SY.

Report message to a moderator

Previous Topic:	Visualize Processes Vertical
Next Topic:	Question about printing the result of a recursive subquery factoring with line indents

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 09:19:08 CDT 2024