We just converted our sql server stored procedures to oracle procedures. Sql Server SP's were highly dependent on session tables (INSERT INTO #table1...) these tables got converted as global temporary tables in oracle. We ended up with aroun 500 GTT's for our 400 SP's

Now we are finding out that working with GTT's in oracle is considered a last option because of performance and other issues.

what other alternatives are there? Collections? Cursors?

Our typical use of GTT's is like so:

Insert into GTT

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'));

Update the GTT

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 = ' ';

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.

I have a three part question:

1. Can someone show how to transform the above sample queries to collections and/or cursors?
2. Since with GTT's you can work natively with SQL...why go away from the GTTs? are they really that bad.
3. What should be the guidelines on When to use and When to avoid GTT's