Home » SQL & PL/SQL » SQL & PL/SQL » Invalid Datatype (Oracle 10g)
Invalid Datatype [message #408669] Wed, 17 June 2009 05:04 Go to next message
deepbans
Messages: 32
Registered: February 2009
Member
Hi,

I created a function
GET_DISTINCT_VLAUES(l_string IN VARCHAR2,l_spr IN VARCHAR2)
RETURN l_str_table

in ABC package body.

L_str_table is table type variable declared as

create type L_str_table is table of varchar2(30);

When i am tring to execute it as

select * from table(abc.GET_DISTINCT_VLAUES('12345,11234',','))

It throwing invalid datatype error.

But if i create this type anonymously and create function outside package it is executing successfully.

Please let me know where i am committing mistake.

Thanks in Advance.
Re: Invalid Datatype [message #408672 is a reply to message #408669] Wed, 17 June 2009 05:09 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
create type L_str_table is table of varchar2(30);
where did you create this type? in package body?

To use "table(abc.GET_DISTINCT_VLAUES('12345,11234',','))", the type should be a database type.

By
Vamsi
Re: Invalid Datatype [message #408674 is a reply to message #408672] Wed, 17 June 2009 05:16 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Yes I created this in package specifiaction
Re: Invalid Datatype [message #408675 is a reply to message #408674] Wed, 17 June 2009 05:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
To use "table(abc.GET_DISTINCT_VLAUES('12345,11234',','))", the type should be a DATABASE type.
Create the type in database, not in package.

By
Vamsi
Re: Invalid Datatype [message #408678 is a reply to message #408675] Wed, 17 June 2009 05:23 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
The Type has been created at the database level also.But error generates when we return this type from a function inside apackage
Re: Invalid Datatype [message #408694 is a reply to message #408678] Wed, 17 June 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.
Post a Test case we can reproduce.

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).

Regards
Michel

Re: Invalid Datatype [message #408757 is a reply to message #408678] Wed, 17 June 2009 11:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to use a type defined in SQL, as anSQL query knows nothing about a type defined in yuor package - it only knows about types defined inSQL.
Re: Invalid Datatype [message #408825 is a reply to message #408757] Thu, 18 June 2009 00:07 Go to previous message
deepbans
Messages: 32
Registered: February 2009
Member
Thanks.It worked.
Previous Topic: Put row results to column format
Next Topic: Function Parameters throwing ORA-28576: lost RPC connection to external procedure agent (merged)
Goto Forum:
  


Current Time: Sat Dec 10 20:45:47 CST 2016

Total time taken to generate the page: 0.08255 seconds