Solved

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

Posted on 2008-06-17
3
951 Views
Last Modified: 2013-12-19
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
Comment
Question by:chrismarx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 21809845
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
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 21809872
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
 

Author Closing Comment

by:chrismarx
ID: 31468153
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question