|
|
|
|
| Re: updating a record in a cursor issue [message #545931 is a reply to message #545928] |
Sat, 03 March 2012 10:37   |
 |
baliberde
Messages: 201
Registered: January 2012
Location: outer space
|
Senior Member |
|
|
My records doubled themselves.
And I changed my code into this:
BEGIN
FOR studgrade_rec IN studgrade_cur
LOOP
INSERT INTO tblStudgrades(studgrade_rec.student_id, studgrade_rec.grade, studgrade_rec.subject_code)
VALUES (:ADDGRADE.studid, :ADDGRADE.subjcode, :ADDGRADE.grades1);
NEXT_RECORD;
END LOOP;
:studprofile.GRADE := :ADDGRADE.GRADES1;
:studprofile.SUBJECTS := :ADDGRADE.SUBJCODE;
--standard.commit;
COMMIT;
HIDE_VIEW('CV_STACKEDaddgrade');
SET_VIEW_PROPERTY('CV_STACKEDaddgrade', VISIBLE, PROPERTY_FALSE);
GO_BLOCK('studprofile');
END;
'coz my previous code only updates one record at a time, since I have to save all records(student_id, grade and subject_code) into my table, i changed to INSERT. I can only insert one record, I don't have any idea how to make loop here.
|
|
|
|
| Re: updating a record in a cursor issue [message #545934 is a reply to message #545931] |
Sat, 03 March 2012 10:45 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
baliberde wrote on Sat, 03 March 2012 16:37My records doubled themselves.
Of course they are. You've got code that adds records to the block every time you enter the block. What did you expect was going to happen?
As for your update. If you tried using forms default functionality then you wouldn't have this issue. Base the block on a table. Use execute query to populate it, make changes and click on the standard save button. Forms will do all necessary updates for you.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: updating a record in a cursor issue [message #545959 is a reply to message #545951] |
Sat, 03 March 2012 13:38 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Yes you can do an insert inside a cursor loop.
What precise code you need I have no idea, because once again your explanation of what you are trying to do is less than clear.
You need to tell us what data you want inserted into what table under what conditions.
The next_record in your current for loop is pointless. Insert inserts directly into the DB table. It does not add values to the form.
|
|
|
|
|
|
|
|
|
|
| Re: updating a record in a cursor issue [message #546022 is a reply to message #546013] |
Sun, 04 March 2012 11:30 |
|
baliberde
Messages: 201
Registered: January 2012
Location: outer space
|
Senior Member |
|
|
i tried to loop to no avail. i searched for new records in sqlplus, but there were no records found.
this is the code i used:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('studprofile');
FOR studgrade_rec IN studgrade_cur
LOOP
INSERT INTO tblStudgrades(student_id, subject_code,grade)
VALUES (:STUDPROFILE.STUD_ID, :STUDPROFILE.SUBJECTS, :STUDPROFILE.grade);
END LOOP;
standard.commit;
END;
[Updated on: Sun, 04 March 2012 11:51] Report message to a moderator |
|
|
|
| Re: updating a record in a cursor issue [message #546026 is a reply to message #546022] |
Sun, 04 March 2012 12:12 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Are you sure that cursor's SELECT returned at least one record?
Did you not learn by now that including the MESSAGE built-in lets you know what's going on? If you are using Forms 10g, you can even run the form in debug mode (which is really helpful while ... well, debugging your programs).
[Updated on: Sun, 04 March 2012 12:13] Report message to a moderator |
|
|
|
|
|
| Re: updating a record in a cursor issue [message #546033 is a reply to message #546022] |
Sun, 04 March 2012 15:05 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
baliberde wrote on Sun, 04 March 2012 17:30
this is the code i used:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('studprofile');
FOR studgrade_rec IN studgrade_cur
LOOP
INSERT INTO tblStudgrades(student_id, subject_code,grade)
VALUES (:STUDPROFILE.STUD_ID, :STUDPROFILE.SUBJECTS, :STUDPROFILE.grade);
END LOOP;
standard.commit;
END;
In the code above, do :STUDPROFILE.STUD_ID, :STUDPROFILE.SUBJECTS and :STUDPROFILE.grade change value each time you go round the loop?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: updating a record in a cursor issue [message #546088 is a reply to message #546084] |
Mon, 05 March 2012 04:46 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
baliberde wrote on Mon, 05 March 2012 10:37So, how will I do that? That's just my problem, I can insert all records but they aren't saving though I have commit there.
You do understand that there is a difference between records in tables in the database and records in datablocks in a form?
Your cursor is selecting records from tables in the DB.
As far as I can tell you want to insert data that is in different records in the datablock in the form.
You need to loop over the records in the datablock, not the records in the database.
To loop over records in a datablock you need to use first_record/nect_record.
baliberde wrote on Mon, 05 March 2012 10:40@LittleFoot - but in my case I have 3 tables, can I base 3 tables on my block?
You're only inserting into one table though, so the block should only need to be based on that table.
|
|
|
|
| Re: updating a record in a cursor issue [message #546093 is a reply to message #546088] |
Mon, 05 March 2012 05:37 |
|
baliberde
Messages: 201
Registered: January 2012
Location: outer space
|
Senior Member |
|
|
I tried to add first_record, but it's still doing the same, or is it wrong?
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('addgrade');
FOR studgrade_rec IN studgrade_cur
LOOP
[b]FIRST_RECORD;[/b]
INSERT INTO tblStudgrades(student_id, subject_code,grade)
VALUES (:ADDGRADE.STUDID, :ADDGRADE.subjcode, :ADDGRADE.grades1);
END LOOP;
standard.commit;
HIDE_VIEW('CV_STACKEDaddgrade');
SET_VIEW_PROPERTY('CV_STACKEDaddgrade', VISIBLE, PROPERTY_FALSE);
GO_BLOCK('studprofile');
END;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: updating a record in a cursor issue [message #546109 is a reply to message #546106] |
Mon, 05 March 2012 06:36 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
When you run the form there is a help menu. One of the options on the help menu is display error. If you get an error when running the form like FRM-40508 then you can click on display error and it'll show you the underlying oracle error that caused it.
And you can't remove a loop you don't have. You are not looping over the addgrade block with that code. That was my point.
You are looping over some records in the DB and using the exact same record in the addgrade block each time.
|
|
|
|
| Re: updating a record in a cursor issue [message #546111 is a reply to message #546109] |
Mon, 05 March 2012 06:41 |
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
|
Senior Member |
|
|
FRM-40508: ORACLE error: unable to INSERT record.
A fatal error occurred while trying to insert a record. The table associated with the current block of the form might not exist, your username might not have authority to perform the specified action on the table, or some other reason might have caused the fatal error.
Action: Contact your DBA.
Level: >25
Type: Error
cookiemonster is right
|
|
|
|
| Re: updating a record in a cursor issue [message #546113 is a reply to message #546109] |
Mon, 05 March 2012 06:45 |
|
baliberde
Messages: 201
Registered: January 2012
Location: outer space
|
Senior Member |
|
|
Ok i will click help menu.
This is the code I used in when-new-block-instance trigger in addgrade block:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code, g.grade
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
-- AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('addgrade');
FOR studgrade_rec IN studgrade_cur
LOOP
:addgrade.studid := studgrade_rec.student_id;
:addgrade.subjcode := studgrade_rec.subject_code;
NEXT_RECORD;
END LOOP;
END;
I have loop, but when I modified it, it doesn't display on my items
[Updated on: Mon, 05 March 2012 06:47] Report message to a moderator |
|
|
|
| Re: updating a record in a cursor issue [message #546115 is a reply to message #546113] |
Mon, 05 March 2012 06:48 |
|
baliberde
Messages: 201
Registered: January 2012
Location: outer space
|
Senior Member |
|
|
DATABASE ERROR: INSERT INTO tblStudgrades(student_id,LNAME,MNAME,FNAME,COURSE,YEARSTATUS,TYPE,Subject_Code,tblStudgrades,grade) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
ERROR:
ORA-00904: "TBLSTUDGRADES": invalid identifier
|
|
|
|
| Re: updating a record in a cursor issue [message #546116 is a reply to message #546115] |
Mon, 05 March 2012 06:52 |
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
|
Senior Member |
|
|
ORA-00904: string: invalid identifier
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.
|
|
|
|
| Re: updating a record in a cursor issue [message #546117 is a reply to message #546113] |
Mon, 05 March 2012 06:55 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What doesn't display on what items?
[rant]
We do not have your form.
We do not have your tables.
We do not have your data.
We do not have your requirements.
We don't know what assumptions you are making.
We only have a very vague idea of what you are trying to do.
We can't possibly know how to fix your code unless we know exactly what you are trying to do.
We can't know exactly what you are trying to do unless you explain it clearly.
You don't get charged by the word to post here.
So try posting more words of explanation.
And what the heck happened to the insert?
[/rant]
[Updated on: Mon, 05 March 2012 06:57] Report message to a moderator |
|
|
|
| Re: updating a record in a cursor issue [message #546120 is a reply to message #546115] |
Mon, 05 March 2012 07:00 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
baliberde wrote on Mon, 05 March 2012 12:48DATABASE ERROR: INSERT INTO tblStudgrades(student_id,LNAME,MNAME,FNAME,COURSE,YEARSTATUS,TYPE,Subject_Code,tblStudgrades,grade) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
ERROR:
ORA-00904: "TBLSTUDGRADES": invalid identifier
Do you have a table called tblStudgrades?
Does it have a column called tblStudgrades?
|
|
|
|
|
|
| Re: updating a record in a cursor issue [message #546125 is a reply to message #546117] |
Mon, 05 March 2012 07:09 |
|
baliberde
Messages: 201
Registered: January 2012
Location: outer space
|
Senior Member |
|
|
Quote:What doesn't display on what items?
my records from studprofile don't display on my items in addgrade block specifically(:addgrade.studid, :addgrade.subjcode, :addgrade.grades1)
this is the code that I used in when-new-block-instance addgrade block:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code, g.grade
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
-- AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('addgrade');
FOR studgrade_rec IN studgrade_cur
LOOP
:addgrade.studid := studgrade_rec.student_id;
:addgrade.subjcode := studgrade_rec.subject_code;
NEXT_RECORD;
END LOOP;
END;
and on my :addgrade.save button when-button-pressed trigger:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('addgrade');
FOR studgrade_rec IN studgrade_cur
LOOP
INSERT INTO tblStudgrades(student_id, subject_code,grade)
VALUES (:ADDGRADE.STUDID, :ADDGRADE.subjcode, :ADDGRADE.grades1);
NEXT_RECORD;
END LOOP;
standard.commit;
HIDE_VIEW('CV_STACKEDaddgrade');
SET_VIEW_PROPERTY('CV_STACKEDaddgrade', VISIBLE, PROPERTY_FALSE);
GO_BLOCK('studprofile');
END;
[Updated on: Mon, 05 March 2012 07:10] Report message to a moderator |
|
|
|
|
|
| Re: updating a record in a cursor issue [message #546129 is a reply to message #546126] |
Mon, 05 March 2012 07:17 |
|
baliberde
Messages: 201
Registered: January 2012
Location: outer space
|
Senior Member |
|
|
This is my requirement:
I have 2 blocks, studprofile and addgrade. In studprofile block, I have a cursor that joins 3 tables, namely(tblEnrolled, tblSubjectSection, tblStudgrades) to display the record of the newly enrolled student. In this studprofile block, I'm going now to input his, the student's, grades. This block has a button named ADD GRADES. When I'm going to click that button: in when-button-pressed trigger
BEGIN
show_view('cv_stackedaddgrade');
:addgrade.grades1 := :studprofile.grade;
:addgrade.subjcode:= :studprofile.subjects;
:addgrade.studid := :studprofile.stud_id;
go_block('addgrade');
END;
, that would immediately direct me to ADDGRADE BLOCK, to add grades. I have included cursor in when-new-block-instance trigger:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code, g.grade
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
-- AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('addgrade');
FOR studgrade_rec IN studgrade_cur
LOOP
:addgrade.studid := studgrade_rec.student_id;
:addgrade.subjcode := studgrade_rec.subject_code;
NEXT_RECORD;
END LOOP;
END;
... Also, in that block I have a save button, when I'm done adding grades I'd click on that to commit to database.
this is the code for my when-button-pressed trigger in save button:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code
FROM tblEnrolled e, tblSubjectSection s, tblStudgrades g
WHERE e.section = s.section
AND e.student_id = g.student_id
AND e.student_id = :studprofile.stud_id
ORDER BY s.subject_code;
BEGIN
GO_BLOCK('addgrade');
FOR studgrade_rec IN studgrade_cur
LOOP
INSERT INTO tblStudgrades(student_id, subject_code,grade)
VALUES (:ADDGRADE.STUDID, :ADDGRADE.subjcode, :ADDGRADE.grades1);
NEXT_RECORD;
END LOOP;
standard.commit;
HIDE_VIEW('CV_STACKEDaddgrade');
SET_VIEW_PROPERTY('CV_STACKEDaddgrade', VISIBLE, PROPERTY_FALSE);
GO_BLOCK('studprofile');
END;
.
My problem is that it doesn't save my records directly into the database though I have commit statement.
[Updated on: Mon, 05 March 2012 07:19] Report message to a moderator |
|
|
|
| Re: updating a record in a cursor issue [message #546143 is a reply to message #546129] |
Mon, 05 March 2012 08:08 |
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
|
Senior Member |
|
|
First how r working opening and fetching records
GO_BLOCK('B');
clear_block(no_commit);
declare
cursor c1 is select * from a WHERE NO=:A.NO ;
a_record c1%rowtype;
begin
open c1;
loop
fetch c1 into a_record;
exit when c1%notfound;
:b.no:=a_record.no;
:b.name:=a_record.name;
next_record;
---insert into b values(a_record.no,a_record.name);
end loop;
close c1;
end;
first_Record;
--EXECUTE_QUERY;
--go_block('a');
--clear_form;
hope it should work for u
[Updated on: Mon, 05 March 2012 08:09] Report message to a moderator |
|
|
|
|
|
| Re: updating a record in a cursor issue [message #546146 is a reply to message #546122] |
Mon, 05 March 2012 08:17 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
baliberde wrote on Mon, 05 March 2012 13:03Yes I have a table tblStudgrades, that where I'm going to save students grade. I don't have tblstudgrade column. I don't know what happened to it.
Forms default behavior 101:
When you have a datablock with the Database Data Block property set to Yes, oracle will do all appropriate inserts and updates for you.
When you enter data in a blank record forms will assume it's a new record and try to insert it.
When you query a record, with execute_query (not populate the block manually with a cursor, that's a new record as far as forms is concerned) and then change the values, forms will assume it's a changed record and will do the appropriate update.
The insert/update are done when you issue a commit.
Forms works out which columns to use in the insert/update by checking all the items in the block and seeing what their Database Item property is set to. All items with that property set to yes are included in the insert/update.
So you've got a database datablock with an item called tblstudgrade which has it's Database Item property set to Yes.
Forms is doing an automatic insert and assumes that item must correspond to a column in the database table because that's what the properties tell it.
If you're going to use default functionality you need to stop writing manual insert statements.
If you're going to do it manually you need to set all database properties to No at item and block level.
Either way you have properties set incorrectly.
I strongly recommend you use the default functionality as your knowledge of how forms works is nowhere near strongly enough to allow you to do it the hard way.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
