0

I would like it if the code below created tables that reflected the amount or rows in table 'NAMES'.

Example: Starting empty after inserting 1 row in to NAMES a table called t1 should be created.

Instead this will create a table named t0 because the procedure runs before that changes to NAMES are committed. How could I get this procedure to run after the rows are committed?

CREATE TABLE NAMES
(
    TABLENAMES varchar(1)
);

/
CREATE OR REPLACE PROCEDURE CREATETABLES AS  
  NAMESROWS NUMBER(3);
 BEGIN

   SELECT COUNT(*) INTO NAMESROWS  FROM NAMES;

   EXECUTE IMMEDIATE 'CREATE TABLE t' || NAMESROWS || ' ( bCOL NUMBER(1))';

END CREATETABLES;

/
CREATE OR REPLACE TRIGGER CREATETABLETRIGGER
 AFTER INSERT OR UPDATE OR  DELETE ON NAMES
  DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    CREATETABLES;
  END;
/
INSERT INTO NAMES VALUES (2);
Nick
  • 3,217
  • 5
  • 30
  • 42
  • Your autonomous transaction can't see the newly-inserted rows. You could just add one to the count, but what if two sessions insert at once? Or a row is delerted? This seems like a horrible thing to be doing anyway - why do you want to create tables on the fly, and why based on other table data? What problem are you really trying to solve with this plan? – Alex Poole Feb 28 '17 at 23:09
  • @AlexPoole The real code dynamically creates views based on information spread across 3 or 4 tables and is too long for a SO question. The problem is the views are always an update (or insert, or delete) old. The code above is just a simplified example of my problem. – Nick Feb 28 '17 at 23:33
  • 1
    Creating any object in the fly seems wrong,. But if there is a real need for this maybe you can have a procedure that (re)creates the view, and have your trigger create a scheduked job that call the procedure? [Similar to this](http://stackoverflow.com/a/9263285/266304). I would really investigate any way to avoid doing this though. Without know why the views needs to be created in the first place it's hard to suggest alternatives. – Alex Poole Feb 28 '17 at 23:51
  • I'd love to know the business case for this... – BobC Mar 01 '17 at 00:01
  • @AlexPoole thanks! this was a great suggestion.@BobC our application lets users define the type and attributes of the data they are working with. So if a pet store bought the app it would not have a dog or cat table. Information about the dogs and cats is actually spread across multiple tables. If someone wanted to inspect the dogs in the database they would have to write a 4 table join to do it. This code makes a view called dogs which has all the dog information in it. – Nick Mar 01 '17 at 13:41
  • OK, good; I think the scenario is close enough to close as a duplicate of the question that answer was for? – Alex Poole Mar 01 '17 at 14:15
  • @AlexPoole I don't think its a duplicate question at all. Due to the way oracle works I think both problems are forced to have the same workarounds but the questions are not the same. – Nick Mar 01 '17 at 15:33
  • @Nick - OK, I wasn't sure, so hadn't closed it yet. But I'm not sure how to answer without pretty much duplicating what Justin wrote before. Assuming that approach worked for you, I guess you could self-answer with what you ended up doing? In the meantime I'll see if I can find a better dupe target... – Alex Poole Mar 01 '17 at 15:35
  • @AlexPoole the closest I found was http://stackoverflow.com/questions/4098771/how-to-define-a-trigger-on-commit-in-oracle it even suggested the same workaround that your link did but that questions ask for a before commit trigger as opposed to this question. – Nick Mar 01 '17 at 16:05

0 Answers0