Oracle Dynamic SQL Question

So my question is:

If the requirement is as simple as

--selecting unique rows from table1
--inserting into temp table
--populatinf rest of data
--displaying temp table data

Then why you need to go for a temp table ? Do it in a single SQL statement, like:

SELECT <Whatever>, CASE WHEN col1 IS NULL THEN <Populate data here>
FROM table1

Here i supposed we want to populate col1 if the value is null (you can adjust accordingly).

Or better you tell us the complete scenario (e.g; what needs to be populated and from where) and maybe we will be able to help you more

I will try these suggestions.
The table I'd like to select from needs 7 other table joins, which then adds unwanted records. This is why I was thinking about a temp table!
Will provide more detail after experimenting.

If you are stuck on temp tables, you might want to read up on Global Temporary tables in Oracle:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/tables.htm#7832

That said, I've never used them and haven't heard of to many others that have.  From my limited understanding of SQL Server, they are pretty widely used to make up for some deficiencies that Oracle can usually get around by other means.

bglodde,
    You almost used the terminology "union" when you said "Loop through the unique records in the temp table, using the value as the key for doing another query to fill in the rest of the data" and it may be that the union operator is what you need.  Let's say that I have a series of tables that have customer contact information in it.  I want to find all of the customers who have had unsatisfactory contacts.  I don't want to see any duplicates, but I want to see all of them.  Of course, since each customer contact was developed uniquely for the system it supported, each one has a different mechanism for finding the result of the contact.  A join of all these tables would be incredibly complex, although getting the information from EACH is realtively straightforward:

select name,address1,city,state,zipcode from customer_master,
  (select custid from call_in,call_notes where call_in.call_id=call_notes.call_id and dispensation < 100
    union
    select custid from outbound_calls market, call_history hist where market.callid = hist.call_id and <more stuff>
   union
   select custid from complaint_letters, <you get the idea) apology_letters
 where apology_letters.custid = customer_master.custid
order by zipcode;

Since union only returns distinct records, this will give you a "temporary" table to select from of the unique custid without having to create a join (that may be multiply-outer joined).

Good luck!
DrSQL

for an example, if you want to create a temp table with 3 columns name,age,sex, then

1. create a datatype in pl/sql., outside of your procedure in sql prompt,

CREATE OR REPLACE TYPE v_object AS OBJECT (name varchar2(20),age number,sex varchar2(2));

CREATE OR REPLACE TYPE v_table AS TABLE OF v_object;

now these two types will be created for you!!

2. Use this temp table v_table to capture your unique records from your select statement in the procedure.,

CREATE OR REPLACE PROCEDURE check_temp_table IS

v_tbl v_table:=v_tbl(v_object(null,null,null));

CURSOR UniquekeysFromMyfirstSELECT IS
 select ... ; //assume you'r selecting only name, age.. and sex will be identified by another query!!

BEGIN
  v_tbl.trim;
  v_tbl.extend ;
  OPEN UniquekeysFromMyfirstSELECT ;
  LOOP
  FETCH UniquekeysFromMyfirstSELECT INTO v_tbl(v_tbl.last).name,v_tbl(v_tbl.last).age;
  EXIT when UniquekeysFromMyfirstSELECT%NOTFOUND;
    SELECT sex INTO v_tbl(v_tbl.last).sex
    from ..

   v_tbl.extend;
 END LOOP;
 CLOSE UniquekeysFromMyfirstSELECT ;
END;


This is for going over a record by record, if you want to do bulk collect, use VARRAY instead of the above approach!!

to open a ref cursor on the top of the temp table you constructed, use

open REF_CURSOR_VAR for select * from TABLE(CAST(v_tbl as v_table));