Home » SQL & PL/SQL » SQL & PL/SQL » need help on multidimentional array access (10.2.0.3)
need help on multidimentional array access [message #348841] Thu, 18 September 2008 01:44 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
I have a pl/sql table of an object.

Declare
TYPE tbl_usage_statement IS TABLE OF my_usage_dtl%ROWTYPE;
v_tbl_usage_statement tbl_usage_statement
:=bl_usage_statement();

begin
for i in(select * from my_usage_dtl) loop
v_tbl_usage_statement (x).uplddnldid := i.i_uplddnldid;
v_tbl_usage_statement (x).seq_id := i.seq_id;
v_tbl_usage_statement (x).postal_cd := i.zipcode;
end loop;

end;

is there any option so that i can populate collection like

for i in(select * from my_usage_dtl) loop
v_tbl_usage_statement (x).(i) := i.i_uplddnldid;
v_tbl_usage_statement (x).(i) := i.seq_id;
v_tbl_usage_statement (x).(i) := i.zipcode;
end loop;

I am struck here as i have around 400 columns in the collection and need to populate collection not only on the basis of above for loop but there are many other conditions inside the loop as well. Please provide any workaround.

Regards,
Navneet



Re: need help on multidimentional array access [message #348854 is a reply to message #348841] Thu, 18 September 2008 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous posts:
Michel Cadot wrote on Wed, 17 September 2008 09:49
Use SQL*Plus and copy and paste your session.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Re: need help on multidimentional array access [message #348862 is a reply to message #348854] Thu, 18 September 2008 02:54 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Michel, This is the sample code(i have taken only three columns of collection , i my proc there are around 400 columns)


Declare
TYPE tbl_usage_statement IS TABLE OF lea_abp_usage_dtl%ROWTYPE;
v_tbl_usage_statement tbl_usage_statement :=tbl_usage_statement();
x number :=1;
begin
for i in(select * from lea_abp_usage_dtl) loop
v_tbl_usage_statement.EXTEND (1);
v_tbl_usage_statement (x).uplddnldid := i.uplddnldid;
v_tbl_usage_statement (x).seq_id := i.seq_id;
v_tbl_usage_statement (x).postal_cd := i.postal_cd;
x :=x+1;
end loop;

end;


My requirement is ,instead of using column names in collection
can i use index of column ie
instead of v_tbl_usage_statement (x).uplddnldid
can i use v_tbl_usage_statement (x).(1)


I know this is going to give error but is there any workaround so that i can use similar to this ??

Regards,
Navneet




Re: need help on multidimentional array access [message #348863 is a reply to message #348862] Thu, 18 September 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one I am aware of.
But what if someone (wants to) adds a new field in the middle? All your code has to be reviewed.

Regards
Michel
Re: need help on multidimentional array access [message #348866 is a reply to message #348863] Thu, 18 September 2008 03:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's no concept of refering to columns from a cursor by a numeric index in Pl/Sql.

Assuming your posted example is somewhat similar to your real problem, and that you have an object of a table rowtype, you can simply assign a whole row of the table to the object at once:
create table test_0098 (col_1 varchar2(10),col_2 number, col_3 number);

insert into test_0098 values ('A',1,2);
insert into test_0098 values ('B',3,4);

declare
  type ty_test_0098 is table of test_0098%rowtype;
  t_tab    ty_test_0098 := ty_test_0098();
  v_idx    pls_integer :=0;
begin
  for rec in (select * from test_0098) loop
    t_tab.extend(1);
    t_tab(t_tab.count) := rec;
  end loop;
  
  dbms_output.put_line(t_tab(1).col_1);
  dbms_output.put_line(t_tab(2).col_1);
end;
/
Re: need help on multidimentional array access [message #348869 is a reply to message #348866] Thu, 18 September 2008 03:22 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
thanks JRowbottom for your help,
I am sorry but i could not post entire problem. actualy within the loop I have to do some other calculations as well so i m not going to populate entire row from cursor to collection.

for example within the for loop cursor,

if i.uplddnldid ='9999' and i.caseid=10 then
v_tbl_usage_statement (x).uplddnldid :=i.uplddnldid
else
v_tbl_usage_statement (x).uplddnldid :=i.uplddnldid-100;
end if;

One more problem( this is major one) is that,there is some conditions which say,
if zipcode=1000 then
x :=x+1;
v_tbl_usage_statement.extend(1);
v_tbl_usage_statement (x) :=v_tbl_usage_statement (x-1);
v_tbl_usage_statement(x).uplddnldid :=i.uplddnldid*20;
end if;

This all is in the loop itself.Now i think you can understand my scenario and give me some workaround.

Re: need help on multidimentional array access [message #348876 is a reply to message #348869] Thu, 18 September 2008 03:39 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 18 September 2008 09:32
From one of your previous posts:
Michel Cadot wrote on Wed, 17 September 2008 09:49
...
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel




Previous Topic: INSERT INTO CLOB Column
Next Topic: spool
Goto Forum:
  


Current Time: Fri Dec 09 15:51:45 CST 2016

Total time taken to generate the page: 0.16937 seconds