need help with PL/SQL Procedure

[Sven]

i try to write a PL/SQL procedure for Oracle 9.2

is there a way to retrieve the number of affected rows of an INSERT or
an UPDATE statement?
i tried different ways with RETURNING INTO... but its not working :(

my statement is like:

insert into BEP_EQUIPMENT_CACHE
(select * from BEP_EQUIPMENT_MV WHERE EQUIPMENT not in (SELECT
EQUIPMENT FROM BEP_EQUIPMENT_CACHE));

please help,
thank you all - sven

[Ken Denny]

sv*******@web.de (Sven) wrote in
news:4f**************************@posting.google.c om:

i try to write a PL/SQL procedure for Oracle 9.2

is there a way to retrieve the number of affected rows of an INSERT or
an UPDATE statement?
i tried different ways with RETURNING INTO... but its not working :(

my statement is like:

insert into BEP_EQUIPMENT_CACHE
(select * from BEP_EQUIPMENT_MV WHERE EQUIPMENT not in (SELECT
EQUIPMENT FROM BEP_EQUIPMENT_CACHE));

please help,
thank you all - sven

Take a look at SQL%ROWCOUNT

--
Ken Denny
http://www.kendenny.com/

[Brent Eamer]

Also, make sure it is the statement immediately following the DML command
otherwise you lose its value (especially if it is an explicit cursor and you
close the cursor ;)
"Ken Denny" <ke*@kendenny.com> wrote in message
news:Xn**********************@216.77.188.18...

sv*******@web.de (Sven) wrote in
news:4f**************************@posting.google.c om:

i try to write a PL/SQL procedure for Oracle 9.2

is there a way to retrieve the number of affected rows of an INSERT or
an UPDATE statement?
i tried different ways with RETURNING INTO... but its not working :(

my statement is like:

insert into BEP_EQUIPMENT_CACHE
(select * from BEP_EQUIPMENT_MV WHERE EQUIPMENT not in (SELECT
EQUIPMENT FROM BEP_EQUIPMENT_CACHE));

please help,
thank you all - sven

Take a look at SQL%ROWCOUNT

--
Ken Denny
http://www.kendenny.com/

[Sven]

perfect. thank you

[Doron]

hi,
i think MERGE best fits your needs.

MERGE INTO new_table n
USING old_table o
ON (n.column = o.column)
WHEN MATCHED THEN UPDATE SET ....
WHEN NOT MATCHED THEN INSERT .....


sv*******@web.de (Sven) wrote in message news:<4f**************************@posting.google. com>...

i try to write a PL/SQL procedure for Oracle 9.2

is there a way to retrieve the number of affected rows of an INSERT or
an UPDATE statement?
i tried different ways with RETURNING INTO... but its not working :(

my statement is like:

insert into BEP_EQUIPMENT_CACHE
(select * from BEP_EQUIPMENT_MV WHERE EQUIPMENT not in (SELECT
EQUIPMENT FROM BEP_EQUIPMENT_CACHE));

please help,
thank you all - sven

[Doron]

hi,
i think MERGE best fits your needs.

MERGE INTO new_table n
USING old_table o
ON (n.column = o.column)
WHEN MATCHED THEN UPDATE SET ....
WHEN NOT MATCHED THEN INSERT .....


sv*******@web.de (Sven) wrote in message news:<4f**************************@posting.google. com>...

i try to write a PL/SQL procedure for Oracle 9.2

is there a way to retrieve the number of affected rows of an INSERT or
an UPDATE statement?
i tried different ways with RETURNING INTO... but its not working :(

my statement is like:

insert into BEP_EQUIPMENT_CACHE
(select * from BEP_EQUIPMENT_MV WHERE EQUIPMENT not in (SELECT
EQUIPMENT FROM BEP_EQUIPMENT_CACHE));

please help,
thank you all - sven