Home » SQL & PL/SQL » SQL & PL/SQL » ora 00902 - invalid datatype (merged)
ora 00902 - invalid datatype (merged) [message #385424] Mon, 09 February 2009 09:43 Go to next message
nicholasp
Messages: 1
Registered: February 2009
Junior Member
why am i getting this error when running this query?
thanks in advance!


SELECT mg.groups || '|' AS str,
nt.column_name AS element_no,
INSTR(mg.groups || '|',
'|',
DECODE(nt.column_name, 1, 1, (nt.column_name - 1) * 6),
DECODE(nt.column_name, 1, 1, 1)) + 1 AS start_pos,
INSTR(mg.groups || '| ',
'|',
DECODE(nt.column_name, 1, 1, (nt.column_name - 1) * 6),
DECODE(nt.column_name, 1, 2, 2)) AS next_pos

FROM
main_groups mg,
TABLE(CAST(MULTISET
(SELECT ROWNUM
FROM dual
CONNECT BY ROWNUM <
((LENGTH(mg.groups) -
LENGTH(REPLACE(mg.groups, '|'))) + 1)) AS
number_pool_excep_table_type)) nt
Re: ora 00902 - invalid datatype [message #385428 is a reply to message #385424] Mon, 09 February 2009 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.
Re: ora 00902 - invalid datatype (merged) [message #385432 is a reply to message #385424] Mon, 09 February 2009 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: ora 00902 - invalid datatype (merged) [message #385535 is a reply to message #385424] Tue, 10 February 2009 01:08 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
You use the datatype
number_pool_excep_table_type
to cast Your subquery to.

This datatype seems invalid/not accesible to the current user.
Previous Topic: Illustrate a many to many relationship (merged 3)
Next Topic: CSV file
Goto Forum:
  


Current Time: Fri Dec 09 02:20:05 CST 2016

Total time taken to generate the page: 0.10957 seconds