Solved

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

Posted on 2008-06-17
3
939 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now