asked on

using table() function in proc works for selects, fails for deletes

see below for code

if i define a table type, and then use bulk collect to fill it with records, i can then use the table() function to use that collection in a sql statement. the code below shows how the deleteable_l_ids and deleteable_s_ids are used. the proc doesnt fail until the delete statement, when it gives me

Error starting at line 1 in command:
execute delete_all_records_by_user('123', 'KLJ');
Error report:
ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at "DELETE_ALL_RECORDS_BY_USER", line 41
ORA-06512: at line 1
22905. 00000 - "cannot access rows from a non-nested table item"
*Cause: attempt to access rows of an item whose type is not known at
parse time or that is not of a nested table type
*Action: use CAST to cast the item to a nested table type

how can i fix this?

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;

Open in new window

Naveen Kumar

in 10g, i am not getting any errors for the below code. i am trying to simulate your code sample.

create or replace TYPE ID_T AS TABLE OF varchar(32);
/

create or replace function dd return ID_T is
deleteable_l_ids id_t;
rec_count NUMBER:=-1;
BEGIN

SELECT DISTINCT(l_id) bulk COLLECT INTO deleteable_l_ids FROM t WHERE u_id = 'ADMIN';

delete from other_table where l_id in ( select column_value from table(dd));

return deleteable_l_ids;

end;
/

select * from table(dd);

the above code is able to delete the record from other_table and also i am getting in the sqlplus output what record it is deleting.

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrismarx

ASKER

that was it! on the money! i had tried using cast, but i didnt realize i needed to cast to my custom type. thank you!!!