Solved

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

Posted on 2008-06-17
3
938 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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

771 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

9 Experts available now in Live!

Get 1:1 Help Now