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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).