Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

sequence

dumontlsdumontls Member Posts: 3
I have created tab_forms using oracle designer forms 6i

after I have input new data I need to fill in the PK and FK with ID numbers prefixed with fixed letters. I have created the auto sequence called seq_auto_id.

how do i recall this into the when-button-pressed function so that I can input the id numbers in the respective blocks(tables) using the following string
BEFORE INSERT
ON RETINA, ALPHA, ALLOCATION, TRIAL, PORT
FOR EACH ROW
SELECT 'RT03/' || seq_AUTO_ID.nextval,
'A' || seq_AUTO_ID.nextval,
'AL' || seq_AUTO_ID.nextval,
'T' || seq_AUTO_ID.nextval,
'P' || seq_AUTO_ID.nextval,
INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
:TRIAL.TRIAL_ID, :PORT.PORT_ID,
FROM DUAL;
END;

The above code wont work

what satement should I use to let forms know that seq_auto_id exists in the DB and then use it to call the vales as above

Thanks

Comments

  • nneomanneoma Member Posts: 61
    : I have created tab_forms using oracle designer forms 6i
    :
    : after I have input new data I need to fill in the PK and FK with ID numbers prefixed with fixed letters. I have created the auto sequence called seq_auto_id.
    :
    : how do i recall this into the when-button-pressed function so that I can input the id numbers in the respective blocks(tables) using the following string
    : BEFORE INSERT
    : ON RETINA, ALPHA, ALLOCATION, TRIAL, PORT
    : FOR EACH ROW
    : SELECT 'RT03/' || seq_AUTO_ID.nextval,
    : 'A' || seq_AUTO_ID.nextval,
    : 'AL' || seq_AUTO_ID.nextval,
    : 'T' || seq_AUTO_ID.nextval,
    : 'P' || seq_AUTO_ID.nextval,
    : INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    : :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    : FROM DUAL;
    : END;
    :
    : The above code wont work
    :
    : what satement should I use to let forms know that seq_auto_id exists in the DB and then use it to call the vales as above
    :
    : Thanks
    :
    :


    the problem with what u are trying to do is just a problem of syntax. all you have to do is create a button in your form, attach a When-Button-Pressed trigger to it with the following code:

    begin
    SELECT 'RT03/' || :sequence.seq_AUTO_ID.nextval,
    'A' || :sequence.seq_AUTO_ID.nextval,
    'AL' || :sequence.seq_AUTO_ID.nextval,
    'T' || :sequence.seq_AUTO_ID.nextval,
    'P' || :sequence.seq_AUTO_ID.nextval,
    INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    FROM DUAL;
    END;

    better still, you could forget about creating a button. just ensure that the boxes to contain the data you want to fill automatically are not enabled at run-time. you can do this simply by specifying the enabled property of these boxes as no at design time. then write a pre-insert trigger on the block that contains the boxes with the exact code written above. please let me know if this does not work but i believe it would.
  • dumontlsdumontls Member Posts: 3
    : : I have created tab_forms using oracle designer forms 6i
    : :
    : : after I have input new data I need to fill in the PK and FK with ID numbers prefixed with fixed letters. I have created the auto sequence called seq_auto_id.
    : :
    : : how do i recall this into the when-button-pressed function so that I can input the id numbers in the respective blocks(tables) using the following string
    : : BEFORE INSERT
    : : ON RETINA, ALPHA, ALLOCATION, TRIAL, PORT
    : : FOR EACH ROW
    : : SELECT 'RT03/' || seq_AUTO_ID.nextval,
    : : 'A' || seq_AUTO_ID.nextval,
    : : 'AL' || seq_AUTO_ID.nextval,
    : : 'T' || seq_AUTO_ID.nextval,
    : : 'P' || seq_AUTO_ID.nextval,
    : : INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    : : :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    : : FROM DUAL;
    : : END;
    : :
    : : The above code wont work
    : :
    : : what satement should I use to let forms know that seq_auto_id exists in the DB and then use it to call the vales as above
    : :
    : : Thanks
    : :
    : :
    :
    :
    : the problem with what u are trying to do is just a problem of syntax. all you have to do is create a button in your form, attach a When-Button-Pressed trigger to it with the following code:
    :
    : begin
    : SELECT 'RT03/' || :sequence.seq_AUTO_ID.nextval,
    : 'A' || :sequence.seq_AUTO_ID.nextval,
    : 'AL' || :sequence.seq_AUTO_ID.nextval,
    : 'T' || :sequence.seq_AUTO_ID.nextval,
    : 'P' || :sequence.seq_AUTO_ID.nextval,
    : INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    : :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    : FROM DUAL;
    : END;
    :
    : better still, you could forget about creating a button. just ensure that the boxes to contain the data you want to fill automatically are not enabled at run-time. you can do this simply by specifying the enabled property of these boxes as no at design time. then write a pre-insert trigger on the block that contains the boxes with the exact code written above. please let me know if this does not work but i believe it would.
    :

    I have use the method you have suggested and when I compile it uisng forms it was accepted without error however when I run the form module it is still not updating the id codes in the respective id columns. any suggestions.
  • dumontlsdumontls Member Posts: 3
    : : I have created tab_forms using oracle designer forms 6i
    : :
    : : after I have input new data I need to fill in the PK and FK with ID numbers prefixed with fixed letters. I have created the auto sequence called seq_auto_id.
    : :
    : : how do i recall this into the when-button-pressed function so that I can input the id numbers in the respective blocks(tables) using the following string
    : : BEFORE INSERT
    : : ON RETINA, ALPHA, ALLOCATION, TRIAL, PORT
    : : FOR EACH ROW
    : : SELECT 'RT03/' || seq_AUTO_ID.nextval,
    : : 'A' || seq_AUTO_ID.nextval,
    : : 'AL' || seq_AUTO_ID.nextval,
    : : 'T' || seq_AUTO_ID.nextval,
    : : 'P' || seq_AUTO_ID.nextval,
    : : INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    : : :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    : : FROM DUAL;
    : : END;
    : :
    : : The above code wont work
    : :
    : : what satement should I use to let forms know that seq_auto_id exists in the DB and then use it to call the vales as above
    : :
    : : Thanks
    : :
    : :
    :
    :
    : the problem with what u are trying to do is just a problem of syntax. all you have to do is create a button in your form, attach a When-Button-Pressed trigger to it with the following code:
    :
    : begin
    : SELECT 'RT03/' || :sequence.seq_AUTO_ID.nextval,
    : 'A' || :sequence.seq_AUTO_ID.nextval,
    : 'AL' || :sequence.seq_AUTO_ID.nextval,
    : 'T' || :sequence.seq_AUTO_ID.nextval,
    : 'P' || :sequence.seq_AUTO_ID.nextval,
    : INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    : :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    : FROM DUAL;
    : END;
    :
    : better still, you could forget about creating a button. just ensure that the boxes to contain the data you want to fill automatically are not enabled at run-time. you can do this simply by specifying the enabled property of these boxes as no at design time. then write a pre-insert trigger on the block that contains the boxes with the exact code written above. please let me know if this does not work but i believe it would.
    :
    by taking out the char string, i can now populate the ID but with numbers only, however with when i click the when-button-presssed it does not commit the record event though i have added the commit; command and get FRM-40508 error.

    i would realy like to resolve this problem.
  • nneomanneoma Member Posts: 61
    : : : I have created tab_forms using oracle designer forms 6i
    : : :
    : : : after I have input new data I need to fill in the PK and FK with ID numbers prefixed with fixed letters. I have created the auto sequence called seq_auto_id.
    : : :
    : : : how do i recall this into the when-button-pressed function so that I can input the id numbers in the respective blocks(tables) using the following string
    : : : BEFORE INSERT
    : : : ON RETINA, ALPHA, ALLOCATION, TRIAL, PORT
    : : : FOR EACH ROW
    : : : SELECT 'RT03/' || seq_AUTO_ID.nextval,
    : : : 'A' || seq_AUTO_ID.nextval,
    : : : 'AL' || seq_AUTO_ID.nextval,
    : : : 'T' || seq_AUTO_ID.nextval,
    : : : 'P' || seq_AUTO_ID.nextval,
    : : : INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    : : : :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    : : : FROM DUAL;
    : : : END;
    : : :
    : : : The above code wont work
    : : :
    : : : what satement should I use to let forms know that seq_auto_id exists in the DB and then use it to call the vales as above
    : : :
    : : : Thanks
    : : :
    : : :
    : :
    : :
    : : the problem with what u are trying to do is just a problem of syntax. all you have to do is create a button in your form, attach a When-Button-Pressed trigger to it with the following code:
    : :
    : : begin
    : : SELECT 'RT03/' || :sequence.seq_AUTO_ID.nextval,
    : : 'A' || :sequence.seq_AUTO_ID.nextval,
    : : 'AL' || :sequence.seq_AUTO_ID.nextval,
    : : 'T' || :sequence.seq_AUTO_ID.nextval,
    : : 'P' || :sequence.seq_AUTO_ID.nextval,
    : : INTO :RETINA.FILE_ID, :ALPHA.ALPHA_ID, :ALLOCATION.ALLOCATION_ID,
    : : :TRIAL.TRIAL_ID, :PORT.PORT_ID,
    : : FROM DUAL;
    : : END;
    : :
    : : better still, you could forget about creating a button. just ensure that the boxes to contain the data you want to fill automatically are not enabled at run-time. you can do this simply by specifying the enabled property of these boxes as no at design time. then write a pre-insert trigger on the block that contains the boxes with the exact code written above. please let me know if this does not work but i believe it would.
    : :
    : by taking out the char string, i can now populate the ID but with numbers only, however with when i click the when-button-presssed it does not commit the record event though i have added the commit; command and get FRM-40508 error.
    :
    : i would realy like to resolve this problem.

    what is the message accompanying FRM-40508 error? i can't really say anything until i know the error message. i really do not think you should take out the char strings in the code. check what datatype the columns have in the database first and what datatypes they have from the properties of the boxes designed to contain their values in the form. ensure that the datatype is varchar and the size declared for the datatype is long enough to contain the data you are trying to input. also, i think you should try commiting the data to the database by clicking on the save button instead of trying to explicitly issue a commit; command. pls let me know what happens.

Sign In or Register to comment.