How to preserve leading zeros in a CSV file using Oracle

Some time back i was working on alerts which will extract the data from Oracle and send alert extracts to the users via email as attachments in 'CSV' format. But when i received the notifications i noticed that the leading zeros in the segment values of the code combinations were truncated.

After some research i found that when ever the Excel opens up a CSV file it defaults all cells to "General" formatting and removes the leading zeros on cell values thus converting them from strings to numbers.

The simple solution is to SELECT the value such that Excel will see it as a string and not a number. So we prefixed an equals sign and double quote on to the value and post fix a double quote. So

SELECT xx_column
FROM xx_table

becomes

SELECT '="'||xx_column||'"'
FROM xx_table

PL/Sql Script to assign responsibilities to Oracle Users

DECLARE
l_user_id NUMBER;

CURSOR cur_rec
IS
SELECT *
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER ('XXXXX');
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'XXXX';

FOR rec_cur IN cur_rec
LOOP
fnd_user_resp_groups_api.insert_assignment
(user_id => l_user_id,
responsibility_id => rec_cur.responsibility_id,
responsibility_application_id => rec_cur.application_id,
security_group_id => 0,
start_date => SYSDATE - 1,
end_date => NULL,
description => NULL
);
COMMIT;
END LOOP;
END;