Solved

NESTED TABLES IN ORACLE 10G

Posted on 2012-03-14
9
472 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.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

707 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

16 Experts available now in Live!

Get 1:1 Help Now