Home » SQL & PL/SQL » SQL & PL/SQL » Regarding bulk update
Regarding bulk update [message #243438] Thu, 07 June 2007 05:31 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I wanna do a bulk update on a table

I wanna update more than one column , when I bulk collect the data into a table type and use it in a forall update statement it throws an error

  1  declare
  2     type t1_t is table of fnd_all%rowtype;
  3     t1 t1_t;
  4     cursor fnd_cursor is 
            select * from fnd_all;
  5  begin
  6     open fnd_cursor;
  7     fetch fnd_cursor bulk collect into t1;
  8     forall i in 1..t1.count
  9         update fnd_application set 
            application_short_name=t1(i).application_short_name,
            last_updated_by=t1(i).last_updated_by;
 10* end;

SQL> /

update fnd_application set application_short_name=t1(i).application_short_name,last_updated_by=t1(i).last_updated_by;
*
ERROR at line 9:
ORA-06550: line 9, column 96:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records



Let me know if I have to declare a table type for each and every column that I am updating ? . In that case should i declare a cursor for each column of the table ?
Re: Regarding bulk update [message #243448 is a reply to message #243438] Thu, 07 June 2007 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do this in your version.
You must declare a table for each column.

Regards
Michel
Re: Regarding bulk update [message #243609 is a reply to message #243448] Fri, 08 June 2007 00:43 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
thanks michel

I have a doubt in bulk insert . I have attached sample code for it .

desc fnd_application_3

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 APPLICATION_ID                            NOT NULL NUMBER
 APPLICATION_SHORT_NAME                    NOT NULL VARCHAR2(50)
 LAST_UPDATE_DATE                          NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER(15)
 CREATION_DATE                             NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER(15)
 LAST_UPDATE_LOGIN                                  NUMBER(15)
 BASEPATH                                           VARCHAR2(20)
 PRODUCT_CODE                                       VARCHAR2(50)

 
  1  declare
  2          cursor fnd_cursor is select * from fnd_all;
  3          cursor fnd_appl_cursor is
  4                  select application_id,application_short_name,last_update_date,
  5             last_updated_by,creation_date,created_by,
  6                  last_update_login,basepath,product_code from fnd_all;
  7          type fnd_appl_type is table of fnd_application_3%rowtype;
  8          fnd_appl_table          fnd_appl_type;
  9  begin
 10          open fnd_appl_cursor;
 11     fetch fnd_appl_cursor bulk collect into fnd_appl_table;
 12     forall i in fnd_appl_table.first..fnd_appl_table.last
 13             insert into fnd_application_3
 14                  values(fnd_appl_table(i));
 15* end;
 16  /

insert into fnd_application_3
*
ERROR at line 13:
ORA-06550: line 13, column 22:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 13, column 10:
PL/SQL: SQL Statement ignored


Let me know if this is wrong . What is the alternative ?
Should i declare table type for each column and also a cursor for each column and then only i can try this logic ?
Re: Regarding bulk update [message #243616 is a reply to message #243609] Fri, 08 June 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do that either. You have to give one value per column.
Why not directly "insert ... select ..."?

Regards
Michel
Re: Regarding bulk update [message #243620 is a reply to message #243616] Fri, 08 June 2007 01:26 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
The complete code goes like this

declare
        cursor fnd_appl_cursor is
                select application_id,application_short_name,last_update_date,last_updated_by,creation_date,created_by,
                last_update_login,basepath,product_code from fnd_all;
        type fnd_appl_type is table of fnd_application_1%rowtype;
        fnd_appl_table          fnd_appl_type;
        cursor fnd_upd_date_cursor is select last_update_date from fnd_all;
        cursor fnd_upd_by_cursor is select last_updated_by from fnd_all;
        cursor fnd_upd_log_cursor is select last_update_login from fnd_all;
        cursor fnd_appid_cursor is select application_id from fnd_all;
        type last_upd_date_table is table of fnd_all.last_update_date%type;
        type last_upd_by_table is table of fnd_all.last_updated_by%type;
        type last_upd_login_table is table of fnd_all.last_update_login%type;
        type appid_table is table of fnd_all.APPLICATION_id%type;
        last_upd_date_table_t   last_upd_date_table;
        last_upd_by_table_t last_upd_by_table;
        last_upd_login_table_t last_upd_login_table;
        appid_table_t appid_table;
begin
        open fnd_appl_cursor;
        open fnd_upd_date_cursor;
        open fnd_upd_by_cursor;
        open fnd_upd_log_cursor;
        open fnd_appid_cursor;
        fetch fnd_appl_cursor bulk collect into fnd_appl_table;
        fetch fnd_upd_date_cursor bulk collect into last_upd_date_table_t;
        fetch fnd_upd_by_cursor bulk collect into last_upd_by_table_t;
        fetch fnd_upd_log_cursor bulk collect into last_upd_login_table_t;
        fetch fnd_appid_cursor bulk collect into appid_table_t;
        forall i in fnd_appl_table.first..fnd_appl_table.last
                update fnd_application_1 set
                        last_update_date=last_upd_date_table_t(i),last_updated_by=last_upd_by_table_t(i),
                        last_update_login=last_upd_login_table_t(i)
                        where application_id=appid_table_t(i);
                if sql%notfound then
                        insert into fnd_application_1
                        values(appl_appl_table(i));
                end if;
end;
/



Here I am using bulk insert as I mentioned earlier . Dono if this loginc is correct .
Let me know if this will work .
I have another option "merge" , but I wanna test the performance between these two .
Re: Regarding bulk update [message #243632 is a reply to message #243620] Fri, 08 June 2007 02:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Fetch your single cursor into multiple tables.
Something like
fetch fnd_appl_cursor bulk collect 
into  l_application_id_tab
,     l_application_short_name_tab
,     l_last_update_date_tab
,     l_last_updated_by_tab
,     l_creation_date_tab
,     l_created_by_tab
,     l_last_update_login_tab
,     l_basepath_tab
,     l_product_code_tab
Re: Regarding bulk update [message #243636 is a reply to message #243620] Fri, 08 June 2007 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes a single merge will do it.

Regards
Michel
Re: Regarding bulk update [message #243787 is a reply to message #243616] Sat, 09 June 2007 00:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Fri, 08 June 2007 16:05
You can't do that either. You have to give one value per column.
Why not directly "insert ... select ..."?

Regards
Michel


But what about UPDATE ... SET ROW

Ross Leishman
Re: Regarding bulk update [message #243792 is a reply to message #243787] Sat, 09 June 2007 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Didn't know about it, seems very interesting... when you can't do it in a single query. Wink

Regards
Michel
Re: Regarding bulk update [message #243793 is a reply to message #243792] Sat, 09 June 2007 00:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes, I should have just pasted in the first line. Lazy moi! Wink

I concur, single statement would be best.

Ross Leishman
Re: Regarding bulk update [message #243797 is a reply to message #243793] Sat, 09 June 2007 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ross,

I tried it.
It only works for update and not for insert.
But it also works for bulk operation but you still need a collection for the rows and a collection for the "where" clause:
SQL> create table t as select empno, ename from scott.emp;

Table created.

SQL> declare
  2    type t_t is table of t%rowtype;
  3    l_t t_t := t_t();
  4    type t_e is table of t.empno%type;
  5    l_e t_e := t_e();
  6  begin
  7    l_t.extend;
  8    l_t(l_t.last).empno := 7369;
  9    l_t(l_t.last).ename := 'Ross';
 10    l_e.extend;
 11    l_e(l_e.last) := l_t(l_t.last).empno;
 12    l_t.extend;
 13    l_t(l_t.last).empno := 7499;
 14    l_t(l_t.last).ename := 'Michel';
 15    l_e.extend;
 16    l_e(l_e.last) := l_t(l_t.last).empno;
 17    forall i in l_t.first..l_t.last 
 18      update t set row = l_t(i) where empno = l_e(i);
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL> select * from t;
     EMPNO ENAME
---------- ----------
      7369 Ross
      7499 Michel
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7844 TURNER
      7900 JAMES
      7902 FORD
      7934 MILLER

12 rows selected.

Using l_t(i).empno in the "where" falls back to the error:
SQL> declare
  2    type t_t is table of t%rowtype;
  3    l_t t_t := t_t();
  4  begin
  5    l_t.extend;
  6    l_t(l_t.last).empno := 1;
  7    l_t(l_t.last).ename := 'Ross';
  8    l_t.extend;
  9    l_t(l_t.last).empno := 2;
 10    l_t(l_t.last).ename := 'Michel';
 11    forall i in l_t.first..l_t.last 
 12      update t set row = l_t(i) where empno = l_t(i).empno;
 13  end;
 14  /
    update t set row = l_t(i) where empno = l_t(i).empno;
                                            *
ERROR at line 12:
ORA-06550: line 12, column 45:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

Regards
Michel
Re: Regarding bulk update [message #243972 is a reply to message #243797] Mon, 11 June 2007 02:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I never said it was perfect...

The inability to reference record variables in a collection within SQL - as you demonsrate - is not fully mitigated by SET ROW.

b.t.w, INSERT is done like this.

Ross Leishman
Re: Regarding bulk update [message #243979 is a reply to message #243972] Mon, 11 June 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I never said it was perfect...

Just wanted to be quite complete in the subject.
As for this example:
SQL> declare
  2    type t_t is table of t%rowtype;
  3    l_t t_t := t_t();
  4  begin
  5    l_t.extend;
  6    l_t(l_t.last).empno := 1;
  7    l_t(l_t.last).ename := 'Ross';
  8    l_t.extend;
  9    l_t(l_t.last).empno := 2;
 10    l_t(l_t.last).ename := 'Michel';
 11    forall i in l_t.first..l_t.last 
 12       insert into t values l_t(i);
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select * from t where empno in (1,2);
     EMPNO ENAME
---------- ----------
         1 Ross
         2 Michel

2 rows selected.

Many thanks Ross, I learned many things in this topic.

Regards
Michel

Re: Regarding bulk update [message #244196 is a reply to message #243979] Mon, 11 June 2007 22:43 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey, this is cool; I post a link and Michel posts syntax. What a team!

If I post a link to my current client's requirements doc, I wonder if it will come back fully coded. Laughing
Previous Topic: full outer join + darabase link
Next Topic: query help need, PLEASE
Goto Forum:
  


Current Time: Mon Dec 05 09:05:51 CST 2016

Total time taken to generate the page: 0.05398 seconds