Home » SQL & PL/SQL » SQL & PL/SQL » Invalid Datatype error for record type (Oracle 11g)
Invalid Datatype error for record type [message #665290] Tue, 29 August 2017 00:43 Go to next message
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?

[Updated on: Tue, 29 August 2017 00:45] by Moderator

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 Go to previous messageGo to next message
cookiemonster
Messages: 12957
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.
Re: Invalid Datatype error for record type [message #665296 is a reply to message #665294] Tue, 29 August 2017 05:14 Go to previous messageGo to next message
fixxxer
Messages: 21
Registered: August 2014
Junior 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;
Re: Invalid Datatype error for record type [message #665316 is a reply to message #665290] Tue, 29 August 2017 14:50 Go to previous message
Solomon Yakobson
Messages: 2687
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.
Previous Topic: Visualize Processes Vertical
Next Topic: Question about printing the result of a recursive subquery factoring with line indents
Goto Forum:
  


Current Time: Thu Dec 14 09:08:11 CST 2017

Total time taken to generate the page: 0.01690 seconds