PL/SQL

[hameja]

Hi Gentmen
Im trying to write a PL/SQL program but the program is not doing anything. Please look at my code and advise or modify
Declare
2 Cursor farm is
3 Select
4 min_in_2.msisdn,substr(min_in_2.datehour,1,8)
5 From min_in_2, farm_win
6 Where
7 min_in_2.msisdn=farm_win.msisdn and min_in_2.act_amt > '2.0'
8 and substr(min_in_2.datehour,1,8) between '20051001' and '20051031';
9 amsisdn varchar(13);
10 adate varchr(10);
11 amount varchar(5);
12 Begin
13 Open farm;
14 Loop
15 Fetch farm into amsisdn,adate;
16 Exit when farm%notfound;
17 End Loop;
18 Exception
19 when no_data_found then
20 insert into errors.farm_erorrs (code, message) values('12', 'not found');
21 Close farm;
22 End;

 

[sem]

From which command do you expect no_data_found exception? I see none

Regards, Dima

http://seminihin.narod.ru

 

[Dagon]

What exactly do you expect it to do ? All it is going to do is fetch data from the farm cursor into variables and then close down. As you are not doing anything with the data (like displaying the results, inserting into a table etc), you won't actually see anything.

 

[hameja]

HI,

I want to insert the results into a table called farm_dups
fields are msisdn, amount, adate. Please amend my scripts if possible.

 

[Beilstwh]

You do not need a cursor. Simply do an insert as follows. Because you never specified an amount, i hardcoded it to be 0. Change it to whatever is approporate.

insert into farm_dups(msisdn, amount, adate)
Select min_in_2.msisdn, 0, substr(min_in_2.datehour,1,8)
From min_in_2, farm_win
Where
min_in_2.msisdn=farm_win.msisdn and min_in_2.act_amt > '2.0'
and substr(min_in_2.datehour,1,8) between '20051001' and '20051031';


Bill
Oracle DBA/Developer
New York State, USA