Results 1 to 11 of 11
  1. #1
    Join Date
    May 2011
    Posts
    19

    Unanswered: Using two explicit Cursors with For loop in Stored Procedure

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Where did you find such a syntax?
    Code:
    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;
    If you need two cursors, you need to declare two cursors. This is nothing but a mess.

    What is
    Code:
    for ed in ref_cur loop
    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.

  3. #3
    Join Date
    May 2011
    Posts
    19
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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,);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2011
    Posts
    19
    thanks anacedent,I did the change but still end up with the same problem...

    Delisha

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2011
    Posts
    19
    Anacedent I got the same error as above..Is my coding ok?..The error is on the Exception Line...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    how many (begin) LOOP statements exist?
    how many "END LOOP;" statements exist?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2011
    Posts
    19
    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.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    if you decide to NOT answer my questions, then I decide to not answer your questions.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.
    Code:
    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
          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.customer_name,
                                 icharges,
                                 imonth,
                                 imail); --values are passed to email notification procedure
    END EBILL_TEST;
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •