Can an oracle SQL query execute a string query selected from a table?

Question

When using oracle SQL is it possible to run a query based on a text_string from a subquery? An example might clarify what I'm trying to do

    select count(sql_text), sql_text
    from
        (select sql_text 
        from query_table) sql_table
    group by sql_text;

The outer query is intended to count the number of results for each query retrieved from the query_table.

Is there some way I can execute the sql statements I retrieved from my query_table in the same query?

Thanks

EDIT: I was able to query sql from a table using the dbms_xmlgen.get_xml() function. I suppose that any command which caused the sql to be parsed and executed would work. That being said, here's the generic code that I was able to accomplish things with:

    select  to_number (
        extractvalue(
        xmltype(
        dbms_xmlgen.getxml('select count(*) c from '|| table_name)), '/ROWSET/ROW/C'))counter,
        sql_text
    from
        (select '('||sql_text||')' table_name 
        from query_table) sql_table;

While perhaps not the most elegant way to do things, it works and is a single sql statement.

Why don't you first call the inner query and then make the main query from your code? — Mohammad Ali Bozorgzadeh
– Mohammad Ali Bozorgzadeh, Commented Feb 7, 2014 at 20:37
@Bozorgzadeh I'm trying to create a view out of the results to remove a bunch of manual coding in a legacy system — Rolan
– Rolan, Commented Feb 7, 2014 at 20:58
man, this helped me a lot, your answer was what I was looking for, thanks! — CJLopez
– CJLopez, Commented Mar 26, 2019 at 17:19

Justin Cave · Accepted Answer · 2014-02-07 21:07:25Z

3

Generally, this isn't a particularly good design-- storing SQL in tables and dynamically executing it introduces all sorts of security and maintenance issues.

It is probably possible (though it's way too late on a Friday that started way too early for me to try to figure it out) to do a really cool XML query along the lines of this query that runs a count(*) against every table in the schema that would do this all in one query.

For the vast majority of programmers, though, the simpler approach would be to loop over the queries, run them one at a time, and store the results somewhere. Potentially the local variable would be added to a collection of counts, for example.

FOR q IN (SELECT sql_text FROM query_table)
LOOP
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || q.sql_text || ')'
     INTO some_local_variable;
  <<do something with the local variable>>
END LOOP;

Since you're trying to create a view, you could take this logic and put it in a pipelined table function. You'd do a PIPE ROW to return data within the loop. Your view could then be created on top of the pipelined table function.

edited Feb 7, 2014 at 21:07

answered Feb 7, 2014 at 20:31

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rolan Over a year ago

Thanks for the link, it showed me that there were functions that could execute my sql and return the values. As for "pipelining", I haven't yet ventured into that conceptual area. Sounds like something I'll need to do sooner rather than later.

Maheswaran Ravisankar · Accepted Answer · 2014-02-07 20:51:33Z

1

Going through Basics..

When the database executes an SQL
1) It would first do SEMANTIC check by validating the syntax and the objects (tables and columns used) in SQL.
2) Based on it, the optimizer draws a PLAN(calculating the indexes to be used, with the available table statistics and histograms)..
3) And the SQL Engine executes the Query as per the PLAN.

So, all these means, the SQL cannot do dynamic object referings.. Since it needs to study all the elements in SQL before execution itself.

Hence, unfortunately, your requirement is NOT possible with a simple SQL solution.

PL/SQL or some other Database specific special tools.. is what you have to opt for.

answered Feb 7, 2014 at 20:51

Maheswaran Ravisankar

17.9k6 gold badges51 silver badges69 bronze badges

1 Comment

Rolan Over a year ago

A concise, clear and direct answer - Thank you. You were right that a more complex sql solution would be required.

Wernfried Domscheit · Accepted Answer · 2014-02-07 21:55:40Z

0

You can do it in PL/SQL, it can look like this (if I understand your requirement properly):

create table sql_commands (cmd varchar2(1000));
insert into sql_commands values ('select * from table_1');
insert into sql_commands values ('select * from table_2');
commit;

declare
begin
   for aLine in (select cmd from sql_commands) loop
      execute immediate aLine.cmd into ... -- depends on your command
   end loop;
end;

answered Feb 7, 2014 at 21:55

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Can an oracle SQL query execute a string query selected from a table?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related