Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    8

    Unhappy Unanswered: Oracle Forms Inserting Data from into Oracle DB

    Hi,
    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:

    Code:
    insert into product 
    values (:PRODUCT.PRODID, :PRODUCT.SUPPLIERID, :PRODUCT.DESCRIPTION, :PRODUCT.PRICE); 
    COMMIT;
    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:

    Code:
    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:

    Code:
    UPDATE product 
    SET supplierid = :PRODUCT.SUPPLIERID, 
    description = :PRODUCT.DESCRIPTION, 
    price = :PRODUCT.PRICE 
    WHERE 
    prodid = :PRODUCT.PRODID; 
    COMMIT;
    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.

    Thank you.

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    It's been a while since I used Forms, but you should be using a sequence to increment your ids, not MAX() + 1.
    Bradley

  3. #3
    Join Date
    Feb 2003
    Posts
    8
    I dont want to use sequences untill its working properly.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    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!

  5. #5
    Join Date
    Feb 2003
    Posts
    8
    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?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

    2) You can create buttons like:

    Create:
    CREATE_RECORD;

    Save:
    COMMIT_RECORD;

    You don't need to write ANY code!!!

  7. #7
    Join Date
    Feb 2003
    Posts
    8
    edit: ok, i -think- i have gotten it working now
    Last edited by calhoun; 02-18-03 at 19:53.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •