I make to try a search engine.Scenario like that; I have a table that it contains text context and I'm fetching some records what suitable according to my query then I want to transfer this founded text of id on a table that created dynamicly on runtime. My sql code as follow but this error
"expression is of wrong type"
SQL
declare
v_table dbms_sql.number_table;
begin
select a_id bulk collect into v_table from (select a_id from b where length(b_data) > 4);
select * from a where a_id in v_table;
end;
DBMS_SQL.NUMBER_TABLE
is an associative array:You can
select into
an associative array, but you cannot use it as a table in aselect
.You could to the
select into
with a nested table but you still couldn't use that in aselect
if the type is declared within your block because it would be a PL/SQL type that isn't valid within an SQL statement.You would need a nested table type defined in SQL (i.e. not in PL/SQL) to achieve this. There is a built-in type to make it easier, but you can define your own:
The second
select
you showed is incomplete so I've made one up. Your first doesn't need the nestedselect
, that can be simplified to:Hopefully you're dong something with the collection before your second
select
, otherwise it's a bit pointless; you could just joina
tob
in the secondselect
to get the same result: