Prasad, October 28, 2004 - 8:47 pm UTC
Duke Ganote, February 11, 2005 - 8:33 am UTC
Great tip!
Nitin, October 12, 2005 - 3:38 pm UTC
just what I needed!
Jermy Hoffman, October 19, 2005 - 11:29 am UTC
THNX
NOT USEFUL COMMENTS
A reader, September 26, 2006 - 7:48 am UTC
...
September 26, 2006 - 3:52 pm UTC
well, gee.
"sorry"?
but - hey, have a nice day anyway...
Great answer. Just what I needed. (as usual in ask Tom)
A reader, October 27, 2006 - 12:38 pm UTC
A reader, February 13, 2007 - 10:53 am UTC
Good explanation
JequE, April 03, 2007 - 4:48 pm UTC
I had the exact same problem and a quick search in your site help me a lot. Thanks for sharing your knowledge.
Same issue with private function
Rashmi Sinha, July 30, 2007 - 1:21 pm UTC
I have a function which is in other package and second function declared as private function in the same package. I am able to use function from the other package in my SQL statement but if I try to use function from the same package it give me PLS 00231 error.
Private Function has forward declaration in the package body.
July 30, 2007 - 5:46 pm UTC
give tiny example of what you mean.
TINY - I dont NOT want all of your code, I want the smallest possible example that demonstrates your issue.
your text is not followable as a specification.
very tricky
Cristian Cudizio, July 31, 2007 - 11:12 am UTC
i've had same problem and until this morning i could not understant it.
I used the statement
INSERT INTO tableX
(x2000averid,
x2000acncid
)
(
SELECT x.i_x2000averid , x.i_x2000avercnc
from table(TOKENIZE(mynestedtable_nt(i).x2000averacnc2 ,mynestedtable_nt(i).x2000averid )) x
where x.i_x2000avercnc is not null
);
In a procedure part of a package. I've used pipelined function "TOKENIZE" not declared in package declaration and i got pls-00231 at compiling time.
If i put declaration of "TOKENIZE" on package declaration it compiles correctly, but on execution gives:
ORA-22905: cannot access rows from a non-nested table item
So i changed the statement adding CAST on TOKENIZE arguments:
INSERT INTO tableX
(x2000averid,
x2000acncid
)
(
SELECT x.i_x2000averid , x.i_x2000avercnc
from table(TOKENIZE(cast(mynestedtable_nt(i).x2000averacnc2 as varchar(100)), cast(mynestedtable_nt(i).x2000averid as number))) x
where x.i_x2000avercnc is not null
);
So i think that when procedure executes, to execute the statement Oracle switch to SQL engine and it is not able to get nested table elements types.
Could you explain better what appens?
Thanks for your help.
Cristian
August 02, 2007 - 10:05 am UTC
it has to do with plsql and changes over the versions.
plsql will parse that sql statement at compile time, extract the references to variables and turn them into binds. It then temporarily lost the "datatype" when it replaced the variables with :bv references. By using cast - you supply that information again and it worked.
this is corrected in current releases of the software - the datatype does not temporarily go missing.
using cast is a perfectly workable solution however
ThanX!
Rob, October 29, 2010 - 5:53 am UTC
Very usefull.
Thanx!
Rob
Don't forget the parametrs
Susi, March 23, 2012 - 11:08 am UTC
Bear in mind that the specification has to be precise. I had a function with one parametr, which I omitted in the package declaration and I still got this error.
Thank you Tom
Hass, May 02, 2012 - 11:59 pm UTC
I am ashamed after seeing your solution .How stupid can i be :)
crystal clear example!
cj, September 19, 2012 - 10:39 am UTC
I'm with Haas from UAE, this seems like such a rookie mistake! Thanks for taking the time to write the pl/sql example.
Spot on !
A reader, October 13, 2017 - 9:18 am UTC
Most useful answer, good job AskTOM !