Home » SQL & PL/SQL » SQL & PL/SQL » Update collections using TABLE operator (Oracle 11.2.0.2.0 Solaris)
Update collections using TABLE operator [message #573713] Wed, 02 January 2013 11:28 Go to next message
uman2631
Messages: 4
Registered: November 2011
Junior Member
I am trying to update a collection using SQL notation and the TABLE() operator.
Is this possible? I can select from the collection just fine. I have seen
examples of updating real tables from a collection, and updating real tables
with a column containing a collection, but no examples of actually updating a collection.

Any suggestions appreciated. Thanks!

Example:

CREATE OR REPLACE TYPE cvrg_wrk_typ AS OBJECT (
   src_id varchar2(60),
   src_cd varchar2(4),
   acct_num varchar2(10));
   
CREATE OR REPLACE TYPE CIF_SM_P.cvrg_wrk_tab is table of cvrg_wrk_typ;

DECLARE
key_wrk_tbl cvrg_wrk_tab;

CURSOR c1 IS
   SELECT * 
     FROM TABLE(key_wrk_tbl) t
     where t.src_cd = 'PCR'; 
         
BEGIN
   key_wrk_tbl := cvrg_wrk_tab();
   key_wrk_tbl.extend;
   key_wrk_tbl(1) := ( 
          cvrg_wrk_typ(src_id=>'12345',
                       src_cd=>'CED',
                       acct_num=>'90210'));
  
   key_wrk_tbl.extend;
   key_wrk_tbl(2) := ( 
          cvrg_wrk_typ(src_id=>'45678',
                       src_cd=>'PCR',
                       acct_num=>'80111'));
   

   FOR c_rec IN C1 LOOP
      dbms_output.put_line('src_id: ',c_rec.src_id);
   END LOOP;

   /* This update statement does not compile (ORA-00903 invalid table) */
   UPDATE TABLE(key_wrk_tbl)
      SET t.acct_num = '10000'
    WHERE t.src_cd = 'PCR';

   /* Neither does this */
   UPDATE TABLE(key_wrk_tbl) e
     SET value(e) = 
          cvrg_wrk_typ(src_id=>src_id,
                       src_cd=>src_cd,
                       acct_num=>'10000')
   WHERE e.src_cd = 'PCR';

   /* Nor this */
   UPDATE TABLE(CAST key_wrk_tbl as cvrg_wrk_typ) e
     SET value(e) = 
          cvrg_wrk_typ(src_id=>src_id,
                       src_cd=>src_cd,
                       acct_num=>'10000')
   WHERE e.src_cd = 'PCR';

END; 


Cheers,

Ulysses
Re: Update collections using TABLE operator [message #573714 is a reply to message #573713] Wed, 02 January 2013 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59816
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am trying to update a collection using SQL notation and the TABLE() operator.
Is this possible?


No.

Regards
Michel
Re: Update collections using TABLE operator [message #573717 is a reply to message #573713] Wed, 02 January 2013 12:13 Go to previous message
Solomon Yakobson
Messages: 2069
Registered: January 2010
Senior Member
uman2631 wrote on Wed, 02 January 2013 12:28
Any suggestions appreciated. Thanks!


DECLARE
    key_wrk_tbl  cvrg_wrk_tab;
    temp_wrk_tbl cvrg_wrk_tab;
BEGIN
    key_wrk_tbl := cvrg_wrk_tab();
    key_wrk_tbl.extend;
    key_wrk_tbl(1) := ( 
                       cvrg_wrk_typ(
                                    src_id=>'12345',
                                    src_cd=>'CED',
                                    acct_num=>'90210'
                                   )
                      );
    key_wrk_tbl.extend;
    key_wrk_tbl(2) := ( 
                       cvrg_wrk_typ(
                                    src_id=>'45678',
                                    src_cd=>'PCR',
                                    acct_num=>'80111'
                                   )
                      );
    dbms_output.put_line('BEFORE');
    dbms_output.put_line('------');
    FOR i IN 1..key_wrk_tbl.COUNT LOOP
      dbms_output.put_line('src_id:   ' || key_wrk_tbl(i).src_id);
      dbms_output.put_line('src_cd:   ' || key_wrk_tbl(i).src_cd);
      dbms_output.put_line('acct_num: ' || key_wrk_tbl(i).acct_num);
    END LOOP;
    SELECT  case t.src_cd
              when 'PCR' then cvrg_wrk_typ(t.src_id,t.src_cd,10000)
              else value(t)
            end
      BULK  COLLECT
      INTO  temp_wrk_tbl
      FROM  TABLE(key_wrk_tbl) t;
    key_wrk_tbl := temp_wrk_tbl;
    dbms_output.put_line('AFTER');
    dbms_output.put_line('-----');
    FOR i IN 1..key_wrk_tbl.COUNT LOOP
      dbms_output.put_line('src_id:   ' || key_wrk_tbl(i).src_id);
      dbms_output.put_line('src_cd:   ' || key_wrk_tbl(i).src_cd);
      dbms_output.put_line('acct_num: ' || key_wrk_tbl(i).acct_num);
    END LOOP;
END; 
/
BEFORE
------
src_id:   12345
src_cd:   CED
acct_num: 90210
src_id:   45678
src_cd:   PCR
acct_num: 80111
AFTER
-----
src_id:   12345
src_cd:   CED
acct_num: 90210
src_id:   45678
src_cd:   PCR
acct_num: 10000

PL/SQL procedure successfully completed.

SQL> 


SY.
Previous Topic: Error handling inside Cursor
Next Topic: Calculate Working Day
Goto Forum:
  


Current Time: Fri Nov 28 07:49:59 CST 2014

Total time taken to generate the page: 0.27703 seconds