title: Oracle - Create a procedure to insert into a dimension table from multi tables
body: <p>I am designing a data warehouse for a small e-commerce, and I need to populate a dimension table for products from two tables product, and product_price_history. For simplicity let's say the columns for each table are as follow:</p>
  
  <p>product (prod_id, brand)
  product_price_history (prod_id, price, min_pirce, start_date, end_date)
  product_dim (prod_key, prod_id, brand, price, min_pirce, start_date, end_date)</p>
  
  <p>And I already have a sequence called prod_seq to generate keys for product_dimension table. This is what I have so far for my procedure, but I', getting errors:</p>
  
  <pre><code>create or replace 
  procedure populate_product_dimension AS
  BEGIN
  INSERT into product_dim (prod_key, prod_id, brand, price, min_pirce, start_date, end_date)
  SELECT seq_prod.nextval FROM DUAL
  SELECT prod_id, brand
  FROM product
  SELECT price, min_pirce, start_date, end_date
  FROM product_price_history
  WHERE prod_id.product_pric_history = prod_id.product &amp;
  prod_id not in(select prod_id from product_dim);
  Commit;
  End populate_product_dimension;
  </code></pre>
  
  <p>I think the issue might be due to how I'm adding prod_key. Any one know a better way to do this?</p>