DECLARE 
V_Eqp varchar(40); 
tel varchar(40); 
V_Sec varchar(40); 
V_Third varchar(40); 
a varchar(40); 

Begin 

Cursor Telcursor is 
SELECT tel_no 
FROM adsltest; 

OPEN Telcursor; 
loop 
FETCH Telcursor INTO a; 
EXIT WHEN Telcursor%NOTFOUND; 

SELECT DOWN_EQPT_ID 
INTO V_Eqp 
FROM ccbs.pairs_status_view 
WHERE TELNO= a.tel_no AND cable_name_down is null; 
DBMS_OUTPUT.PUT_LINE(V_Eqp) ; 

Select  GIS_EQUIPMENT_NAME 
Into  V_Sec 
From ccbs.equipment_conversion 
Where CCBS_EQPT_ID = V_EQP; 
DBMS_OUTPUT.PUT_LINE(V_Sec) ;  

select ADSL_SERVICE   
Into  V_Third 
From ne.equipment 
where EQUIPMENT_name = V_Sec; 
DBMS_OUTPUT.PUT_LINE(V_Third) ; 

  

update testadsl 
set adsl_value
where a.tel_no = ccbs.pairs_status_view.telno

End; 

  

end loop 

  

CLOSE Telcursor; 

end; 

  

SELECT DOWN_EQPT_ID 
INTO V_Eqp 
FROM ccbs.pairs_status_view 
WHERE TELNO= a.tel_no AND cable_name_down is null; 
DBMS_OUTPUT.PUT_LINE(V_Eqp) ; 

SELECT DOWN_EQPT_ID 
INTO V_Eqp 
FROM ccbs.pairs_status_view 
WHERE TELNO= Telcursor.a AND cable_name_down is null; 
DBMS_OUTPUT.PUT_LINE(V_Eqp) ; 

Declare
     
     reqTelNo varchar(40);
     Eqpt_id varchar(40);
     ccbcEqpt_id varchar(40);
     GisEqpt varchar(40);
     GisEqptTwo varchar(40);
     Adsl varchar(40);
     gisTelNumber varchar(40);

      cursor c5 is
        select tel_no,ADSL_SPEED
        from adsl_test;
      cursor c6 is
        select DOWN_EQPT_ID,telno
        from ccbs.pairs_status_view
        where telno = reqTelNo and cable_name_down is null;
              cursor c7 is
        Select  GIS_EQUIPMENT_NAME , CCBS_EQPT_ID
                   From ccbs.equipment_conversion
                   Where Eqpt_id = CCBS_EQPT_ID;
      cursor c8 is
        select ADSL_SERVICE, EQUIPMENT_name
                    From ne.mv_equipment
                    Where EQUIPMENT_name= GisEqpt ;                    

   begin
     open c5;
     loop
       fetch c5 into reqTelNo, Adsl;
       exit when c5%notfound;
       dbms_output.put_line(chr(10)|| reqTelNo);
       begin
         open c6;
         loop
           fetch c6 into Eqpt_id, gisTelNumber;
           exit when c6%notfound;    
           --dbms_output.put_line (Eqpt_id || gisTelNumber);
       end loop;
       close c6;
       end;

        begin
           open c7;
           loop
             fetch c7 into GisEqpt, ccbcEqpt_id;
             exit when c7%notfound;  
             --dbms_output.put_line (GisEqpt || ccbcEqpt_id); 
            End loop;
            Close c7;
            End;  


        begin
           open c8;
           loop
             fetch c8 into Adsl, GisEqptTwo;
             exit when c8%notfound; 
             --dbms_output.put_line (GisEqpt || ccbcEqpt_id); 
                                             

                if  Adsl =  null then
                  dbms_output.put_line (GisEqpt || GisEqptTwo);

               Else
                 Update adsl_test
                 Set ADSL_SPEED= Adsl
                 Where GisEqpt = GisEqptTwo;
               End if;

               End loop;
               Close c8;
               End;  

             end loop;
     close c5;
   end;

if  Adsl =  null then

reqTelNo    adsl_test.tel_no%TYPE;

