Forms :: Updating Record Must Be Exception To Query
Jan 31, 2013
SELECT COUNT(*)
INTO v_ctr
FROM leave_type_govt_service
WHERE (
:block_name.min BETWEEN minimum AND maximum
[code]......
This code in my forms really works for inserting a new record. But if I will update the record itself for example, in the table 'Type 1'
Minimum Maximum
1 5
6 10
11 15
and I will update the 6 - 10 to e.g. 7, It must update but my query includes 6 - 10 data so it raise the trigger failure.
Note: The AND condition in the last part is for unique types. Because even if e.g. 'Type 2' has a Min and Max data same as the 'Type 1' the trigger will not prompt.
when I updated a record in my form check my screenshot: and clicked save button the result was:
as you can see in the message at the bottom, it has 18 records. And since my original data(subjects) are just 9. And also the grades that I input didn't display all.
This is my code in when-new-block-instance trigger: DECLARE CURSOR studgrade_cur IS SELECT e.student_id, s.subject_code --, g.grade
I am using Forms 6i and developed a Master Detail Oracle Form for Oracle EBS 11i application.
I was asked to write a pl/sql program so that.. When a user updating an existing record at the same time another user query the same record and try to update the same time, the record should be locked and a message should popup saying the record is being updated by another user.
when no data found this query generate error ora-01403 but if i count these transaction and then apply this trigger it works very well i am not interested to count because it takes a time i want that when no data found exception clause took control but i am not understand how to add exception clause in this trigger
select sum(nvl(gl_quantity,0)),sum(nvl(gl_amount,0)) into cr_qty,cr_amt from account.glhis where gl_drcr='CR' and gl_account=:prod_consume_auto.store_code and gl_date<=:prod_master_auto.voc_date and gl_voc_no<>:prod_master_auto.voc_no group by gl_account;
I have to implement exception handling in the exception block of a trigger, Quote:exception
when ora_java.java_error then message( 'Unable to call out to java, ' || ora_java.last_error ); ORA_JAVA.CLEAR_EXCEPTION;
when ORA_JAVA.EXCEPTION_THROWN then ex := ORA_JAVA.LAST_EXCEPTION; message( Exception_.toString(ex)); -- lv_exception := Exception_.getMessage(ex);
I get an error for the line: 'message( Exception_.toString(ex));'I have imported the java classes FException et IObject with their methods.
I have to create a Web Service Client, so I wonder if the paragraph Quote:when ORA_JAVA.EXCEPTION_THROWN then ex := ORA_ JAVA. LAST_ EXCEPTION; is mandatory.
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 24 2013 March loc1 55007 78 57 28 2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 2013 March loc1 55007 78 57 2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)
is there any possibility or hint to avoid updating record if columns do not change? For example, I have UPDATE:
UPDATE MY_TABLE SET COLUMN_A = 'ABC' WHERE COLUMN_B = 12
if the value in COLUMN_A is 'ABC' I do not want to do this update. Of course I can add in WHERE 'AND COLUMN_A<>'ABC' but when I am updating a lot of columns it will be annoying
I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.
I have created an application as database authentication.In the application, I have developed a screen based on a tabular form,when I try to update a record ,the record is updated with apex_public_user insted of the database login.
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
I'm a newbie to PL/SQL. I had a quick query about trapping exceptions.
I have a sample table called my_emp, which contains last name, salary, etc. I have written the following code that takes in an employee salary and if the salary exists it displays the last name and corresponding salary. If two or more rows are returned, the exception handles it. Likewise if there are no records with that salary, the exception takes care of it.
I was trying to input an alphanumeric input, such as 1bbb as the salary and of course ORA-06502 error pops up in the sql command line. I now want to trap this using an exception but whatever I try I still get the ORA-06502 in the calling environment rather than getting the 'Not a number' or 'Some other error occured' message. why the WHEN VALUE_ERROR or the WHEN OTHERS exceptions are not trapping the error?
DECLARE v_sal NUMBER (12) := '&Enter_salary'; v_last_name VARCHAR2(10); BEGIN SELECT last_name
APEX 4.2Oracle 11g Database We are using the standard exception handler that was introduced in APEX 4.1, and we have code in packages & procedures in the database (following proper processes of keeping code in the database where possible). When an exception is found in the procedures/ packages/functions, should the APEX application level exception handler catch any errors that occur or should they be handled in the package/procedure/function they occurred in? Why I ask if, we right now have exception handling code in the pl/code bodies BUT they write their errors to the same table that Apex's Exception handler does, but the errors are NOT presented to the user using the APEX exception handling mechanism.
I am trying to create and update query to use as a trigger for updating information in a database which is dependent upon time.
I have a severity level which needs to be decreased in value after a set amount of time. the time of the action is recorded automatically on the creation of the the record. after an hour anything at severity level 2 needs to go up to 1, after 2 hours level 3 goes up to 2 and 3 hours level 4 goes up to 3
so far this is what I have
UPDATE calllog SET timedate = current_timestamp, severityid = severityid - 1 WHERE ((severityid = 2 and timedate >= current_timestamp + (1/24)) or (severityid = 3 and timedate >= current_timestamp + (2/24)) or (severityid = 4 and timedate >= current_timestamp + (3/24)))
I am not getting any errors back from this sql but it is not updating the relevent information. I am using one cell for time and date in format = DD-MON-YYYY HH24:MM:SS.
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
How can i catch the exception thrown by a PLSQL_TABLE.POPULATE_BLOCK. Actually i want to display alert messages if POPULATE_BLOCK doesn't contain any records. i wrote the PLSQL_TABLE.POPULATE_BLOCK(bk_data, 'block name');
In query procedure after that if i write any code for alert message then it is not getting executed it may be because of exception is handled by PLSQL_TABLE.POPULATE_BLOCK
i've created an alert with 2 buttons and the code for cancel button is as follows:
... if alert_id = alert_button2 then raise form_trigger_failure; end if; ...
this code is in a pll and when called from a form it displays a non oracle exception message. How to do nothing when the user presses cancel on alert pop-up? apart from using null instead of raising the above exception?
After our client upgraded to 11g database and application server, they are getting this error while trying to access another form from the option menu (The option menu is generated with a library, the values are taken from a database table). This issue occurs only in one particular scenerio, and only in on form, other forms with similar functionality and same structure are working perfectly fine. The error is written below.
FRM-21011: PL/SQL unhandled exception ORA-06508
I tried in my environment (10G), no error exist there.In 11G environment, I tried re-attaching a library in the form and compiling again, the error does not come after recompiliation. But the client can still see the error even after recompiling the form (after reattaching that particular library).I could find the below info on google:
FRM-21011: PL/SQL unhandled exception %s. Cause: An unhandled exception occurred while executing a menu trigger. Action: Examine the text of the exception in this message. If this indicates a cause, correct it. If the problem persists, contact Oracle Support Services. Level: 25 Trigger: None
I am using form builder 10G, will compiling the form using Form builder 11G?
My requirement, if there is no record in emp table when validate the EMP_NO_CHK text field, i need to set the focus on that field (EMP_NO_CHK) itself. But while execute the following code, I got error.chieve the task.
--EMP_NO_CHK_WHEN_VALIDATE_ITEM Trigger ----------------------------------- Declare cursor c is Select * from emp where Emp_no = :header.empno; c1 emp%rowtype; n NUMBER;
I am using Forms 6i,From my Form, user selects "sno" and upload BLOB image through OAF Upload utility. It stores the rowid of "SNO" and image...into Table "t1"...Now, i want to update the BLOB image into the original table"t2" using rowid..For this , i wrote the rowlevel trigger on Table "t1", in the trigger, i called a Procedure...In that procedure .. i am trying to update the BLOB column..It is not updating the BLOB column..But it is updating the Other values.. except BLOB column.
I have a form with two data blocks, one parent, one child block.
The parent is holds mineral lease info while the child holds the mineral owner info, such as addresses and phone numbers. One owner can be in the owner block multiple times (different owner types). The form only displays one owner at a time.
We have a separate master owner table which holds owner address. (We set it up this way because we get electronic info from mineral companies that we have to load each year).
As you tab through the owner block, it checks the FEIN against the master table and pulls updated address info from the master table.
I have a problem in which if an owner is on the lease multiple times, when you tab through the first instance, it pulls in the new address info, but when you go to the next instance, it won't update. If you requery, it seems that the first update actually updated all the owner records on that lease. How can I turn this off?
