This content has been marked as final. Show 3 replies
Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production
PL/SQL Release 126.96.36.199.0 - Production
CORE 188.8.131.52.0 Production
TNS for Linux: Version 184.108.40.206.0 - Production
NLSRTL Version 220.127.116.11.0 - Production
I have created an object and inserted records in it.
What is the advantage of of doing so?
You do not have to use "Delete" statement to delete the values from collection. Oracle provides "delete" method and also you should initialize the collection before populating it; See below
CREATE OR REPLACE TYPE test_type AS OBJECT ( col1 number, col2 varchar2(10) ); CREATE OR REPLACE TYPE tab_type is table of test_type; DECLARE test_tab tab_type:=tab_type(); l_cnt NUMBER; BEGIN select test_type(col1,col2) bulk collect into test_tab from (select 1 as col1,'test1' as col2 from dual union all select 2,'test2' from dual); DBMS_OUTPUT.PUT_LINE('Before deleting:'||test_tab.count); IF test_tab.count>0 THEN test_tab.delete(1); END IF; DBMS_OUTPUT.PUT_LINE('After deleting:'||test_tab.count); l_cnt := test_tab.count; END;
Edited by: Sg049 on Feb 20, 2013 2:57 PM
o/p Before deleting:2 After deleting:1
I have a scenario wherein I insert few records into a collection table. I'm gonna filter few records from collection table based on the filter.
I want to delete the records that didn't match the filter. Right now, I'm inserting the records into a physical table and deleting from there. I do no want to use a physical table. Trying to avoid it.
Would like to delete from collection itself.
DELETE FROM TABLE(CAST(lv_attr_filter_tab as EDMS_CSPP_DISC_REQ_TAB_TYPE)) WHERE NOT EXISTS (SELECT 1 FROM edms_disc_lines_stg edls WHERE edls.req_id = edrg.request_id AND edls.disc_line_id = edrg.discount_id AND UPPER(edls.disc_status) IN ('ACTIVE');