We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
create or replace TYPE ID_T AS TABLE OF varchar(32); / create or replace PROCEDURE DELETE_ALL_RECORDS_BY_USER( USER_ID_TO_DELETE IN VARCHAR2, PROJECT_ID IN VARCHAR2) AS deleteable_l_ids id_t; deleteable_s_ids id_t; boolean char := 0; rec_count NUMBER; BEGIN /** * Get l_ids for user */ SELECT DISTINCT(L_ID) bulk COLLECT INTO deleteable_l_ids FROM lkjb WHERE u_id = u_id_to_delete; /** * Get s_ids for user */ SELECT DISTINCT(S_ID) bulk COLLECT INTO deleteable_s_ids FROM slk WHERE l_id in (SELECT * FROM TABLE(deleteable_l_ids)); select count(*) into rec_count from o where o.s_id in (select * FROM TABLE(deleteable_s_ids)) dbms_output.put_line('o records deleted = ' || rec_count); --WORKS!! delete FROM o where o.sub_id in (select * FROM TABLE(deleteable_s_ids)); --FAILS!! --commit EXCEPTION when others then ROLLBACK; DBMS_OUTPUT.PUT_LINE('delete_all_records_by_user_errors ' || SQLCODE || ' , ' || substr(SQLERRM,1,1000)); --at 10g, add this for line numbers; "format_error_back_trace function" END DELETE_ALL_RECORDS_BY_USER;
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.