OraFAQ Forum: Forms » Master Detail database block populating

Home » Developer & Programmer » Forms » Master Detail database block populating (Oracle forms 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Master Detail database block populating [message #678216]

Mon, 18 November 2019 06:02

ANASKABIR
Messages: 15
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 2105 times)

Report message to a moderator

Re: Master Detail database block populating [message #678219 is a reply to message #678216]

Mon, 18 November 2019 08:13

cookiemonster
Messages: 13963
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

ANASKABIR
Messages: 15
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

ANASKABIR
Messages: 15
Registered: November 2019

Junior Member

AND I APPRICATE YOUR HELP AND KINDNESS Smile

Report message to a moderator

Re: Master Detail database block populating [message #678237 is a reply to message #678236]

Tue, 19 November 2019 05:24

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Master Detail database block populating [message #678240 is a reply to message #678237]

Tue, 19 November 2019 05:43

cookiemonster
Messages: 13963
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.

Report message to a moderator