Home » SQL & PL/SQL » SQL & PL/SQL » Collection return Null (10g 10.1.2.0.4)
Collection return Null [message #334183] Tue, 15 July 2008 13:07 Go to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Hello,

we start using object collection not long ago and here is something i don't know why it's not working.

create or replace type test_mfa_ot as object
(
  id number(10)
);

CREATE OR REPLACE TYPE liste_test_mfa_ot AS TABLE of test_mfa_ot;


declare
  v_liste_mfa liste_test_mfa_ot := new liste_test_mfa_ot();

  cursor cur_c1 (p_liste in liste_test_mfa_ot) 
  is
  select id
  from table(p_liste);

  v_number number;
begin
  v_liste_mfa.extend(1);
  v_liste_mfa(1).id := 123;

  dbms_output.put_line('Test 1:'||v_liste_mfa(1).id);

  open cur_c1 (v_liste_mfa);
  fetch cur_c1 into v_number;
  close cur_c1;

  dbms_output.put_line('Test 2:'||v_number);
end;


In this code i was expecting getting the same value in the second dbms_output than in the first one. But instead it seem to return null the second time why?

Thank for helping

Re: Collection return Null [message #334187 is a reply to message #334183] Tue, 15 July 2008 13:51 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
not sure, i think what you mean is this?


declare
  v_liste_mfa liste_test_mfa_ot := liste_test_mfa_ot();
  v_o test_mfa_ot;
  cursor cur_c1 (p_liste in liste_test_mfa_ot)
  is
  select id
  from table(p_liste);
  v_number number;
begin
  v_o := test_mfa_ot(25);
  v_liste_mfa.extend(1);
  v_liste_mfa(1) := v_o;
  dbms_output.put_line('Test 1:'||v_liste_mfa(1).id);
  open cur_c1 (v_liste_mfa);  
  fetch cur_c1 into v_number;
  close cur_c1; 
  dbms_output.put_line('Test 2:'||v_number); 
end;
/

Re: Collection return Null [message #334188 is a reply to message #334183] Tue, 15 July 2008 14:02 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Ahhh! ok it make sense.

I should not assigne my list directly like this "v_liste_mfa(1).id := 123;"
but instead assign it with the base type of the object like this "v_liste_mfa(1) := new test_mfa_ot(123);"

So this is solving my problem.

Thank a lot

Regards

Re: Collection return Null [message #334191 is a reply to message #334188] Tue, 15 July 2008 14:10 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
but i'm still wondering why didnt oracle complained on this:

v_liste_mfa.extend(1);
v_liste_mfa(1).id := 123;

were as you're assigning a value to the first element of the array which is an object that hasn't been instantiated yet =( if someone could explain that =)
Previous Topic: Difficult counting question. (counting by day when date entry for that day is not present)
Next Topic: plsql routines expose as native web services in 11g
Goto Forum:
  


Current Time: Tue Feb 11 02:17:44 CST 2025