ora-00902 invalid data type

247890

Hello all -

I have a function that returns a ref cursor.

When I test this function using PL/SQL developer, it works just fine and returns ref cursor with a set of price values for a set of products.

However, when I test the same function from SQL*Plus, it errors out.

select get_price('0123','p1,p2,p3') from dual;

Error at line 1:
ORA-00902: invalid datatype

Could you please help me out. Thanks in advance.

OrionNet

Hello,
Can you post your get_price procedure/function?

Try using anonymous pl/sql block simple example,

CREATE OR REPLACE FUNCTION getpmnorgrow (empid emp.id%TYPE)
   RETURN emp%ROWTYPE --
IS
   result   emp%ROWTYPE;
BEGIN
   SELECT *
   INTO result
   FROM emp
   WHERE id = empid;

   RETURN result;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      raise_application_error (-20100, 'error');
      RETURN NULL;
END;
/

DECLARE
   retval   emp%ROWTYPE;
   empid    NUMBER;
BEGIN
   empid    := 1;

   retval   := getpmnorgrow (empid);
   DBMS_OUTPUT.put_line ('employee name=' || retval.name);
   COMMIT;
END;

Regards

Tubby

variable my_cursor refcursor;

exec :my_cursor := get_price('0123','p1,p2,p3');


print :my_cursor

OrionNet

Check this link , discussing the same error


844608

Regards

247890

Hi,

Thanks for your response.
-------------------------------------------------------------------------

Edited by: user6773 on Jan 5, 2009 6:05 AM

247890

Hi Tubby,

I get an error msg when I try this. Not sure ..

Karthick2003

Whats the error you are getting?

247890

When I try to test it:

select get_price('0123','p1,p2,p3') from dual;

Error at line 1:
ORA-00902: invalid datatype

SomeoneElse

select get_price('0123','p1,p2,p3') from dual;

Why do you have p1,p2,p3 in quote marks?

Is your function expecting a character string here? Or should they be separate parameters?

247890

Yes, it is expecting a string of values for the second parameter (set of products).

SomeoneElse

I have no idea what your function does, but try this:

SQL> var results refcursor
SQL> exec :results := get_price('0123','p1,p2,p3');
SQL> print results