Home » Developer & Programmer » Forms » Master Detail database block populating (Oracle forms 11g)
icon5.gif  Master Detail database block populating [message #678216] Mon, 18 November 2019 06:02 Go to next message
ANASKABIR
Messages: 7
Registered: November 2019
Junior Member
I have created Three level master detail database block and their replica Three level master detail database block. I want to populate data to replica database block from original database block.

In the Button I tried This .. But did not work for all master detail data ..

BEGIN
GO_BLOCK ('DIVISION_R');
:DIVISION_R.DIVISION_NAME := :DIVISION.DIVISION_NAME;

GO_BLOCK ('DISTRICT_R');

LOOP
SELECT DISTRICT_NAME
INTO :DISTRICT_R.DISTRICT_NAME
FROM DISTRICT;
NEXT_RECORD;
STANDARD.COMMIT;
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
END LOOP;


GO_BLOCK ('UPAZILA_R');

LOOP

SELECT UPAZILA_NAME
INTO :UPAZILA_R.UPAZILA_NAME
FROM UPAZILA;

NEXT_RECORD;
STANDARD.COMMIT;
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
END LOOP;

END;





Example Picture: in the attachment
  • Attachment: Form.JPG
    (Size: 38.29KB, Downloaded 20 times)
Re: Master Detail database block populating [message #678219 is a reply to message #678216] Mon, 18 November 2019 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 13745
Registered: September 2008
Location: Rainy Manchester
Senior Member
You haven't said what is doing, which is always a good idea.

I can take a guess:
You're going to each target block, looping over it and exiting when :SYSTEM.LAST_RECORD = 'TRUE'.
:SYSTEM.LAST_RECORD = 'TRUE' will be true straight away since you're starting with an empty block.

You need to loop over the source block and only exit the loop when :SYSTEM.LAST_RECORD = 'TRUE' is true in that block.

Something like this (pseudo code)
go_block(source);
first_record;
LOOP

  go_block(target);
  IF system.current_record != 1 OR :<item in target block> IS NOT NULL THEN
    --If we're not at the start of the process then we need to create a new record in target
    next_record; 
  END IF:
  :<target item> := :<source item>;
  go_block(source);
  EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
  --go to next source record.
  next_record;

END LOOP;

[Updated on: Mon, 18 November 2019 08:13]

Report message to a moderator

Re: Master Detail database block populating [message #678235 is a reply to message #678219] Tue, 19 November 2019 04:50 Go to previous messageGo to next message
ANASKABIR
Messages: 7
Registered: November 2019
Junior Member
Thank You But These block are Master detail.. let me show you the Script



-------------------------------------------DIVISION---------------------------------------------------------------

DROP TABLE DIVISION CASCADE CONSTRAINTS;

CREATE TABLE DIVISION
( DIVISION_NO NUMBER(30),
DIVISION_NAME VARCHAR2(100)
);

ALTER TABLE DIVISION ADD CONSTRAINT DIVISION_PK PRIMARY KEY (DIVISION_NO);

-------------------------------------------DIVISION---------------------------------------------------------------

---------------------------------------DISTRICT---------------------------------------------------------------------

DROP TABLE DISTRICT CASCADE CONSTRAINTS;

CREATE TABLE DISTRICT
(
DISTRICT_NO NUMBER,
DISTRICT_NAME VARCHAR2(200),
DIVISION_NO NUMBER(30)
);

ALTER TABLE DISTRICT ADD CONSTRAINT DISTRICT_PK PRIMARY KEY (DISTRICT_NO);

ALTER TABLE DISTRICT ADD CONSTRAINT DISTRICT_FK FOREIGN KEY (DIVISION_NO) REFERENCES DIVISION (DIVISION_NO);

---------------------------------------DISTRICT---------------------------------------------------------------------

-------------------------------------UPAZILA-----------------------------------------------------------

DROP TABLE UPAZILA CASCADE CONSTRAINTS;

CREATE TABLE UPAZILA
(
UPAZILA_NO NUMBER,
UPAZILA_NAME VARCHAR2(200),
DISTRICT_NO NUMBER(30)
);


ALTER TABLE UPAZILA ADD CONSTRAINT UPAZILA_PK PRIMARY KEY (UPAZILA_NO);

ALTER TABLE UPAZILA ADD CONSTRAINT UPAZILA_FK FOREIGN KEY (DISTRICT_NO) REFERENCES DISTRICT(DISTRICT_NO);

-------------------------------------UPAZILA-----------------------------------------------------------


****************************************************************************************************
REPLICA TABLE
****************************************************************************************************



-------------------------------------------DIVISION_R---------------------------------------------------------------

DROP TABLE DIVISION_R CASCADE CONSTRAINTS;

CREATE TABLE DIVISION_R
( DIVISION_NO_R NUMBER(30),
DIVISION_NAME VARCHAR2(100)
);

ALTER TABLE DIVISION_R ADD CONSTRAINT DIVISION_PK_R PRIMARY KEY (DIVISION_NO_R);

-------------------------------------------DIVISION_R---------------------------------------------------------------

---------------------------------------DISTRICT_R---------------------------------------------------------------------

DROP TABLE DISTRICT_R CASCADE CONSTRAINTS;

CREATE TABLE DISTRICT_R
(
DISTRICT_NO_R NUMBER,
DISTRICT_NAME VARCHAR2(200),
DIVISION_NO_R NUMBER(30)
);

ALTER TABLE DISTRICT_R ADD CONSTRAINT DISTRICT_PK_R PRIMARY KEY (DISTRICT_NO_R);

ALTER TABLE DISTRICT_R ADD CONSTRAINT DISTRICT_FK_R FOREIGN KEY (DIVISION_NO_R) REFERENCES DIVISION_R (DIVISION_NO_R);

---------------------------------------DISTRICT_R---------------------------------------------------------------------

-------------------------------------UPAZILA_R-----------------------------------------------------------

DROP TABLE UPAZILA_R CASCADE CONSTRAINTS;

CREATE TABLE UPAZILA_R
(
UPAZILA_NO_R NUMBER,
UPAZILA_NAME VARCHAR2(200),
DISTRICT_NO_R NUMBER(30)
);


ALTER TABLE UPAZILA_R ADD CONSTRAINT UPAZILA_PK_R PRIMARY KEY (UPAZILA_NO_R);

ALTER TABLE UPAZILA_R ADD CONSTRAINT UPAZILA_FK_R FOREIGN KEY (DISTRICT_NO_R) REFERENCES DISTRICT_R(DISTRICT_NO_R);

-------------------------------------UPAZILA_R-----------------------------------------------------------


---------------------------------------------------------- DATA----------------------------------------------------------

INSERT INTO DIVISION (DIVISION_NO, DIVISION_NAME)
VALUES (1, 'Dhaka');

INSERT INTO DIVISION (DIVISION_NO, DIVISION_NAME)
VALUES (2, 'Khulna');

INSERT INTO DIVISION (DIVISION_NO, DIVISION_NAME)
VALUES (3, 'Rajshahi');

COMMIT;

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (1, 'Faridpur', 1);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (2, 'Gazipur', 1);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (3, 'Gopalgonj', 1);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (4, 'Jamalpur', 1);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (5, 'Bagerghat', 2);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (6, 'Chuadanga', 2);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (7, 'Jessore', 2);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (8, 'jhenaidah', 2);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (9, 'Bogra', 3);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (10, 'Joypurhat', 3);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (11, 'Natore', 3);

INSERT INTO DISTRICT (DISTRICT_NO, DISTRICT_NAME, DIVISION_NO)
VALUES (12, 'Pabna', 3);

COMMIT;


INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (1, 'Bhanga', 1);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (2, 'Boalmari', 1);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (3, 'Sadarpur', 1);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (4, 'Saltha', 1);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (5, 'Chitalmari', 5);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (6, 'Fakirhat', 5);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (7, 'Kochua', 5);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (8, 'Mongla', 5);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (9, 'Adamdighi', 9);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (10, 'Dhunot', 9);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (11, 'Sherpur', 9);

INSERT INTO UPAZILA (UPAZILA_NO, UPAZILA_NAME, DISTRICT_NO)
VALUES (12, 'Sonatola', 9);

COMMIT;

SELECT * FROM DISTRICT;
SELECT * FROM UPAZILA;
SELECT * FROM DIVISION;


NEED TO SAVE THE EXACT SAME DATA IN DISTRICT_R, UPAZILA_R,DIVISION_R USING ONE BUTTON IN FORMS.

[Updated on: Tue, 19 November 2019 04:52]

Report message to a moderator

Re: Master Detail database block populating [message #678236 is a reply to message #678235] Tue, 19 November 2019 04:54 Go to previous messageGo to next message
ANASKABIR
Messages: 7
Registered: November 2019
Junior Member
AND I APPRICATE YOUR HELP AND KINDNESS Smile
Re: Master Detail database block populating [message #678237 is a reply to message #678236] Tue, 19 November 2019 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66713
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

And don't post your message in UPPER case which means, in all forums, you are shouting.

Re: Master Detail database block populating [message #678240 is a reply to message #678237] Tue, 19 November 2019 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 13745
Registered: September 2008
Location: Rainy Manchester
Senior Member
What makes you think I haven't realised the blocks are master/detail?

I've showed you the process for one block. You really ought to be able to extend it to the other three.

And as Michel said - stop shouting.
Re: Master Detail database block populating [message #678241 is a reply to message #678240] Tue, 19 November 2019 06:00 Go to previous messageGo to next message
ANASKABIR
Messages: 7
Registered: November 2019
Junior Member
ok. Got It. Thank You again Smile
Re: Master Detail database block populating [message #678243 is a reply to message #678237] Tue, 19 November 2019 06:02 Go to previous messageGo to next message
ANASKABIR
Messages: 7
Registered: November 2019
Junior Member
ok.. I'm new here .. sorry ..
Re: Master Detail database block populating [message #678249 is a reply to message #678240] Wed, 20 November 2019 00:01 Go to previous messageGo to next message
ANASKABIR
Messages: 7
Registered: November 2019
Junior Member
thanks a lot … the code works fine.. and sorry for the previous misunderstanding … i was so much in depression ..
Re: Master Detail database block populating [message #678250 is a reply to message #678249] Wed, 20 November 2019 04:01 Go to previous message
cookiemonster
Messages: 13745
Registered: September 2008
Location: Rainy Manchester
Senior Member
No worries - glad you got it working.
Previous Topic: install forms 6i and developper 2000 under windows 10 64 bits
Next Topic: Move Item on a Canvas
Goto Forum:
  


Current Time: Thu Dec 05 22:31:08 CST 2019