CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Deva K
Deva K

Reputation: 87

Execute SQL query in another Query as result

I have a table which contains SQL query as one of the columns. Based on record id, I want to execute SQL query which is in the record.

Master_Table
------------------------------
|Rec_ID | Query              |
------------------------------
|1      | SELECT * from EMP  |
------------------------------
|2      | SELECT * FROM DEPT |
------------------------------

SELECT Query FROM Master_Table WHERE Rec_ID=1

I am expecting that If I select Rec_Id =1, I have to EMP records. If I select Rec_Id=2, I need to get Dept records.

Is it possible to do it in SQL query?

Upvotes: 2

Views: 1199

Answers (3)

Jon Heller
Jon Heller

Reputation: 36808

There are a few ways to run dynamic SQL in SQL. In 18c we can use a polymorphic table function. If it's OK to get the results as XML we can use DBMS_XMLGEN.getXML. If we're able to create custom PL/SQL objects we can use Oracle data cartridge to build a Method4 solution.

For example, after installing Method4, we can run SQL like this:

select * from table(method4.dynamic_query(
    '
        select query
        from master_table
        where rec_id = 1
    '
));

The above code will work with the below sample schema:

create table master_table as
select 1 rec_id, 'SELECT * from EMP'  query from dual union all
select 2 rec_id, 'SELECT * FROM DEPT' query from dual;

create table emp(emp_name varchar2(100));
create table dept(dept_name varchar2(100));

The preceding information literally answers your question. But I agree with Mark D Powell that this design is often a bad idea and we should only create code like this after we've evaluated alternative designs.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use sys_refcursor 

SQL> create or replace function get_emp_tab return sys_refcursor is
  v_rc  sys_refcursor;
  v_sql varchar2(4000);
begin
  select query into v_sql from Master_Table where Rec_ID = 1
  open v_rc for v_sql;
  return v_rc;
end;
/
SQL> declare
       v_rc sys_refcursor;
begin
   :v_rc := get_emp_tab;
end;
/
SQL> print v_rc;

Upvotes: 0

Mark D Powell
Mark D Powell

Reputation: 184

Vsau, I agree with JNevill in that you need PL/SQL to execute the 'SQL' from your table column. Also I will add that these kind of designs are usually a bad idea. You want your application SQL to be static sql using bind variables otherwise your system will suffer from too high a percentage of hard parses and resulting contention on the dictionary objects and you may run into shared pool fragmentation issues.

Upvotes: 0

Related Questions