Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Problem with a PL/SQL process... (APEX 3.2)
Problem with a PL/SQL process... [message #539573] Tue, 17 January 2012 03:56 Go to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hello Smile

I'm having a problem with the following PL/SQL process:
delete legal_person_spoken_language
where muid = :P19_MUID;
FOR i in 1..APEX_APPLICATION.G_F02.count
LOOP
if APEX_APPLICATION.G_F02(i) is not null then
   insert into legal_person_spoken_language(muid, language_code)
   values(:P19_muid, APEX_APPLICATION.G_F02(i));
end if;
END LOOP;

I am picking this app up half way through its production and I have been using version 4.0 of apex (not that that really has anything to do with it) and I don't really know PL/SQL very well. But this code isn't working properly and I can't figure out why...I've checked all the table/column/item names and they're all correct.
I assume the 'APEX_APPLICATION.G_F02(i)' refers to a table with checkboxes on the page. The thing is...the process adds a whole load of extra entries into the database when I run the process:
/forum/fa/9724/0/
you can see the 'en' and 'fr' at the end...they're the right ones...the rest I have no idea what they are...
The really strange thing is I have 4 tables on the page all with the almost exact same code within this process and they all seem to do different things...o_O The first and third work as they should, the second (this one) does as described above and the fourth one doesn't work at all...it doesn't insert anything into the database...
Anyone have any ideas? I'm stuck...
thanks in advance for any/all help Smile

Incase you decided you need all 4 bits of code they are:
delete legal_person_expertise_area
where muid =  :P19_MUID;
FOR i in 1..APEX_APPLICATION.G_F01.count
LOOP
if APEX_APPLICATION.G_F01(i) is not null then
   insert into legal_person_expertise_area(muid, expertise_area_id)
   values(:P19_muid, APEX_APPLICATION.G_F01(i));
end if;
END LOOP;

delete legal_person_spoken_language
where muid =  :P19_MUID;
FOR i in 1..APEX_APPLICATION.G_F02.count
LOOP
if APEX_APPLICATION.G_F02(i) is not null then
   insert into legal_person_spoken_language(muid, language_code)
   values(:P19_muid, APEX_APPLICATION.G_F02(i));
end if;
END LOOP;


delete legal_person_role
where muid = :P19_MUID;
FOR i in 1..APEX_APPLICATION.G_F03.count
LOOP
if APEX_APPLICATION.G_F03(i) is not null then
   insert into legal_person_role(muid, legal_role_code)
   values(:P19_muid, APEX_APPLICATION.G_F03(i));
end if;
END LOOP;


delete LEGAL_PERSON_PRACTICE_GROUP
where MUID = :P19_MUID;
FOR i in 1..APEX_APPLICATION.G_F04.count
LOOP
if APEX_APPLICATION.G_F04(i) is not null then
   insert into LEGAL_PERSON_PRACTICE_GROUP(MUID, PRACTICE_GROUP_ID)
   values(:P19_MUID, APEX_APPLICATION.G_F04(i));
end if;
END LOOP;
  • Attachment: Untitled.png
    (Size: 18.34KB, Downloaded 3434 times)
Re: Problem with a PL/SQL process... [message #539729 is a reply to message #539573] Wed, 18 January 2012 05:03 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Can you please give me the query for each of these reports? It looks like the checkbox number 2 might be used in two of them. Most likely here in both query two and four, leading to g_f02 to contain items both from report 2 and report 4, and g_f04 not to contain anything
Re: Problem with a PL/SQL process... [message #539762 is a reply to message #539729] Wed, 18 January 2012 06:58 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
I can:
select 
ea.expertise_area_id,
ea.expertise_area_name,
APEX_ITEM.CHECKBOX(1,ea.expertise_area_id,DECODE(lpea.expertise_area_id,null,'','CHECKED') ) sel
from LEGAL_PERSON_EXPERTISE_AREA lpea, expertise_area ea
where MUID (+)= :P8_MUID
  and lpea.expertise_area_id (+)= ea.expertise_area_id
order by muid, ea.expertise_area_name

select 
ea.language_code,
ea.language_name name,
APEX_ITEM.CHECKBOX(2,ea.language_code,DECODE(lpea.language_code,null,'','CHECKED') ) sel
from LEGAL_PERSON_spoken_language lpea, spoken_language ea
where MUID (+)= :P8_MUID
  and lpea.language_code (+)= ea.language_code
order by muid, language_name

select 
ea.legal_role_code,
ea.legal_role_name name,
APEX_ITEM.CHECKBOX(3,ea.legal_role_code,DECODE(lpea.legal_role_code,null,'','CHECKED') ) sel
from LEGAL_PERSON_role lpea, legal_role ea
where MUID (+)= :P8_MUID
  and lpea.legal_role_code (+)= ea.legal_role_code

select 
ea.practice_group_id,
ea.practice_group_name name,
APEX_ITEM.CHECKBOX(4,lpea.practice_group_id,DECODE(lpea.practice_group_id,null,'','CHECKED') ) sel
from LEGAL_PERSON_practice_group lpea, practice_group ea
where MUID (+)= :P8_MUID
  and lpea.practice_group_id (+)= ea.practice_group_id

That did seem to be the problem with the 'spoken languages' (second one). I changed all the 2s in the report and process to 7s, and that fixed it (a guy on the oracle forums suggested it)...but I don't know what it was...something must have contained checkboxes with the '2' but I can't find anything on my page o_O

unfortunately this wasn't the problem with the 'practice group' (fourth one). I tried changing all them to something else but it still didn't enter anything...:/

thanks

EDIT: oh I just realised these are the reports from page 8 and the processes are from page 19...but I copied them over so they are exactly the same...don't take that into account

[Updated on: Wed, 18 January 2012 07:00]

Report message to a moderator

Re: Problem with a PL/SQL process... [message #539780 is a reply to message #539762] Wed, 18 January 2012 09:22 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
What happens if you add just the value of lpea.practice_group_id to the select clause of your query? Does it have a value? I notice that you're outer joining with lpea, so it might show a row in your query results even if practice_group doesn't have a matching value in lpea. It's lpea.practice_group_id your setting as the return value of your checkbox, so if there's no value it won't insert anything.

[Updated on: Wed, 18 January 2012 09:22]

Report message to a moderator

Re: Problem with a PL/SQL process... [message #539784 is a reply to message #539780] Wed, 18 January 2012 09:44 Go to previous message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
ahhhhh you're right! that 'lpea' in the "CHECKBOX(4,lpea.practice..." should be 'ea' Well spotted!
and there I was saying I had checked all the table/column names *sigh*

thanks its working now Smile
Previous Topic: Calling report from a interactive report page
Next Topic: Can I do this with SQL?
Goto Forum:
  


Current Time: Tue Mar 19 01:48:16 CDT 2024