CODEWITHSUNDEEP
oracle-database
Марсель Абдуллин
Марсель Абдуллин

Reputation: 331

How do I run a dynamic query in select Oracle?

I need something like this:

begin
for group_level in (select * from group_level)
loop
execute immediate 'select :gost from erp_trips' using group_level.gost_id;
end loop;
end;

Group_level is a table in which gost_id is the name of the field that you need to get from erp_trips(in this case 'GOSB')

Code is executed but the table does not output, what is the problem?

Upvotes: 0

Views: 42

Answers (1)

MT0
MT0

Reputation: 167774

You can use:

DECLARE
  p_sql  CLOB;
  c_cur  SYS_REFCURSOR;
  p_gost VARCHAR2(200);
BEGIN
  FOR p_row IN (SELECT gost_id FROM group_level) LOOP
    IF p_sql IS NOT NULL THEN
      p_sql := p_sql || ' UNION ALL ';
    END IF;
    p_sql := p_sql || 'SELECT ' || p_row.gost_id || ' AS gost FROM erp_trips';
  END LOOP;
  OPEN c_cur FOR p_sql;
  LOOP
    FETCH c_cur INTO p_gost;
    EXIT WHEN c_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( p_gost );
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE group_level ( gost_id ) AS
SELECT 'GOSTA' FROM DUAL UNION ALL
SELECT 'GOSTC' FROM DUAL UNION ALL
SELECT 'GOSTD' FROM DUAL;

CREATE TABLE erp_trips ( gosta, gostb, gostc, gostd ) AS
SELECT LEVEL || 'A', LEVEL || 'B', LEVEL || 'C', LEVEL || 'D'
FROM   DUAL
CONNECT BY LEVEL <= 5;

Outputs:

1A
2A
3A
4A
5A
1C
2C
3C
4C
5C
1D
2D
3D
4D
5D

db<>fiddle here

Upvotes: 1

Related Questions