oracle-plsql-map.sql

CREATE OR REPLACE PROCEDURE mystoredproc
IS
      CURSOR template_cur IS
             SELECT folder_id, (ROWNUM+100000) new_folder_id
             FROM tcom_folder_template
             WHERE folder_template_id = p_template_id
             ORDER BY folder_id;
      template_cur_rec   template_cur%ROWTYPE;
      TYPE folderid_table IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
      folderid_map folderid_table;
BEGIN
      folderid_map('1'):= 1;
      folderid_map('2'):= 2;
      FOR template_cur_rec IN template_cur
      LOOP
          folderid_map(template_cur_rec.folder_id):=template_cur_rec.new_folder_id;
      END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        dbms_output.put_line('Error: ' || SQLCODE || ' SQL Error Msg: ' || SUBSTR(SQLERRM, 1, 1950));
END mystoredproc;