Thanks. It worked.

DECLARE
TYPE num_tab_type is TABLE OF NUMBER NOT NULL
INDEX BY BINARY_INTEGER;

num_tab num_tab_type;

BEGIN
num_tab(1) := 20;
num_tab(2) := NULL;
END;
/
I am trying to assign null to an index by table which is declared as NOT NULL. I get the following error instead of the numeric or value error exception.

num_tab(2) := NULL;
*
ERROR at line 9:
ORA-06550: line 9, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored

Can you please let me know what is wrong in the above pl/sql block?

DECLARE
TYPE num_tab_type is TABLE OF NUMBER NOT NULL
^
|
---------------------------------------------+

That mean that every element must be NOT NULL....

Lars

Tom,

My comment was to the orginal "a reader" - simply by pointing out the definition in the block gave the error - not low level Oracle type handling.

Simply removing the "NOT NULL" in the type would execute the block...

Lars

If the NOT NULL constraint is removed, the block works without any errors. I know that I should not assign NULL to an index-by table which is declared as NOT NULL.

I was just checking to see if I would get "numeric or value error" exception when NULL is assigned to an index-by table declared as NOT NULL.

Tom:

Not able to make this work. could you tell me what i am doing wrong ?

Basically, i need to store the rowids in the sql collection and then use this to update the actual table using TABLE(CAST..). I tried this script before actual implementation.

ERROR at line 7:
ORA-06550: line 7, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 7, column 10:
PL/SQL: Statement ignored
ORA-06550: line 9, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 10:
PL/SQL: Statement ignored
ORA-06550: line 11, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 10:



SQL> @testrowid
SQL> drop type rowidtab
  2  /

Type dropped.

SQL> create or replace type rowidtyp as object(row_id varchar2(18))
  2  /

Type created.

SQL> create or replace type rowidtab as table of rowidtyp
  2  /

Type created.

SQL> 
SQL> set serveroutput on
SQL> declare
  2     l_rowid_array rowidtab := rowidtab() ;
  3  
  4  BEGIN
  5  
  6           l_rowid_array.extend;
  7           l_rowid_array(1) := 'AAEH7kAApAAD/3CAAD' ;
  8           l_rowid_array.extend;
  9           l_rowid_array(2) := 'AAEH7kAApAABRDsAAV' ;
 10           l_rowid_array.extend;
 11           l_rowid_array(3) := 'AAEH7kAApAABRDsAAV' ;
 12           dbms_output.put_line('l_rowid_array.cnt :'||l_rowid_array.count) ;
 13  
 14  END;
 15  /
         l_rowid_array(1) := 'AAEH7kAApAAD/3CAAD' ;
                             *
ERROR at line 7:
ORA-06550: line 7, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 7, column 10:
PL/SQL: Statement ignored
ORA-06550: line 9, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 10:
PL/SQL: Statement ignored
ORA-06550: line 11, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 10:
PL/SQL: Statement ignored

ops$tkyte%ORA10GR2> declare
  2     l_rowid_array rowidtab := rowidtab() ;
  3  BEGIN
  4
  5           l_rowid_array.extend;
  6           l_rowid_array(1) := rowidtyp('AAEH7kAApAAD/3CAAD') ;
  7           l_rowid_array.extend;
  8           l_rowid_array(2) := rowidtyp('AAEH7kAApAABRDsAAV') ;
  9           l_rowid_array.extend;
 10           l_rowid_array(3) := rowidtyp('AAEH7kAApAABRDsAAV') ;
 11           dbms_output.put_line('l_rowid_array.cnt :'||l_rowid_array.count) ;
 12
 13  END;
 14  /
l_rowid_array.cnt :3

PL/SQL procedure successfully completed.

i am trying to assign values to a object which is a part of table type

But it is giving me a error
PLS-00382: expression is of wrong type

CREATE OR REPLACE TYPE DateType IS OBJECT ( PayPeriod DATE );
/

CREATE OR REPLACE TYPE TableList IS TABLE OF DateType;
/


