| Sequence [message #528922] |
Thu, 27 October 2011 12:06  |
Hometown
Messages: 35
Registered: October 2010
Location: India
|
Member |
|
|
Dear All
I have master detail block. My requirement is to make the form multi user i.e by introducing autonumber facility that is through sequence. I have created a sequnce as follows
Create sequence seq_sponsorship_code
start with 129
increment by 1
cache 20;
Sequence created successfully. I checked the sequence using currval and nextval pseudocolumns. it was generating fine but when I tried to add this to the add button inorder to generate the next sequnce number the value doesn't gets displayed in the first field of the master block following code is written on it
declare
var number(10);
Begin
go_item('tbl_sponsorship.student_code');
:global.butt:='PADD';
Select ihelp.seq_sponsorship_code.nextval into var from dual;
:tbl_sponsorship.sponsorship_code:=var;
go_item('tbl_sponsorship.student_code');
clear_form(no_validate);
button_add;
Enabling;
go_item('tbl_sponsorship.student_code');
Exception
when others then
null;
End;
The code compiles successfully but it doesn't displays the next sequence number in text field :tbl_sponsorship.sponsorship_code
The first one hundered and twenty eight (128) records have been added by using the following code in add button
declare
var number(10);
Begin
go_item('tbl_sponsorship.student_code');
:global.butt:='PADD';
go_item('tbl_sponsorship.student_code');
clear_form(no_validate);
button_add;
Select nvl(max(sponsorship_code),0)+1
into var
from tbl_sponsorship;
:tbl_sponsorship.sponsorship_code:= ltrim(rtrim(var));
Enabling;
go_item('tbl_sponsorship.student_code');
Exception
when others then
null;
End;
Any help will be much appreciated
|
|
|
|
|
|
|
|
| Re: Sequence [message #528969 is a reply to message #528959] |
Thu, 27 October 2011 15:17  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Littlefoot wrote on Thu, 27 October 2011 15:14
The second code: you don't use a sequence at all, but MAX + 1 which is (when used the way you do) wrong in a multi-user environment because two (or more) users will (sooner or later)
Littlefoot, I think he is saying that is how the original records were created and that's why he wants to rewrite to code for a multi-user environment. That's how I read it, but I have not been having a good week here, often misinterpreting things.
Quote:
Sequence can be used differently, no code is needed at all. Open ID's property palette window and put the sequence into its "initial value" property::sequence.seq_sponsorship_code.nextval
Just re-emphasizing that this is a better way to use a sequence in a form field.
|
|
|
|
|
|
| Re: Sequence [message #529037 is a reply to message #528969] |
Fri, 28 October 2011 07:21 |
Hometown
Messages: 35
Registered: October 2010
Location: India
|
Member |
|
|
Thanks for the reply. Yes, joy_division got it right.I have inserted the first 128 records via using first code which uses max function, in-order to make it multi user I tried to create a sequence the code for which has been given in my previous message.
Littlefoot was right in pointing out the code is in vain because I have written clear_form and was expecting to see the nextval from the sequence in sponsorship_code field. after removing clear_form from the code and using the following code declare
var number(10);
Begin
go_item('tbl_sponsorship.student_code');
:global.butt:='PADD';
Select ihelp.seq_sponsorship_code.nextval into var from dual;
:tbl_sponsorship.sponsorship_code:=var;
go_item('tbl_sponsorship.student_code');
End;
This has started a new problem. Every time I open the form it displays the message "Could not reserve record (2 tries) Keep trying and this happens on all the individual computers.
Please advice.
|
|
|
|
| Re: Sequence [message #529041 is a reply to message #529037] |
Fri, 28 October 2011 08:06 |
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That means the records are locked by someone.
Find who's got them locked and get them to commit or rollback.
|
|
|
|
| Re: Sequence [message #529042 is a reply to message #529037] |
Fri, 28 October 2011 08:09 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Hometown wrote on Fri, 28 October 2011 08:21Thanks for the reply. Yes, joy_division got it right.I have inserted the first 128 records via using first code which uses max function, in-order to make it multi user I tried to create a sequence the code for which has been given in my previous message.
Littlefoot was right in pointing out the code is in vain because I have written clear_form and was expecting to see the nextval from the sequence in sponsorship_code field. after removing clear_form from the code and using the following code declare
var number(10);
Begin
go_item('tbl_sponsorship.student_code');
:global.butt:='PADD';
Select ihelp.seq_sponsorship_code.nextval into var from dual;
:tbl_sponsorship.sponsorship_code:=var;
go_item('tbl_sponsorship.student_code');
End;
This has started a new problem. Every time I open the form it displays the message "Could not reserve record (2 tries) Keep trying and this happens on all the individual computers.
Why do you have 2 go_items to the same item in this trigger?
You do not need to load the sequence into a variable and then the variable into the filed; just go directly to the field, but as Littlefoot pointed out, just put :sequence.seq_sponsorship_code.nextval in the initial value property (and disable to field so no one can go to it).
|
|
|
|
| Re: Sequence [message #529045 is a reply to message #529041] |
Fri, 28 October 2011 08:17 |
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Fri, 28 October 2011 14:06That means the records are locked by someone.
Find who's got them locked and get them to commit or rollback.
That said you appear to be using this code to insert new records. That error only happens when oracle forms thinks you're trying to update a record. So why does oracle think you're trying to update? Presumably you're querying records at some point in this.
|
|
|
|
| Re: Sequence [message #529078 is a reply to message #529045] |
Fri, 28 October 2011 10:26 |
Hometown
Messages: 35
Registered: October 2010
Location: India
|
Member |
|
|
|
I have checked on all computers and no one has locked the records, which means the problem persists somewhere else.
|
|
|
|
| Re: Sequence [message #529103 is a reply to message #529078] |
Fri, 28 October 2011 13:10 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
|
You are probably locking it yourself in your own session due to some sloppy code. This is not a knock, it's just to say that there is some code that is executing that does not need to execute.
|
|
|
|
| Re: Sequence [message #529180 is a reply to message #529103] |
Sat, 29 October 2011 06:23 |
Hometown
Messages: 35
Registered: October 2010
Location: India
|
Member |
|
|
I have checked entered records on SQL plus. There are two tables
1. tbl_sponsorship
2. tbl_sponsorship_detail
This is the structure of the table one
Name Null? Type
------------------------------- -------- ----
SPONSORSHIP_CODE NOT NULL NUMBER(5)
STUDENT_CODE NUMBER(5)
COUNTRY_CODE NUMBER(5)
DIRECT_DEBIT_AMOUNT NUMBER(12,2)
STUDENT_COUNTRY_CODE NUMBER(5)
The second table is
Name Null? Type
------------------------------- -------- ----
SPONSORSHIP_CODE NUMBER(5)
CONTACT_CODE NUMBER(6)
SPONSORSHIP_TYPE VARCHAR2(10)
DONATION_CODE NUMBER
DD_CODE NUMBER
DD_LINE_NO NUMBER
SPONSORSHIP_SDATE DATE
SPONSORSHIP_EDATE DATE
COUNTRY_CODE NUMBER(5)
STUDENT_NAME VARCHAR2(45)
CONTACT_NAME VARCHAR2(45)
DONATION_END_DATE DATE
DONATION_DATE DATE
DONATION_AMOUNT NUMBER(5)
DD_DETAIL_AMOUNT NUMBER(10,2)
After performing the count function on sponsorship_code on both tables. The following result are obtained
select count(sponsorship_code) from tbl_sponsorship;
COUNT(SPONSORSHIP_CODE)
-----------------------
133
select count(sponsorship_code) from tbl_sponsorship_detail;
COUNT(SPONSORSHIP_CODE)
-----------------------
143
The first table (tbl_sponsorship) is master block on form frm_sponsorship and the second one is the detail on the same form
Is the difference between number of records entered through the form in master and detail records causing problem. Both the blocks are joined on sponsorship_code ie. tbl_sponsorship_detail.sponsorship_code = tbl_sponsorship.sponsorship_code
Please reply
|
|
|
|
|
|
| Re: Sequence [message #529263 is a reply to message #529212] |
Sun, 30 October 2011 10:37 |
Hometown
Messages: 35
Registered: October 2010
Location: India
|
Member |
|
|
Littlefoot wrote on Sat, 29 October 2011 13:31Why do you think that master and detail table should have the same number of records? They would in a 1:1 case; otherwise, you can have master records without detail records, or master records with many detail records. So ...
You are right about it, Let me inform you all that the message "could not reserve record. Keep trying isn't coming anymore. I couldn't figure out as to how it disappeared. I am working on it and keep you informed if a manage to discover the cause for it.
Secondly, I have tab canvas comprising of two tab pages page one is Data and page 2 is LOOKUP data page in based on tbl_sponsorship and tbl_sponsorship_detail. Lookup page is based on vew_sponsorship_detail.
There is When mouse double click written at block level on block vew_sponsorship_detail, following is the code for it
Declare
var varchar2(1000);
Begin
go_item('vew_sponsorship_detail.sponsorship_code');
var:='sponsorship_code='||:system.cursor_value;
if :system.cursor_value is not null then
set_block_property('tbl_sponsorship',default_where,var);
go_block('tbl_sponsorship');
execute_query(no_validate);
set_item_property('button_bar.padd',enabled,property_true);
set_item_property('button_bar.pedit',enabled,property_true);
set_item_property('button_bar.pdelete',enabled,property_true);
end if;
END;
Every time when I got to Lookup page and double click it to move to Data page it ask me " Do want to save the changes you made". How do I get rid off this message.
I have tried using clear_message, still it come up.
Please advice.
|
|
|
|
| Re: Sequence [message #529280 is a reply to message #529263] |
Sun, 30 October 2011 13:30 |
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I would assume that the tbl_sponsorship block has changes that forms believe need to be applied to the DB.
When you do execute-query this forces it to ask the question.
Check the block status of that block before doing the execute query.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
