Results 1 to 3 of 3

Thread: Oracle reports

  1. #1
    Join Date
    Sep 2003
    Posts
    15

    Unanswered: Oracle reports

    Hi,
    I am trying to build a report which gives a list of addresses as output. I am using a cursor and looping thru it using a for loop. My problem is that when I run the report, only the last record is printed. I have written a function to check for the conditions and am calling it in the BEFOREREPORT trigger. I do not know what the problem is Pls Help Have been working on it for 2 days :-( This is the code I have written.
    Thanks in Advance.
    The CP variables are the Place holders which i have assigned to the fields on the the form. Is this the reason why my records are not being displayed??

    FUNCTION TEST RETURN BOOLEAN IS
    BEGIN
    DECLARE

    CURSOR ADDR_CUR IS (SELECT ADDRESS_LINE1,ADDRESS_LINE2, CITY,STATE,ZIP_CODE,ADDRESS_TYPE FROM COMPANY_ACTIVE_ADDRESSES_VIEW;

    ADDR_REC ADDR_CUR%ROWTYPE;

    BEGIN
    FOR ADDR_REC IN ADDR_CUR LOOP

    IF ADDR_REC.ADDRESS_TYPE ='URL' THEN
    :CP_ADDRLINE1 := ADDR_REC.ADDRESS_LINE1;
    :CP_ADDRLINE2 := ADDR_REC.ADDRESS_LINE2;
    :CP_CITY:=ADDR_REC.CITY;
    :CP_STATE:=ADDR_REC.STATE;
    :CP_ZIP:=ADDR_REC.ZIP_CODE;
    :CP_TYPE := ADDR_REC.ADDRESS_TYPE;
    ELSIF ADDR_REC.ADDRESS_TYPE ='MAIL' THEN
    :CP_ADDRLINE1 := ADDR_REC.ADDRESS_LINE1;
    :CP_ADDRLINE2 := ADDR_REC.ADDRESS_LINE2;
    :CP_CITY:=ADDR_REC.CITY;
    :CP_STATE:=ADDR_REC.STATE;
    :CP_ZIP:=ADDR_REC.ZIP_CODE;
    :CP_TYPE := 'MAIL';
    ELSIF ADDR_REC.ADDRESS_TYPE ='LEGAL' THEN
    :CP_ADDRLINE1 := ADDR_REC.ADDRESS_LINE1;
    :CP_ADDRLINE2 := ADDR_REC.ADDRESS_LINE2;
    :CP_CITY:=ADDR_REC.CITY;
    :CP_STATE:=ADDR_REC.STATE;
    :CP_ZIP:=ADDR_REC.ZIP_CODE;
    :CP_TYPE := 'LEGAL';
    END IF;
    END LOOP;
    END;
    return (TRUE);
    END;
    Last edited by narass; 09-02-03 at 17:24.

  2. #2
    Join Date
    Sep 2003
    Posts
    7
    For this kind of query / display, don't use a before report trigger, instead set the query up as a reference cursor within your data model.

    If that doesn't help, let me know.

    Methos

  3. #3
    Join Date
    Sep 2003
    Posts
    15
    Hi Methos,
    I tried using the ref cursor as u suggested, but how do i loop thru it and assign values? I am stuck over here now. I tried using it in a for loop but it does not bring up any values. How do I return the records that has been selected?

    Thank you




    Originally posted by methos
    For this kind of query / display, don't use a before report trigger, instead set the query up as a reference cursor within your data model.

    If that doesn't help, let me know.

    Methos

Posting Permissions

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