title: Missing Keyword in EXECUTE IMMEDIATE SELECT INTO Statement - Oracle PL/SQL
body: <p>The query below is a simplified version of the PL/SQL I am trying to run that just shows the general structure. In short, I am trying to extract information from <code>SPECIAL_TABLE</code> into the variables <code>c, d</code>.</p>
  
  <pre><code>DECLARE
    c NUMBER;
    d NUMBER;
  BEGIN
    FOR all_tab IN
    (SELECT * FROM all_tables)
    LOOP
      BEGIN
        EXECUTE IMMEDIATE 'SELECT a, d INTO c, d
        FROM ' || :name || '.SPECIAL_TABLE WHERE name = '''
        :name || ''' AND table_name = ''' || all_tab.table_name || ''';
  
      ...
  
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED');
      END;
  END;
  </code></pre>
  
  <p>The above query does not work (by above query I mean the <code>EXECUTE IMMEDIATE</code>), and Oracle states <code>905 missing keyword</code>. Following the flow <a href="https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm" rel="nofollow noreferrer">here</a> is followed as far as I can tell. Note that the <code>'''</code> is so that I can escape a single <code>'</code> for the <code>SELECT</code> statement, and then finish off the whole string all together. What is strange, is that I can follow the below code and run into no errors:</p>
  
  <pre><code>DECLARE
    c NUMBER;
    d NUMBER;
  BEGIN
    FOR all_tab IN
    (SELECT * FROM all_tables)
    LOOP
      BEGIN
        EXECUTE IMMEDIATE 'SELECT COUNT(*)
        FROM ' || :name || '.SPECIAL_TABLE WHERE name = '''
        :name || ''' AND table_name = ''' || all_tab.table_name || ''' INTO c;
  
      ...
  
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED');
      END;
  END;
  </code></pre>
  
  <p>The only thing that changes is that I am using <code>MAX(...)</code>, and seemingly no longer following the specified syntax in the above hyperlink. I'm not sure about the syntax part and why exactly this works, and <code>MAX(...)</code> would ensure only returning a single row. It seems like this would be the source of the problem, but when I perform the <code>SELECT</code> query by itself it only returns a <em>single row</em> with the two columns specified. The primary key for the table is <code>PRIMARY KEY (a, b)</code> so it shouldn't return more than one row regardless.</p>
  
  <p>What am I missing here and how can I get my initial statement to work to assign the result of a query into multiple variables in Oracle 11g?</p>