BULK COLLECT INTO Help

[szeiss]

Using 10g. I'm trying to use BULK COLLECT INTO to create some variables to use in some calculations and some if..then..else statements. I don't know if I'm using it correctly or not.

DECLARE
TYPE employeeData_type 
  IS TABLE OF temp_table%ROWTYPE 
  INDEX BY PLS_INTEGER;

employeeData  employeeData_type;

BEGIN

First I insert the data into a temporary table along with my username.

INSERT INTO temp_table (
    emp_id,
    last_name,
    first_name,
    username
)
(
SELECT
    emp_id,
    last_name,
    first_name,
    p_username (parameter)
FROM
    emp_data)

Then, I select that data where the username = p_username 

SELECT 
    emp_id,
    last_name,
    first_name
BULK COLLECT INTO
    employeeData
FROM
    temp_table 
WHERE
    username = p_username
)

Then I insert that data into the actual table for my report.

INSERT INTO actual_employee_table
    emp_id,
    last_name,
    first_name,
    username
)
(
SELECT DISTINCT
    emp_id,
    last_name,
    first_name,
    p_username
FROM
    temp_table 
WHERE
    temp_table.username = p_username
)

Then I'd like to use certain data from the second select to do some If..Then..Else statement updates on the actual_employee_table.

For example:

If employeeData.emp_id is not null THEN........

Can someone help?

Thanks,
SZ

 

[Dagon]

Why are you selecting from temptable into variables but not using those variables in the insert ? Why don't you just insert directly from temp_table to actual_employee_table using insert..select ? Moreover, why don't you just copy the data directly from emp_data to actual_employee_table using an insert..select ?

For Oracle-related work, contact me through Linked-In.

 

[szeiss]

This is a just an example of what I want to do, not the actual queries. There's some other stuff going on in the queries. I'd just like to know if I'm using the BULK COLLECT INTO correctly.

 

[Dagon]

The BULK SELECT looks reasonable.

For Oracle-related work, contact me through Linked-In.

 

[Thargy]

Temporary tables often indicate a hangover from SQL server. Is this some kind of migration from SQL server? If it is, forget all about temporary tables - they are almost never needed in oracle.

Regards

T