[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

NESTED TABLES IN ORACLE 10G

I need some assistance with pulling the values from a nested table and updating a base table with these values. I am using the Web ToolKit for PL SQL in order to get this done. My sample code is as the follows:

On my Display form that shows the data from my base table is as the following:
Defined my variables:
P_Name   VAR2_TABTYPE;
P_Address VAR2_TABTYPE;
P_Score    NUM_TABTYPE;

v_name  varchar2,
v_address varchar2(60),
v_testscore number,
int_count number :=0;

cursor c_pull_datavalues is
select name, address, testscore
from APP_SCORECARD.T_SCOREDATA;

Begin
open c_pull_datavalues;
Loop
fetch c_pull_datavalues into
v_name,
v_address,
v_testscore

EXIT WHEN c_pull_datavalues%NOTFOUND;
int_count := int_count + 1;

P_Name(int_count) := v_name;
P_Address(int_count) := v_address;
P_Score(int_count) := v_testscore;

END LOOP;
CLOSE c_pull_datavalues;

HTP.FORMOPEN('APP_SCORECARD.PKG_TESTCENTER.P_UPDATESCORES','POST');
HTP.TABLEROWOPEN;
HTP.TABLEHEADER('NAME', cattributes=>'width="5% style="font-size: 85%"');
HTP.TABLEHEADER('ADDRESS', cattributes=>'width="5% style="font-size: 85%"');
HTP.TABLEHEADER('SCORE', cattributes=>'width="5% style="font-size: 85%"');
HTP.TABLEROWCLOSE;

FOR v_count IN 1..int_count LOOP
HTP.TABLEROWOPEN;
HTP.TABLEDATA(Htf.FormText(cname=>'IN_Name', csize=>'9', cvalue=>P_Name(v_count)));
HTP.TABLEDATA(Htf.FormText(cname=>'IN_Address', csize=>'60', cvalue=>P_Address(v_count)));
HTP.TABLEDATA(Htf.FormText(cname=>'IN_SCORE', csize=>'9', cvalue=>P_Score(v_count)));
HTP.TABLEDATA(Htf.formsubmit(cname=>'IN_UPDATE', cvalue=>'UPDATE');
HTP.TABLEROWCLOSE;

END LOOP;

I want to be able to pass the values from these nested tables to another procedure in order to update my base table. Can you please help??

Thank You,
SeaGhost
0
Seaghost
Asked:
Seaghost
  • 5
  • 2
  • 2
1 Solution
 
flow01Commented:
Is the problem in passing the values to an other procedure or in inserting ?


create or replace procedure p2 (P1_Name   VAR2_TABTYPE,
P1_Address VAR2_TABTYPE,
P1_Score    NUM_TABTYPE)
is
begin
  for i1 in 1 .. p1_name.count loop
    insert into tab1 (name, address,score) values (p1_name(i1), p1_address(i1),p1_score(i1);
  end;
  -- or try bulk insert
 -- forall i1 in 1 .. p1_name.count
  --    insert into tab1 (name, address,score) values (p1_name(i1), --p1_address(i1),p1_score(i1);

end


calling procedure p2:

p2(p_name,p_adress,p_score);
0
 
SeaghostAuthor Commented:
Oh great, let me give this a try in the morning and will ping you back to let you know my outcome. You have greatly helped me on something that is last piece of my critical application.

Thank You,
0
 
SeaghostAuthor Commented:
I am trying to just update the fields in my base table with the values that are captured in my nested table variables. However; when I bring the variables into my update procedure, nothing gets updated in the base table field. My code is as the following:

Procedure P_UPDATE_DISPLAY_FORM(IN_UPDATE IN VARCHAR2, IN_NAME IN P_NAME%TYPE, IN_ADDRESS IN P_ADDRESS%TYPE, IN_SCORE IN P_SCORE%TYPE)

BEGIN

FOR i IN 1..IN_NAME..COUNT LOOP
UPDATE APP_SCORECARD.T_SCOREDATA
SET SCORE=IN_SCORE(i)
WHERE NAME = IN_NAME(i)
END LOOP;

---Re-Display the form with updated information
APP_SCORECARD.PKG_TEST.P_STUDENTSCORES
END P_UPDATE_DISPLAY_FORM;

Furthermore; I am unable to call my original form and receive a 404 error, which is quite generic. Is there something that I am calling that is not correct?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
flow01Commented:
is the a way to debug the procedure ?
(for example writing data to a debug_table of an os-table using for example utl_file or maybe temporary to your form using htp

DECLARE
 PROCEDURE WRITE_DEBUG_INFORMATION(P1 VARCHAR2) IS
 BEGIN
      ...  
 END;
BEGIN

write_debug_information('count:' || in_name.count);
FOR i IN 1.. IN_NAME.COUNT LOOP
   write_debug_information('name:' || in_name(i1));
UPDATE APP_SCORECARD.T_SCOREDATA
SET SCORE=IN_SCORE(i)
WHERE NAME = IN_NAME(i);
write_debug_information('rows updated:' || sql%rowcount);
END LOOP;

END;
0
 
slightwv (䄆 Netminder) Commented:
Could the problem be a simple commit issue?  I don't see a commit after you have updated the table.
0
 
SeaghostAuthor Commented:
I have just added the commit clause at the end of my loop and it appears that I do see the procedure being called in the URL portion of the web browser, however; it will still show a 404 Error without displaying the values when I have temporarily include on the form.
0
 
slightwv (䄆 Netminder) Commented:
The 404 error is a different question.   I'm not a pl/sql server pages person.
0
 
SeaghostAuthor Commented:
I have found the issue. I had  a total of 10 different fields on the actual form and I just included the three fields that I was testing. According to your guidance; it has really resolved the issue and I very much appreciated.
0
 
SeaghostAuthor Commented:
This Expert is fantastic. He has really helped me through a jam and he was very prompt on his responses.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now