title: Trying to use EXECUTE IMMEDIATE, cannot compile procedure
body: <p>Trying to write a procedure which takes no values in, adds a sale price column to my existing product table, then loops through to calculate a sale price and insert that into the new column.</p>
  <p>I haven't been able to get anything to work, I think it's something to do with Oracle not liking <code>ALTER TABLE</code> to be run from inside a procedure, but I don't know, and I don't know enough to direct my attempts anywhere else.</p>
  <p>This is my attempt</p>
  <pre><code>CREATE or REPLACE PROCEDURE ProductLineSale as
  BEGIN
      DECLARE
         NewSalePrice NUMBER(6,2):=0; 
      EXECUTE IMMEDIATE 'alter table ' || Product || 'add or replace column' || 'SalePrice NUMBER(6,2);'
      FOR p in (SELECT ProductStandardPrice FROM Product
              group by ProductStandardPrice)
          LOOP
              CASE WHEN p.ProductStandardPrice&gt;=400 THEN NewSalePrice:=.9*price
                   WHEN p.ProductStandardPrice&lt;400 THEN NewSalePrice:=.85*price
              INSERT INTO Product(SalePrice)
              VALUES(NewSalePrice)
          END LOOP;
  END ProductLineSale
  </code></pre>
  <p>Product is the literal name of the Product table in my database. SalePrice is what I would like the new column to be named.</p>
  <p>SQLDeveloper won't compile the procedure. The error I get is fairly cryptic as well:</p>
  <blockquote>
  <p>Error(2,10): PLS-00103: Encountered the symbol &quot;=&quot; when expecting one of the following:     constant exception      table long double ref    char time timestamp interval date binary national character    nchar.</p>
  </blockquote>