Running SQL based on a query stored within a field?

Question

I'm trying to populate a database with certain values, based on a query stored within another table.

Table1:

ID      SQL                                         Title
1       select ID from userTable where ID = 1       Query 1
2       select ID from userTable where ID > 7       Query 2

I basically need to grab the 'SQL' field, and populate another table with information from the above, once the script has run it'll look something like:

ID      userID      Title
1       1           Query 1
2       8           Query 2
2       9           Query 2
2       10          Query 2

And so on.

I hope that makes sense, I have absolutely no clue if it's possible - I've tried creating it as a procedure but I can't find anyway whatsoever to run the 'SQL' code from table 1 in the procedure.

Alternatively I will do it in PHP, though it'd be preferable to get this into SQL as it's easier to automate.

Thank you

Tony Andrews · Accepted Answer · 2012-04-10 10:02:05Z

3

This is an odd thing to be doing (I would query why) but this code should do it:

declare
  rc sys_refcursor;
  l_id table1.id%type;
begin
  for r_sql in (select id, sql, title from table1) loop
    open rc for r_sql.sql;
    loop
      fetch rc into l_id;
      exit when rc%notfound;
      insert into table2 (id, userid, title) values (r_sql.id, l_id, r_sql.title);
    end loop;
    close rc;
  end loop;
end;

answered Apr 10, 2012 at 10:02

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

1 Comment

Nick Over a year ago

Ah, you're a star - thank you so much, will try to implement this now (as for why, we're trying to populate a reminders/notification section just for certain people)

Collectives™ on Stack Overflow

Running SQL based on a query stored within a field?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related