| Table type assignement [message #2780] |
Tue, 13 August 2002 02:00  |
Philippe
Messages: 7
Registered: May 2002
|
Junior Member |
|
|
Hi,
The following function should return an array :
(source http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061
) ....thanks to Tood Barry
FUNCTION str2tbl( p_str in varchar2 ) return
my_TableType
as
l_str long default p_str || ',';
l_n number;
l_data my_TableType := my_TableType();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
So I should be able to do this :
TYPE my_TableType IS TABLE OF NUMBER;
my_contrats my_TableType;
my_contrats := str2tbl(my_liste_contrats);
However it returns the error "expression is of wrong type" with prompt on the word 'str2tbl'
Does anybody knows what is wrong with this PLSQL code ?
Thanks a lot,
Philippe
|
|
|
|
| Re: Table type assignement [message #2789 is a reply to message #2780] |
Tue, 13 August 2002 10:57  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Philippe, if you are really using this to meet your original requirement, the type needs to be a SQL type (create or replace type My_TableType...), not a PL/SQL type as you show in your example.
|
|
|
|
| Re: Table type assignement [message #2797 is a reply to message #2789] |
Tue, 13 August 2002 23:27 |
Philippe
Messages: 7
Registered: May 2002
|
Junior Member |
|
|
Hi Barry,
I Built all in a package where my main procedure is called by send a string parameter like '1,10,12,6'.
So, since the type must be declared in SQL, It seems not possible to use the askTom example.
So, now I am doing a bad thing: With the ASP, I build online the procedure creation script with hardcoding in it the values that I cannot send as parameter '1,10,12,6'.
Of course these values are subject to change at each query.
Then after have executed the procedure, I delete it and do the same process at each query.
I decided to use a procedure for reason of efficiency against a simple SQL query.
Do you think there is a way to send this kind of string parameters '1,10,12,6' or '1','10','12,'6'
to a procedure in the aim to use it in a test select ...WHERE ...IN ()
Thanks
PS: If you need something about ASP,it is my field of application...
|
|
|
|
| Re: Table type assignement [message #2812 is a reply to message #2789] |
Wed, 14 August 2002 09:06 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
No, Philippe, you are misunderstanding the askTom example. It is totally dependent on creating a TYPE at the SQL level - in fact, that is the only way this process works. You have to have that type defined at that level so that the SQL engine can translate the collection into a "table" on the fly.
Please review the example again - it does exactly what you need. I use this method all the time for dynamic queries such as this.
-Todd
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
