Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Creating reports

RE: Creating reports

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 08 Jun 2001 09:40:48 -0700
Message-ID: <F001.003227DD.20010608095025@fatcity.com>

Are these tables in APPS schema? Is this a stored proc? If yes, who is the owner of this stored proc?

Did you consider using Global Temporary Tables?

Regards

Waleed

-----Original Message-----
Sent: Friday, June 08, 2001 11:06 AM
To: Multiple recipients of list ORACLE-L

I initially created my script without COMMMIT but it still gave me the same problem. Can you think of other cause of the problem?

Thanks,
Mark

-----Original Message-----
Waleed
Sent: Thursday, June 07, 2001 6:02 PM
To: Multiple recipients of list ORACLE-L

Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application

                                would be disrupted by a COMMIT or ROLLBACK
statement not issued directly by the application itself, use the DISABLE
                                form of this clause to prevent procedures
and stored functions called during your session from issuing these statements.
                                You can subsequently allow procedures and
stored functions to issue COMMIT and ROLLBACK statements in your session
                                by issuing the ENABLE form of this clause.

                                Some applications (such as SQL*Forms)
automatically prohibit COMMIT and ROLLBACK statements in procedures and
                                stored functions. Refer to your application
documentation.

This is from Oracle Doc.

Regards,

Waleed

-----Original Message-----
Sent: Thursday, June 07, 2001 7:37 PM
To: Multiple recipients of list ORACLE-L

I am creating a custom report using Oracle Report Builder 6.0.8.8.3. I have created the report and they decided to register it in Oracle Application. The way the report works is that it would have to deletes all records from several custom tables and inserts records into it again. So what I did was to create a script in the BEFORE REPORT trigger that deletes all records from the table. It compiles fine but when I try to run the report it seems like it deosn't run execute the script (please see below script). I tried it too in BEFORE PARAMETER FORM and AFTER PARAMETER FORM but to no avail.

        PROCEDURE P_DLT_RECORDS IS
        BEGIN
                DELETE FROM AWW_RA_CUSTOMER_TRX_LINES_ALL
                WHERE CUSTOMER_TRX_ID IS NOT NULL;
                COMMIT;
        END;

I then close the whole application and open the report. At this point it giave me an error

        REP-1247: Report contains uncompiles PL/SQL

I then go to program unit, at this point it shows an error:

        "ERROR 907 at line 0, column 0
        cannot load library unit APPS.AWW_RA_CUSTOMER_TRX_LINES_ALL

(reference by

P_DLT_RECORDS) How should I get this one fixed? Am I putting it in the wrong place? Are there other libraries that have to be loaded for this to work? When I insert records records in the custom tables can I just put it in the same trigger where I delete records. If possible please attach a sample script for deleting and inserting records. please see below the script I created in inserting records in the table. Please let me know if there are changes.
        INSERT INTO AWW_RA_CUSTOMER_TRX_LINES_ALL
        SELECT ALL AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID,
                SUM(AR.RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT)
        FROM AR.RA_CUSTOMER_TRX_LINES_ALL
        WHERE AR.RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE='LINE'
                AND AR.RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE2
IN
('WHOLESALE', 'WILL ADVISE', 'WINTER BOOKING', 'RMA')
        GROUP BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID
        ORDER BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID;

Thanks in advace for the help.

Mark Liggayu

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Liggayu
  INET: markliggayu_at_allweatherwindows.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: Waleed.Khedr_at_FMR.COM Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu INET: markliggayu_at_allweatherwindows.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: Waleed.Khedr_at_FMR.COM Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Jun 08 2001 - 11:40:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US