title: Porting MySQL Stored Procedure to Oracle
body: <p>I'm trying to port a stored procedure from MySQL to Oracle, and I'm having a lot of trouble. I've gone through Oracle documentation, and I'm having trouble doing very basic things like declaring variables properly. I was hoping someone could show me how to properly declare and set variables.</p>
  
  <p>My stored procedure is used to add values to two different tables and ensure that it's being mapped properly and the foreign keys aren't being violated.</p>
  
  <p>Here is my MySQL Code:</p>
  
  <pre><code> CREATE DEFINER=root@% PROCEDURE proc_add_entry(IN theName vARCHAR(50), IN theKey VARCHAR(50), IN theOtherData VARCHAR(50), IN theOtherData2 INT, IN theStartDate DATE, IN theEndDate DaTE, IN theReferenceDate DaTE)
       LANGUAGE SQL
       NOT DETERMINISTIC
       CONTAINS SQL
       SQL SECURITY DEFINER
   BEGIN
   declare theNameID int ;
   declare theKeyID int ;
   declare theOtherDataID int default null;
   declare error bool default false;
   declare continue handler for SQLEXCEPTION
      set error = true;
  
      set theKeyID = (select KeyID from map_alias ma where ma.alias = trim(theKey));
      set theOtherDataID = (select theOtherDataID from map_otherdata mc where mc.otherdata = trim(theOtherData));
  
      set theNameID = (select max(nameID) from inserttable);
      set theNameID = theNameID + 1;
      insert into inserttable values (theNameID , theKeyID , theOtherDataID , theOtherData2, theStartDate , 
      theEndDate , theReferenceDate);
  
      if error = true then
          insert into errors_inserttable values (theNameID , theKeyID , theOtherDataID , theOtherData2, theStartDate , 
      theEndDate , theReferenceDate);
      end if;
  
      set error = false;
      insert into map_inserttable (theNameID , datasourceid, theName) values (theNameID , 1, theName);
      if error = true then
          insert into errors_map_inserttable  (theNameID , datasourceid, theName) values (theNameID , 1, theName);
      end if;
  
   END
  </code></pre>
  
  <p>In Oracle, my last statement are being ignored (ORA-00922: Missing or invalid option). It should be a local variable, so I'm not sure why i'm getting that particular error.</p>
  
  <p>I'm struggling to declare the continue handler as well. I'm getting the error:          </p>
  
  <pre><code> Error(16,27): PLS-00103: Encountered the symbol "FOR" when expecting one of the following:     := . ( @ % ; not null range default character.
  </code></pre>
  
  <p>Here is my oracle code so far:</p>
  
  <pre><code> CREATE OR REPLACE PROCEDURE PROC_ADD_ENTRY
   (
     THENAME IN VARCHAR2  
   , THEKEY IN VARCHAR2  
   , THEOTHERDATA IN VARCHAR2  
   , THEOTHERDATA2 IN NUMBER  
   , THEFIRSTDATE IN DATE  
   , THELASTDATE IN DATE  
   , THEREFERENCEDATE IN DATE  
   ) AS
    THENAMEID INT;
    THEKEYID INT;
    THEOTHERDATAID int;
    ERROR bool default false;
   BEGIN
   declare continue HANDLER FOR SQLEXCEPTION set error = true;
  
  
  
     set THEKEYID = (select KEYID from map_INSERTTABLE mc where mc.Key = trim(THEKEY));
   END PROC_ADD_ENTRY;
  </code></pre>
  
  <p>I'm sure this is stupidly simple for someone that uses oracle, but I'm reading the documentation and I'm seeing conflicting information on where and how to declare variables, continue handlers, and assign values to variables. (is it := or = to assign values? Do i use the word declare after the begin statement to declare variables, or do I do it the way I show below?)</p>
  
  <p>If someone could show me:</p>
  
  <p>a) where to declare a local variable</p>
  
  <p>b) how to assign a value to it (i.e. 1 to an int)</p>
  
  <p>c) how to assign a value from the DB to a variable (set var = select number from table_number tn where tn.number = 1)</p>
  
  <p>d) how to declare a continue handler properly</p>
  
  <p>I would really appreciate it.</p>