| FRM-41003 [message #516933] |
Thu, 21 July 2011 07:59  |
 |
msdtjw
Messages: 25
Registered: March 2011
|
Junior Member |
|
|
Hello
I have a form that is using a PL/SQL cursor to retrieve data into a tabular form. For the most part the data retrieval works. But i get the form error "FRM-41003 this function cannot be performed here". The cursor is in the (when-new-block-instance) trigger. I am not sure if this is the appropriate trigger to use.
I am including the code below.
the unassigned_flag is a check_box on the form used to designate a different selection criteria.
Thanks for any help
DECLARE
cursor maj_adv is
select a.spriden_id,
a.spriden_last_name||', '||a.spriden_first_name,
a.sgbstdn_degc_code_1,
a.stvmajr_desc,
b.stvmajr_desc,
b.spriden_id,
(case when b.spriden_last_name is not null then
(rtrim(b.spriden_last_name)||',
||rtrim(b.spriden_first_name))end)
from sgbstdn a, spriden a, stvmajr a, stvmajr b, sgradvr, spriden b
where a.sgbstdn_dept_code = :key_block.dept_code
and a.sgbstdn_term_code_eff =
(select max(b.sgbstdn_term_code_eff)
from sgbstdn b
where sgbstdn_pidm = a.sgbstdn_pidm
and b.sgbstdn_term_code_eff <= :key_block.term)
and a.sgbstdn_levl_code = 'UG'
and a.sgbstdn_majr_code_1 = a.stvmajr_code
and a.sgbstdn_majr_code_conc_1 = b.stvmajr_code(+)
and a.spriden_pidm = a.sgbstdn_pidm
and a.spriden_change_ind is null
and sgradvr_pidm(+) = a.sgbstdn_pidm
and sgradvr_term_code_eff (+) = :key_block.term
and sgradvr_advr_code(+) = 'MAJR'
and b.spriden_pidm(+) = sgradvr_advr_pidm
and b.spriden_change_ind is null;
cursor maj_adv_un is
select a.spriden_id,
a.spriden_last_name||', '||a.spriden_first_name,
a.sgbstdn_degc_code_1,
a.stvmajr_desc,
b.stvmajr_desc,
null,
null
from sgbstdn a, spriden a, stvmajr a, stvmajr b
where a.sgbstdn_dept_code = :key_block.dept_code
and a.sgbstdn_term_code_eff =
(select max(b.sgbstdn_term_code_eff)
from sgbstdn b
where b.sgbstdn_pidm = a.sgbstdn_pidm
and b.sgbstdn_term_code_eff <= :key_block.term)
and a.sgbstdn_levl_code = 'UG'
and a.sgbstdn_majr_code_1 = a.stvmajr_code
and a.sgbstdn_majr_code_conc_1 = b.stvmajr_code(+)
and a.spriden_pidm = a.sgbstdn_pidm
and a.spriden_change_ind is null
and not exists(select 'Y'
from sgradvr
where sgradvr_pidm = a.sgbstdn_pidm
and sgradvr_term_code_eff = :key_block.term
and sgradvr_advr_code = 'MAJR');
BEGIN
if :unassigned_flag = 'Y' then
open maj_adv_un;
loop
fetch maj_adv_un
into :student_id,
:student_name,
:degc_code,
:maj_desc,
:conc_desc,
:advisor_id,
:advisor_name;
exit when maj_adv_un%notfound;
next_record;
end loop;
close maj_adv_un;
first_record;
else
open maj_adv;
loop
fetch maj_adv
into :student_id,
:student_name,
:degc_code,
:maj_desc,
:conc_desc,
:advisor_id,
:advisor_name;
exit when maj_adv%notfound;
next_record;
end loop;
close maj_adv;
first_record;
end if;
END;
|
|
|
|
|
|
|
|
| Re: FRM-41003 [message #516951 is a reply to message #516946] |
Thu, 21 July 2011 08:31  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Not the code you posted above you didn't. Toad won't recognize :key_block.term (it would be happy with :term).
Oracle will reject a from clause like this:
from sgbstdn a, spriden a, stvmajr a, stvmajr b, sgradvr, spriden b
No matter what tool you run it from.
|
|
|
|
|
|
|
|
|
|
| Re: FRM-41003 [message #517090 is a reply to message #517087] |
Fri, 22 July 2011 07:29 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Hmmm... apparently in certain cases you can get that to work. So long as you don't try to reference a column name that exists in more than one table that shares an alias.
However, it's so obviously wrong I don't understand why you haven't just changed it. You use aliases to help indentify which columns belong to which tables, that's the whole point, there is no other. You can't do that with your query.
None of this tells what causes the orginal error. I can see nothing in the code you've posted that would cause it. I suspect it's coming from elsewhere. Run the form in debug mode or put messages in the code to work out which line is causing the error.
|
|
|
|
|
|
| Re: FRM-41003 [message #517119 is a reply to message #517090] |
Fri, 22 July 2011 13:28 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Fri, 22 July 2011 13:29I can see nothing in the code you've posted that would cause it. I suspect it's coming from elsewhere. Run the form in debug mode or put messages in the code to work out which line is causing the error.
|
|
|
|
| Re: FRM-41003 [message #517123 is a reply to message #517119] |
Fri, 22 July 2011 14:25 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is most probably wrong, but ... won't cost much to check.
If I understood what you said, the form sometimes works OK, sometimes not.
Just for testing purposes, remove the CASE from the first cursor declaration (return a string, such as 'testing'). If it turns out that CASE is the culprit (i.e. when a cursor that doesn't contain CASE is used forms works OK), rewrite it so that it uses DECODE instead of CASE.
Why? Because the fact that this code works OK in TOAD (or SQL*Plus or some other client) doesn't mean much, because Forms' PL/SQL engine is always a few steps "behind" database's PL/SQL engine, so - some features that work OK in SQL*Plus just won't work in Forms.
Furthermore, as you said that you modified the code, perhaps you could repost it. This time make sure that it is properly formatted (indentation and stuff) and enclosed into [code] tags. See the first Cookiemonster's reply for more information.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
