[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 965
  • Last Modified:

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

0
chrismarx
Asked:
chrismarx
  • 2
1 Solution
 
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
 
Naveen KumarProduction 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
 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now