title: ways to avoid global temp tables in oracle
body: <p>We just converted our sql server stored procedures to oracle procedures.  Sql Server SP's were highly dependent on session tables (<code>INSERT INTO #table1...</code>) these tables got converted as global temporary tables in oracle.  We ended up with aroun 500 GTT's for our 400 SP's</p>
  
  <p>Now we are finding out that working with GTT's in oracle is considered a last option because of performance and other issues.  </p>
  
  <p>what other alternatives are there? Collections? Cursors?</p>
  
  <p>Our typical use of GTT's is like so:</p>
  
  <p>Insert into GTT</p>
  
  <pre><code>INSERT INTO some_gtt_1
    (column_a,
     column_b,
     column_c)
    (SELECT someA,
        someB,
        someC
       FROM TABLE_A
      WHERE condition_1 = 'YN756'
        AND type_cd = 'P'
        AND TO_NUMBER(TO_CHAR(m_date, 'MM')) = '12'
        AND (lname LIKE (v_LnameUpper || '%') OR
        lname LIKE (v_searchLnameLower || '%'))
        AND (e_flag = 'Y' OR
        it_flag = 'Y' OR
        fit_flag = 'Y'));
  </code></pre>
  
  <p>Update the GTT</p>
  
  <pre><code>UPDATE some_gtt_1 a
  SET column_a = (SELECT b.data_a FROM some_table_b b 
                 WHERE a.column_b = b.data_b AND a.column_c = 'C')
  WHERE column_a IS NULL OR column_a = ' ';
  </code></pre>
  
  <p>and later on get the data out of the GTT.  These are just sample queries, in actuality the queries are really complext with lot of joins and subqueries. </p>
  
  <p>I have a three part question:</p>
  
  <ol>
  <li>Can someone show how to transform
  the above sample queries to
  collections and/or cursors? </li>
  <li>Since
  with GTT's you can work natively
  with SQL...why go away from the
  GTTs? are they really that bad.  </li>
  <li>What should be the guidelines on
  When to use and When to avoid GTT's</li>
  </ol>