Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Collections and Conditional Deletes (RDBMS 9iR2 on Windows NT 2003 Server)
PL/SQL Collections and Conditional Deletes [message #321282] Mon, 19 May 2008 13:47 Go to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Good Afternoon -

I have a question regarding associative arrays and the ability to conditionally delete elements in such an array. If I define a PL/SQL index by table in code and I bulk collect into a variable and then execute a FOR i IN c.FIRST .. c.LAST loop, can I delete an unknown number of elements from the array based on a specific condition? An example may be able to better explain the scenario:

BEGIN

    DECLARE
    
        TYPE r_record IS RECORD (employee_id         NUMBER,
                                 employee_first      VARCHAR2(100),
                                 employee_last       VARCHAR2(100)
                                );
                                
        TYPE t_table IS TABLE OF r_record INDEX BY BINARY_INTEGER;
        
        l_record_set          	   			t_table;   
        l_syscursor         				sys_refcursor;
        
    BEGIN
    
        OPEN l_syscursor FOR
        'SELECT'||
        ' employee_id,'||
        ' employee_first,'||
        ' employee_last '||
        'FROM'||
        ' some_table '||
        'ORDER BY'||
        ' 3';
        
        FETCH l_syscursor BULK COLLECT
         INTO l_record_set;
        CLOSE l_syscursor;
        
        FOR n IN l_record_set.FIRST .. l_record_set.LAST LOOP
        
            IF l_record_set (n).employee_last = 'KRINGLE' THEN
            
                -- Required: Remove all elements in collection WHERE last_name = 'KRINGLE' 
                
            END IF;
            
        END LOOP;
        
     END;
     
END;


One final caveat to this issue is that when analyzing the employee_last value in the IF statement, the indexed employee_last value may not be the first instance of KRINGLE found in the collection. I know I ordered by employee_last when I opened the cursor, but this example is a simplified version of the real-time issue I am trying to overcome. I would still delete all elements from the collection where employee_last = 'KRINGLE'.

Thoughts?

Thank you,
Steve
Re: PL/SQL Collections and Conditional Deletes [message #321293 is a reply to message #321282] Mon, 19 May 2008 15:58 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Hi,

You could alter the cursor to just not read the required records? SQL is a much more powerful filter than conditions on an array. However I am presuming you want to do something else with the records.

If there is something you need to do with the records, then you can use the .DELETE operation of a collection.

If you are planning the BULK insert the records back to a table, you will need to be using 10g, and use the new forall syntax for inserting sparse arrays.(10gR1 and less cannot bulk insert a sparse array into a table) If you are using less than 10gR2, you will need to copy the array to a new array to bulk insert the records.

Of course, I have no idea what you plan to do with the data, but thought I'd cover a few bases for you.
Re: PL/SQL Collections and Conditional Deletes [message #321294 is a reply to message #321282] Mon, 19 May 2008 16:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following is what you asked for, but I have to suspect that you may be doing things the hard way. If you could provide the full requirements, perhaps other solutions could be offered. For example, there are other collection types that do not have the sparse problem and if based on sql types, you can use the table function. Also, if you can index on the last name, then you can delete conditionally based on the last name using the index, instead of looping through the whole collection.

SCOTT@orcl_11g> CREATE TABLE some_table AS
  2  SELECT empno AS employee_id,
  3  	    job   AS employee_first,
  4  	    ename AS employee_last
  5  FROM   emp
  6  /

Table created.

SCOTT@orcl_11g> INSERT INTO some_table VALUES (1, 'CHRIS', 'KRINGLE')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO some_table VALUES (9999, 'CHRISTOPH', 'KRINGLE')
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> BEGIN
  2  
  3  	 DECLARE
  4  
  5  	     TYPE r_record IS RECORD (employee_id	  NUMBER,
  6  				      employee_first	  VARCHAR2(100),
  7  				      employee_last	  VARCHAR2(100)
  8  				     );
  9  
 10  	     TYPE t_table IS TABLE OF r_record INDEX BY BINARY_INTEGER;
 11  
 12  	     l_record_set				     t_table;
 13  	     l_syscursor				     sys_refcursor;
 14  
 15  	 BEGIN
 16  
 17  	     OPEN l_syscursor FOR
 18  	     'SELECT'||
 19  	     ' employee_id,'||
 20  	     ' employee_first,'||
 21  	     ' employee_last '||
 22  	     'FROM'||
 23  	     ' some_table '||
 24  	     'ORDER BY'||
 25  	     ' 3';
 26  
 27  	     FETCH l_syscursor BULK COLLECT
 28  	      INTO l_record_set;
 29  	     CLOSE l_syscursor;
 30  
 31  	     --
 32  
 33  	     DBMS_OUTPUT.PUT_LINE ('BEFORE:');
 34  	     FOR i IN l_record_set.FIRST .. l_record_set.LAST LOOP
 35  	       DBMS_OUTPUT.PUT_LINE (i || ' ' || l_record_set(i).employee_last);
 36  	     END LOOP;
 37  
 38  	     --
 39  
 40  	     FOR n IN l_record_set.FIRST .. l_record_set.LAST LOOP
 41  		 IF l_record_set.EXISTS(n) AND l_record_set (n).employee_last = 'KRINGLE' THEN
 42  		     -- Required: Remove all elements in collection WHERE last_name = 'KRINGLE'
 43  		     FOR i IN l_record_set.FIRST .. l_record_set.LAST LOOP
 44  		       IF l_record_set.EXISTS(i) AND l_record_set(i).employee_last = 'KRINGLE' THEN
 45  			 l_record_set.DELETE (i);
 46  		       END IF;
 47  		     END LOOP;
 48  		     --
 49  		 END IF;
 50  	     END LOOP;
 51  
 52  	     --
 53  
 54  	     DBMS_OUTPUT.PUT_LINE ('------------------------------------------------');
 55  	     DBMS_OUTPUT.PUT_LINE ('AFTER:');
 56  	     FOR i IN l_record_set.FIRST .. l_record_set.LAST LOOP
 57  	       IF l_record_set.EXISTS(i) THEN
 58  		 DBMS_OUTPUT.PUT_LINE (i || ' ' || l_record_set(i).employee_last);
 59  	       END IF;
 60  	     END LOOP;
 61  
 62  	     --
 63  
 64  	  END;
 65  
 66  END;
 67  /
BEFORE:
1 ADAMS
2 ALLEN
3 BLAKE
4 CLARK
5 FORD
6 JAMES
7 JONES
8 KING
9 KRINGLE
10 KRINGLE
11 MARTIN
12 MILLER
13 SCOTT
14 SMITH
15 TURNER
16 WARD
------------------------------------------------
AFTER:
1 ADAMS
2 ALLEN
3 BLAKE
4 CLARK
5 FORD
6 JAMES
7 JONES
8 KING
11 MARTIN
12 MILLER
13 SCOTT
14 SMITH
15 TURNER
16 WARD

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 



Re: PL/SQL Collections and Conditional Deletes [message #321306 is a reply to message #321294] Mon, 19 May 2008 19:28 Go to previous message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Hi Coleing and Barbara -

Thank you both for your replies, I appreciate the input as I am always looking to use different techniques to accomplish tasks. I'm sorry I didn't post a more thorough explanation of what I was trying to accomplish. Essentially, I am validating distribution line level detail on data in the purchasing module of EBS 11i. In this program, I have staged data to this level of detail and am performing about a dozen validations against each record. If one distribution line fails one of the validations, I want to mark the entire document in the staging table with a specific error flag value and error message. The array I am dealing with already contains the header level identifier, and this is my 'KRINGLE' from the example. I want to cease validation once the error is encountered, mark the entire document with the same error message (as each PO can contain many distributions), and then begin validation against the next document in the array.

I would love to learn more about other collection types, and have been reading about a few, but haven't necessarily gotten to delve too deep into them as I inherited a project that is on a tight deadline. I am hoping next year to rewrite pieces of this application such that it is robust and optimized, but one nice thing about this project is that the entire application will be shut down and I will be the only user allowed access to the database during execution.

Again, thank you both for your replies, I appreciate the time and effort.

Regards,
Steve
Previous Topic: variable in a view
Next Topic: quering list partition
Goto Forum:
  


Current Time: Sun Dec 11 00:40:04 CST 2016

Total time taken to generate the page: 0.15524 seconds