Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00902 invalid data type - pl/sql table cast error (oracle 11g)
ORA-00902 invalid data type - pl/sql table cast error [message #569892] Fri, 02 November 2012 16:44 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
HI,

I have created the following type

create or replace type type_sh_fld_rec
(
s_count count,
s_name varchar2(200),
e_date date);
/

create or replace type t_table_s_field as table of type_sh_fld_rec;
/


I have used these types in package pkg_shippers PROCEDURE process_shipments(userid);

when I run the following sql

select * from table(cast(DEV.PKG_SHIPPERS.process_shipments(324) AS T_TABLE_S_FIELD));

I get the following error.

ORA-00902 Invalid datatype.


I have tried to create the types and table from the sql prompt instead of the package spec, still no luck

Any insight into how I can get over this problem is greatly appreciated.

Note: The sql statement works fine if I run it as schema owner but not as user who has all the privilleges.

Your prompt reply is greatly appreciated.


Thanks
Re: ORA-00902 invalid data type - pl/sql table cast error [message #569893 is a reply to message #569892] Fri, 02 November 2012 16:54 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did that user acquire privileges? Directly, or via role? (Should be the first option - directly).
Re: ORA-00902 invalid data type - pl/sql table cast error [message #569906 is a reply to message #569893] Fri, 02 November 2012 21:32 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
The user usually gets access through grants ex: grant select on shippers to user;

Do we need to give specific grants to this user to be able to successfully execute the sql statement in this case?
Re: ORA-00902 invalid data type - pl/sql table cast error [message #569907 is a reply to message #569906] Fri, 02 November 2012 22:03 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
privileges acquired via ROLE do NOT apply within named PL/SQL procedures
direct GRANT is required
Re: ORA-00902 invalid data type - pl/sql table cast error [message #569914 is a reply to message #569892] Sat, 03 November 2012 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ The type must indeed be created with CREATE TYPE
2/ The user must have EXECUTE privilege on the package
3/ Depending on the package declaration but as default, AUTHID DEFINER, the owner of the package must have DIRECT privilege on any object it uses in this package.

Note that your type is not valid:
SQL> create or replace type type_sh_fld_rec 
  2  (
  3  s_count count,
  4  s_name varchar2(200),
  5  e_date date);
  6  /

Warning: Type created with compilation errors.

SQL> sho err
Errors for TYPE TYPE_SH_FLD_REC:
LINE/COL
-------------------------------------------------------------------------------
ERROR
-------------------------------------------------------------------------------
2/1
PLS-00103: Encountered the symbol "(" when expecting one of the following:

   ; is authid as compress compiled wrapped under


Regards
Michel
Re: ORA-00902 invalid data type - pl/sql table cast error [message #569965 is a reply to message #569914] Sat, 03 November 2012 15:55 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
I might have mistyped the syntax but the type has been created without errors. The owner of the package does have privilleges to the object. The schema owner can execute the sql statment fine but the user can not execute the statement.

Re: ORA-00902 invalid data type - pl/sql table cast error [message #569966 is a reply to message #569965] Sat, 03 November 2012 16:08 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
>Any insight into how I can get over this problem is greatly appreciated.
do use TYPE datatype since it adds nothing but complications & errors
Re: ORA-00902 invalid data type - pl/sql table cast error [message #569967 is a reply to message #569965] Sat, 03 November 2012 16:10 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I might have mistyped the syntax but the type has been created without errors


This is why you must NOT type but COPY AND PASTE.

Quote:
The schema owner can execute the sql statment fine but the user can not execute the statement.


So some of the points I mentioned are not satisfied.

Use SQL*Plus and copy and paste your session, the WHOLE session including user privileges and roles (taken from the dictionary NOT from what you think they have), objects creation (all of them type, package...) and execution (both when it works and when it does not). In short, all that allows us to reproduce what you have.

Regards
Michel
Previous Topic: Need Query, but cant test it...
Next Topic: missing parenthesis error
Goto Forum:
  


Current Time: Tue Sep 02 03:24:13 CDT 2014

Total time taken to generate the page: 0.12146 seconds