Hi,

I am trying to create two explicit cursors to read data from a table and a view respectively..
The first cursor will get customers addresses in a table and the second cursor will get the bill charges from a view and pass it to another stored procedure which will then output it as an Electronic Bill Notification to the customers email address.
Below is the nested cursor I created and I am not too sure what its strengths will be like. The reason why I am using this is because I am dealing with data records over 10,000. i got an error which is
[Error] Syntax check (29: 16): ERROR line 29, col 16, ending_line 29, ending_col 20, Found 'table', Expecting: ; -or- .. := DEFAULT NOT NULL -or- % -or- ( . @

when compiling the script.


CREATE OR REPLACE PROCEDURE TMIS.EBILLS2(C_NUM NUMBER) IS
/
--DECLARE
CURSOR EDETAILS
IS
SELECT DISTINCT(CUSTOMER_ACCOUNT_NUMBER) as "CUST_NUM",CUSTOMER_NAME ,EMAIL,
CURSOR (select R_CHARGES_TOTAL
from BILL_LEDGERS b
where b.custkeyvalue=c.customer_account_number)

FROM EBILL_CUSTOMERS c
AND CYCLE = C_NUM
ORDER BY 2;

-- Declare Variables to hold Values from Outer Cursor
icount number default 0;
imonth varchar2(10);
crlf VARCHAR2(2) := chr(13)||chr(10);
ierr varchar2(100);
imail VARCHAR2(80);
istring VARCHAR2(1000);
-- Declare PL/SQL Table to hold Values from Inner Cursor
i_charges is table of BILL_LEDGERS@ABS_SYSTEM.TELIKOMPNG.COM.PG.R_CHARGES_TOTAL%type index by binary_integer ;


BEGIN
icount := 0;
imonth := to_char(sysdate,'MON-YYYY');
dbms_output.enable(200000);
FOR l in edetails
LOOP
BEGIN

--fetch values for outter cursor and display
imail := erow.email;
istring := l.cust_num ||' ; '|| l.customer_name ||' ; '||imonth ||' ; '|| imail;
icount := icount + 1;
dbms_output.put_line(ICOUNT||' | '|| imonth ||' | '|| l.cust_num ||' | '|| l.customer_name ||' | '|| imail);

-- Fetch Values from Inner Cursor

for ed in ref_cur
loop
begin
icharges:=ed.R_CHARGES_TOTAL;

-- Print Details to ebill notification
if l_cust_num=erow.cust_num
then Dbms_Output.Put_Line ( icharges );
end if;
end loop;

END LOOP;
EXCEPTION WHEN OTHERS THEN
--ierr := substr(sqlerrm,1,1000);
dbms_output.put_line(sqlerrm);
-- raise_application_error(-20000, 'Unable to send ebill notification: '|| sqlerrm);
END;

EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
dbms_output.put_line(sqlerrm);
--raise_application_error(-50000, 'Unable to send ebill notification: '|| sqlerrm);
END;
/

Please is there a better way I can do this?



Many thanks
Delisha

Where did you find such a syntax?
If you need two cursors, you need to declare two cursors. This is nothing but a mess.

What is supposed to do? What is "ref_cur"?

Remove WHEN OTHERS exception handler, it is useless; let Oracle raise the error itself.

I think that you should rewrite it from scratch, step by step. Test frequently! If you still have errors, post your new code here. This time, make sure to properly format it and enclose it into the CODE tags which will preserve formatting. Code, as you posted it, is close to unreadable.

Thanks Littlefoot, I was suppose to edit the code first before posting,I just realized my mistake..

Below is the new code I wrote and I am not too sure what the error is all about as there are no results found when i try to goggle it..I am new to Oracle cursors and stored procedure..

--Note:The customer number is the value used to compare the table and the view and --give the customer bill charges.

CREATE OR REPLACE PROCEDURE EBILL_TEST(C_NUM NUMBER,CUST_NUM number) IS
CURSOR EDETAILS IS
SELECT DISTINCT(CUSTOMER_ACCOUNT_NUMBER) as "CUST_NUM"
,CUSTOMER_NAME ,EMAIL
FROM EBILL_CUSTOMERS
WHERE EMAIL IS NOT NULL AND CYCLE = C_NUM
ORDER BY 2;

CURSOR EBILL_CHARGES IS
SELECT DISTINCT(CUST_KEYVALUE) AS CUSTOMER_ACCOUNT_NUMBER, R_CHARGES_TOTAL
FROM BILL_LEDGERS
WHERE CUST_KEYVALUE=CUST_NUM
ORDER BY 2;


icount number default 0;
imonth varchar2(10);
crlf VARCHAR2(2) := chr(13)||chr(10);
ierr varchar2(100);
imail VARCHAR2(80);
istring VARCHAR2(1000);
icharges NUMBER(20,5);


BEGIN
icount := 0;
imonth := to_char(sysdate,'MON-YYYY');
dbms_output.enable(200000);
FOR erow in edetails
LOOP
BEGIN

imail := erow.email;
istring:= erow.cust_num ||' ; '|| erow.customer_name ||' ; '||imonth ||' ; '|| imail;

dbms_output.put_line(ICOUNT||' | '|| imonth ||' | '|| erow.cust_num ||' | '|| erow.customer_name ||' | '|| imail);
end loop;--inner loop

FOR edetails in EBILL_CHARGES
LOOP
BEGIN

icharges:=edetails.r_charges_total;
end loop;

IF edetails.customer_account_number= erow.cust_num
then
dbms_output.put_line(icharges);
end if;

icount := icount + 1;
End loop;


dbms_output.put_line(ICOUNT||' | '|| imonth ||' | '|| erow.cust_num ||' | '|| erow.customer_name ||' | '|| imail ||' | '|| icharges);


email_ebill_notification(erow.cust_num,erow.custom er_name,icharges,imonth,imail,);--values are passed to email notification procedure

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END EBILL_TEST;



The error i got is " [Error] Syntax check (58: 4): ERROR line 58, col 4, ending_line 58, ending_col 12, Found 'EXCEPTION', Expecting: ( SELECT -or- $IF : BEGIN CASE CLOSE COMMIT CONTINUE CURSOR DECLARE DELETE EXECUTE EXIT FALSE FETCH FOR FORALL GOTO identifier IF INSERT LOCK LOOP MERGE MULTISET NEW NULL OPEN PRAGMA RAISE RETURN ROLLBACK SAVEPOINT SET SQL THE TRUE UPDATE WHILE WITH -or- END -or- <<

Thank you so much in advance

It may not like the rightmost comma (to the right of "imail" ) in the line below

>email_ebill_notification(erow.cust_num,erow.custo m er_name,icharges,imonth,imail,);

thanks anacedent,I did the change but still end up with the same problem...

Delisha

since we don't have your tables or data, we can't debug, test, run or improve your posted code.

what results if you do as below?

--dbms_output.put_line(ICOUNT||' | '|| imonth ||' | '|| erow.cust_num ||' | '|| erow.customer_name ||' | '|| imail ||' | '|| icharges);
--email_ebill_notification(erow.cust_num,erow.custom er_name,icharges,imonth,imail,);--values are passed to email notification procedure

Anacedent I got the same error as above..Is my coding ok?..The error is on the Exception Line...

how many (begin) LOOP statements exist?
how many "END LOOP;" statements exist?

I used the loops to open the cursor and end to close the two cursors respectively..Is this correct?.Then I use the conditional statement to compare the customer number in the first cursor with the customer number in the second cursor and if they correspond then it will output the bill charges and put it to the buffer to be read in by the email notification procedure..Is my code ok to handle that?

Many thanks.

if you decide to NOT answer my questions, then I decide to not answer your questions.

This kind of code were YOU supposed to post; I asked you to format it and enclose it into the CODE tags; why didn't you do it? Instructions are here.
There was an extra comma, extra BEGIN (without an END), extra space in a variable name ... basically, errors you were supposed to fix yourself. These are just result of you being careless.

As you've already been told: we don't have your tables, procedures, whatever you use here so it is difficult to guess whether the above code will work or not.