OraFAQ Forum: Forms » forms problem !

Home » Developer & Programmer » Forms » forms problem !

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

forms problem ! [message #211131]

Tue, 26 December 2006 11:54

oracle06
Messages: 36
Registered: December 2005

Member

Hello All,
-There are two blocks with master-detail
relationship,displaying multiple records at a time.
every record associated with one checkbox(it is in data
block,so there is only one checkbox named chk1).
-on selecting checkbox, value should be insert into another
table that is dept_emp,and save records.
-after inserting in ot another table records should be
permanently deleted from master table,so that if user will
execute it again than checked records should not come again.
at the time of insertion,a batchno should be stored in
batch_no column like:- suppose i am selecting 5 records at a
time the batch no for all 5 records should be 1.

Things to check:-

I have tried it but still i am facing some problem

-i am generating batchno through sequence,i am decalring global variable and in this global variable i am using sequence.nextval value on new form instance but problem is that if user will exit the form without selecting any checkbox,the value of sequence will increase and next time there will be gaps in sequence number.

-if i will check one checkbox after that i uncheck it,still vaue is inserting in table.

-how to delete checked records.

create table syntax:-
----------------------------------------- ----------------------
create table dept_emp(BATCH_NO NUMBER(7,2)NOT NULL,
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
EMPNO NUMBER(4)NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPARTMENT_ID NUMBER(2))
create sequence syntax:-
----------------------------------------------------------------
create sequence seq_batchno start with 1 minvalue 1 maxvalue 1E27 increment by 1 cache 20;

I am attaching module also.
Please guide.

Attachment: MODULE1.fmb
(Size: 108.00KB, Downloaded 1168 times)

[Updated on: Tue, 26 December 2006 11:55]

Report message to a moderator

Re: forms problem ! [message #211151 is a reply to message #211131]

Tue, 26 December 2006 18:36

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

Post your code on when-checkbox-changed

Report message to a moderator

Re: forms problem ! [message #211243 is a reply to message #211151]

Wed, 27 December 2006 09:40

oracle06
Messages: 36
Registered: December 2005

Member

hi Wency,
ok this is the code on when-check-box-changedL:-

BEGIN
IF CHECKBOX_CHECKED('dept.CHK1')THEN
INSERT INTO DEPT_EMP VALUES
(:global.batchno,:DEPT.DEPTNO,
:DEPT.DNAME,
:DEPT.LOC,
:EMP.EMPNO,
:EMP.ENAME,
:EMP.JOB,
:EMP.MGR,
:EMP.HIREDATE,
:EMP.SAL,
:EMP.COMM,
:EMP.DEPARTMENT_ID);

end if;
END;
*I have attached whole module with previous message.

Report message to a moderator

Re: forms problem ! [message #211296 is a reply to message #211131]

Wed, 27 December 2006 19:45

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

1. batchno
-do not use global variable, use the following instead so that it will only generate next sequence number if you need to insert.

SELECT NVL(MAX(batchno), 0) + 1 
into v_batchno
from dual;

2. how to delete checked records.
DELETE_RECORD;

3. if i will check one checkbox after that i uncheck it,still vaue is inserting in table.
-I suggest to modify your code, do it on key-commit trigger (use other applicable trigger if restriction on code arise).
I assume that you only created 1 check-box and it is a base item so that it will display the same number as the records retrieved. If so, then read the records on that block from top to bottom. Insert those checked items then delete it.

eg:

  go_block('DETAILS');

    first_record;
      loop

       If :clmrdocs.chk_submit = 'Y' Then--test if check box was checked
        --INSERT INTO DEPT_EMP --put your code here
	--  CLEAR_RECORD; 
          DELETE_RECORD;
       End If;

       If :system.last_record = 'TRUE' then
	Exit;
       Else
    Next_record; 
       End If;
     end loop;

Report message to a moderator

Re: forms problem ! [message #211326 is a reply to message #211296]

Thu, 28 December 2006 00:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

wency

batchno
-do not use global variable, use the following instead so that it will only generate next sequence number if you need to insert.

SELECT NVL(MAX(batchno), 0) + 1 
into v_batchno
from dual;

This piece of code is about to return an error (instead of a next sequence number). Also, such an approach (i.e. MAX + 1) would work correctly only in a single-user environment. Anything more than that will sooner or later cause duplicate 'batchno' value (including possible unique/primary key constraint violation).

For sequence numbers, use SEQUENCES:

SELECT sequence_name.nextval
  INTO whatever
  FROM dual;

Report message to a moderator

Re: forms problem ! [message #211331 is a reply to message #211131]

Thu, 28 December 2006 00:21

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

Nice spotting littlefoot!
That must be from DEPT_EMP and not from dual.
Anyway, I aggree with that nextval, on its consistency.

Report message to a moderator

Re: forms problem ! [message #211524 is a reply to message #211331]

Fri, 29 December 2006 02:42

canny
Messages: 10
Registered: December 2006

Junior Member

hi
i understood that you are learning forms and i modified some parts in ur module suiting to ur requirements
check the attachment below

Attachment: MODULE1.fmb
(Size: 108.00KB, Downloaded 1146 times)

Report message to a moderator

Re: forms problem ! [message #211576 is a reply to message #211524]

Fri, 29 December 2006 10:33

oracle06
Messages: 36
Registered: December 2005

Member

hi

PDE-PEP006 enoded program unit has an unknown format

when I am opening module above message is comming.If I am trying to see any trigger code nothing is comming.

Report message to a moderator

Re: forms problem ! [message #211578 is a reply to message #211296]

Fri, 29 December 2006 11:02

oracle06
Messages: 36
Registered: December 2005

Member

Thanks Wency,Littlefoot
I have done both things.now it is working.I have done it without using global variable and code for checkbox check or uncheck I have written on when button pressed.
- One problem came in picture is that once record is inserted,I check it in database.I found records are not inserting in order,it is inserting in between old records.

- for deleting records I am writting:

DELETE from dept where deptno =(select deptno from dept_emp);
but exception raised on when-button-pressed.

actually I have to delete records permanently from both master(dept) and detail(emp)table those inserted in dept_emp.

Report message to a moderator

Re: forms problem ! [message #211587 is a reply to message #211578]

Fri, 29 December 2006 13:34

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Your first "problem" is not a problem. Records are NOT inserted into the database in any particular order. I believe you noticed this behaviour using SELECT * FROM your_table; If you want the records ordered by something, you'll have to use the ORDER BY clause.
DELETE statement raised, I presume, TOO-MANY-ROWS error. It was because of the '=' sign - modify it to 'IN' and it should be OK. The problem is that it will raise an error whenever there's more than one 'dept_emp.deptno' for every 'dept.deptno'.

Report message to a moderator

Re: forms problem ! [message #212674 is a reply to message #211131]

Sun, 07 January 2007 09:51

oracle06
Messages: 36
Registered: December 2005

Member

Thank you Littlefoot.

But now I dont have to delete records from main table I want,when i click on ckeck box and checked records are inserting on when-button-pressed in dept_emp (new)table,so those records are inserted in new table should not come again when I again execute query or run the form,but I dont want to delete records permanently.
There are two data blocks with master detail relationship.I have again made another block with the same master table and I did its relationship with already exist detail block(detail block is in stacked canvas so it is comming by default at all content canvas.)when I execute the form first screen shows the first master deatil data when i execute.there is a button to go next block(status of the form normal now),next block is for multiple records with same tables,(detail block is on stacked canvas),

code when-buton-pressed

go_block('block name');
enter_query;

so now status of the form is enter-query.
but if i am executing here then there is an error message

FRM-40505 : Oracle error:unable to perform query.
FRM-41047 :can not navigate out of urrent block in enter-query
mode.(message at console)

please guide.

Upd-mod: The forum screen builder will wrap code and do paragraph numbering - please use them.

[Updated on: Sun, 07 January 2007 17:38] by Moderator

Report message to a moderator

Previous Topic:	SET CURRENT_SCHEMA='SYSTEM' problem
Next Topic:	Granting Privaleges Help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 19:10:45 CST 2025