CODEWITHSUNDEEP
sqloracle-databaseplsql
Revious
Revious

Reputation: 8146

How to build a dynamic query in oracle?

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.

Upvotes: 1

Views: 270

Answers (2)

StevieG
StevieG

Reputation: 8709

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;
/

Upvotes: 0

Anjan Biswas
Anjan Biswas

Reputation: 7912

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;

Upvotes: 1

Related Questions