Select adsl_test.tel_no,adsl_test.adsl_speed,ccbs.pairs_status_view.down_eqpt_id, ccbs.pairs_status_view.telno, ccbs.equipment_conversion. GIS_EQUIPMENT_NAME, ccbs.equipment_conversion. CCBS_EQPT_ID, ne.mv_equipment. ADSL_SERVICE, ne.mv_equipment. EQUIPMENT_name 
From adsl_test, ccbs.pairs_status_view, ccbs.equipment_conversion, ne.mv_equipment
Where adsl_test.tel_no = ccbs.pairs_status_view.telno and and cable_name_down is null and  ____!!!;

begin
         open c6;
         loop
           fetch c6 into Eqpt_id, gisTelNumber;
           exit when c6%notfound;    
           --dbms_output.put_line (Eqpt_id || gisTelNumber);
       end loop;
       close c6;

SQL> create table adsl_test
  2  (telno varchar2(20),
  3   adsl_service varchar2(20)
  4  );

Table created.

SQL> insert into adsl_test (telno, adsl_service) values ('123-456', null);

1 row created.

SQL> create table pairs_status_View
  2  (down_eqpt_id number,
  3   telno varchar2(20)
  4  );

Table created.

SQL> insert into pairs_status_view (down_eqpt_id, telno) values (123, '123-456');

1 row created.

SQL> create table equipment_conversion
  2  (gis_equipment_name varchar2(20),
  3   ccbs_eqpt_id number
  4  );

Table created.

SQL> insert into equipment_conversion (gis_equipment_name, ccbs_eqpt_id) values ('ES name', 123);

1 row created.

SQL> create table equipment
  2  (adsl_service varchar2(20),
  3   equipment_name varchar2(20)
  4  );

Table created.

SQL> insert into equipment (adsl_service, equipment_name) values ('I want this AS', 'ES name');

1 row created.

SQL>

SQL> update adsl_test at set
  2    at.adsl_service = (select e.adsl_service
  3                       from equipment e
  4                       where e.equipment_name = (select ec.gis_equipment_name
  5                                                 from equipment_conversion ec
  6                                                 where ec.ccbs_eqpt_id = (select psv.down_eqpt_id
  7                                                                          from pairs_status_view psv
  8                                                                          where psv.telno = at.telno
  9                                                                         )
 10                                                )
 11                       );

1 row updated.

SQL>

SQL> select * from adsl_test;

TELNO                ADSL_SERVICE
-------------------- --------------------
123-456              I want this AS

SQL>

  Update adsl_test
  Set ADSL_SPEED= Adsl
  Where GisEqpt = GisEqptTwo;

IF GisEqpt = GisEqptTwo THEN
  UPDATE adsl_test
  SET adsl_speed = adsl
  WHERE tel_no = reqTelNo;
END IF;

update adsl_test at 
     set at.adsl_speed = (select e.adsl_service
                         from ne.mv_equipment e, adsl_test at
                         where e.equipment_name = (select ec.gis_equipment_name
                                                   from equipment_conversion ec
                                                   where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                      from ccbs.pairs_status_view psv
                                                                      where at.tel_no in psv.telno and psv.cable_name_down is null
                                                                           )
                                                 )
                        )
Where at.tel_no in (select telno from ccbs.pairs_status_view);

select e.adsl_service, b.tel_no
                         from ne.mv_equipment e, adsl_test b
                         where e.equipment_name = (select ec.gis_equipment_name
                                                   from equipment_conversion ec
                                                   where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                      from ccbs.pairs_status_view psv
                                                                      where psv.cable_name_down is null and b.tel_no = 064616351
                                                                       )
                                                                                             );

select e.adsl_service
                         from ne.mv_equipment e
                         where e.equipment_name = (select ec.gis_equipment_name
                                                   from equipment_conversion ec
                                                   where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                      from ccbs.pairs_status_view psv
                                                                      where psv.cable_name_down is null and telno = 064616351
                                                                       )
                                                                                             );

update adsl_test at 
     set at.adsl_speed = (select e.adsl_service
                         from ne.mv_equipment e
                         where e.equipment_name = (select ec.gis_equipment_name
                                                   from equipment_conversion ec
                                                   where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                      from ccbs.pairs_status_view psv
                                                                      where at.tel_no in psv.telno and psv.cable_name_down is null
                                                                           )
                                                 )
                        )
Where at.tel_no  in (select psv.telno from ccbs.pairs_status_view psv where at.tel_no =psv.telno );