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

chrismarxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
see this link :

http://www.oracle-base.com/forums/viewtopic.php?f=2&t=6731&start=0&st=0&sk=t&sd=a

try :

delete FROM o where o.sub_id in (select * FROM TABLE(cast(deleteable_s_ids) as ID_T);
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
chrismarxAuthor Commented:
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!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.