hi all,
i'm new in pl sql issue and i'm trying to get tel numbers from adsltest table using cursor then use the tel in another select statment to get DOWN_EQPT_ID where the telno in ccbs.pairs_status_view table = tel_no in adsltest.
this is argent question anyone can help please?

thanks for your replay.
i don't think that i can do that in one select because the tel_no in adsltest table is a set of telno found in ccbs.pairs_status_view table, so i can't say adsltest.tel_no=ccbs.pairs_status_view.telno. i get one tel number from adsltest table and check if its found in the other table to get DOWN_EQPT_ID value for existing table.
what is more, i'll use the DOWN_EQPT_ID value to get another one from another table and do the same for fourth table.

Please make sure that your test case is proper before asking us for help.

See in your code you have following mistakes..

1. you are declaring telcursor in executable section
2. we dont understand why you are using cursor here
3. in the select query you are referencing a variable by a.tel_no,'a' is the name of the variable ,instead of variable you can reference the cursor.

code you have written

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) ;

You have to rewrite as

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) ;

4.in the next 2 select statements you have not assigned any value to v_seq and v_eqp

sorry for bothering,
i'm new and must done many mistakes, i keep trying and now my code is working but with wrong result.
i'll try to explain what i want to do again in more detils, please be pataint with me
i have four tables:

1- is adsl_test( this table contains two feilds
one is thetelephone number and the other is empty, i have to
fill it with ADSL_SERVICE value.
2- the second table is (ccbs.pairs_status_view)
its feilds:DOWN_EQPT_ID,TELNO
I want to select DOWN_EQPT_ID where tel_no in first table
= telno in ccbs.pairs_status_view.
3-third table is (ccbs.equipment_conversion)and the
feild for this table is GIS_EQUIPMENT_NAME,CCBS_EQPT_ID.
i want to find the GIS_EQUIPMENT_NAME where
the DOWN_EQPT_ID i selected previously = CCBS_EQPT_ID.
(note:selection from this table might be null)
4- the forth table is (ne.equipment)and
has ADSL_SERVICE,EQUIPMENT_NAME
I want to find ADSL_SERVICE where EQUIPMENT_NAME=GIS_EQUIPMENT_NAME i selected previously.
finally i want to update the ADSL_TEST table to fill adsl_service for telno found within.

As of the rest of the code, hm, that's quite a mystery; why did you use that many cursors, loops, just to make a little update? Did you try to do that in a single SQL statement (as previously suggested)? You can join 4 tables in one statement.

Some thoughts:
1) Cursor For Loops are wonderful things - you should use them
2) Calling cursors c<number> just makes it harder to follow the code, make the names descriptive of what they do.
3) Variables should be typed to the column that's being used to populate them i.e.

reqTelNo adsl_test.tel_no%TYPE;

4) Variables should be named with a prefix so you can tell at glance they're not column names.
5) The Adsl variable is being populated by two different cursors - that's only going to confuse matters. In fact I'm not sure why you're selecting ADSL_SPEED in c5 you don't need it.
6) Most of those loops almost certainly shouldn't be loops. Either those cursors only find one row each time, in which case you should use select into, or they find multiple rows and the variables you're fetching them into are being constantly overwritten.

7) This can almost certainly be done in a single update statement with no cursors needed.

it is about where condition, i don't have a primary or forign keys so i can't say (Where adsl_test.tel_no = ccbs.pairs_status_view.telno).
so i need to get value each time and use it with the next statement .
what is wrong with using this cursor? i thought it is what i have to do?!!

Loops over all the rows in the cursor and overwrites the variables with the new values each time.
Since your cursor doesn't have an order by, the values you end up with when the loop has finished - to be used by the next cursor - are random
(out of the possible values returned by the cursor of course).

That being the case you have no way of telling what values the adsl_test table is eventually going to be updated with.

Using cursors isn't necessary. Knowing exactly which value from which row you're going to use for your update is.

Littelfoot the code you sent is what i'm looking for, but i still have problems with the code. see the code i used first but i don't get any result, looks like the code hang up..

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);

i tried to test my code by just execute the select statment but i got (NO ROWS SELECTED)

Littelfoot the code you sent is what i'm looking for, but i still have problems with the code. see the code i used first but i don't get any result, looks like the code hang up..

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);

i tried to test my code by just execute the select statment but i got (NO ROWS SELECTED)