[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
10/11/2009 at 05:51PM PDT, ID: 24803395
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.2

Form not inserting records properly. Oracle Error

Asked by geeta_m9 in Oracle 3rd Party Tools

Tags: Oracle Forms

I have a form which has three parts, a key block and two data blocks. The first data block is connected to a table called majors. The second data block is connected to a table called course. The relationship between majors and courses is one to many. Currently how it works is that  I enter a set of values in the key block and when I page down, it retrieves a single record from the majors table which matches the criteria I entered in the keyblock section. When I page down again to the courses data block, it retrieves a set of records (10 to a block) from the courses table and displays it in the bottom section of the form. It does this through the WHERE clause in the properties section of each of these blocks. Currently the form can insert a brand new record or records and edit existing records without any problem at both the majors and courses data blocks. When I enter a year code in the key block, what happens is that it fetches the maximum year less than or equal to the entered year. For instance, if I enter 2009 year in the key block and the record in the majors block only has a maximum year of 2008 with all the other criteria entered being equal (or matching), then it will display the particular 2008 majors record.

The problem I am facing is this:

I have a maintenance button in the majors data block. When I click on the maintenance button, what I would like it to do is to make a copy of the current record in the majors block and duplicate it. Then change the year in the majors record to the same year as the one entered in the key block.  At the same time, it will also execute a user defined trigger, COPY-FORWARD, to query the records from  the courses table. Then, when I page down to the courses block, it will display the matching courses records.  It seems to work the first time, but after that starts to do weird things, such as the following error (see attached). Inside the COPY-FORWARD  trigger are two statements "Insert Record" and "Duplicate Record". In the ON-INSERT trigger, is the code to insert a new record in the courses table and query the records from the courses table  so that when I page down to the courses data block they will show up. I also have a queries in the WHERE clauses of the properties section of the majors and courses data blocks (see code section below). Maybe the COPY-FORWARD and ON-INSERT triggers are clashing with the WHERE clause of the data block properties or perhaps  I am not using the correct triggers for my purpose. I am not well experienced with forms and would appreciate someone's help!
One of the experts had suggested that I shouldn't be using the ON-INSERT trigger for a related problem, so maybe I am not using the correct triggers. 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:

Inside COPY-FORWARD trigger
 
create_record;
duplicate_record;
:majors_acyr_code := :key_block.acyr_code;
 
Inside ON-INSERT trigger
 
:majors_code := :key_block.sbgi_code;
:majors_degt_trns := :key_block.degt_trns;
:majors_degc_inst := :key_block.degc_inst;
:majors_majr_code := :key_block.majr_code;
:majors_conc_code := :key_block.conc_code;
 
insert_record;
 
insert into courses(
	COURSES_CODE,		
	COURSES_DEGT_TRNS,		
	COURSES_DEGC_INST,		
	COURSES_MAJR_CODE,		
	COURSES_CONC_CODE,		
	COURSES_SEQNO,			
	COURSES_SUBJ_CODE,		
	COURSES_CRSE_NUMB,		
	COURSES_CREDITS,		
	COURSES_ACYR_CODE,		
	COURSES_CSTA_CODE,
	COURSES_COUNTER,
	COURSES_ACTIVITY_DATE		
)
select COURSES_CODE,		
                                                 COURSES_DEGT_TRNS,		
			 COURSES_DEGC_INST,		
			 COURSES_MAJR_CODE,		
			 COURSES_CONC_CODE,		
			 COURSES_SEQNO,			
			 COURSES_SUBJ_CODE,		
			 COURSES_CRSE_NUMB,		
			 COURSES_CREDITS,		
			 :MAJORS_ACYR_CODE,		
			 COURSES_CSTA_CODE,
			 COURSES_COUNTER,
			 SYSDATE
from COURSES
where courses_acyr_code = (
	select max(courses_acyr_code)
	from courses
	where courses_acyr_code < :majors_acyr_code
	and courses_code = :majors_code
                and courses_degt_trns = :majors_degt_trns
                and courses_degc_inst = :majors_degc_inst
	and courses_majr_code = :majors_majr_code
	and courses_conc_code = :majors_conc_code
)
and courses_code = :majors_code
and courses_degt_trns = :majors_degt_trns
and courses_degc_inst = :majors_degc_inst
and courses_majr_code = :majors_majr_code
and courses_conc_code = :majors_conc_code;
WHERE courses_code = :majors_code
  AND courses_degt_trns = :majors_degt_trns
  AND courses_degc_inst = :majors_degc_inst
  AND courses_majr_code = :majors_majr_code
  AND courses_conc_code = :majors_conc_code
  AND courses_acyr_code = :majors_acyr_code
ORDER BY courses_seqno, courses_subj_code, courses_crse_numb
 
The WHERE clause in the properites data block of the majors data block is as follows:
 
WHERE majors_code = :key_block.code 
AND majors_degt_trns = :key_block.degt_trns 
AND majors_degc_inst = :key_block.degc_inst 
AND majors_majr_code = :key_block.majr_code 
AND ((majors_conc_code = :key_block.conc_code) OR (:key_block.conc_code IS NULL))
AND majors_acyr_code = (SELECT MAX (B.majors_acyr_code)
FROM majors B
WHERE B.majors_acyr_code <= :key_block.acyr_code
AND B.majors_code = :key_block._code
AND B.majors_majr_code = :key_block.majr_code
AND B.majors_degt_trns = :key_block.degt_trns
AND B.majors_degc_inst = :key_block.degc_inst
AND ((B.majors_conc_code = :key_block.conc_code) OR (:key_block.conc_code IS NULL)))
 
The WHERE clause in the courses properties data block  is as follows:
 
WHERE courses_code = :majors_code
  AND courses_degt_trns = :majors_degt_trns
  AND courses_degc_inst = :majors_degc_inst
  AND courses_majr_code = :majors_majr_code
  AND courses_conc_code = :majors_conc_code
  AND courses_acyr_code = :majors_acyr_code
ORDER BY courses_seqno, courses_subj_code, courses_crse_numb
Attachments:
 
Forms-Error.JPG (79 KB) (File Type Details) 
Form Error
Form Error
 
[+][-]10/11/09 10:45 PM, ID: 25548788

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10/12/09 08:24 AM, ID: 25552030

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10/12/09 10:28 PM, ID: 25557136

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10/30/09 09:58 AM, ID: 25704852

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Oracle 3rd Party Tools
Tags: Oracle Forms
Sign Up Now!
Solution Provided By: geeta_m9
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20091111-EE-VQP-91 - Hierarchy / EE_QW_3_20080625