| type tables in plsql/ variable IN list [message #336286] |
Fri, 25 July 2008 09:25  |
eaganfox
Messages: 6
Registered: July 2008
Location: Minnesota
|
Junior Member |
|
|
One of the solutions to the variable IN list problem is to create a type table (type of VARCHAR2 or NUMBER or whatever), and create a function that breaks up the IN string into rows, and use that function in an IN clause. All implementations have this type table physically created in the database before accessing it in the package. I however just want it to be a TABLE TYPE declaration in a package and use that in the IN clause, like this:
and fromccy in (SELECT * FROM TABLE(CAST(pkg_dao.f_in_list(p_currency_list) as pkg_ceows_types.tabty_varchar2_500)))
In implementations on the internet, tabty_varchar2_500 is a physical table type object created in the database. But I am trying to use it in declared format.
TYPE tabty_varchar2_500 is table of VARCHAR2(500);
This however does not work. Any ideas from the gurus? I don't want to create a type table in the schema unless it is used to store something and not just for program manipulation.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