DECLARE
List1 TableList;
d date;
BEGIN
List1(1) :='01-jan-2010';
List1(2) :='01-feb-2010';
FOR c1 IN (SELECT payperiod FROM TABLE(CAST(List1 AS TableList))) LOOP
DBMS_OUTPUT.PUT_LINE(c1.payperiod);
END LOOP;
END;

i dont want to assign the values in one go in the table type
i want to do it one by one

  1  declare
  2  list1 sys.odciDateList := sys.odciDateList();
  3  begin
  4    list1.extend;
  5    list1(1) := to_date( '01-jan-2010' );
  6* end;
ops$tkyte%ORA10GR2> /

PL/SQL procedure successfully complete

i am using pl/sql table type not varray
That's why i cannot use extend

So provide a way to assign values in pl/sql table type

CREATE OR REPLACE TYPE DateType IS OBJECT ( PayPeriod DATE );
/

CREATE OR REPLACE TYPE TableList IS TABLE OF DateType;
/
DECLARE
  List1 TableList;

create or replace procedure value_diff
as temps2 number(5);
temps number(5);
k number(5);

begin
for i in (select to_number(substr(f_file_name,18,5),9999)
from iuc_dat_msc001_rafp where f_file_name like '%RCPA1%'
ORDER BY to_date(substr(f_file_name,5,8),'yyyymmdd'))
loop
dbms_output.put_line(i);
temps2:=i;
temps:=i+1;
k:=temps2-temps;
if
k>1 then
dbms_output.put_line('the difference in rows ='|| k );
elsif
k<1 then
k:=abs(k);
dbms_output.put_line('the difference in rows ='|| k );
end if;

end loop;
end;
Compilation errors for PROCEDURE SCOTT.VALUE_DIFF

Error: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
Line: 11


Error: PL/SQL: Statement ignored
Line: 11


Error: PLS-00382: expression is of wrong type
Line: 12
Text: temps2:=i;

Error: PL/SQL: Statement ignored
Line: 12


Error: PLS-00306: wrong number or types of arguments in call to '+'
Line: 13


Error: PL/SQL: Statement ignored
Line: 13

pls help i just cant figure out what is the problem and
how to rectify it.

ops$tkyte%ORA11GR2> create or replace procedure value_diff
  2   as temps2 number(5);
  3      temps number(5);
  4      k number(5);
  5  
  6  begin
  7  for i in (select to_number(substr(f_file_name,18,5),9999) number_part
  8              from iuc_dat_msc001_rafp
  9             where f_file_name like '%RCPA1%'
 10             ORDER BY to_date(substr(f_file_name,5,8),'yyyymmdd'))
 11    loop
 12        dbms_output.put_line(i.number_part);
 13      temps2:=i.number_part;
 14      temps:=i.number_part+1;
 15      k:=temps2-temps;
 16      if  k!=0
 17      then
 18          dbms_output.put_line('the difference in rows ='|| abs(k) );
 19      end if;
 20  
 21    end loop;
 22  end;
 23  /

Procedure created.

Thanks a lot.Atleast now it makes sense.

Hii tom,i would like to create a table in which if there is a jump of more than two values in the sequence it should dislay the date and the total sum of values jumped for that particular date.
fno f_date
10799 12/31/2011
10944 12/31/2011
10945 12/31/2011
10946 12/31/2011
10947 12/31/2011
10948 12/31/2011
10949 12/31/2011
10950 12/31/2011
10951 12/31/2011
10952 12/31/2011
10748 12/31/2011
10809 12/31/2011
10810 12/31/2011
11014 1/1/2012
11015 1/1/2012
11016 1/1/2012
11017 1/1/2012
11018 1/1/2012
10971 1/1/2012
10972 1/1/2012
10978 1/1/2012
10979 1/1/2012
11003 1/1/2012
11004 1/1/2012
11005 1/1/2012
11006 1/1/2012
for ex.the output should look like this

fl_misd fl_date
410 12/31/2011
67 1/1/2012