| Cursors and Set_Block_Property [message #392733] |
Thu, 19 March 2009 01:55  |
anugraha
Messages: 15
Registered: February 2009
|
Junior Member |
|
|
Hi All !
I have two forms form1 and form2..In my form1 i have 2 blocks blk1 and blk2..And a block blk in form2..
Now the blk1 of form1 and blk of form2 are based on same table.
I would retrieve the datas entered in blk1 of form1 in the blk of form2 using the execute_query.Since i want to retrieve based on conditions i used set_block_property before execute_query..
Now my issue is, i have to retrieve the value employeed in blk of form2 from blk2 of form1 and have to retrieve values of other fields correspondingly using execute_query in form2..
I could retrieve the employeeids alone from blk2 of form1 in form2 using cursor concept(since there would be more than one employeeid)..
Now how could i merge these two, that is after retrieving the employeeid the corresponding values of various other fields must be retrieved..
If i give simply execute_query after my cursor coding, all records are retrieved instead of the records of employee ids that are retrieved..
Here is the coding which i tried
form:form2
trigger:when_new_form_instance
declare
c1rec varchar2(10);
cursor c1 is
select employeeid
from emp_reporting
where reporting_officer = :global.employeeid;
begin
go_block('hodal');
first_record;
for c1rec in c1 loop
:hodal.employeeid := c1rec.employeeid;
Set_Block_Property('HODAL',DEFAULT_WHERE,'employeeid=c1rec and (status like ''Pending%'') or (status = ''Cancelled'')');
execute_query;
next_record;
end loop;
first_record;
/* Here emp_reporting is the blk2 of form1 and hodal is the blk of form2*/
At runtime the above coding gives me the error
frm-40505 unable to run the query
Pls help to resolve this
Thanks and Regards
anu
[Updated on: Thu, 19 March 2009 02:15] Report message to a moderator |
|
|
|
| Re: Cursors and Set_Block_Property [message #392820 is a reply to message #392733] |
Thu, 19 March 2009 06:42  |
anugraha
Messages: 15
Registered: February 2009
|
Junior Member |
|
|
Hi !
I tried with the following coding,
declare
crec varchar2(10);
c1rec varchar2(10);
cursor c1 is
select employeeid
from emp_reporting
where reporting_officer = :global.employeeid;
cursor c2 is
select employeename,leavetype,fromdate,todate,number_of_days,reason,outstationpermissionrequired,contactaddressduringleavee, contactnumberduringleave,prefix_leave,prefixing_leave,suffix_leave,suffixing_leave,joiningdate,joiningsession from empaply
where employeeid='c1rec.employeeid' and status like 'Pending%' or status = 'Cancelled';
begin
go_block('hodal');
first_record;
for c1rec in c1 loop
:hodal.employeeid := c1rec.employeeid;
next_record;
for crec in c2 loop
:employeename:=crec.employeename;
:leavetype:=crec.leavetype;
:fromdate:=crec.todate;
:number_of_days:=crec.number_of_days;
:reason:=crec.reason;
:outstationpermissionrequired:=crec.outstationpermissionrequired;
:contactaddressduringleavee:=crec.contactaddressduringleavee;
:contactnumberduringleave:=crec.contactnumberduringleave;
:prefix_leave:=crec.prefix_leave;
:prefixing_leave:=crec.prefixing_leave;
:suffix_leave:=crec.suffix_leave; :suffixing_leave:=crec.suffixing_leave; :joiningdate:=crec.joiningdate; :joiningsession:=crec.joiningsession next_record;
end loop;
end loop;
end;
first_record;
I get no error while compiling and runtime,..
But the issue its reading cursor c1 alone and cursor c2 is not running , please correct my coding if am wrong..
Thanks and Regards
anu
|
|
|
|
| Re: Cursors and Set_Block_Property [message #392848 is a reply to message #392733] |
Thu, 19 March 2009 08:32 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well the 2nd cursor isn't doing anything because of this:
where employeeid='c1rec.employeeid'
I assume you're trying to restrict the query by the employee_id retrieved in the first query.
Thing is anything in quotes is a character string.
So rather than getting rows where the employeeid is the employeeid from the first cursor it's trying to get rows where the employeeid equals the text 'c1rec.employeeid'.
I imagine no rows match this.
Look up cursor parameters in the oracle documentation to fix this.
Some other issues.
Remove these:
crec varchar2(10);
c1rec varchar2(10);
Oracle isn't actually using them. It couldn't anyway - you can't stick values from multiple columns in a single varchar.
FOR loop variables are created on the fly when oracle encounters the FOR.....LOOP command and are local to the loop.
Don't try to declare them.
I assume those two cursors can be combined into a single select.
You should always reference datablock items as
rather than
It avoids any possible confusion.
More generally
The simplest way to get a set of ID's between two forms is to write them to a table along with an identifier for the set. Then pass the identifier to the set to the 2nd form and query the table in the 2nd form.
Alternatively you can write them to an array in a database package and then access that array in the 2nd form.
Also bear in mind that execute_query is a block operation not a record operation.
You can't go_block, execute_query, next_record, execute_query.
The 2nd query will overwrite the results of the first.
Finally - can you please indent your code properly.
Your 2nd post is so wide I initially missed some of the code at the end and thought you were missing a next_record command.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
