Oracle Application R12|Payables|FRM-40735 WHEN-VALIDATE-ITEM Trigger Raised Unhandled Exception ORA- [UPD]

Filed under Oracle E-Business Suite, Tip of the WeekTagged with FND: Enable Cancel Query, FRM-40735, FRM-40735: ON-ERROR trigger, FRM-40735: ON-ERROR trigger raised unhandled exception ORA-01001, FRM-40735: ON-ERROR trigger raised unhandled exception ORA-01001 R12, sunthar tharma pittsburgh, sunthar tharmalingam pittsburgh

Oracle Application R12|Payables|FRM-40735 WHEN-VALIDATE-ITEM Trigger Raised Unhandled Exception ORA-

Subject: Resolving FRM-40735 Errors Troubleshooting Details I. Introduction II. Common FRM-XXXXX Error Accompanying FRM-40735 III. Common Predefined PL/SQL Exceptions Accompanying FRM-40735 IV. Common ORA-XXXXXX Errors Accompanying FRM-40735 V. General Information References Applies to: Oracle Forms - Version: 4.5 to 10.1 Information in this document applies to any platform. Purpose This document describes common conditions that cause FRM-40735 errors to occur in Oracle Forms applications and provides tips to troubleshoot and resolve these errors. Last Review Date The FRM-40735 error occurs when Oracle Forms encounters an unexpected event during runtime processing. The error usually appears in the following format: FRM-40735: RAISED AN UNHANDLED EXCEPTION indicates the trigger that was executing when the unexpected event occurred. If the trigger code fails to handle this unexpected event, Oracle Forms reports a FRM-40735 to the user. identifies the unexpected event that occurred while the trigger was processing. This can be a predefined PL/SQL exception, a user defined exception, or an underlying server or application error. After you receive a FRM-40735, press [display error] to see any further errors that Oracle Forms has encountered. This bulletin describes some conditions that cause this error, as well as some techniques to resolve this error. II. Common FRM-XXXXX Error Accompanying FRM-40735 FRM-40831: Truncation occurred: value too long for field . Cause: A statement attempted to assign a value to an item or parameter that is not large enough to hold the entire value. Action: Increase the Maximum Length to accommodate the assigned value. If you are assigning a value to an item, also increase the Query Length of that item. For additional information, see PL/SQL predefined exception VALUE_ERROR below. III. Common Predefined PL/SQL Exceptions Accompanying FRM-40735 What is an unhandled exception? Sometimes, the FRM-40735 error indicates that a predefined PL/SQL exception has been encountered during the processing of a trigger. When a program falls into a predefined set of conditions, this raises a PL/SQL exception. If the code does not contain an exception handler to trap this exception, an unhandled exception error is reported to the user. Some of these exceptions are described below. VALUE_ERROR: An arithmetic, conversion, truncation, or constraint error occurred. Causes and solutions for this error include: Cause: Calling a stored procedure which has an OUT parameter of type CHAR. Action: Change the datatype of the OUT parameter to VARCHAR2. If the procedure returns only one value, use a function instead of a procedure since functions can return values of type CHAR. Cause: Type mismatch between parameters when calling a subprogam. Action: Check the syntax of subprogram calls. The datatypes of the values passed in the subprogram call must be compatible with the datatypes of the parameters in the subprogram declaration. Cause: Attempting to assign a value to an item or parameter that is not large enough to hold the entire value. Action: Increase the Maximum Length to accommodate the assigned value.If you are assigning a value to an item, also increase the Query Length of the item. If you are assigning a value to a PL/SQL variable, increase the size of the variable to accommodate the assigned value. The correct syntax for declaring CHAR and VARCHAR2 variables is as follows: CHAR[ maximum length ] VARCHAR2[ maximum length ] If no maximum length is given, it defaults to 1. For example, if you assign a value to a variable in a PL/SQL program unit as follows: DECLARE my_string VARCHAR2; BEGIN my_string := 'ABCDEFG'; END; you receive a VALUE_ERROR exception at runtime. Instead, use the following: DECLARE my_string VARCHAR2(7); BEGIN my_string := 'ABCDEFG'; END; For another example, if you assign a value to an item in a PL/SQL program unit as follows: Item Name: empno Maximum Length: 4 Query Length: 4 :empno := 12345678; you receive a VALUE_ERROR exception at runtime. To correct this, change the properties for the item as follows: Item Name: empno Maximum Length: 8 Query Length: 8 :empno := 12345678; Cause: For Oracle Forms versions prior to Version 4.5.6.X, using either GET_MENU_ITEM_PROPERTY or SET_MENU_ITEM_PROPERTY with the LABEL property causes a VALUE_ERROR exception at runtime. Action: Upgrade to Version 4.5.6.X or higher if you must use the LABEL property for the GET_MENU_ITEM_PROPERTY or SET_MENU_ITEM_PROPERTY built-ins. CURSOR_ALREADY_OPEN Cause: You attempted to open an explicit cursor that is already open. This most commonly occurs when attempting to use an OPEN statement in addition to a cursor FOR loop. Action: Use %ISOPEN to test if the cursor is already open before attempting to open it. For example: IF c1%ISOPEN THEN ... ELSE OPEN c1; END IF; INVALID_CURSOR Cause: You attempted to close a cursor that is not open. Action: 1. Make sure the cursor is open before attempting to close it. 2. Use %ISOPEN to test if the cursor is already open before attempting to close it. For example: IF c1%ISOPEN THEN CLOSE c1; ELSE ... END IF; NO_DATA_FOUNDCause: A SELECT ... INTO ... statement returns no records. Action: 1. Execute a similar select statement in SQLPLUS and see if records are returned. 2. Create an exception handler to trap this exception. NOT_LOGGED_ON Cause: The code is attempting to perform a database call, and you are currently not logged on to the RDBMS. This can occur if a database session was never established, the session has timed out, or the connection to the server has dropped. Action: Log back onto the server. TOO_MANY_ROWS Cause: A SELECT ... INTO ... statement returns more than one record. Action: 1. Narrow your criteria so that one record is returned.2. Use an explicit cursor if you desire more than one record to be returned from the SELECT statement. IV. Common ORA-XXXXXX Errors Accompanying FRM-40735 ORA-604: Error Occurred at recursive SQL level %s Cause: If an Oracle Forms application attempts to open a cursor to issue a SQL statement against the server and the server has reached a limit on the maximum number of open cursors, the Oracle Forms application reports a FRM-40735. A cursor is an area of memory that the database uses to execute an SQL statement. The default value of OPEN_CURSORS is 50. The maximum value for OPEN_CURSORS is determined by your environment. You need to determine the optimal setting for OPEN_CURSORS for your environment. Actions: -- ORA-00604 can usually be corrected by increasing the OPEN_CURSORS database parameter. This is an Oracle7 Server database parameter, not an Oracle Forms specific parameter. Consult with your Database Administrator to increase the value of OPEN_CURSORS. If you are using Personal Oracle7, also increase the OPEN_CURSORS parameter in the oracle.ini file or the Windows Registry File. -- Decrease the number of cursors that the Oracle Forms application uses. This is desirable in cases where increasing the value of the OPEN_CURSORS parameter is not feasible. Use explicit cursors instead of implicit cursors when possible. This gives the developer greater control over when cursors are closed by Oracle Forms, freeing valuable memory resources. For example, the following SELECT statement opens an implicit cursor: SELECT ename FROM emp WHERE empno = :empno; If the Cursor Mode property for the form is set to Open, this cursor remains open until the form containing this SQL statement is closed. Re-code the same SQL SELECT statement to use an explicit cursor as follows: DECLARE CURSOR my_cursor IS SELECT ename FROM EMP WHERE empno = :empno; BEGIN OPEN my_cursor; FETCH my_cursor INTO :ename; CLOSE my_cursor; END; This cursor closes when the CLOSE statement is processed. By default, Oracle Forms assigns a database cursor for each SQL statement implicitly issued from Oracle Forms. This improves processing, but causes Oracle Forms to use more cursors in a Runform session. Setting the Runform parameter OptimizeTP to the value "no" causes Oracle Forms to reuse cursors for INSERT, UPDATE, DELETE, and SELECT FOR UPDATE statements. The application uses fewer cursors but degrades in processing performance. @ For additional information on optimizing cursor usage in Oracle Forms applications, refer to the following Oracle Support bulletins: @ Note 596.1 OLS 101549.597 (V3) QUICK GUIDE TO USING EXPLICIT CURSORS @ OLS 101372.438 (V3) MINIMIZING CURSOR USAGE WITHIN SQL*FORMS ORA-942: Table or view does not exist Cause: The object does not exist, the object has been referenced incorrectly, or you do not have privileges to the object. Action: 1. Log into SQL*Plus, and describe the table or view. If you receive another ORA-942, check if the owner of the object has granted your user privileges on the object. 2. Qualify the object name with the schema name as follows: schema_name.object_name. 3. Create a synonym on the object and grant appropriate privileges on the synonym to the user. ORA-1000: Maximum number of open cursors exceeded Cause/Action: See ORA-604 ORA-2289: Sequence does not exist Cause: The specified sequence does not exist, or the user does not have the required privilege to perform this operation. Action: 1. Make sure you are using the correct sequence name. The following Data Dictionary views contain lists of valid sequences in the database: USER_SEQUENCES : description of sequences created in user's schema DBA_SEQUENCES : description of all sequences in the database (must have DBA privileges to use this view) ALL_SEQUENCES : description of sequences accessible to the user 2. Make sure the user has SELECT privileges on the sequence. The user must have SELECT privileges on the sequence in order to generate the next value in the sequence. To verify that the user has been granted privileges on the sequence, have the user log into SQL*Plus and issue the following statement: SQL> SELECT SEQUENCE_NAME, SEQUENCE_OWNER FROM ALL_SEQUENCES The owner of the sequence can use either of the following statements to GRANT SELECT privileges on the sequence: SQL> GRANT SELECT ON TO SQL> GRANT SELECT ON TO PUBLIC 3. Create a public synonym for the sequence. If the sequence has been created in another user's schema, you must use the following notation: SELECT .NEXTVAL INTO :block.field_name FROM DUAL; The owner of the sequence can create a synonym for the sequence which allows other users to access the sequence without prefacing the schema name. SQL> CREATE PUBLIC SYNONYM FOR Other users can now use the following syntax: SELECT .NEXTVAL INTO :block.field_name FROM DUAL; Consult with your Database Administrator for more information about sequences, privileges, and synonyms. 4. If the sequence is on a remote database, you can reference the sequence using either of the following methods: -- Create the synonym for the remote sequence, hiding the link information. For example: SQL> CONNECT system/manager SQL> CREATE PUBLIC SYNONYM remote_seq_syn 2 FOR remote_seq@db_link; Next, reference the synonym, not the database link in the Oracle Forms application. For example: SELECT .NEXTVAL FROM DUAL; -- Reference the sequence and link information in the Oracle Forms application. For example: SELECT .NEXTVAL FROM DUAL@db_link ORA-106552: DDE exception DMLERR_DATAACKTIMEOUT Cause: A DDE data request has timed out. Action: 1. Increase the TIMEOUT parameter in the DDE.EXECUTE built-in. 2. Trap the DDE.DMLERR_DATAACKTIMEOUT exception. ORA-106556: DDE exception DMLERR_NO_CONV_ESTABLISHED Cause: The client's attempt to establish a DDE conversation has failed. Action: 1. Check the service and topic name in DDE.INITIATE calls. EXCEL: The service name is 'EXCEL', and the topic name is the name of the spreadsheet. For example: 'C:\TEST.XLS' WORD: The service name is 'WINWORD', and the topic name is the name of the document. For example: 'C:\TEST.DOC'. 2. Check the name of the file to open in DDE.APP_BEGIN calls. EXCEL: Appid := DDE.APP_BEGIN('C:\MSOFFICE\EXCEL\EXCEL.EXE C:\ABC.XLS', DDE.APP_MODE_NORMAL) WORD: Appid := DDE.APP_BEGIN('C:\MSOFFICE\WINWORD\WINWORD.EXE C:\TEST.DOC', DDE.APP_MODE_NORMAL) For additional information on establishing DDE conversations, refer to the following Oracle Support bulletins: Note 37660.1 WIN: Oracle Forms DDE Example for MS Excel Note 32967.1 WIN: Oracle Forms DDE Example for MS Word Note 32867.1 WIN: Oracle Forms DDE Example For WordPerfect Refer also to Oracle Forms v4.5 Advanced Techniques Manual, Chapter 12. ORA-304500: Failed ORA_FFI call Cause: Oracle Forms is unable to call a function in the specified dll. Action: 1. Make sure the dll is located in either the ORACLE_HOME or DOS path. 2. Make sure that 32-bit dlls are used with 32-bit applications. Likewise, 16-bit dlls should only be used with 16-bit applications. V. General Information When FRM-40735 occurs, you sometimes need to create an exception handler to trap the error that Oracle Forms is encountering. An exception handler can be used to perform a certain set of operations whenever the error condition is encountered. When you use packages such as ORA_FFI and DDE, creating an exception handler within the package is a good idea. For additional information on exception handlers, refer to Oracle Support bulletin Note 61383.1 Trapping Database or Application Errors. References Note 47428.1 - OERR: FRM-40735 Trigger raised unhandled exception Note 545.1 - Exception Handlers: Validation and Error Handling For Forms 3.0 and 4.0 Note 554.1 - (V3) TESTING FOR EXISTENCE IN V3 TRIGGERS Note 61383.1 - How to Trap Database or Application Errors in Oracle Forms? PL/SQL User's Guide and Reference Manual: Error Handling PL/SQL User's Guide and Reference Manual: Implicit Datatype Conversions PL/SQL User's Guide and Reference Manual: Fundamentals 350c69d7ab