Oracle dynamic table creation on runtime

Question

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;

Answer 1

DBMS_SQL.NUMBER_TABLE is an associative array:

Unlike a database table, an associative array:

• Does not need disk space or network operations
• Cannot be manipulated with DML statements

You can select into an associative array, but you cannot use it as a table in a select.

You could to the select into with a nested table but you still couldn't use that in a select 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:

declare
  v_table sys.odcinumberlist;
  v_table2 sys.odcinumberlist;
begin
  select a_id bulk collect into v_table
  from (select a_id from b where length(b_data) > 4);

  select a.a_id bulk collect into v_table2
  from table(v_table) vt
  join a on a.a_id = vt.column_value;
end;
/

anonymous block completed

The second select you showed is incomplete so I've made one up. Your first doesn't need the nested select, that can be simplified to:

  select a_id bulk collect into v_table
  from b
  where length(b_data) > 4;

Hopefully you're dong something with the collection before your second select, otherwise it's a bit pointless; you could just join a to b in the second select to get the same result:

  select a.<columns> ...
  from a
  join b on b.a_id = a.a_id
  where length(b.b_date) > 4;