title: Run SQL through JDBC, how to parameterize Table Names
body: <p>If I have two tables:</p>
  
  <pre><code>CREATE TABLE Test_Persons_A 
  (
      PersonID int,
      LastName varchar(255),
      FirstName varchar(255)
  );
  
  INSERT INTO Test_Persons_A (PersonID, LastName, FirstName)
  VALUES (11, 'LN_A1', 'FN_A1');
  
  INSERT INTO Test_Persons_A (PersonID, LastName, FirstName)
  VALUES (12, 'LN_A2', 'FN_A2');
  
  CREATE TABLE Test_Persons_B 
  (
      PersonID int,
      LastName varchar(255),
      FirstName varchar(255)
  );
  
  INSERT INTO Test_Persons_B (PersonID, LastName, FirstName)
  VALUES (21, 'LN_B1', 'FN_B1');
  
  INSERT INTO Test_Persons_B (PersonID, LastName, FirstName)
  VALUES (22, 'LN_B2', 'FN_B2');
  
  commit;
  </code></pre>
  
  <p>The effect I want to achieve is equivalent to executing each the following two queries from java code through JDBC (oracle) </p>
  
  <p>Block A:</p>
  
  <pre><code>select PersonID as PID, LastName as LN, FirstName as FN
      from Test_Persons_A tp
      where tp.LASTNAME like '%1%'
  </code></pre>
  
  <p>Block B:</p>
  
  <pre><code>select PersonID as PID, LastName as LN, FirstName as FN
      from Test_Persons_B tp
      where tp.LASTNAME like '%2%'
  </code></pre>
  
  <p>You can see that the only difference are:</p>
  
  <ul>
  <li>Table Name</li>
  <li>LASTNAME cretiria</li>
  </ul>
  
  <p>But in my case, each of the the blocks are in fact a huge 'With...Select...' clause, and the java code (legacy, I can't change an iota) in fact reads each sql block from a .sql file as below before executing it through JDBC. </p>
  
  <ul>
  <li>hugeQueryA.sql</li>
  <li>hugeQueryB.sql</li>
  </ul>
  
  <p>My question is: to avoid duplicating this huge block of code, instead, is it the best to construct a single large stored procedure (or functions)?</p>
  
  <ul>
  <li>hugeStoredProc</li>
  </ul>
  
  <p>put it in my DB, then construct two simple sql</p>
  
  <ul>
  <li>simpleQueryA.sql</li>
  <li>simpleQueryB.sql</li>
  </ul>
  
  <p>to call the stored procedure with parameters (specified in each of the sql)? </p>
  
  <ul>
  <li><p>Can I do this without having to use Dynamic SQL (since to do that I assume I will need change the content of the original huge sql files into strings and handle all the special chars - plus, would that also going to look awful?)?  </p></li>
  <li><p>More generally, when I need to 'parameterize' the table name, and can't use 'substitution variables', is dynamic SQL the only way to go?</p></li>
  </ul>