I need to read fields from different tables. The name of the table is written in the column of another one.
Ie: ENI_FLUSSI_HUB has got a FLH_TOUT_NAME column. I want to read field1 and field2 from the table specified in FLH_TOUT_NAME
I thought to use a pipelined function. But I must know witch fields the query would return..
I need to solve the problem using an PL/SQL function that I can call from within an SQL statement.
declare
TYPE ref_cursor IS REF CURSOR;
c_cursor ref_cursor;
--Declare variables stmt, fld1, fld2
begin
FOR c_rec in (SELECT FLH_TOUT_NAME
from ENI_FLUSSI_HUB) --For multiple tables
LOOP
stmt := 'SELECT field1, field2 FROM '|| c_rec.FLH_TOUT_NAME;
open c_cursor for stmt;
LOOP --For multiple rows in table c_rec.FLH_TOUT_NAME
FETCH c_cursor INTO fld1, fld2;
exit when c_cursor%notfound;
dbms_output.put_line ('Table='||c_rec.FLH_TOUT_NAME||' Field 1 ='||fld1||' Field 2='||fld2);
END LOOP;
END LOOP;
...
...
exception
...
...
end;
select should only return one value of any type. You can use the same code in the function but make sure it returns some value using RETURN statement.declare
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(1000);
l_tablename VARCHAR2(20);
l_field1 VARCHAR2(20);
l_field2 VARCHAR2(20);
BEGIN
--this assumes only 1 row in ENI_FLUSSI_HUB, if there are more, then use a cursor
select FLH_TOUT_NAME
into l_tablename
from ENI_FLUSSI_HUB;
query_str := 'SELECT field1, field2 FROM ' || l_tablename;
OPEN c FOR query_str;
LOOP
FETCH c INTO l_field1, l_field2;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line('Field1: ' || l_field1);
dbms_output.put_line('Field2: ' || l_field2);
END LOOP;
CLOSE c;
END;
/
