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.

View 1 Replies


ADVERTISEMENT

Forms :: Updating A Record In A Cursor?

Mar 3, 2012

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

[code]...

View 39 Replies View Related

Forms :: Lock Record When Two Users Updating Existing One At Same Time

Aug 22, 2013

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.

View 2 Replies View Related

Forms :: How To Add Exception In Given Query

Nov 3, 2011

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;

View 3 Replies View Related

Forms :: Frm-40735 Pre-query Trigger Raised Unhandled Exception Ora-06502

Jun 17, 2013

When i select Filnumber(DEC99) and press QUERY button, i get error"frm-40735 pre-query trigger raised unhandled exception ora-06502".

View 1 Replies View Related

Forms :: Implement Exception Handling In Exception Block Of A Trigger

Oct 10, 2011

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.

View 2 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

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).

View 5 Replies View Related

SQL & PL/SQL :: How To Raise Exception When Inserting Record In Table

Jul 5, 2011

How to raise a exception when inserting a record in a table using plsql?

View 11 Replies View Related

SQL & PL/SQL :: Updating Table With Latest Record

Mar 17, 2011

DROP TABLE TESTING CASCADE CONSTRAINTS;
CREATE TABLE TESTING
(
DATAPERIOD DATE,
EMPLID VARCHAR2(20 BYTE),
B_OS_REASON VARCHAR2(9 BYTE)
)
TABLESPACE USERS;

[Code] ........

SQL> select * from testing order by 1;

DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972

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.)

DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972 XXXXXX

View 5 Replies View Related

Avoid Updating Record If Columns Do Not Change?

Nov 29, 2012

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

View 1 Replies View Related

Forms :: Query In Record Group

Mar 6, 2011

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.

p.s: That record group is built in run-time.

View 1 Replies View Related

Forms :: Get Record Group Query

Apr 3, 2010

How can I get record group query in code and chang it ?( for example change its where clus). I need to get its Query.

the record group exists and I must get its query not make it.

and I know about Populate_Group_With_Query for changing but cant I have the query to change?

FOR EXAMPLE :
my record group query is :
select a ,b from A where A.c=1;

and I use it in an LOv.

Now i want get this record group in code and change its WHERE cluse after pressing A BUTTON How can I do it?

View 2 Replies View Related

Application Express :: Updating Record As Apex_public_user instead Of Login User?

Sep 9, 2013

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. 

View 3 Replies View Related

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

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:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

SQL & PL/SQL :: Exception Query - Alphanumeric Input?

Dec 27, 2012

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

[code]...

View 3 Replies View Related

Application Express :: Exception Handling Pl Packages And APEX Exception Handler

Oct 7, 2013

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.

View 2 Replies View Related

Create / Update Query To Use As Trigger For Updating Info In DB Dependent Upon Time

Dec 7, 2010

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.

View 3 Replies View Related

Forms :: Populating Values To Record Group From Multi Record Datablock

Jul 26, 2012

My procedure proc_ex is in when_validate_item trigger

I have one Multi Record data block in my form with values in its items

I need to Populate multi record block values to one Record Group using

add_group_row,
add_group_column,
set_group_char_cell to populate values to record group

Let us suppose my multi record data block looks like

item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80

how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies

View 3 Replies View Related

Forms ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

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.

View 4 Replies View Related

Forms :: Only New Record Have Saved Last Record No Longer Exist

Jun 29, 2013

I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.

View 4 Replies View Related

Forms :: Choose Lov In First Record That Won't Be Display In Second Record Lov

Sep 10, 2012

I have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...

View 4 Replies View Related

Forms :: FRM-21011 - PL/SQL Unhandled Exception ORA-306500

Dec 26, 2006

I am getting the follwoing error at the time of opening the application menu

FRM-21011: PL/SQL unhandled exception ORA-306500.

View 9 Replies View Related

Forms :: How To Catch Exception Thrown By A Table

Feb 11, 2010

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

What will be the solution to this problem ?

View 1 Replies View Related

Forms :: Alert Cancel Button Gives Non Oracle Exception

Jan 20, 2011

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?

View 5 Replies View Related

Forms :: Display Line Number Where Exception Raise?

Feb 25, 2011

We have Forms 10g for our application.

When i raise a exception in a procedure / trigger, i want to show the line number where the exception happened. is there any builtin to show that.

For eg

Exception when others
message('Error in this procedure');
raise;

I want the above exception to raise with line number also. How do i do it. I need some built in procedure in forms.

View 10 Replies View Related

Forms :: After Upgrading To 11g / FRM-21011 - PL/SQL Unhandled Exception ORA-06508

Mar 16, 2012

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?

View 4 Replies View Related

Forms :: ORA-305500 Exception On When-Button-Pressed Trigger?

Jun 20, 2005

why this exception is occuring .my code is below.

Procedure Edit_File
(v_linked_file in out file_link.linked_file%type,
v_copy_status in out file_link.copy_status%type,
i_ext in varchar2) is

[code]....

View 18 Replies View Related

Forms :: Use RAISE FORM-FAILURE-TRIGGER In EXCEPTION?

Jun 12, 2012

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;

[code]...

View 4 Replies View Related

Forms :: Updating BLOB Column

Mar 11, 2012

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.

View 1 Replies View Related

Forms :: Updating Multiple Records

Mar 30, 2010

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?

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved