Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, GAJANANA.

Asked: March 07, 2017 - 5:51 am UTC

Last updated: March 07, 2017 - 7:45 am UTC

Version: 11.1.0.6.0

Viewed 1000+ times

You Asked

Hi Tom,


I have table by email with column id which stores the all the email ids, now i altered table by adding one more column to it as userid.
i want all the usernames to store in userid column. i have written code which is not getting compiled.


declare
type aat is table of email%rowtype index by pls_integer;
var aat;
begin
select * bulk collect into var from email;
forall i in 1..var.last
insert into email(userid) select substr(var(i).id,1,instr(var(i).id,'@',1,1)-1) from email;
end;
/


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_EN834NM5BQG83SGB2F2RYCDBL.html

and Connor said...

Thanks for giving us a livesql test case - makes life easier for us.

You were very close - you dont actually need PL/SQL to do it, so I've provided a SQL solution and a PL/SQL one (if you were just wanting to learn about forall)

SQL> create table email (id varchar2(30));

Table created.

SQL> insert into email values('gajanan1@gmail.com');

1 row created.

SQL> insert into email values('gajanan1@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> insert into email values('gajanana@gmail.com');

1 row created.

SQL> alter table email add userid varchar2(30);

Table altered.

SQL>
SQL>
SQL> desc email
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               VARCHAR2(30)
 USERID                                                                           VARCHAR2(30)

SQL>
SQL> update email
  2  set userid=substr(id,1,instr(id,'@',1,1)-1);

55 rows updated.

SQL>
SQL> select * from email;

ID                             USERID
------------------------------ ------------------------------
gajanan1@gmail.com             gajanan1
gajanan1@gmail.com             gajanan1
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana
gajanana@gmail.com             gajanana

55 rows selected.

SQL>
SQL> declare
  2  type aat is table of email%rowtype index by pls_integer;
  3  var aat;
  4  begin
  5  select * bulk collect into var from email;
  6  forall i in 1..var.last
  7    update email set userid = substr(var(i).id,1,instr(var(i).id,'@',1,1)-1)
  8    where id = var(i).id;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

bulk insert using forall

GAJANANA GANJIGATTI, March 07, 2017 - 10:48 am UTC

Thank you very much for understanding me about it. thank you very much.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library