Home » RDBMS Server » Performance Tuning » type tables in plsql/ variable IN list
type tables in plsql/ variable IN list [message #336286] Fri, 25 July 2008 09:25 Go to next message
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.
Re: type tables in plsql/ variable IN list [message #336289 is a reply to message #336286] Fri, 25 July 2008 09:34 Go to previous messageGo to next message
szogu
Messages: 21
Registered: July 2008
Junior Member
Must be a SQL TYPE not A PLS/SQL TYPE,
SELECT statements recognize SQL types, That is why should be created in Database.

Regards,
Re: type tables in plsql/ variable IN list [message #336290 is a reply to message #336289] Fri, 25 July 2008 09:36 Go to previous messageGo to next message
eaganfox
Messages: 6
Registered: July 2008
Location: Minnesota
Junior Member
Oh thanks. I did not know that.
Re: type tables in plsql/ variable IN list [message #336360 is a reply to message #336286] Fri, 25 July 2008 17:54 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
You can use Pl/Sql types, but only under very few circumstances:-

Look Up "Pipelined Table Functions"

You can keep your plsql type, and still use the array results in an in-list as they are "piped out" as if they are rows and doesnt require sql types.

[Updated on: Fri, 25 July 2008 17:54]

Report message to a moderator

Previous Topic: need help to optimize the query
Next Topic: Tuning 'LIKE' Clause
Goto Forum:
  


Current Time: Wed Dec 04 18:06:33 CST 2024