Unanswered: Oracle Forms Inserting Data from into Oracle DB
I am trying to use a form with a Button to make a new record, this sets the prodid feild to max(prodid) + 1 from the products table in the database. It then sets everything to blank.
This works fine, but, when i try to save (another button) the record, which has:
insert into product
values (:PRODUCT.PRODID, :PRODUCT.SUPPLIERID, :PRODUCT.DESCRIPTION, :PRODUCT.PRICE);
As the PL/SQL For the WHEN-BUTTON-PRESSE trigger.
When this button is pressed, the Runtime window says at the bottom:
"FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-00001"
If i take Database Error from the menu, it displays:
UPDATE PRODUCT SET PRODID=:1,SUPPLIERID=:2,DESCRIPTION=:3,PRICE=:4 WHERE ROWID=:5
As being the SQL statement in error.
This is (obviously) not right, since on the form Prodid is 3009, supplierid is 1005 etc.
In SQL *Plus, if i select * from product where prodid = 1005, 3009, :1 I get various errrors (no rows selected for the first two and Bind variable "1" not declared for the last.
Similarily, if i use the Update button, which has WHEN-BUTTON-PRESSED code of:
SET supplierid = :PRODUCT.SUPPLIERID,
description = :PRODUCT.DESCRIPTION,
price = :PRODUCT.PRICE
prodid = :PRODUCT.PRODID;
It gives an error (which i would expect since it doesnt exist yet).
Specifically: FRM-40509: ORACLE error: unable to UPDATE record.
The SQL statement in error is the same as previously mentioned in this.
If i use Forms Runtime's own Add Item Button, along with my Update button this inserts without a problem, and, as expected the row appreas in SQL *Plus.
I appologize if i have done something stupidly obvious but i am new to Oracle and would appreciate any help as to why i cant use my own Button and Save for the insersion of new data.
Re: Oracle Forms Inserting Data from into Oracle DB
First, I'd like to reiterate Bradley's point - you should NOT select MAX(id)+1 to generate a unique ID, you should use a SEQUENCE. Unless this is a single-user database (Oracle databases usually are not) then selecting MAX(id)+1 is bad. Users will find they have to wait seconds, possibly even minutes, to save 1 record.
You can create a sequence in SQL Plus like this:
SQL> create sequence xxx_seq;
(change "xxx" to your table name or whatever)
You can then use the sequence in a PRE-INSERT trigger on the Form:
SELECT xxx_seq.NEXTVAL INTO :block.id FROM DUAL;
The ORA-00001 error means that you are violating a unique constraint - i.e. you have tried to insert the same ID value twice. This is because you are writing your own insert code in the button - but you don't need to, because forms inserts the record automatically when you save (assuming the block is based on the table).
Your button should just say "COMMIT_FORM;" or, since you want the block cleared afterwards, perhaps "CLEAR_FORM(DO_COMMIT):"
The SQL statement that you see when you do Display Error is not wrong, it is showing the statement executed, which uses bind variables = :1, :2 etc. These are "placeholders" for the actual values you entered (3009, 1005 etc.) Ignore that!
Originally posted by calhoun
So it is not possible to make a button to create a new record?
Useing my make record button it sets the id, and makes everything else blank, but thats not able to be inserted in any way?
You would normally do this:
1) Create a block called EMP based on the table EMP (for example), with items based on the columns of EMP