Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
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.
|migration MS SQL database to Oracle||30||71|
|Trigger to update a specific column in Table B after insert or update record from Table A||13||54|
|error doing substr||3||37|
|SQL query to select row with MAX date||7||42|