Hi,
I have a database with thousands of tables containing the same kind of information.
I need to write a program to aggregate these informations and thought about using a sqlmacro.
-- This is a very simplified concept
create or replace
function get_val (p_table_name varchar2)
return varchar2 SQL_Macro
is
return 'select col1,col2 from p_table_name';
end;
/
select col1, col2
from get_val(t.table_name)
, table_list t; --Table_list contains the list of the table to take
And it always tells that the table doesn't exist.
The documentation talks about DBMS_TF.TABLE_T, which works if you pass the table as the parameter (and not the table's name).
How can I do that? Do I have to write a function returning the rows from the table?
Thank you
You need to pass tables to macros using the dbms_tf.table_t parameter. When calling the macro, you pass the table name itself, not a string containing the table name.
For example, this fails because the table_name parameter is a string:
create or replace function mcro ( table_name varchar2 )
return clob sql_macro
as
begin
return 'select count(*) from table_name';
end;
/
select * from mcro ( 'employees' );
ORA-00942: table or view "CHRIS"."TABLE_NAME" does not exist
Instead of replacing the table_name with the parameter valud, the query searches for a table called "TABLE_NAME" (as the error shows).
Here's the revised version which enables you to pass the table:
create or replace function mcro ( table_name dbms_tf.table_t )
return clob sql_macro
as
begin
return 'select count(*) from table_name';
end;
/
select * from mcro ( employees );
So you can't use rows in another table to pass the table names to the macro.
If you want to do this, you could either rewrite the function to use dynamic SQL
Or generate a script of the macro calls with a query like:
select
' select * from mcro ( "' || table_name || '" ); '
from user_tables;