how many rows update using loop in oracle using pl/sql procedure

set serveroutput on

declare

cursor c1 is

select a,B from ab;

counter integer := 0;

begin

for cr in c1 loop

update item

set BASIC_PRICE=cr.b

where itemcode=cr.a;

counter := counter + sql%rowcount;

end loop;

dbms_output.put_line('Number of total lines affected update operation: '||counter);

end;

/

Number of total lines affected update operation: 128

PL/SQL procedure successfully completed.

How to split a phone no to country code,std code,Land Line more

with ph as

(select '091-044-234678' phone from dual),
 ph1 as
(select phone,substr(phone,instr(phone,'-',1)+1) as phone2 from ph),
 ph2 as
(select phone,phone2,substr(phone2,instr(phone2,'-',1)+1) as phone3 from ph1)
select * from ph2
select substr(phone,0,instr(phone,'-',1)-1) phone,
substr(phone2,0,instr(phone2,'-',1)-1)phone2,phone3
 from ph2

-------------

Find List of Referenced dependencies for a package or procedure or view

select referenced_owner,referenced_name from all_dependencies where  lower(name)='package Name'

Get Financial Year Week no for given date

Financial Week start from 01-Apr each year. So for 01-Apr of that year i should get week number as 1.

select
to_char(SYSDATE) the_date,
to_char(add_months(SYSDATE,-3),'WW') fiscal_week
from dual

Connect By Root Example

I have a Table person_map which will have 2 columns,
Person_id,Mapped_person_id

For Example
Person_ID Mapped_Person_id
1 2
2 3 
3 4
5 6
6 7

I require a query which will give output like below
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7

Scripts
Create table Person (person_id Number, Mapped_person_id Number);

INSERT INTO PERSON VALUES ( 1,2);
INSERT INTO PERSON VALUES ( 2,3);
INSERT INTO PERSON VALUES ( 3,4);
INSERT INTO PERSON VALUES ( 5,6);
INSERT INTO PERSON VALUES ( 6,7);

Solution:

SELECT level, person_id AS original_person_id, 

CONNECT_BY_ROOT person_id AS root_person_id, mapped_person_id
      FROM person
 CONNECT BY person_id = prior mapped_person_id;

How to find Table Last Altered Date/Time in Oracle






select * from dba_objects
where owner='

'
and object_name ='


'