Solved

NESTED TABLES IN ORACLE 10G

Posted on 2012-03-14
9
475 Views
Last Modified: 2012-03-15
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
Comment
Question by:Seaghost
  • 5
  • 2
  • 2
9 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 37722863
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
 

Author Comment

by:Seaghost
ID: 37723111
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
 

Author Comment

by:Seaghost
ID: 37724896
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
 
LVL 20

Accepted Solution

by:
flow01 earned 405 total points
ID: 37725800
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37725829
Could the problem be a simple commit issue?  I don't see a commit after you have updated the table.
0
 

Author Comment

by:Seaghost
ID: 37725935
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37726264
The 404 error is a different question.   I'm not a pl/sql server pages person.
0
 

Author Comment

by:Seaghost
ID: 37726416
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
 

Author Closing Comment

by:Seaghost
ID: 37726420
This Expert is fantastic. He has really helped me through a jam and he was very prompt on his responses.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now